How to remove user specific grant and revoke

Started by Andrusalmost 3 years ago12 messagesgeneral

kobruleht2@hot.ee

almost 3 years ago

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.

Erik Wienhold

ewie@ewie.name

almost 3 years ago

In reply to: Andrus (#1)

Re: How to remove user specific grant and revoke

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]https://www.postgresql.org/docs/12/functions-info.html#FUNCTIONS-ACLITEM-FN-TABLE 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

Erik Wienhold

ewie@ewie.name

almost 3 years ago

In reply to: Erik Wienhold (#2)

Re: How to remove user specific grant and revoke

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

Andrus

kobruleht2@hot.ee

almost 3 years ago

In reply to: Erik Wienhold (#3)

Re: How to remove user specific grant and revoke

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.

Erik Wienhold

ewie@ewie.name

almost 3 years ago

In reply to: Andrus (#4)

Re: How to remove user specific grant and revoke

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

Andrus

kobruleht2@hot.ee

almost 3 years ago

In reply to: Andrus (#1)

Re: How to remove user specific grant and revoke

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:

Show quoted text

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.

Import Notes

Reply to msg id not found: b35dbd8f-9896-9708-08a8-5a1747c0ece3@hot.ee

Erik Wienhold

ewie@ewie.name

almost 3 years ago

In reply to: Andrus (#6)

Re: How to remove user specific grant and revoke

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

Andrus

kobruleht2@hot.ee

almost 3 years ago

In reply to: Erik Wienhold (#7)

Re: How to remove user specific grant and revoke

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.

Andrus

kobruleht2@hot.ee

almost 3 years ago

In reply to: Andrus (#8)

Re: How to remove user specific grant and revoke

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.

#10

Erik Wienhold

ewie@ewie.name

almost 3 years ago

In reply to: Andrus (#8)

Re: How to remove user specific grant and revoke

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

#11

Erik Wienhold

ewie@ewie.name

almost 3 years ago

In reply to: Andrus (#9)

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

#12

Andrus

kobruleht2@hot.ee

almost 3 years ago

In reply to: Erik Wienhold (#10)

Re: How to remove user specific grant and revoke

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 foundhttps://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.