0

I need to grant SELECT privileges in a specific schema to a specific user. I use the following command:

GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser;

The command works fine, but only for tables that were already created. It doesn't however auto grant privileges when a new table is added. To fix it, I changed the default settings (docs):

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO myuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON SEQUENCES TO myuser;

But the commands above don't solve the issue. But if grant privileges ALL, not SELECT, everything is ok:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON TABLES TO myuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON SEQUENCES TO myuser;

How can I grant to myuser only SELECT privileges for all the tables that will be created later in schema myschema?

Community
  • 1
  • 1
Andrey Frost
  • 23
  • 1
  • 6
  • Do you have a list of command to reproduce your problem? It seems to work fine for me. Also, which version are you running? – Marth Feb 02 '20 at 13:36
  • You have to `ALTER DEFAULT PRIVILEGES FOR` the `ROLE` that will create the tables. – Laurenz Albe Feb 02 '20 at 16:48

0 Answers0