I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL
privileges and johnrole has only been granted the SELECT
privilege.
Problem is when a user from janerole creates a new table, then a user from johnrole can't SELECT
from the new table.
ERROR: permission denied for relation mopery
I tried to use ALTER DEFAULT
in different ways to automate that:
ALTER DEFAULT PRIVILEGES FOR ROLE johnrole IN SCHEMA public
GRANT SELECT ON TABLES TO johnrole;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO johnrole;
ALTER DEFAULT PRIVILEGES FOR ROLE johnrole GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO GROUP johnrole;
None of them work and I still end up with the same error.
When I use ALTER DEFAULT
to automate future tables privileges for user e.g john then everything works, he can SELECT
from new tables created in the database without any error.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO john;
This is how I added people to roles:
GRANT johnrole TO adam;
GRANT janerole TO eva;
Granted usage on Blackacre database like this:
GRANT USAGE ON SCHEMA public TO johnrole;
GRANT USAGE ON SCHEMA public TO janerole;
Gave some privileges like that:
GRANT ALL ON ALL TABLES IN SCHEMA public TO janerole;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO johnrole;
After that I tried to give automatic privileges with ALTER DEFAULT
as above described don't work. Any ideas how to get default privileges working properly?