> On 23/08/2023 18:42 CEST Hellen Jiang <hjiang@xxxxxxxxxxxxxxxxxxxxx> wrote: > > 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; It says "dbreadonly" instead of "readonly". Just a typo or a different role? > -- 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; -- Erik
