Re: Read only user permission - Mailing 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;