Column-level security

#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;