Give default privileges to another SuperUser ?

Started by celati Laurentabout 4 years ago2 messagesgeneral
Jump to latest
#1celati Laurent
laurent.celati@gmail.com

Good morning,

I'm a superUser. I have defined different roles for several "simple" users.
They have rights for
read/select/use on any existing table. And also on any table that will be
generated in the future (DEFAULT PRIVILEGE).

Here is my method:

--FOR PUBLIC DIAGRAM

GRANT USAGE ON SCHEMA public TO "user";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "user";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "user";

--FOR OTHER XXX DIAGRAM

GRANT USAGE ON SCHEMA XXXX TO "user";
GRANT SELECT ON ALL TABLES IN SCHEMA XXX TO "user";
ALTER DEFAULT PRIVILEGES IN SCHEMA XXX GRANT SELECT ON TABLES TO "user";

This procedure works well when I perform the table creation operation (when
I am "grantor").
However, when my SuperUser colleague tries to create a table on his side,
with his own superUser connection role, the table is indeed created but is
only
visible by those who have a superUser connection role: simple users do not
seem to be able, by default, to view, access new tables
created by my colleague.

Does a person know how to give privileges to a SuperUser role so that when
he creates a new table, whether in the public schema or another schema, the
table will be by default visible, 'selectable' by everyone (by the other
roles superUser AND all 'simple' user roles that they only have 'usage'
privileges on schemas, ''select' privileges on tables).
Hoping to have been rather clear.
Thank you so much.

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: celati Laurent (#1)
Re: Give default privileges to another SuperUser ?

On Thu, Feb 17, 2022 at 2:14 PM celati Laurent <laurent.celati@gmail.com>
wrote:

ALTER DEFAULT PRIVILEGES IN SCHEMA XXX GRANT SELECT ON TABLES TO "user";

Does a person know how to give privileges to a SuperUser role so that when
he creates a new table, whether in the public schema or another schema, the
table will be by default visible,

ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]

This use case is why the "FOR target_role" clause exists.

David J.