Did it help? If yes - maybe you can help me?
On 19th of September, Stephen Frost committed patch:
Row-Level Security Policies (RLS) Building on the updatable security-barrier views work, add the ability to define policies on tables to limit the set of rows which are returned from a query and which are allowed to be added to a table. Expressions defined by the policy for filtering are added to the security barrier quals of the query, while expressions defined to check records being added to a table are added to the with-check options of the query. New top-level commands are CREATE/ALTER/DROP POLICY and are controlled by the table owner. Row Security is able to be enabled and disabled by the owner on a per-table basis using ALTER TABLE .. ENABLE/DISABLE ROW SECURITY. Per discussion, ROW SECURITY is disabled on tables by default and must be enabled for policies on the table to be used. If no policies exist on a table with ROW SECURITY enabled, a default-deny policy is used and no records will be visible. By default, row security is applied at all times except for the table owner and the superuser. A new GUC, row_security, is added which can be set to ON, OFF, or FORCE. When set to FORCE, row security will be applied even for the table owner and superusers. When set to OFF, row security will be disabled when allowed and an error will be thrown if the user does not have rights to bypass row security. Per discussion, pg_dump sets row_security = OFF by default to ensure that exports and backups will have all data in the table or will error if there are insufficient privileges to bypass row security. A new option has been added to pg_dump, --enable-row-security, to ask pg_dump to export with row security enabled. A new role capability, BYPASSRLS, which can only be set by the superuser, is added to allow other users to be able to bypass row security using row_security = OFF. Many thanks to the various individuals who have helped with the design, particularly Robert Haas for his feedback. Authors include Craig Ringer, KaiGai Kohei, Adam Brightwell, Dean Rasheed, with additional changes and rework by me. Reviewers have included all of the above, Greg Smith, Jeff McCormick, and Robert Haas.
So this is actually huge change. I found some mails about related changes from 2007!
Most of the thing is described pretty clearly in the commit log, but let's see how it really looks when used.
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.