The following bug has been logged on the website:
Bug reference: 14833
Logged by: Ivo Limmen
Email address: ivo@limmen.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@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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs