0

I want to create default permissions on the schema and automatically assign these permissions to newly created tables. For this purpose, I've created a role and assign a user. Afterward, I've created default permission with the following statements. But there are no default permissions in the newly created tables. How can I manage users and roles on the newly created tables?

CREATE SCHEMA ${db_schema};

#for create read only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE ${db_name} TO readonly;
GRANT USAGE ON SCHEMA  ${db_schema} to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA ${db_schema} TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA ${db_schema} TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA ${db_schema} GRANT SELECT ON TABLES TO readonly;

#for create read write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE ${db_name} TO readwrite;
GRANT USAGE, CREATE ON SCHEMA ${db_schema} TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ${db_schema} TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA ${db_schema} GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA ${db_schema} TO readwrite;
grant readwrite to ${db_user_name}
ALTER DEFAULT PRIVILEGES IN SCHEMA ${db_schema} GRANT USAGE ON SEQUENCES TO readwrite;

# create a user and assign read-write role
create user appuser with password 'secret_password'
grant readwrite to appuser

# create a user and assign read-write role
create user reportuser with password 'secret_password'
grant readonly to reportuser

# create a table with appuser who has the read-write permission
CREATE TABLE usertestscehema.accounts (
username VARCHAR ( 50 ) UNIQUE NOT NULL
);
insert into accounts (username) values ('test');

# select table with reportuser who has the read-only permission
select * from accounts

Finally, I got the below error;

SQL Error [42501]: ERROR: permission denied for table accounts

Afterward, I examined the permissions with the following statement and I didn't see any permission related to reportuser who has my readonly user.

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='accounts'

Where am I missing about this problem?

New contributor
arifisik is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • You need to use ALTER DEFAULT PRIVILEGES FOR ROLE whoever_creates_the_table. Group membership doesn't count. – Laurenz Albe May 3 at 8:05