Read only user permission

[Hellen Jiang <hjiang@xxxxxxxxxxxxxxxxxxxxx>]

Hi,

 

I have created readonly and readwrite roles with the following grants: however, readonly user does not have access to the new tables created by readwrite user. (readonly user has the access to new tables created by admin).

Any idea how I can grant the access to readonly to make sure it has the read access to NEW tables created by readwrite user?

 

-- Read-only role

GRANT CONNECT ON DATABASE mydatabase TO readonly;

GRANT USAGE ON SCHEMA public TO dbreadonly;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbreadonly;

 

-- Read/write role

GRANT CONNECT ON DATABASE mydatabase TO readwrite;

GRANT USAGE, CREATE ON SCHEMA public TO readwrite;

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO readwrite;

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE,TRUNCATE ON TABLES TO readwrite;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO readwrite;