Re: Row security policies using session variable can be circumvented

Greetings,

* Ivo Limmen (ivo(dot)limmen(at)qsd(dot)nl) wrote:
> 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;

Given that you didn't change roles in the above script, one can presume
that the view 'test' is owned by the same role which owns 'accounts'.
What I don't believe you're remembering is that when a view accesses a
relation, it does so with the privileges of the view's *owner*, not of
the user who is querying the view. As RLS isn't applied when the owner
of a relation is accessing that relation, what you're seeing here is
that queries against the view aren't having RLS applied because the
'accounts' relation, when access through the view, is being done so with
the privileges of the role that owns the 'test' view.

The same is true of the GRANT system. In other words, even without RLS
involved, if an account has access to the 'test' view, then they are
able to access the rows in the 'accounts' table, even though that role
may not have been GRANT'd access to the accounts table. More
specifically-

> create role tmp;
> grant all on accounts to tmp;
> grant all on test to tmp;

The above 'grant all on accounts to tmp;' isn't necessary for the 'tmp'
role to be able to access 'test' and therefore the records in
'accounts', because that access is done as the owner of the 'test' view.

> Is this a bug? Or am I doing something wrong?

This isn't a bug but simply how views work (and have always worked with
the GRANT system and the RLS system simply operates in the same manner).

Thanks!

Stephen