I have a PG Database and create two Roles:
- Full access role to database and all schemas
- 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")?
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
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();