Row level security is coming to PostgreSQL

Before PostgreSQL 9.5 (which is in alpha2 currently) you could grant access to individual columns of a table to users or roles. A little test script to demonstrate this:

(postgres@[local]:5432) [postgres] > select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

create table t ( id int primary key, name varchar(50), salary bigint );
insert into t ( id, name, salary ) values ( 1,'name1',1000), (2,'name2',20000), (3,'name3',3000);
create user u1 UNENCRYPTED PASSWORD 'u1';
create user u2 UNENCRYPTED PASSWORD 'u2';
grant select (id,name) on t to u1;
grant select (name,salary) on t to u2;

User u1 has the right to select columns “id” and “name” and user u2 has the right to select columns “name” and “salary”:

(u1@[local]:5432) [postgres] > select user;
 current_user 
--------------
 u1
(1 row)

(u1@[local]:5432) [postgres] > select id,name from t;
 id | name  
----+-------
  1 | name1
  2 | name2
  3 | name3
(3 rows)

(u1@[local]:5432) [postgres] > select id,name,salary from t;
ERROR:  permission denied for relation t
(u1@[local]:5432) [postgres] > 


(u2@[local]:5432) [postgres] > select user;
 current_user 
--------------
 u2
(1 row)

(u2@[local]:5432) [postgres] > select name,salary from t;
 name  | salary 
-------+--------
 name1 |   1000
 name2 |  20000
 name3 |   3000
(3 rows)

(u2@[local]:5432) [postgres] > select id,name,salary from t;
ERROR:  permission denied for relation t
(u2@[local]:5432) [postgres] > 

As you can see from the above selecting data from columns the users are not authorized to results in a “permission denied” message.

Now lets build the same test case on PostgreSQL 9.5 alpha2:

postgres=# select version();
                                                     version                                                  
    
--------------------------------------------------------------------------------------------------------------
----
 PostgreSQL 9.5alpha2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-
bit
(1 row)


create table t ( id int primary key, name varchar(50), salary bigint );
insert into t ( id, name, salary ) values ( 1,'name1',1000), (2,'name2',20000), (3,'name3',3000);
create user u1 UNENCRYPTED PASSWORD 'u1';
create user u2 UNENCRYPTED PASSWORD 'u2';
grant select (id,name) on t to u1;
grant select (name,salary) on t to u2;

The new feature “row level security” enables us to do the following:

alter table t enable row level security;

As a first step row level security needs to be enabled for the table. For this the “alter table” command was extended which is already reflected in the documentation for PostgreSQL 9.5.

But this can not be all we need to do, right? No conditions, no checks at this point in time. This is where the policies come into the game:

create policy policy1 on t for select to u2 using (salary < 3000);

In words this policy does the following: If the user is “u2” and this user is selecting from “t” then only let the user “u2” see rows that have a salary less than 3000. Does it work?

postgres=> select user;

 current_user 
--------------
 u2
(1 row)

postgres=> select name,salary from t;
 name  | salary 
-------+--------
 name1 |   1000
(1 row)

Exactly what the policy was defined for. Do the column permissions still apply?

postgres=> select id from t;
ERROR:  permission denied for relation t
postgres=> 

Yes, so starting with PostgreSQL 9.5 a mixture of column and row level security is possible. Btw: Describing tables that have policies applied was enhanced, too:

postgres=> d t
              Table "public.t"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(50) | 
 salary | bigint                | 
Indexes:
    "t_pkey" PRIMARY KEY, btree (id)
Policies:
    POLICY "policy1" FOR SELECT
      TO u2
      USING ((salary < 3000))

In addition there is now a new system information function which tells if the current user has row level security enabled for a table:

postgres=> select user;
 current_user 
--------------
 u2
(1 row)
postgres=> select row_security_active('t');
 row_security_active 
---------------------
 t
(1 row)

Conclusion: Yet another great feature which made it into PostgreSQL. If you are willing to alpha/beta test there is a wiki page which describes the best practices.