PostgreSQL – Permission Issues for New Views

postgresql

I have a RDS postgres database that only uses the public schema.

I expect the webservice user to get access to all newly created views. When I create a view using the master user, I see all the grants as expected.

Here are the DEFAULT PRIVILEGES I have setup.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES TO webservice;

When the rormigration user creates a view in the public schema, I see it is owned by rormigration and none of the DEFAULT PRIVILEGES are present. I have to switch the owner to master and grant rights.

What can I do to fix this permission issues?

Best Answer

As the documentation says in https://www.postgresql.org/docs/11/sql-alterdefaultprivileges.html:

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.

So the best option is to create a group, put webservice and the user that create the views in that group and then apply the ALTER DEFAULT PRIVILEGES.

Related Solutions

PostgreSQL – Resolving ‘Alter Default Privileges’ Issue

Either you've forgot to add a permission to SELECT in the GRANT below, quoted from the question:

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw;

... or, if you really meant that having the role access1_rw doesn't let read the tables even tough it lets write to them, then you must grant the role access1_ro to user1 (in addition to granting the role access1_rw).

Not having either of those has the consequence that user1 was never granted the right to SELECT from tables in the mentioned schema.

PostgreSQL – How to Grant Privileges as Superuser

ALTER DEFAULT PRIVILEGES without a FOR ROLE clause alters the default privileges for objects created by only the current role (the role executing the ALTER DEFAULT PRIVILEGES). So in the first case, you altered the superuser's default privileges, which has no effect on testuser's default privileges.