Postgresql 9.* – user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE

permissionspostgresqlschema

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 be

ALTER DEFAULT PRIVILEGES FOR ROLE manager_u ...

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