Обсуждение: Give default privileges to another SuperUser ?
Good morning,
Here is my method:
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.
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).
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.
Hoping to have been rather clear.
Thank you so much.
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 [, ...] ]
[ FOR { ROLE | USER } target_role [, ...] ]
This use case is why the "FOR target_role" clause exists.
David J.