Thread: How to remove user specific grant and revoke

How to remove user specific grant and revoke

From

Andrus

Date:

03 June 2023, 10:16:54

User groups table is defined as

    CREATE TABLE IF NOT EXISTS public.kaspriv
    (
        id serial primary key,
        user character(10) NOT NULL,
        group character(35) NOT NULL
    ...
    )

There are hundreds of users. Earlier time grant and revoke commands were executed for every user separately. Later revoke and grant commands for public were added:

    REVOKE ALL ON TABLE public.kaspriv FROM PUBLIC;
    GRANT SELECT ON TABLE public.kaspriv TO PUBLIC;

pgAdmin SQL tab still shows revoke and grant commands for every user also:

    REVOKE ALL ON TABLE public.kaspriv FROM PUBLIC;
    REVOKE ALL ON TABLE public.kaspriv FROM someuser;
    REVOKE ALL ON TABLE public.kaspriv FROM someotheruser;
    ...
    GRANT SELECT ON TABLE public.kaspriv TO PUBLIC;
    GRANT SELECT ON TABLE public.kaspriv TO someuser;
    GRANT SELECT ON TABLE public.kaspriv TO someother;
    ...

How to remove those unnecessary user-specific GRANT and REVOKE commands to make rights cleaner? pgAdmin does not have delete option for those.

Something like

    DROP REVOKE ALL ON TABLE public.kaspriv FROM all EXCEPT public;
    DROP GRANT SELECT ON TABLE public.kaspriv FROM all EXCEPT public;

This will be one-time action. It can be done manually in pgadmin or using some script running once.

Using

    PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu,
    compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

and latest pgAdmin 7.2

Posted also in https://stackoverflow.com/questions/76394896/how-to-remove-unnecessary-grant-and-revoke-privileges-from-table

Andrus.

Re: How to remove user specific grant and revoke

From

Erik Wienhold

Date:

03 June 2023, 15:46:16

> On 03/06/2023 09:16 CEST Andrus <kobruleht2@hot.ee> wrote:
>
> User groups table is defined as
>
> CREATE TABLE IF NOT EXISTS public.kaspriv
> (
> id serial primary key,
> user character(10) NOT NULL,
> group character(35) NOT NULL
> ...
> )
>
> There are hundreds of users. Earlier time grant and revoke commands were
> executed for every user separately. Later revoke and grant commands for
> public were added:
>
> REVOKE ALL ON TABLE public.kaspriv FROM PUBLIC;
> GRANT SELECT ON TABLE public.kaspriv TO PUBLIC;
>
> pgAdmin SQL tab still shows revoke and grant commands for every user also:
>
> REVOKE ALL ON TABLE public.kaspriv FROM PUBLIC;
> REVOKE ALL ON TABLE public.kaspriv FROM someuser;
> REVOKE ALL ON TABLE public.kaspriv FROM someotheruser;
> ...
> GRANT SELECT ON TABLE public.kaspriv TO PUBLIC;
> GRANT SELECT ON TABLE public.kaspriv TO someuser;
> GRANT SELECT ON TABLE public.kaspriv TO someother;
> ...
>
> How to remove those unnecessary user-specific GRANT and REVOKE commands to
> make rights cleaner? pgAdmin does not have delete option for those.

When you run

    REVOKE SELECT ON TABLE public.kaspriv FROM someuser;

does it also remove the accompanying REVOKE ALL statement for that user?
That REVOKE SELECT should remove the ACL for someuser from pg_class.relacl and
pgAdmin should no longer find any ACL for that role and thus no longer emit
REVOKE ALL.

> Something like
>
> DROP REVOKE ALL ON TABLE public.kaspriv FROM all EXCEPT public;
> DROP GRANT SELECT ON TABLE public.kaspriv FROM all EXCEPT public;
>
> This will be one-time action. It can be done manually in pgadmin or using
> some script running once.

Automate this with aclexplode[0] to get the privileges for specific grantees.
Loop over the result set in a DO block, generate the REVOKE commands, and
EXECUTE them.

    SELECT acl.grantee::regrole, acl.privilege_type
    FROM pg_class, aclexplode(relacl) acl
    WHERE oid = 'public.kaspriv'::regclass;

> Using
>
> PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
>
> and latest pgAdmin 7.2

[0] https://www.postgresql.org/docs/12/functions-info.html#FUNCTIONS-ACLITEM-FN-TABLE

--
Erik

Re: How to remove user specific grant and revoke

From

Erik Wienhold

Date:

03 June 2023, 16:07:52

> On 03/06/2023 14:46 CEST Erik Wienhold <ewie@ewie.name> wrote:
>
> > On 03/06/2023 09:16 CEST Andrus <kobruleht2@hot.ee> wrote:
> >
> > DROP REVOKE ALL ON TABLE public.kaspriv FROM all EXCEPT public;
> > DROP GRANT SELECT ON TABLE public.kaspriv FROM all EXCEPT public;
> >
> > This will be one-time action. It can be done manually in pgadmin or using
> > some script running once.
>
> Automate this with aclexplode[0] to get the privileges for specific grantees.
> Loop over the result set in a DO block, generate the REVOKE commands, and
> EXECUTE them.
>
>     SELECT acl.grantee::regrole, acl.privilege_type
>     FROM pg_class, aclexplode(relacl) acl
>     WHERE oid = 'public.kaspriv'::regclass;

Or just execute those REVOKE ALL commands (except for PUBLIC) that pgAdmin
already gives you.

--
Erik

Re: How to remove user specific grant and revoke

From

Andrus

Date:

03 June 2023, 19:14:03

Hi!

Or just execute those REVOKE ALL commands (except for PUBLIC) that pgAdmin
already gives you.

pgAdmin gives

REVOKE ALL ON TABLE public.kaspriv FROM someuser;

I ran it but pgAdmin still gives this statement.

Andrus.

Re: How to remove user specific grant and revoke

From

Erik Wienhold

Date:

03 June 2023, 19:38:10

> On 03/06/2023 18:14 CEST Andrus <kobruleht2@hot.ee> wrote:
>
> > Or just execute those REVOKE ALL commands (except for PUBLIC) that pgAdmin
> > already gives you.
> pgAdmin gives
> REVOKE ALL ON TABLE public.kaspriv FROM someuser;
> I ran it but pgAdmin still gives this statement.

What are the current table privileges when you run this in psql:

    \dp public.kaspriv

--
Erik

Re: How to remove user specific grant and revoke

From

Andrus

Date:

03 June 2023, 23:33:22

Hi!

I tried

alter role alekspoluh reset all

After this command pgAdmin still shows revoke and grant commands for alekspoluh role.

How to remove all grant and revoke assignments for role ?

Andrus.

03.06.2023 20:50 Andrus kirjutas:

Hi!
> REVOKE must be executed by the grantor (sba_owner) or a superuser in case you
not already tried that.  
REVOKE was executed by superuser, postgres. PgAdmin uses this user to log on.
Do you get any errors?
There are no errors.
Andrus.

Re: How to remove user specific grant and revoke

From

Erik Wienhold

Date:

03 June 2023, 23:57:56

> On 03/06/2023 22:33 CEST Andrus <kobruleht2@hot.ee> wrote:
>
> I tried
> alter role alekspoluh reset all

This only resets role-specific settings, not privileges.

> After this command pgAdmin still shows revoke and grant commands for
> alekspoluh role.
> How to remove all grant and revoke assignments for role ?

Please confirm that \dp public.kaspriv no longer shows an ACL for alekspoluh
after running:

    REVOKE ALL ON public.kaspriv FROM alekspoluh;

There must be something wrong with pgAdmin if it still shows REVOKE ALL for
that role after its ACL is gone.  Looking at the code, pgAdmin emits REVOKE ALL
for any grantee it find in the ACL.

https://github.com/pgadmin-org/pgadmin4/blob/REL-7_2/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py#L712

--
Erik

Re: How to remove user specific grant and revoke

From

Andrus

Date:

04 June 2023, 00:34:32

Hi!

After this command pgAdmin still shows revoke and grant commands for
alekspoluh role.
How to remove all grant and revoke assignments for role ?

Please confirm that \dp public.kaspriv no longer shows an ACL for alekspoluh
after running:
	REVOKE ALL ON public.kaspriv FROM alekspoluh;

psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.

sba=# REVOKE ALL ON public.kaspriv FROM alekspoluh;
REVOKE
sba=# \o result.txt
sba=# \dp public.kaspriv

sba=# \q

#grep alekspoluh result.txt

Returns nothing. So output does not contain this role.

There must be something wrong with pgAdmin if it still shows REVOKE ALL for
that role after its ACL is gone.  Looking at the code, pgAdmin emits REVOKE ALL
for any grantee it find in the ACL.

https://github.com/pgadmin-org/pgadmin4/blob/REL-7_2/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py#L712

I re-opened pgadmin. alekspoluh role is no more displayed in kaspriv table sql window.

pgadmin shows only single reset role command. Now it shows

REVOKE ALL ON TABLE public.kaspriv FROM yllelohmus;

I ran

REVOKE ALL ON TABLE public.kaspriv FROM yllelohmus;

After that pgadmin shows next single revoke command:

REVOKE ALL ON TABLE public.kaspriv FROM villuuus;

It looks like pgAdmin shows only one REVOKE command but actually there are more revokes.

Should I ran separate revoke commands for every user to remove those revokes ?

pgAdmin also shows about 100 grant commands for users like

GRANT SELECT ON TABLE public.kaspriv TO paide;

How to remove user-spefic grants ?

Andrus.

Re: How to remove user specific grant and revoke

From

Andrus

Date:

04 June 2023, 01:08:46

Hi!

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.

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?

Andrus.

Re: How to remove user specific grant and revoke

From

Erik Wienhold

Date:

04 June 2023, 03:36:07

> On 03/06/2023 23:34 CEST Andrus <kobruleht2@hot.ee> wrote:
>
> psql (12.2 (Debian 12.2-2.pgdg100+1))
>  Type "help" for help.
>
>  sba=# REVOKE ALL ON public.kaspriv FROM alekspoluh;
>  REVOKE
>  sba=# \o result.txt
>  sba=# \dp public.kaspriv
>
> sba=# \q
> #grep alekspoluh result.txt
> Returns nothing. So output does not contain this role.
>
> I re-opened pgadmin. alekspoluh role is no more displayed in kaspriv table
> sql window.
> pgadmin shows only single reset role command. Now it shows
> REVOKE ALL ON TABLE public.kaspriv FROM yllelohmus;
>
> I ran
>
> REVOKE ALL ON TABLE public.kaspriv FROM yllelohmus;
> After that pgadmin shows next single revoke command:
> REVOKE ALL ON TABLE public.kaspriv FROM villuuus;
> It looks like pgAdmin shows only one REVOKE command but actually there are
> more revokes.

From your first message I was under the impression that pgAdmin shows one
REVOKE ALL for every GRANT, i.e. all REVOKE commands at once.  If that is not
the case you may have found a bug in pgAdmin.  Please ask on the pgadmin-support
list or open a GitHub issue.

Speaking of which, I found https://github.com/pgadmin-org/pgadmin4/issues/5926
which looks like the behavior you're describing.  But this was already fixed in
7.2 and your original post says that you're using 7.2.  Please check if your
version is correct.

--
Erik

Re: How to remove user specific grant and revoke

From

Erik Wienhold

Date:

04 June 2023, 03:36:32

> 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

Re: How to remove user specific grant and revoke

From

Andrus

Date:

04 June 2023, 10:00:46

Hi!

>From your first message I was under the impression that pgAdmin shows one
REVOKE ALL for every GRANT, i.e. all REVOKE commands at once.  If that is not
the case you may have found a bug in pgAdmin.  Please ask on the pgadmin-support
list or open a GitHub issue.

Speaking of which, I found https://github.com/pgadmin-org/pgadmin4/issues/5926
which looks like the behavior you're describing.  But this was already fixed in
7.2 and your original post says that you're using 7.2.  Please check if your
version is correct.

In pgAdmin 7.2 right clicking in table name and selecting Refresh does nothing.

Smells like a bug.

Right clicking in Tables and selecting Refresh worked.

Andrus.