Re: How to remove user specific grant and revoke

> 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