Re: Row security policies using session variable can be circumvented

On Thu, Sep 28, 2017 at 5:12 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> 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).
>

Might I suggest pointing out this implication ​in the docs instead of just
having the user make the connection themselves.

Right now:

https://www.postgresql.org/docs/9.6/static/sql-createpolicy.html

"As with normal queries and views, permission checks and policies for the
tables which are referenced by a view will use the view owner's rights and
any policies which apply to the view owner."

I would simply add (though it is redundant with the content of
ddl-rowsecurity).

"Thus, if the view owner and table owner are the same role no policies will
be enforced when querying the table through the view unless subsequently
alters the table and force[s] row level security."

Especially since this would seem to be one valid exception that is implied
when we write:

https://www.postgresql.org/docs/9.6/static/ddl-rowsecurity.html

"(However, the table's owner is typically not subject to row security
policies.)"

I'd probably write "is, by default, not subject" instead of "typically".

In there it discusses how the OP would probably be advised to make this
work as expected:

https://www.postgresql.org/docs/9.6/static/sql-altertable.html

ALTER TABLE ... FORCE ROW LEVEL SECURITY

The entire sentence is:

"Superusers and roles with the BYPASSRLS attribute always bypass the row
security system when accessing a table. Table owners normally bypass row
security as well, though a table owner can choose to be subject to row
security with ALTER TABLE ... FORCE ROW LEVEL SECURITY."

I would consider adding:

"This can be useful, for instance, one wish to create a view that enforces
the RLS policies of the underlying table and have the view be owned by the
same role that owns the table."

David J.