5

I read the following question before: Grant privileges on future tables in PostgreSQL?.

But if I run the following query while logged in as rbourgeon (which is a superuser):

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

I notice that the user username will have the privileges only on the future tables created by me, and not on all the future tables.

The official PostgreSQL documentation, on this issue, reads:

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.) Currently, only the privileges for tables (including views), sequences, and functions can be altered.

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. The privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas. Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type.

Is there a workaround? Because many users may create tables in this database.

R. Bourgeon
  • 923
  • 1
  • 9
  • 25
  • well, this can be seen as a problem from practical point of view of small company because it mean to include granting into tasks but from security point of view of a really big company it makes sense because other users could be surprised that their objects are visible to others - see here https://stackoverflow.com/questions/21513996/alter-default-privileges-for-a-group-role-in-postgresql – JosMac Jun 07 '18 at 15:04
  • You will need to use the `target_role` (`ALTER DEFAULT PRIVILEGES FOR xyz …`) to change the default privileges for all users that may create tables. – Bergi Nov 15 '19 at 18:59

0 Answers0