Re: Read only user permission - Mailing list pgsql-general

From jian he
Subject Re: Read only user permission
Date
Msg-id CACJufxHb2VaXDUuCRe_DkLK_y=56a2K_3bReS9B8BPJxdW16Ag@mail.gmail.com
Whole thread Raw
In response to Re: Read only user permission  (Marcos Pegoraro <marcos@f10.com.br>)
List pgsql-general
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;



pgsql-general by date:

Previous
From: Tushar Takate
Date:
Subject: Re: PostgreSQL DB cluster migration from centos7/RHEL7/OEL7 to RHEL8
Next
From: Adam Lee
Date:
Subject: Re: [EXTERNAL] Oracle FDW version