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 lasun 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

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