From: | Erik Wienhold <ewie(at)ewie(dot)name> |
---|---|
To: | Hellen Jiang <hjiang(at)federatedwireless(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Read only user permission |
Date: | 2023-08-23 17:51:13 |
Message-ID: | 2050922589.351126.1692813073578@office.mailbox.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 23/08/2023 18:42 CEST Hellen Jiang <hjiang(at)federatedwireless(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Hellen Jiang | 2023-08-23 19:23:34 | Re: Read only user permission |
Previous Message | Tom Lane | 2023-08-23 17:46:29 | Re: Read only user permission |