I have a PG Database and create two Roles:

  1. Full access role to database and all schemas
  2. Read-Only access to all schemas

Then I create users and assign one of them the first role with full access and the other user the read-only access role.

Next I create a Table as User with full access, now the table is owned by the User.

Then I try to access the table (select) as read only user and get permission denied.

I already tried the following Code Snippets:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO READONLY_ROLE;
ALTER DEFAULT PRIVILEGES FOR ROLE FULL_ROLE IN SCHEMA public GRANT SELECT ON TABLES TO READONLY_ROLE;

I think the main Problem is, that the Table is not owned by the Role, it is owned by the user that has the Role assigned. But I don't want to connect ReadOnly User to Full User.

Isn't it possible to work with such a role based approach? Is it maybe possible to change the default owner of the tables and also feature tables to the full role and not the user (without "SET ROLE" before "CREATE TABLE")?


Update 25.04.2019

Here are the sql statements to generate the roles and users:

Create Write Role:

CREATE ROLE write_role;
GRANT ALL PRIVILEGES ON DATABASE test_db TO write_role;
GRANT ALL ON SCHEMA public TO write_role;

Create Read Role:

CREATE ROLE read_role;
GRANT CONNECT ON DATABASE test_db TO read_role;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_role;
ALTER DEFAULT PRIVILEGES FOR ROLE write_role IN SCHEMA public GRANT SELECT ON TABLES TO read_role;

Create Write User:

CREATE USER write_user WITH ENCRYPTED PASSWORD '';
GRANT write_role TO write_user;

Create Read User:

CREATE USER read_user WITH ENCRYPTED PASSWORD '';
GRANT read_role TO read_user;

Create Table as write_user and insert data:

CREATE TABLE link (
 ID serial PRIMARY KEY,
 url VARCHAR (255) NOT NULL,
 name VARCHAR (255) NOT NULL,
 description VARCHAR (255),
 rel VARCHAR (50)
);
INSERT INTO link (url, name)
VALUES
 ('http://www.postgresqltutorial.com','PostgreSQL Tutorial');

Information about the table:

\dt
                 List of relations
 Schema | Name | Type  |           Owner
--------+------+-------+---------------------------
 public | link | table | write_user

Select as read_user:

$ select * from link;
ERROR:  permission denied for table link

Update 01.05.2019

Thanks to a colleague at work we found a not Plan A but Plan Z solution that works. This article was the inspiration: Change Ownership via Trigger

The reason why this article is also very important is, that other users with write_role are also not able to delete tables created by another user.

Here is the event trigger I've adapted a bit:

CREATE OR REPLACE FUNCTION trg_create_set_owner()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO write_role', obj.object_identity);
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %s to read_role', obj.schema_name);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_set_owner
 ON ddl_command_end
 WHEN tag IN ('CREATE TABLE')
 EXECUTE PROCEDURE trg_create_set_owner();

1 Answers

0
Jeremy On

A few things to check:

  • Did you create the table in the public schema? If not, does READONLY_ROLE have USAGE privileges on the schema in which the table resides?
  • ALTER DEFAULT PRIVILEGES only changes privileges for objects created after this is run. Did you create your table before running this?
  • Are you trying to select from the table with the readonly_role or a user that has the readonly_role assigned to it? If the latter, does the user inherit privileges (the default)?

EDIT: Thanks for all the additional info. ALTER DEFAULT PRIVILEGES only works for one role and is not inherited (if a user was a member of multiple roles with different default privileges, I think it wouldn't be trivial to figure out what privileges to apply to new objects).

Before creating the table as write user, just run:

SET ROLE write_role;