0

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?

0

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.

0

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;
  • Why don't my DEFAULT PRIVILEGES on public create grants when rormigration creates the view? They work fine when master does this. When I run GRANT SELECT ON TABLE public.test TO webservice; I get the error WARNING: no privileges were granted for "test" I have switch the owner from rormigration to master before these grants work. – Deerup Aug 14 at 19:34
  • try ALL PRIVILEGES i dont understand your explanations... who grant who and what did you expect ? – eytienne Aug 14 at 19:50
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.