Postgres permission issues for new views

Question

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?

Answer 1

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.

Answer 2

DEFAULT PRIVILEGES only apply to objects that the user creates. I didn't have DEFAULT PRIVILEGES setup for rormigration so users didn't get rights to objects created by rormigration.

I reviewed DEFAULT PRIVILEGES and then ran the missing grants while logged in as rormigration.

SELECT *
FROM pg_default_acl acl
JOIN pg_namespace ns ON ns.oid = acl.defaclnamespace;

Answer 3

i think you're looking to make a group :

create role webserviceGroup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO webserviceGroup;

create role webservice in group webserviceGroup;
create role rormigration in group webservice;