Thread: Default Privilege Table ANY ROLE

Default Privilege Table ANY ROLE

From
Nicolas Paris
Date:
Hi

I d'like my user be able to select on any new table from other users.

> ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner1"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
> ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner2"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
> ...


Do I really have to repeat the command for all users ?

The problem is I have many user able to create tables and all of them
have to read each other. 

Thanks



-- 
nicolas


Re: Default Privilege Table ANY ROLE

From
Ron
Date:
On 11/14/2018 08:19 AM, Nicolas Paris wrote:
> Hi
>
> I d'like my user be able to select on any new table from other users.
>
>> ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner1"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
>> ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner2"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
>> ...
>
> Do I really have to repeat the command for all users ?
>
> The problem is I have many user able to create tables and all of them
> have to read each other.

Would ROLE Groups solve your problem?


-- 
Angular momentum makes the world go 'round.


Re: Default Privilege Table ANY ROLE

From
Nicolas Paris
Date:
On Wed, Nov 14, 2018 at 10:05:51AM -0600, Ron wrote:
> On 11/14/2018 08:19 AM, Nicolas Paris wrote:
> > Hi
> > 
> > I d'like my user be able to select on any new table from other users.
> > 
> Would ROLE Groups solve your problem?


Maybe yes, not sure what it is. I tested this:

> create role myrolegroup;
> ALTER DEFAULT PRIVILEGES  FOR  ROLE "myrolegroup"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser";
> create myuser1 inherit  in role myrolegroup;
> create myuser2 inherit  in role myrolegroup;

But if myuser2 creates a table, then myuser1 cannot select on it. I
guess that's because inheritance only apply for GRANT (which is
distinct from DEFAUL PRIVILEGE).


Thanks,

-- 
nicolas


Re: Default Privilege Table ANY ROLE

From
Laurenz Albe
Date:
Nicolas Paris wrote:
> I d'like my user be able to select on any new table from other users.
> 
> > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner1"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
> > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner2"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
> > ...
> 
> 
> Do I really have to repeat the command for all users ?
> 
> The problem is I have many user able to create tables and all of them
> have to read each other. 

This is one setup that I can come up with:

CREATE ROLE tableowner NOINHERIT;
CREATE ROLE tablereader;
ALTER DEFAULT PRIVILEGES FOR ROLE tableowner IN SCHEMA myschema GRANT SELECT ON TABLES TO tablereader;

CREATE ROLE alice LOGIN IN ROLE tableowner, tablereader;
CREATE ROLE bob LOGIN IN ROLE tableowner, tablereader;

Now whenever "alice" has to create a table, she runs

SET ROLE tableowner;
CREATE TABLE myschema.newtable(x integer);
RESET ROLE;

Then all these tables belong to "tableowner", and each user in group "tablereader"
can SELECT from them:

\z myschema.newtable 
                                     Access privileges
  Schema  |   Name   | Type  |       Access privileges       | Column privileges | Policies 
----------+----------+-------+-------------------------------+-------------------+----------
 myschema | newtable | table | tableowner=arwdDxt/tableowner+|                   | 
          |          |       | tablereader=r/tableowner      |                   | 
(1 row)

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com



Re: Default Privilege Table ANY ROLE

From
Nicolas Paris
Date:
On Wed, Nov 14, 2018 at 09:04:44PM +0100, Laurenz Albe wrote:
> Nicolas Paris wrote:
> > I d'like my user be able to select on any new table from other users.
> > 
> > > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner1"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
> > > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner2"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
> > > ...
> > 
> > 
> > Do I really have to repeat the command for all users ?
> > 
> > The problem is I have many user able to create tables and all of them
> > have to read each other. 
> 
> Now whenever "alice" has to create a table, she runs
> SET ROLE tableowner;
> Then all these tables belong to "tableowner", and each user in group "tablereader"
> can SELECT from them:

Yes, this step is overhead to me:
> SET ROLE tableowner;

In my mind, both bob/alice inherit from the same group, so they should
share the table they build according to this:

> ALTER DEFAULT PRIVILEGES FOR ROLE tableowner IN SCHEMA myschema GRANT SELECT ON TABLES TO tablereader;




-- 
nicolas


Re: Default Privilege Table ANY ROLE

From
Tom Lane
Date:
Maybe I'm missing something, but doesn't this solve your problem
as stated?

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public;

            regards, tom lane


Re: Default Privilege Table ANY ROLE

From
Nicolas Paris
Date:
On Wed, Nov 14, 2018 at 03:53:39PM -0500, Tom Lane wrote:
> Maybe I'm missing something, but doesn't this solve your problem
> as stated?
> 
> ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public;


Not sure that's equivalent to what I am looking for below (but is not allowed):

> ALTER DEFAULT PRIVILEGES  FOR  ROLE  *.* IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"

-- 
nicolas


Re: Default Privilege Table ANY ROLE

From
Nicolas Paris
Date:
On Wed, Nov 14, 2018 at 03:19:00PM +0100, Nicolas Paris wrote:
> Hi
> 
> I d'like my user be able to select on any new table from other users.
> 
> > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner1"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
> > ALTER DEFAULT PRIVILEGES  FOR  ROLE "theowner2"  IN SCHEMA "myschema" GRANT  select ON TABLES TO "myuser"
> > ...
> 
> 
> Do I really have to repeat the command for all users ?
> 
> The problem is I have many user able to create tables and all of them
> have to read each other. 
> 

There is apparently no trivial solution, could the Postgres DCL be
extended with this syntax in the future ?

> ALTER DEFAULT PRIVILEGES  FOR  ALL ROLE  IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser"




-- 
nicolas


Re: Default Privilege Table ANY ROLE

From
Stephen Frost
Date:
Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Maybe I'm missing something, but doesn't this solve your problem
> as stated?
>
> ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public;

That just means that the 'ROLE' in the result is the current role, as
per the docs:

-----
The name of an existing role of which the current role is a member. If
FOR ROLE is omitted, the current role is assumed.
-----

There was much discussion of being able to have 'FOR ALL ROLES' or
similar for ALTER DEFAULT PRIVILEGES when it went in, but there was a
lot of concern about one user getting to define the default privileges
for objects created by some other user.

The thought mentioned up-thread of having a 'group role' which can be
assigned to a role and then used for the default privileges when a role
creates an object seems like a neat idea, but I'm not sure how we'd deal
with overlaps.  Specifically:

User u1, member of role r1.

Role r1 has CREATE rights on schema s1.

DEFAULT PRIVILEGES on schema s1 for r1 say GRANT SELECT ON TABLES TO r2.

User u1 runs CREATE TABLE t1 in s1.

Table t1 has GRANT SELECT ON t1 TO r2 applied.

On that vein, I'd love it if table t1 was then also OWNED by r1, as that
is the role which allows the CREATE to happen.

The issue here though is that a given user 'u1' could have access to the
schema 's1' through multiple other roles and there's no way to say which
role to use, and obviously we'd want it to somehow be deterministic.

If we did allow the 'FOR ALL ROLES' as suggested- who would be allowed
to set that?  I certainly don't want more superuser-only things, but
currently a schema owner isn't allowed to set the privileges on objects
in their schema on a per-object level, at least not explicitly, and we
don't allow them to set DEFAULT PRIVILEGES for some other role:

=*> alter default privileges for role r2 in schema ts grant select on tables to r1;
ERROR:  must be member of role "r2"

I do think there's an argument to be made for having a default role who
is explicitly allowed to set/change the privileges on objects in the
system, but in an ideal world that role wouldn't be allowed to access
any of the data in the system.  Such a role could have this right,
perhaps.

Thanks!

Stephen

Attachment

Re: Default Privilege Table ANY ROLE

From
Tom Lane
Date:
Stephen Frost <sfrost@snowman.net> writes:
> There was much discussion of being able to have 'FOR ALL ROLES' or
> similar for ALTER DEFAULT PRIVILEGES when it went in, but there was a
> lot of concern about one user getting to define the default privileges
> for objects created by some other user.

Yeah, it's hard to see how you could allow such a command to anybody
but a superuser.

            regards, tom lane


Re: Default Privilege Table ANY ROLE

From
Nicolas Paris
Date:
On Fri, Nov 16, 2018 at 03:17:59PM -0500, Tom Lane wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > There was much discussion of being able to have 'FOR ALL ROLES' or
> > similar for ALTER DEFAULT PRIVILEGES when it went in, but there was a
> > lot of concern about one user getting to define the default privileges
> > for objects created by some other user.
> 
> Yeah, it's hard to see how you could allow such a command to anybody
> but a superuser.
> 

I have some applications using specific schema. I don't wan't them to be
superuser, but I wan't them to be able to access any table in that
schema.

Because many users are able to create tables in that schema, I have to
write one ALTER DEFAULT PRIVILEGE foreach user.


Any chance to have superuser per schema ?


-- 
nicolas