Thread: [BUGS] Row security policies using session variable can be circumvented
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:
Setup
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@qsd.nl');
insert into accounts (user_id, manager, company, contact_email) values (2, 'piet', 'Google', 'info@google.com');
insert into accounts (user_id, manager, company, contact_email) values (null, 'piet', 'Microsoft', 'info@microsoft.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;
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@qsd.nl');
insert into accounts (user_id, manager, company, contact_email) values (2, 'piet', 'Google', 'info@google.com');
insert into accounts (user_id, manager, company, contact_email) values (null, 'piet', 'Microsoft', 'info@microsoft.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?
Best regards,
Ivo Limmen
Ivo Limmen
Principal Consultant
m: +31 6 53 92 40 33
@: ivo.limmen@qsd.nl
Principal Consultant
m: +31 6 53 92 40 33
@: ivo.limmen@qsd.nl
| QSD B.V. Loolaan 89 3971 PM Driebergen-Rijsenburg t: +31 343 76 41 50 w: http://www.qsd.nl/ @: info@qsd.nl |
Dit bericht is vertrouwelijk en kan geheime informatie bevatten enkel bestemd voor de geadresseerde. Indien dit bericht niet voor u is bestemd, verzoeken wij u dit onmiddellijk aan ons te melden en het bericht te vernietigen. Aangezien de integriteit van het bericht niet veilig gesteld is middels verzending via internet, kan QSD niet aansprakelijk worden gehouden voor de inhoud daarvan. Hoewel wij ons inspannen een virusvrij netwerk te hanteren, geven wij geen enkele garantie dat dit bericht virusvrij is, noch aanvaarden wij enige aansprakelijkheid voor de mogelijke aanwezigheid van een virus in dit bericht. Op al onze rechtsverhoudingen, aanbiedingen en overeenkomsten waaronder QSD goederen en/of diensten levert zijn met uitsluiting van alle andere voorwaarden de Leveringsvoorwaarden van QSD van toepassing. Deze worden u op aanvraag direct kosteloos toegezonden.
This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the QSD liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted. On all offers and agreements under which QSD supplies goods and/or services of whatever nature, the Terms of Delivery from QSD exclusively apply. The Terms of Delivery shall be promptly submitted to you on your request.
QSD B.V.
This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the QSD liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted. On all offers and agreements under which QSD supplies goods and/or services of whatever nature, the Terms of Delivery from QSD exclusively apply. The Terms of Delivery shall be promptly submitted to you on your request.
QSD B.V.
KvK Utrecht 53067231
Greetings,
* Ivo Limmen (ivo.limmen@qsd.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@qsd.nl');
> insert into accounts (user_id, manager, company, contact_email) values (2,
> 'piet', 'Google', 'info@google.com');
> insert into accounts (user_id, manager, company, contact_email) values
> (null, 'piet', 'Microsoft', 'info@microsoft.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
Re: [BUGS] Row security policies using session variable can be circumvented
From
"David G. Johnston"
Date:
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:
"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:
"(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:
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.