Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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 - peter
You 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 clients
    for all
    to public
    using ( 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 clients
    for all
    to public
    using ( 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.


Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL