Postgres - Column-level security

• Introduction
• Using a table view
• Column-level permissions
• Column-level encryption
• Tasks
• Pending

Introduction

PostgreSQL is a secure database with extensive security features at various levels.

At the top-most level, database clusters can be made secure from unauthorized users using host-based authentication, different authentication methods (LDAP, PAM), restricting listen address, and many more security methods available in PostgreSQL.

When an authorized user gets database access, further security can be implemented at the object level by allowing or denying access to a particular object. This can be done using various role-based authentication measures and using GRANT and REVOKE commands.

We are going to talk about security at a more granular level, where a column or a row of a table can be secured from a user who has access to that table but whom we don’t want to allow to see a particular column or a particular row.

Table-level security can be implemented in PostgreSQL at two levels.

1. Column-level security
2. Row Level Security

At column 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 cannot see or use those columns when selecting or sorting.

Now let’s see how we can implement this.

Create a new database, for example acme, and connect to it:

create database acme;

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.

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' );

An admin user with full access to the employee table can currently access salary information:

grant select on employee to admin;
set role admin;
select * from employee;

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;

Change to the admin role and try to access the table directly:

set role admin;
select * from emp_info;

ERROR:  permission denied for table employee

Now, if you try to access the table using the view, you will get the expected result:

select * from emp_info;

 empno | ename  |    address
-------+--------+---------------
     1 | john   | 2 down str
     2 | clark  | 132 south avn
     3 | soojie | Down st 17th
(3 rows)

admin can find employee information via the emp_info view, but cannot access the salary and account_number columns from the table.

select * from emp_info where salary > 200;

ERROR:  column "salary" does not exist
LINE 1: select * from emp_info where salary > 200;

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.

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.

Now let’s give SELECT permission on all columns except salary and account_number:

set role postgres;
grant select (empno, ename, address) on employee to admin;

Now admin can access the employee table, but only the empno, ename and address columns.

set role admin;
select empno, ename, address from employee;

As we can see, the admin user has access to the employee table’s columns except for salary and account_number.

select empno, ename, address, salary from employee;

ERROR:  permission denied for table employee

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.

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.

set role postgres;
create user finance;
grant select (empno, ename, address,account_number) on employee to finance;

To achieve this, we will insert data in the employee table using pgcrypto functions and a secret key.

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'));

As we can see, selecting data from the employee table’s account_number column is showing encryption

select * from employee;

 empno | ename  |    address    | salary |                                                                        account_number
-------+--------+---------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
     1 | john   | 2 down str    |  20000 | \xc30d04070302bab00e85c9d2e7756cd23b012c6123e9741b4133a3abf43953851e6d85bbaf251a02fa2e520d216c95e715b85e3d97f1cc8406a34eee3eb903a0424085921a235fdaae069f29
     2 | clark  | 132 south avn |  80000 | \xc30d040703022c8869f26b6b14617fd23b01067792421334c917175c515df40d0580aab72869571d0d5818407a4eccf15d2cf8830255fab0eed3b0ba18bf022f3de646457b09d71ae5c408f9
     3 | soojie | Down st 17th  |  60000 | \xc30d04070302c1703ef686e1548967d23c01ba66201c5aae9b6d67d5ecc40b703bee28b9e4987b86909e39d782ed78f2f4f118b59626ff95a71f4959e718ccf0e76437281cd53f3d847a6ccac1

Now if an admin user wants to see data it can view it, but in the encrypted form

grant select on employee to admin;
set role admin;
select * from employee;

If the table owner wants to share actual data with the finance user, the key can be shared, and finance can view actual data:

set role finance;
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:

set role admin;
select empno, ename, address,pgp_sym_decrypt(account_number::bytea,'password') 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.

Tasks

Pending

• Supabase - Column Level Security