hope you can help me.
I have 3 users
- First is me, the db admin. I run the GRANT queries, I can do anything. (name doesn't matter now)
- Second is the data manager user. = manager_u
- Third is the data viewer user. = viewer_u
The "data viewer" should read from views created by "data manager", without managing privileges afterwards
For that reason I create a schema, owned by manager, that should be full open to the viewer.
The problem/actual situation is (after running my following code) this.
When manager_u create a named view, viewer_u cannot SELECT from it.
If I run this, AFTER the creation:
GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;
he can read. Everything works.
But I like that he can read any further views without managing his privileges.
I tried with that, with no success:
ALTER DEFAULT PRIVILEGES FOR ROLE viewer_u IN SCHEMA views_for_viewer_u GRANT SELECT ON TABLES TO viewer_u;
Here, the complete code:
--
-- QUERIES as db admin
--
CREATE ROLE manager_u LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE viewer_u LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
ALTER ROLE manager_u WITH PASSWORD 'yyy';
ALTER ROLE viewer_u WITH PASSWORD 'xxx';
GRANT CONNECT ON DATABASE mydb TO manager_u;
GRANT CONNECT ON DATABASE mydb TO viewer_u;
---
--- schema
---
CREATE SCHEMA IF NOT EXISTS views_for_viewer_u;
ALTER SCHEMA views_for_viewer_u OWNER TO manager_u;
GRANT ALL PRIVILEGES ON SCHEMA views_for_viewer_u TO manager_u;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA views_for_viewer_u TO manager_u;
ALTER DEFAULT PRIVILEGES FOR ROLE manager_u IN SCHEMA views_for_viewer_u GRANT ALL PRIVILEGES ON TABLES TO manager_u;
---
--- privileges on views_for_viewer_u schema
--- I think the problem is here but I'm going crazy
--- I beg help
---
GRANT USAGE ON SCHEMA views_for_viewer_u TO viewer_u;
GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;
ALTER DEFAULT PRIVILEGES FOR ROLE viewer_u IN SCHEMA views_for_viewer_u GRANT SELECT ON TABLES TO viewer_u;
--
-- QUERIES as data manager: manager_u
--
CREATE OR REPLACE VIEW views_for_viewer_u.proxy_table
AS SELECT * FROM another_schema.a_table;
--
-- QUERIES as viewer: viewer_u
--
SELECT * FROM views_for_viewer_u.proxy_table; -- this is what is not working
--
--
-- thanks to anyone can give an hint
--
--
Best Answer
Your mistake is in the
ALTER DEFAULT PRIVILEGES
command. It should beALTER DEFAULT PRIVILEGES FOR ROLE manager_u ...
This way, the views created by
manager_u
will get the permission granted.