PostgreSQL Row-Level Security Policies

Additional security can be added to tables to prevent users from accessing rows they shouldn't be able to see.

Say you had a table with log data, where the username column contained the database user name which created the log entry:

 CREATE TABLE log ( id serial primary key, username text, log_event text);

But you don't want users to see the log entries from other users, so we create a policy that says you're allowed to see the row if the username column matches the current user running the query:

 CREATE POLICY policy_user_log ON log FOR ALL TO PUBLIC USING (username = current_user);

And then we enable Row Level Security on the table:

 ALTER TABLE log ENABLE ROW LEVEL SECURITY;

As the user "report", we would then only see rows where the username column contained the value 'report':

 # SELECT * FROM log;  id | username | log_event ----+----------+---------------- 1 | report | DELETE issued 4 | report | Reset accounts (2 rows)

As the user "messaging", we see a different set of rows:

  id | username  |  log_event----+-----------+---------------------- 2 | messaging | Message queue purged 3 | messaging | Reset accounts (2 rows)

Whereas the "postgres" user, as the superuser would get:

  id | username  |  log_event----+-----------+---------------------- 1 | report| DELETE issued 2 | messaging | Message queue purged 3 | messaging | Reset accounts 4 | report| Reset accounts (4 rows)

That's because the superuser sees all rows due to the BYPASSRLS attribute on the superuser role by default.

If you have multiple policies, they are all OR'd together. For example, if you had the following 2 policies:

 CREATE POLICY policy_user_log ON log FOR ALL TO PUBLIC USING (username = current_user);

 CREATE POLICY policy_even_ids_only ON log FOR ALL TO PUBLIC USING (id % 2 = 0);

This would effectively result in the following being added to the WHERE clause of any statement:

 WHERE ((username = current_user) OR (id % 2 = 0))

So as long as it matches one policy, it passes, so as the report user mentioned above, we'd now see the following:

  id | username  |  log_event----+-----------+---------------------- 1 | report| DELETE issued 2 | messaging | Message queue purged 4 | report| Reset accounts (3 rows)

The additional policy now allows the row where the id is 2 because it matches the new policy. The first row doesn't match the new policy, but it's returned because it matches the first. So polices are permissive rather than restrictive.

For this, we'll create simple test table, couple of test users, and will see how it will work.

create table clients (id serial primary key,account_name text not null unique,account_manager text not null);CREATE TABLE create user peter;CREATE ROLE create user joanna;CREATE ROLE create user bill;CREATE ROLE grant all on table clients to peter, joanna, bill;GRANT grant all on sequence clients_id_seq to peter, joanna, bill;GRANT insert into clients (account_name, account_manager)values ('initrode', 'peter'), ('initech', 'bill'), ('chotchkie''s', 'joanna');INSERT 0 3

With this in place, we can run some test. Obviously, each of the users can now query whole table:

$ \c - peterYou are now connected to database "depesz" as user "peter". $ select * from clients; id | account_name | account_manager----+--------------+----------------- 1 | initrode | peter 2 | initech | bill 3 | chotchkie's | joanna(3 rows)

Thanks to security policies we should be able to make it so that user can select only its “own" rows.

create policy just_own_clients on clientsfor allto publicusing ( account_manager = current_user );CREATE POLICY alter table clients ENABLE ROW LEVEL SECURITY;ALTER TABLE

And that's it. Now, I can only see rows belonging to myself:

$ select * from clients; id | account_name | account_manager ----+--------------+----------------- 1 | initrode | peter(1 row) $ \c - joanna $ select * from clients; id | account_name | account_manager ----+--------------+----------------- 3 | chotchkie's | joanna(1 row)

What's more – you can't even insert rows that you wouldn't be able to see:

$ \c - peter $ insert into clients (account_name, account_manager) values ('hack', 'bill');ERROR: new row violates WITH CHECK OPTION for "clients"DETAIL: Failing row contains (4, hack, bill). $ insert into clients (account_name, account_manager) values ('hack', 'peter');INSERT 0 1

To be honest, I'm a bit at loss why just “USING ()" in the policy works for both insert and select, but I'm quite happy with it.

Of course, you can override the default, and make it possible, for example, for anyone to create new clients for Bill, so he'll have less time for whatever he's doing when not working:

drop policy just_own_clients on clients;DROP POLICY create policy just_own_clients on clientsfor allto publicusing ( account_manager = current_user )with check ( account_manager in ( 'bill', current_user ) );CREATE POLICY

Now, one can still only see it's own clients:

$ select * from clients; id | account_name | account_manager ----+--------------+----------------- 1 | initrode | peter 5 | hack | peter(2 rows)

But can easily add more work for Bill:

$ insert into clients (account_name, account_manager) values ('hack2', 'bill');INSERT 0 1

Of course your policies can be arbitrarily complex – after all, it's a normal check constraint, which can do anything you want, including, with appropriate extensions, querying external systems.

It's a great addon, and a welcome addition to PostgreSQL. It took some time to get here, but I, for one, definitely appreciate the work of everyone involved. Thanks a lot.