1

I created a role and set privileges to tables, then created a user and grant the role to it by postgres user.

After the user login database, it can't select any tables at all.

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

CREATE USER user1 WITH PASSWORD '123';
GRANT readonly TO user1;

When check table privileges, I can't see the new user is been granted there:

SELECT * FROM information_schema.table_privileges where grantee='user1';
(null)

Why the grant command doesn't work?

PS: This way works in development environment but doesn't work in production environment.

4
  • 1
    Yes, no privilege was granted to user1. Still, the user will be able to SELECT, because it inherits the privileges from readonly. Dec 19, 2023 at 11:47
  • @LaurenzAlbe If login with user1, why it can't SELECT? Dec 21, 2023 at 5:07
  • 1
    Either the table is in a different schema, or you didn't run the statements in your question, or you ran the statements in a different database. Dec 21, 2023 at 6:55
  • @LaurenzAlbe After 2 days, I can select by the new user. I think if there is something cache in the database environment so that didn't grant successfully. Dec 26, 2023 at 1:13

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.