Re: Default Privilege Table ANY ROLE

[Laurenz Albe <laurenz.albe@xxxxxxxxxxx>]

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