Re: BUG #14833: Row security policies using session variable can be circumvented

2017-09-28 11:25 GMT+02:00 <ivo(at)limmen(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 14833
> Logged by: Ivo Limmen
> Email address: ivo(at)limmen(dot)org
> PostgreSQL version: 9.5.8
> Operating system: Linux Mint 18.2
> Description:
>
> Dear postgres developers,
>
> I am using:
>
> psql --version: psql (PostgreSQL) 9.5.8
> apt: postgresql-9.5 _9.5.8-0ubuntu0.16.04.1
> uname -a: Linux utopia 4.10.0-35-generic #39~16.04.1-Ubuntu SMP Wed Sep 13
> 09:02:42 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
>
> (It's a Linux Mint 18.2 system)
>
> We have row security policy in place on our database. We do not use
> current_user on the policies but session variables. This all seemed to work
> perfectly until we started using views.
>
> I have no idea if this is a bug or normal operation as I could not find
> anything on this in the documentation (9.6 current)
>
> Steps to reproduce:
>
>
> CREATE TABLE accounts (user_id integer, manager text, company text,
> contact_email text);
>
> ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
>
> CREATE POLICY account_managers ON accounts
> USING (user_id is null or user_id = current_setting('x.id')::integer);
>
> insert into accounts (user_id, manager, company, contact_email) values (1,
> 'jan', 'QSD', 'info(at)qsd(dot)nl');
> insert into accounts (user_id, manager, company, contact_email) values (2,
> 'piet', 'Google', 'info(at)google(dot)com');
> insert into accounts (user_id, manager, company, contact_email) values
> (null, 'piet', 'Microsoft', 'info(at)microsoft(dot)com');
>
> create view test as select * from accounts;
>
> create role tmp;
> grant all on accounts to tmp;
> grant all on test to tmp;
>
> -- you will see all because we have no session variable set and we are
> still
> using role postgres
> select * from accounts;
>
> set role tmp;
> set session x.id to 2;
>
> -- we only see row 2 and 3 (as expected)
> select * from accounts;
>
> -- we see ALL records... not expected
> select * from test;
>
> Is this a bug? Or am I doing something wrong?
>
>
AFAICT, this isn't a bug. Views are executed with the permissions of the
owner of the view, not with the permissions of the user executing the
query. This view has been created by postgres, so it is executed with the
postgres user permissions.

--
Guillaume.