Re: Read only user permission

On Thu, Aug 24, 2023 at 2:49 AM Marcos Pegoraro <marcos(at)f10(dot)com(dot)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;