postgresql 9.* - user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE

Question

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

Answer 1

Default privileges are given at the creation moment based on the creator role.

FOR ROLE xxx

stands for that.

the problem is here: xxx should be who will make the creation query... if "FOR ROLE xxx" is omitted, "FOR ROLE actual_role" will be implicit: db admin, in my case.

So I had to do that.

because manager_u is who will create the tables...

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;
--- here
ALTER DEFAULT PRIVILEGES FOR ROLE manager_u IN SCHEMA views_for_viewer_u GRANT 
SELECT ON TABLES TO viewer_u;

Answer 2

Your mistake is in the ALTER DEFAULT PRIVILEGES command. It should be

ALTER DEFAULT PRIVILEGES FOR ROLE manager_u ...

This way, the views created by manager_u will get the permission granted.

• Gromish: Hello, first, thanks for your kind reply. I tried with the advice you gave (and I corrected the question after your suggestion), but didn't worked. The main problem is the viewer privileges, not the manager privileges. I think that after giving the ownership (I do) of the schema to the manager, his privileges are already enough. How can I give SELECT privileges (only) to a viewer for future VIEW(s) inside a schema? I'm a bit confused.
• Laurenz Albe: Then you actually did something different from what you said, because that would definitely be the right thing. Check the default privileges with \ddp in psql.