1

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

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.

  • 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. – Gromish May 22 at 17:35
  • 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. – Laurenz Albe May 22 at 19:12
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.