Re: How to remove user specific grant and revoke - Mailing list pgsql-general

From Erik Wienhold
Subject Re: How to remove user specific grant and revoke
Date
Msg-id 1147003960.809525.1685838992758@office.mailbox.org
Whole thread Raw
In response to Re: How to remove user specific grant and revoke  (Andrus <kobruleht2@hot.ee>)
List pgsql-general
> On 04/06/2023 00:08 CEST Andrus <kobruleht2@hot.ee> wrote:
>
> > Should I ran separate revoke commands for every user to remove those
> > revokes ?
> > How to remove user-spefic grants ?
> After running revoke commands in psql, GRANT commands disappeared magically.
> It looks like pgAdmin does not allow execute REVOKO commands.

I don't think so.  There's nothing special about REVOKE that pgAdmin may
disallow.

> After running script which adds user group tabel modification rights for
> admin users:
> CREATE POLICY kaspriv_sel_policy ON kaspriv FOR SELECT USING (true);
>  CREATE POLICY kaspriv_mod_policy ON kaspriv USING (
>  lower(kasutaja)= current_user OR kasutaja in
>  ( select kasutaja from kasutaja where ','||firmad||','
>  LIKE '%,'|| (select firmad from kasutaja where lower(kasutaja)= current_user) || ',%'
>  )
>  );
>  ALTER TABLE kaspriv ENABLE ROW LEVEL SECURITY;
>  revoke all on kaspriv from public;
>  grant select on kaspriv to public;
>  grant insert, update, delete on kaspriv to admin1, admin2;
>
> pgAdmin shows revoke commands for those users:
> REVOKE ALL ON TABLE public.kaspriv FROM admin1;
>  REVOKE ALL ON TABLE public.kaspriv FROM admin2;
> How to prevent pgAdmin to show those revokes?

pgAdmin includes the REVOKE commands so that roles will only get the privileges
listed in the subsequent GRANT commands when executing that script.  This makes
sure that the script will reproduce the current privileges regardless of what
may be granted at some later point (in case of an already existing table and
CREATE TABLE IF NOT EXISTS is used) and regardless of any default privileges
that may be defined when creating a new table.

--
Erik



pgsql-general by date:

Previous
From: Erik Wienhold
Date:
Subject: Re: How to remove user specific grant and revoke
Next
From: Steve Baldwin
Date:
Subject: Connection error to new pg15 instance