Postgresql – Postgres role does not inherit permissions from its member role

inheritancepermissionspostgresqlrole

I want to group my postgres database permissions to set of roles and then assign users to these parent roles to manage all permissions from a central place. I created a role called admins and granted permission as below (had to use EXECUTE command since my statement is dynamic. role_name is 'admins' and schem is 'core').

    EXECUTE format('CREATE ROLE %I WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION', role_name);

    EXECUTE format('GRANT ALL ON ALL SEQUENCES IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON SEQUENCES TO %I WITH GRANT OPTION', schem, role_name);

    EXECUTE format('GRANT ALL ON ALL TABLES IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO %I WITH GRANT OPTION', schem, role_name);

    EXECUTE format('GRANT ALL ON ALL FUNCTIONS IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON FUNCTIONS TO %I WITH GRANT OPTION', schem, role_name);

After creating the role, I created a user call 'kasun' and assign to the above created 'admin' role.

CREATE USER kasun WITH PASSWORD 'password' INHERIT;
GRANT admins to kasun

I could login to the database from user 'kasun', but still I can't run even a select statement. (this schema and tables are already there created by application using a high privileged user)

ERROR:  permission denied for schema core
LINE 1: select * from core.partners;

When I check the user settings it shows user 'kasun' has been created correctly and it is a member of role 'admins'. When I check the permission using following two queries, I could see 'admins' role has all the permission I granted, but role 'kasun' doesn't have any record.

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'admins';
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'kasun';

I can't think of anything I did wrong. Next option is to grant the permissions to each individual user. I don't like to go on that path when there is a clean way of doing this. I hope someone could shed some light to see what is going on here.
(I am running PostgreSQL 11.6 on Amazon RDS)

Thanks in advance!

Kasun

Best Answer

Thanks to one of my colleague, I could resolve this issue. You have to first grant permission to the schema. EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schem, role_name);.

Until then things won't work. Also, there was a typo in my passing parameter which I found after I passed this step.

I was debugging the issue in another direction since I was referring the admin user created by RDS when database getting created. That user has all the grant records in role_table_grants table. That is why I thought this is an inheritance issue. Good learning BTW :)

Related Question