From: | ivo(at)limmen(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Cc: | ivo(at)limmen(dot)org |
Subject: | BUG #14833: Row security policies using session variable can be circumvented |
Date: | 2017-09-28 09:25:29 |
Message-ID: | 20170928092529.28566.24392@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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?
Best regards,
Ivo Limmen
From | Date | Subject | |
---|---|---|---|
Next Message | jeanpierre.carayol | 2017-09-28 10:01:43 | Re: Something strang on "left join" |
Previous Message | Ivo Limmen | 2017-09-28 09:16:21 | Row security policies using session variable can be circumvented |