PostgreSQL : Accessing tables owned by another user

Viewed 5112

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
1 Answers
Related