Sorry it is a typo in the email. My readonly role is dbreadonly. It works well so far except no access to new tables created by read write role. It has access to new tables created by admin role.
I granted dbreadonly as the following:
-- Read-only role
GRANT CONNECT ON DATABASE mydatabase TO dbreadonly;
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;
From: Erik Wienhold <ewie@ewie.name>
Date: Wednesday, August 23, 2023 at 1:57 PM
To: Hellen Jiang <hjiang@federatedwireless.com>, pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Read only user permission
> On 23/08/2023 18:42 CEST Hellen Jiang <hjiang@federatedwireless.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