From: | Hellen Jiang <hjiang(at)federatedwireless(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Read only user permission |
Date: | 2023-08-23 16:42:06 |
Message-ID: | BLAPR17MB419317757B0A5D24F5076CBCD21CA@BLAPR17MB4193.namprd17.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Marcos Pegoraro | 2023-08-23 17:43:21 | Re: Read only user permission |
Previous Message | Christoph Moench-Tegeder | 2023-08-23 16:03:23 | Re: archive_command debugging |