Re: Default Privilege Table ANY ROLE

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