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 -- --