postgresqlrolesowner

PostgreSQL : Accessing tables owned by another user

As a Superuser, I have created two roles in Postgres on the same schema:

  1. read_only_with_create_view
  2. read_write

Then I created two users from each role:

  1. read_only_with_create_view_user
  2. read_write

Now any new views created by read_only_with_create_view_user cannot be accessed by read_write_user as the owner for views is different (read_only_with_create_view_user).

So what is the way to access all new views by read_write_user?

I want everything created by one user to be accessible to another user.

Steps I followed:

CREATE ROLE read_only_role WITH
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

GRANT CONNECT ON DATABASE mydb to read_only_role;
GRANT USAGE,CREATE ON SCHEMA myschema TO read_only_role;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO read_only_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA myschema TO read_only_role;

CREATE USER read_only_with_create_view_user
WITH PASSWORD '*****'
in ROLE read_only_role;

-- Now created new views using this role. That means read_only_with_create_view_user is owner of those views.

-- Creating new read-write role. 

CREATE ROLE rw_role WITH
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity' IN ROLE read_only_role;

GRANT CONNECT ON DATABASE mydb to rw_role;

GRANT USAGE ON SCHEMA myschema TO crn_rw_role_qa;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema TO rw_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschema TO rw_role;

CREATE USER read_write_user
WITH PASSWORD '*****'
in role rw_role;

After login with read_write_user, when I try to access new views created by read_only_with_create_view_user, I get this error:

ERROR:  permission denied for relation view_name
********** Error **********

ERROR: permission denied for relation view_name
SQL state: 42501
Solution

  • You can set a role as a member of another role:

    GRANT read_only_with_create_view_user TO read_write_user;

    More info here.

    EDIT

    It will never work as you expect. See your current user schema:

    enter image description here

    Role read_write_user will cannot access objects owned by read_only_with_create_view_user simply because both don't have any relationship. To make this works as you expect you can reassign objects ownership to an "upper" level role, in this case: read_only_role (because everybody is a member of this role). But be warned that it will no longer be a read-only role.

    You can do one of the following:

    --Connected as read_only_with_create_view_user
CREATE VIEW my_view AS SELECT 1;

--Assign ownership to top-level role
ALTER VIEW my_view OWNER TO read_only_role;

    Or you may prefer this approach:

    --Connected as read_only_with_create_view_user
--Change current user to read_only_role
SET role = read_only_role;

--Create a view...
CREATE VIEW my_view AS SELECT 1;

--Turn back to read_only_with_create_view_user
RESET role;

    If you prefer to do all at once you can reassign ownership of objects owned by read_only_with_create_view_user to your top-level role in just one command:

    REASSIGN OWNED BY read_only_with_create_view_user TO read_only_role;

    Finally, if you don't want to break your read-only rule you can also, of course, give permission directly to the object.

    -- As read_only_with_create_view_user, execute the following:
CREATE VIEW my_view_2 AS SELECT 1;
GRANT SELECT ON my_view_2 TO read_write_user