Thread: [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));
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
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
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 >
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 selectin 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 thinkof is to check the condition in a security definer function where you temporarily disable row level security. But thisis 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
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
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
Attachment
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 >
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
Attachment
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 >
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
Attachment
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