Recursive row level security policy

Hello

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Simon Charette
Sent: Freitag, 16. Dezember 2016 06:15
To: pgsql-general@postgresql.org
Subject: [GENERAL] Recursive row level security policy

Hello there,

I'm not sure I'm posting to the appropriate mailing list so don't hesitate to redirect me to the appropriate one.

I've been trying to setup a policy that allows "accounts" table rows to only be seen by their owner by using the
current_user to compare them by name.

Unfortunately it looks like I'm either missing something or there's a limitation in the current row level security
implementation that prevents me from doing this.

Here's the actual SQL to reproduce the issue:

CREATE TABLE "accounts" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(50) NOT NULL UNIQUE,
"owner_id" integer NOT NULL
);

INSERT INTO accounts(id, name, owner_id)
VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);

GRANT SELECT ON accounts TO PUBLIC;

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (owner_id = (SELECT id FROM accounts WHERE name = current_user));

I think that should be:

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (name = current_user);

Regards
Charles

CREATE ROLE foo;
SET ROLE foo;
SELECT * FROM accounts;
-- ERROR: infinite recursion detected in policy for relation "accounts"

Is there any way to alter the "account_ownership" policy's USING clause to avoid this infinite recursion or a way to
model my schema to prevent this from happening?

Thank you for your time,
Simon

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Charles,

Unfortunately this will only return accounts matching the current_user's name.

I would expect "SET ROLE foo; SELECT name FROM accounts" to return "foo" and
"bar" and not only "foo" like your proposed solution would do.

Simon

2016-12-16 0:57 GMT-05:00 Charles Clavadetscher <clavadetscher@swisspug.org>:

Hello

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Simon Charette
Sent: Freitag, 16. Dezember 2016 06:15
To: pgsql-general@postgresql.org
Subject: [GENERAL] Recursive row level security policy

Hello there,

I'm not sure I'm posting to the appropriate mailing list so don't hesitate to redirect me to the appropriate one.

I've been trying to setup a policy that allows "accounts" table rows to only be seen by their owner by using the
current_user to compare them by name.

Unfortunately it looks like I'm either missing something or there's a limitation in the current row level security
implementation that prevents me from doing this.

Here's the actual SQL to reproduce the issue:

CREATE TABLE "accounts" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(50) NOT NULL UNIQUE,
"owner_id" integer NOT NULL
);

INSERT INTO accounts(id, name, owner_id)
VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);

GRANT SELECT ON accounts TO PUBLIC;

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (owner_id = (SELECT id FROM accounts WHERE name = current_user));

I think that should be:

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (name = current_user);

Regards
Charles

CREATE ROLE foo;
SET ROLE foo;
SELECT * FROM accounts;
-- ERROR: infinite recursion detected in policy for relation "accounts"

Is there any way to alter the "account_ownership" policy's USING clause to avoid this infinite recursion or a way to
model my schema to prevent this from happening?

Thank you for your time,
Simon

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Simon

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Simon Charette
Sent: Freitag, 16. Dezember 2016 07:02
To: Charles Clavadetscher <clavadetscher@swisspug.org>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Recursive row level security policy

Hello Charles,

Unfortunately this will only return accounts matching the current_user's name.

I would expect "SET ROLE foo; SELECT name FROM accounts" to return "foo" and "bar" and not only "foo" like your
proposed solution would do.

True. I did oversee the real target.

The problem is that the policy for select on the table will be cheked each time a select is performed. So having a select in the using condition will check the policy again, and so on.

I am not sure how to solve this with policies and I assume that somebody else may come up with an idea. One thing I can think of is to check the condition in a security definer function where you temporarily disable row level security. But this is quite a nasty thing to do...

A workaround would be the "old way" using views:

CREATE VIEW public.v_accounts AS
SELECT * FROM accounts
WHERE owner_id = (SELECT owner_id FROM accounts WHERE name = CURRENT_USER);

REVOKE SELECT ON accounts FROM public;
GRANT SELECT ON v_accounts TO public;

charles@charles=# set role foo;
SET

charles@charles=> SELECT * FROM accounts;
ERROR: permission denied for relation accounts

charles@charles=> select * from v_accounts ;
id | name | owner_id
----+------+----------
1 | foo | 1
2 | bar | 1
(2 rows)

Instead of granting select on the table you only grant it on the view.

Hope this helps.
Bye
Charles

Simon

2016-12-16 0:57 GMT-05:00 Charles Clavadetscher <clavadetscher@swisspug.org>:

Hello

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Simon
Charette
Sent: Freitag, 16. Dezember 2016 06:15
To: pgsql-general@postgresql.org
Subject: [GENERAL] Recursive row level security policy

Hello there,

I'm not sure I'm posting to the appropriate mailing list so don't hesitate to redirect me to the appropriate one.

I've been trying to setup a policy that allows "accounts" table rows
to only be seen by their owner by using the current_user to compare them by name.

Unfortunately it looks like I'm either missing something or there's a
limitation in the current row level security implementation that prevents me from doing this.

Here's the actual SQL to reproduce the issue:

CREATE TABLE "accounts" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(50) NOT NULL UNIQUE,
"owner_id" integer NOT NULL
);

INSERT INTO accounts(id, name, owner_id)
VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);

GRANT SELECT ON accounts TO PUBLIC;

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (owner_id = (SELECT id FROM accounts WHERE name =
current_user));

I think that should be:

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (name = current_user);

Regards
Charles

CREATE ROLE foo;
SET ROLE foo;
SELECT * FROM accounts;
-- ERROR: infinite recursion detected in policy for relation "accounts"

Is there any way to alter the "account_ownership" policy's USING
clause to avoid this infinite recursion or a way to model my schema to prevent this from happening?

Thank you for your time,
Simon

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello again

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
Clavadetscher
Sent: Freitag, 16. Dezember 2016 07:41
To: 'Simon Charette' <charette.s@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Recursive row level security policy

Hello Simon

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Simon
Charette
Sent: Freitag, 16. Dezember 2016 07:02
To: Charles Clavadetscher <clavadetscher@swisspug.org>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Recursive row level security policy

Hello Charles,

Unfortunately this will only return accounts matching the current_user's name.

I would expect "SET ROLE foo; SELECT name FROM accounts" to return
"foo" and "bar" and not only "foo" like your proposed solution would do.

True. I did oversee the real target.

The problem is that the policy for select on the table will be cheked each time a select is performed. So having a
select in the using condition will check the policy again, and so on.

I am not sure how to solve this with policies and I assume that somebody else may come up with an idea. One thing I
can think of is to check the condition in a security definer function where you temporarily disable row level
security. But this is quite a nasty thing to do...

Forget this. It would not work anyway.

A workaround would be the "old way" using views:

CREATE VIEW public.v_accounts AS
SELECT * FROM accounts
WHERE owner_id = (SELECT owner_id FROM accounts WHERE name = CURRENT_USER);

REVOKE SELECT ON accounts FROM public;
GRANT SELECT ON v_accounts TO public;

charles@charles=# set role foo;
SET

charles@charles=> SELECT * FROM accounts;
ERROR: permission denied for relation accounts

charles@charles=> select * from v_accounts ; id | name | owner_id
----+------+----------
1 | foo | 1
2 | bar | 1
(2 rows)

Instead of granting select on the table you only grant it on the view.

Hope this helps.
Bye
Charles

Simon

2016-12-16 0:57 GMT-05:00 Charles Clavadetscher <clavadetscher@swisspug.org>:

Hello

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Simon
Charette
Sent: Freitag, 16. Dezember 2016 06:15
To: pgsql-general@postgresql.org
Subject: [GENERAL] Recursive row level security policy

Hello there,

I'm not sure I'm posting to the appropriate mailing list so don't hesitate to redirect me to the appropriate

one.

I've been trying to setup a policy that allows "accounts" table
rows to only be seen by their owner by using the current_user to compare them by name.

Unfortunately it looks like I'm either missing something or there's
a limitation in the current row level security implementation that prevents me from doing this.

Here's the actual SQL to reproduce the issue:

CREATE TABLE "accounts" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(50) NOT NULL UNIQUE,
"owner_id" integer NOT NULL
);

INSERT INTO accounts(id, name, owner_id)
VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);

GRANT SELECT ON accounts TO PUBLIC;

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (owner_id = (SELECT id FROM accounts WHERE name =
current_user));

I think that should be:

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (name = current_user);

Regards
Charles

CREATE ROLE foo;
SET ROLE foo;
SELECT * FROM accounts;
-- ERROR: infinite recursion detected in policy for relation "accounts"

Is there any way to alter the "account_ownership" policy's USING
clause to avoid this infinite recursion or a way to model my schema to prevent this from happening?

Thank you for your time,
Simon

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/16/2016 01:02 AM, Simon Charette wrote:

Unfortunately this will only return accounts matching the current_user's name.

I would expect "SET ROLE foo; SELECT name FROM accounts" to return "foo" and
"bar" and not only "foo" like your proposed solution would do.

Perhaps:

8<--------------------------
CREATE TABLE "accounts" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(50) NOT NULL UNIQUE,
"owner_id" integer NOT NULL
);

INSERT INTO accounts(id, name, owner_id)
VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);

GRANT SELECT ON accounts TO PUBLIC;

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE FUNCTION get_owner_id(luser text) RETURNS integer AS $$
SELECT id FROM accounts WHERE name = luser
$$ LANGUAGE sql STRICT STABLE SECURITY DEFINER;

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (owner_id = get_owner_id(current_user));

CREATE ROLE foo;
SET ROLE foo;

SELECT * FROM accounts;
id | name | owner_id
----+------+----------
1 | foo | 1
2 | bar | 1
(2 rows)
8<--------------------------

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Thanks a lot Joe, that seems to work!

I suppose this works because PostgreSQL cannot introspect the
get_owner_id procedure to detect it's querying the "accounts" table
and thus doesn't warn about possible infinite recursion?

Simon

2016-12-16 9:36 GMT-05:00 Joe Conway <mail@joeconway.com>:

On 12/16/2016 01:02 AM, Simon Charette wrote:

Unfortunately this will only return accounts matching the current_user's name.

I would expect "SET ROLE foo; SELECT name FROM accounts" to return "foo" and
"bar" and not only "foo" like your proposed solution would do.

Perhaps:

8<--------------------------
CREATE TABLE "accounts" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(50) NOT NULL UNIQUE,
"owner_id" integer NOT NULL
);

INSERT INTO accounts(id, name, owner_id)
VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3);

GRANT SELECT ON accounts TO PUBLIC;

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE FUNCTION get_owner_id(luser text) RETURNS integer AS $$
SELECT id FROM accounts WHERE name = luser
$$ LANGUAGE sql STRICT STABLE SECURITY DEFINER;

CREATE POLICY account_ownership ON accounts FOR SELECT
USING (owner_id = get_owner_id(current_user));

CREATE ROLE foo;
SET ROLE foo;

SELECT * FROM accounts;
id | name | owner_id
----+------+----------
1 | foo | 1
2 | bar | 1
(2 rows)
8<--------------------------

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/17/2016 01:01 PM, Simon Charette wrote:

Thanks a lot Joe, that seems to work!

Good to hear.

I suppose this works because PostgreSQL cannot introspect the
get_owner_id procedure to detect it's querying the "accounts" table
and thus doesn't warn about possible infinite recursion?

Not exactly. RLS does not get applied to the superuser, and the
get_owner_id procedure was 1) SECURITY DEFINER, and 2) created/owned by
postgres. Thus the procedure executes without invoking the RLS policy
and avoids the infinite recursion.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Ahh makes sense, thanks for the explanation!

I was assuming USING() clauses were executed in the context of the
owner of the policy, by passing RLS.

2016-12-17 13:18 GMT-05:00 Joe Conway <mail@joeconway.com>:

On 12/17/2016 01:01 PM, Simon Charette wrote:

Thanks a lot Joe, that seems to work!

Good to hear.

I suppose this works because PostgreSQL cannot introspect the
get_owner_id procedure to detect it's querying the "accounts" table
and thus doesn't warn about possible infinite recursion?

Not exactly. RLS does not get applied to the superuser, and the
get_owner_id procedure was 1) SECURITY DEFINER, and 2) created/owned by
postgres. Thus the procedure executes without invoking the RLS policy
and avoids the infinite recursion.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Simon,

* Simon Charette (charette.s@gmail.com) wrote:

Ahh makes sense, thanks for the explanation!

I was assuming USING() clauses were executed in the context of the
owner of the policy, by passing RLS.

No, as with views, a USING() clause is executed as the caller not the
owner of the relation. Security Definer functions can be used to
execute actions in the policy as another user.

Note that RLS won't be applied for the table owner either (unless the
relation has 'FORCE RLS' enabled for it), so you don't have to have
functions which are run as superuser to use the approach Joe
recommended.

Thanks!

Stephen

On 12/17/2016 02:04 PM, Stephen Frost wrote:

Note that RLS won't be applied for the table owner either (unless the
relation has 'FORCE RLS' enabled for it), so you don't have to have
functions which are run as superuser to use the approach Joe
recommended.

Good point, thanks, I should have mentioned that. You would be better
off having a different user own both the table and the function in order
to avoid using/abusing the superuser for that purpose. Just be aware
that FORCE RLS would break that solution.

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development