October 2nd, 2014 by depesz | Tags: , , , , , | 9 comments »
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
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
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 user peter;
create user joanna;
create user bill;
grant all on table clients to peter, joanna, bill;
grant all on sequence clients_id_seq to peter, joanna, bill;
insert into clients (account_name, account_manager)
    values ('initrode', 'peter'), ('initech', 'bill'), ('chotchkie''s', 'joanna');

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 );
alter table clients ENABLE ROW LEVEL SECURITY;

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');

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;
create policy just_own_clients on clients
    for all
    to public
    using ( account_manager = current_user )
    with check ( account_manager in ( 'bill', current_user ) );

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');

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.

  1. 9 comments

  2. # Tony
    Oct 2, 2014

    JUST NICE!!!

  3. # Thom Brown
    Oct 2, 2014

    One thing that kept tripping me up when testing this feature was that overlapping polices are permissive rather than restrictive, in that they are combined using OR rather than AND. So if one policy says the account_name must match the current user, but another says the id must be between 1 and 20, the targeted user would see rows that would violate the first policy but don’t violate the second, and vice versa.

    e.g. user bob would see

    id account_name
    1 jane
    2 joe
    3 carl
    4 melissa

    20 rob
    33 bob

  4. Oct 3, 2014

    it definitely can be a problem. Perhaps we could get some way to change the “OR” into “AND”?

  5. # Thom Brown
    Oct 3, 2014

    @Depesz Yeah, I had thought about whether it might be worthwhile choosing a mode for the policy.



    CREATE RESTRICTIVE POLICY must_match_user… — user_col = current_user
    CREATE PERMISSIVE POLICY can_match_high_id… — id > 50
    CREATE PERMISSIVE POLICY can_match_country… — country in (‘UK’,’Ireland’)

    SELECT * FROM mytable;

    gets planned to…

    SELECT * FROM mytable
    WHERE (user_col = current_user) — must_match_user
    AND ((id > 50) — can_match_high_id
    OR (country in (‘UK’,’Ireland’))); — can_match_country

    Permissive could just be the default, and having only permissive would be no different from the current functionality. Having only restrictive means all policies have to be met. Having a mix means all restrictive polices must be met, and at least one of the permissive policies. Of course, rather than creating several policies, one could roll them into a single policy.

    I haven’t given the above idea much thought, so there could be a glaring problem with it, but I did expect a restrictive model in the first place.

  6. # Joe
    Oct 4, 2014

    Pity column level security doesn’t let SELECT * work where you only have access to a subset of the fields in the table

  7. # Matt
    Oct 6, 2014

    Polices work like roles. Not just PostgreSQL roles, but roles in any operating system or application. If you assign multiple roles to a user, the access is OR’ed together. This is in line with just about all ACL technology. Policies work the same way here.

  8. # Thom Brown
    Oct 7, 2014

    @Matt Yes, I discussed the nature of RLS with the author of the feature previously, and he described it in terms of granting permission. However, the check options can easily switch one into the mindset of using check constraints, where overlapping check constraints would effectively be connected via AND rather than OR.

    I think there’s usefulness in both modes, but I don’t think it comes all that naturally to think of it as being permissive. I’ve looked at what happens in Oracle since and discovered that their model is restrictive, so it’s going to be equally surprising to those coming from that database system too.

  9. # Matt
    Oct 11, 2014

    @Thom I have used exclusion constraints before (very useful for rejecting overlaps among rows!) and am somewhat used to the CHECK keyword having more than one concept behind it. For me, check constraints on columns have always been about enforcing data integrity within the row. RLS is about security, a very different subject matter. I would expect that anyone diving into this would start with the concept and end with the syntax. Your concern is valid if they were to start at the syntax and make a non-security related restriction out of it. This seems like a very “tail wagging the dog” way of building code. I would expect that they would consult the PostgreSQL documentation at some point in process of making out the syntax and usage.

    For me, when I think “lets restrict rows”, the first and only thing that comes to mind is roles, and the OR behavior does not violate the “principle of least surprise” like an AND would. I guess I have been wanting simple row level security for a long time, and any other type of row restriction purpose, I believe, belongs in the original query. I just do not think that DBAs should be putting application layer concepts in the database where it affects row retrieval for *EVERYTHING*. But for security, this makes sense, and it even takes database privileges to bypass RLS. But if RLS where used for non-security purposes, then eventually, someone has to bypass RLS for an application logic reason using database privileges. By choosing “AND” RLS checks, it seems like we are encoring what I think are bad designs.

    I would not want to see PostgreSQL model Oracle behavior. Oracle is an unnecessarily complex database with technologies that were implemented either before the implications were fully understood, rushed into releases, or built to support old behavior and syntax, no matter how unintuitive and inconvenient they might be. Open source projects eventually get forked or replaced for going the route that they take. Its current RLS AND behavior sounds like it was chosen based on syntax as opposed to semantics, or perhaps just the feel good concept of being “restrictive”. It probably made more sense to a manager who was not involved in the details.

  10. # manas
    Aug 17, 2015

    It would have been nice if RFS took a stored procedure (OR Function) to do the authentication.
    With that flexibility it would be easy to push down application level security to DB. (At least in applications where I work.)

Leave a comment