July 12, 2024
Summary: In this tutorial, you will learn how to enable column-level security to control access to individual columns in a table.
Table of Contents
Introduction to column-level security
As the name suggests, at this level of security we want to allow the user to view only a particular column or set of columns, making all other columns private by blocking access to them, so users can not see or use those columns when selecting or sorting. Now let’s see how we can implement this.
How to enable column-level security
This can be achieved by various methods. Let’s explore each of them one by one.
Using a table view
The simplest way to achieve column-level security is to create a view that includes only the columns you want to show to the user, and provide the view name to the user instead of the table name.
Example with table view
I have an employee table with basic employee details and salary-related information. I want to provide information to an admin user, but do not want to show the admin information about employee salary and account numbers.
Let’s create a user and table with some data:
create user admin;
create table employee ( empno int, ename text, address text, salary int, account_number text );
insert into employee values (1, 'john', '2 down str', 20000, 'HDFC-22001' );
insert into employee values (2, 'clark', '132 south avn', 80000, 'HDFC-23029' );
insert into employee values (3, 'soojie', 'Down st 17th', 60000, 'ICICI-19022' );
select * from employee;
empno | ename | address | salary | account_number
-------+--------+---------------+--------+----------------
1 | john | 2 down str | 20000 | HDFC-22001
2 | clark | 132 south avn | 80000 | HDFC-23029
3 | soojie | Down st 17th | 60000 | ICICI-19022
(3 rows)
An admin user with full access to the employee table can currently access salary information, so the first thing we want to do here is to revoke the admin user’s access to the employee table, then create a view with only required columns — empno, ename and address — and provide this view access to the admin user instead.
revoke SELECT on employee from admin;
create view emp_info as select empno, ename, address from employee;
grant SELECT on emp_info TO admin;
\c postgres admin
You are now connected to database "postgres" as user "admin".
select * from employee;
ERROR: permission denied for table employee
select * from emp_info;
empno | ename | address
-------+--------+---------------
1 | john | 2 down str
2 | clark | 132 south avn
3 | soojie | Down st 17th
(3 rows)
select * from emp_info where salary > 200;
ERROR: column "salary" does not exist
LINE 1: select * from emp_info where salary > 200;
As we can see, admin can find employee information via the emp_info view, but cannot access the salary and account_number columns from the table.
Column-level permissions
Another good option for securing a column is to grant access to particular columns only to the intended user. In the above example, we don’t want the admin user to access the salary and account_number columns of the employee table. Instead of creating views, we can instead provide access to all columns except salary and account_number.
Example of column-level permissions
Let’s take a look at how this works using queries. We have already revoked SELECT privileges on the employee table, so admin cannot access employees.
\c postgres admin
You are now connected to database "postgres" as user "admin".
select * from employee;
ERROR: permission denied for table employee
Now let’s give SELECT permission on all columns except salary and account_number:
\c postgres postgres
You are now connected to database "postgres" as user "postgres".
grant select (empno, ename, address) on employee to admin;
\c postgres admin
You are now connected to database "postgres" as user "admin".
select empno, ename, address, salary from employee;
ERROR: permission denied for table employee
select empno, ename, address from employee;
empno | ename | address
-------+--------+---------------
1 | john | 2 down str
2 | clark | 132 south avn
3 | soojie | Down st 17th
(3 rows)
As we see, the admin user has access to the employee table’s columns except for salary and account_number.
An important thing to remember in this case is that the user should not have GRANT access on table. You must revoke SELECT access on the table and provide column access with only columns you want the user to access. Column access to particular columns will not work if users already have SELECT access on the whole table.
Column-level encryption
Another way to secure a column is to encrypt just the column data, so the user can access the column but can not see the actual data. PostgreSQL has a pgcrypto module for this purpose. Let’s explore this option with the help of a basic example.
Example of column-level encryption
Here we want user admin to see the account_number column, but not the exact data from that column; at the same time, we want another user, finance, to be able to access the actual account_number information. To accomplish this, we will insert data in the employee table using pgcrypto functions and a secret key.
\c postgres postgres
You are now connected to database "postgres" as user "postgres".
create user finance;
grant select (empno, ename, address,account_number) on employee to finance;
CREATE EXTENSION pgcrypto;
TRUNCATE TABLE employee;
insert into employee values (1, 'john', '2 down str', 20000, pgp_sym_encrypt('HDFC-22001','emp_sec_key'));
insert into employee values (2, 'clark', '132 south avn', 80000, pgp_sym_encrypt('HDFC-23029', 'emp_sec_key'));
insert into employee values (3, 'soojie', 'Down st 17th', 60000, pgp_sym_encrypt('ICICI-19022','emp_sec_key'));
select * from employee;
empno | ename | address | salary | account_number
-------+--------+---------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | john | 2 down str | 20000 | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302
2 | clark | 132 south avn | 80000 | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf
3 | soojie | Down st 17th | 60000 | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535
(3 rows)
revoke SELECT on employee from admin;
grant select (empno, ename, address,account_number) on employee to admin;
As we can see, selecting data from the employee table’s account_number column is showing encryption. Now if an admin user wants to see data it can view it, but in the encrypted form.
\c postgres admin
You are now connected to database "postgres" as user "admin".
select empno, ename, address,account_number from employee;
empno | ename | address | account_number
-------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | john | 2 down str | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302
2 | clark | 132 south avn | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf
3 | soojie | Down st 17th | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535
(3 rows)
If the table owner wants to share actual data with the finance user, the key can be shared, and finance can view actual data:
\c postgres finance
You are now connected to database "postgres" as user "finance".
select empno, ename, address, account_number from employee;
empno | ename | address | account_number
-------+--------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | john | 2 down str | \xc30d04070302b0ee874432c065456ad23b012bf61c2e4377555de29a749e7b252aa2dd3f41a763417774ad1d02bae45e6b6cbaa0d41eebcad39a8003fcbcf0b67989ced6657c362e41ca4302
2 | clark | 132 south avn | \xc30d040703025976b98d9021d4cd63d23b01f07a3c3baa91254b9fbf55e0206bafb056120be42446f07f658bbab8d25eeba4fbb6c737b77b5bb080c973beba7443c27f4e5a494b1d2e89e7bf
3 | soojie | Down st 17th | \xc30d040703023fec833ec5e407467cd23c019864a798593c184177a6df1c1c49b769b068e043a853579d2097239c65c9c8ffb81141b502f2c6206f569225edde72233b089ca814ac8eebdef535
(3 rows)
select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'emp_sec_key') from employee;
empno | ename | address | pgp_sym_decrypt
-------+--------+---------------+-----------------
1 | john | 2 down str | HDFC-22001
2 | clark | 132 south avn | HDFC-23029
3 | soojie | Down st 17th | ICICI-19022
(3 rows)
When a user who does not have a key tries to see data with a random key, they get an error:
\c postgres admin
You are now connected to database "postgres" as user "admin".
select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'random_key') from employee;
ERROR: Wrong key or corrupt data
The method shown above is highly based on trust. The pgcrypto module has other methods that use private and public keys to do the same work.