Thread: default privileges are npt working
Hi,
I have a postgres instance running on version 15 in centos7.
I have created a custom database and revoked all public privileges from that database.
Then I have created a custom schema in that custom database.
Now I have created one writer user and one reader user by postgres superuser and then granted connect privileges on the database.
Then I have given all privileges of schema level and table level to the writer user so that it can create tables and insert data in the tables in that schema.
And for reader user I have granted usage only privileges on schema level and select privileges on table level so that it can only read the data of tables.
Then I granted default "select" privileges to reader user to read data of all tables created by writer user using below command:
alter default privileges in schema <custom schema> grant select on tables to <reader user>.
but when I am connected to the reader user I am not able to read the data inserted by the writer user and getting permission denied error.
I can only see the list of tables created by the writer user, not the data.
Am I missing something here? Please let me know.
My Goal: To read the data by reader user inserted by writer user.
Regards.
Hi.
I think the ALTER DEFAULT PRIVILEGES command affects only tables that are created after the command is executed. Tables created by the writer user before you executed the ALTER DEFAULT PRIVILEGES command would not automatically have select privileges granted to the reader user. You can try by explicitly granting select privileges on the existing tables to the reader user.On Fri, 30 Aug 2024 at 16:14, Atul Kumar <akumar14871@gmail.com> wrote:
Hi,I have a postgres instance running on version 15 in centos7.I have created a custom database and revoked all public privileges from that database.Then I have created a custom schema in that custom database.Now I have created one writer user and one reader user by postgres superuser and then granted connect privileges on the database.Then I have given all privileges of schema level and table level to the writer user so that it can create tables and insert data in the tables in that schema.And for reader user I have granted usage only privileges on schema level and select privileges on table level so that it can only read the data of tables.Then I granted default "select" privileges to reader user to read data of all tables created by writer user using below command:alter default privileges in schema <custom schema> grant select on tables to <reader user>.but when I am connected to the reader user I am not able to read the data inserted by the writer user and getting permission denied error.I can only see the list of tables created by the writer user, not the data.Am I missing something here? Please let me know.My Goal: To read the data by reader user inserted by writer user.Regards.
## Atul Kumar (akumar14871@gmail.com): > Then I granted default "select" privileges to reader *user *to read data of > all tables created by writer *user* using below command: > > alter default privileges in schema <custom schema> grant select on tables > to <reader user>. "ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.)" That's the first two sentences from https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html If you want to grant/revoke access on existing objects, use plain old GRANT/REVOKE: https://www.postgresql.org/docs/current/sql-grant.html Regards, Christoph -- Spare Space.
On Friday, August 30, 2024, Atul Kumar <akumar14871@gmail.com> wrote:
I have a postgres instance running on version 15 in centos7.I have created a custom database and revoked all public privileges from that database.
Would be better to provide the actual psql script of what you’ve done instead of writing it out in prose. That way the question of whether you’ve done something wrong or not is directly and accurately answerable
David J.