Thread: [BUGS] BUG #14833: Row security policies using session variable can becircumvented

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?

2017-09-28 11:25 GMT+02:00 <ivo@limmen.org>:

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: