BUG #14833: Row security policies using session variable can be circumvented

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

Responses

Browse pgsql-bugs by date

  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