Have you tried grant pg_read_all_data to readonly ? regards Marcos
Em qua., 23 de ago. de 2023 às 14:30, Hellen Jiang < hji...@federatedwireless.com> escreveu: > 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; > > > > >
