Hello everyone, I have 3 users in my database: A_User, B_User.

Question

Hello everyone, I have 3 users in my database: A_User, B_User.

And postgres user by default.

The authorizations I give are as follows:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO A_User;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO A_User;

Here is the result I did not expect:
1.A_User can access existing tables.
2.A_User can access the new tables created by postgres user.
3.BUT A_User cannot access the new tables created by B_User.

Shouldn't A_User have access to the new tables created by B_User?

#programming #russian

0

25.09.2024

4 ответов

5 просмотров

Sercan Автор вопроса

Stefanie Janine Stölting
Here is what the documentation is saying : target_...

Then the authorizations I gave with “ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO A_User;” do not cover all objects under that schema. It only covers the objects owned by the postgres user? So, for READ users can access each new table, while authorizing my user with DDL authorization “ALTER DEFAULT PRIVILEGES FOR ROLE B_User GRANT SELECT ON TABLES TO A_User;” ?

0

1 месяц назад

Stefanie Janine Stölting

Sercan
Then the authorizations I gave with “ALTER DEFAULT...

As documented, all tables for the role that executed the ALTER DEFAULT PRIVILEGES, or for the given role name. And you can create a role without login and use it like a group. If you alter default privileges for these roles, members of that group role have the rights inherited.

0

1 месяц назад

Sercan Автор вопроса

Stefanie Janine Stölting
As documented, all tables for the role that execut...

Thank you, I learned what I thought I knew. So, do these two codes work the same for A_User to access the new tables created by B_User: SET ROLE B_User; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO A_User; and ALTER DEFAULT PRIVILEGES FOR ROLE B_User IN SCHEMA public GRANT SELECT ON TABLES TO A_User;

0

1 месяц назад

Stefanie Janine Stölting · Accepted Answer

Stefanie Janine Stölting

Here is what the documentation is saying : target_role Change default privileges for objects created by the target_role, or the current role if unspecified. https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html You have altered the default privileges while connected with role postgres. Therefore the changes are only applied for tables owned by the role postgres.

0

1 месяц назад

169 похожих чатов

Hello everyone, I have 3 users in my database: A_User, B_User.

4 ответов

Похожие вопросы