Thread: Read only user permission

Hellen Jiang <hjiang@federatedwireless.com> writes:
> I have created readonly and readwrite roles with the following grants: however, readonly user does not have access to
thenew 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
readwriteuser? 

The readwrite user (not the admin) would need to issue ALTER DEFAULT
PRIVILEGES granting that.

            regards, tom lane

> 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

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:DengXian; panose-1:2 1 6 0 3 1 1 1 1 1;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}@font-face {font-family:"\@DengXian"; panose-1:2 1 6 0 3 1 1 1 1 1;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:10.0pt; font-family:"Calibri",sans-serif;}span.EmailStyle19 {mso-style-type:personal-reply; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt; mso-ligatures:none;}div.WordSection1 {page:WordSection1;}

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;

 

On Thu, Aug 24, 2023 at 2:49 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
>
> Have you tried grant pg_read_all_data to readonly ?
>

I tried. seems pg_read_all_data really means "read all data no matter what".
so you cannot grant pg_read_all_data then revoke certain schema privileges.

begin;
    create user my_user login;
    GRANT pg_read_all_data TO my_user;
    create schema a;
    create schema b;
    create table a.a as select 1;
    create table b.b as select 2;
    revoke all PRIVILEGES on schema a, b from my_user CASCADE;
    -- revoke all PRIVILEGES on schema a from my_user CASCADE;
    revoke all PRIVILEGES on all tables in schema a,b from my_user CASCADE;
    set role my_user;
    table a.a;
    table b.b;
rollback;

> On 24/08/2023 10:11 CEST Dominique Devienne <ddevienne@gmail.com> wrote:
>
> On Wed, Aug 23, 2023 at 7:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > The readwrite user (not the admin) would need to issue ALTER DEFAULT
> > PRIVILEGES granting that.
>
> Hi. I'm confused Tom. Why should it matter who created the new tables?
>
> I'm also using DEFAULT PRIVILEGES, for the very reason they dynamically
> extend to newly created objects in the schema.
> And I don't remember reading anything like what you imply. Could you please
> pinpoint in the doc where this would be explained?
>
> DEFAULT PRIVILEGES only apply to DDLs made by the role who did the GRANT'ing?
>
> In my case, the schema owner does both the granting and DDLs, so I guess I'll
> be OK.
> I just want to make sure I understand the situation better. Thanks, --DD

This is implied by:

    "You can change default privileges only for objects that will be
     created by yourself or by roles that you are a member of."

https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html#SQL-ALTERDEFAULTPRIVILEGES-DESCRIPTION

--
Erik