Re: [GENERAL] Recursive row level security policy

2016-12-17 Thread Joe Conway
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

Re: [GENERAL] Recursive row level security policy

2016-12-17 Thread Stephen Frost
Simon, * Simon Charette (charett...@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 re

Re: [GENERAL] Recursive row level security policy

2016-12-17 Thread Simon Charette
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 : > On 12/17/2016 01:01 PM, Simon Charette wrote: >> Thanks a lot Joe, that seems to work! > > Good to hear. >

Re: [GENERAL] Recursive row level security policy

2016-12-17 Thread Joe Conway
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

Re: [GENERAL] Recursive row level security policy

2016-12-17 Thread Simon Charette
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 : > On 12/16/2016 01:02 AM,

Re: [GENERAL] Recursive row level security policy

2016-12-16 Thread Joe Conway
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<

Re: [GENERAL] Recursive row level security policy

2016-12-15 Thread Charles Clavadetscher
tag, 16. Dezember 2016 07:02 > > To: Charles Clavadetscher > > 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&

Re: [GENERAL] Recursive row level security policy

2016-12-15 Thread Charles Clavadetscher
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&

Re: [GENERAL] Recursive row level security policy

2016-12-15 Thread Simon Charette
16-12-16 0:57 GMT-05:00 Charles Clavadetscher : > Hello > >> -Original Message- >> From: pgsql-general-ow...@postgresql.org >> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Simon Charette >> Sent: Freitag, 16. Dezember 2016 06:15 >> To: pgsql

Re: [GENERAL] Recursive row level security policy

2016-12-15 Thread Charles Clavadetscher
Hello > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@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

[GENERAL] Recursive row level security policy

2016-12-15 Thread Simon Charette
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 l