Обсуждение: How to remove user specific grant and revoke
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.
> 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
> 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
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.
> 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
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.
Hi!
> REVOKE must be executed by the grantor (sba_owner) or a superuser in case youREVOKE was executed by superuser, postgres. PgAdmin uses this user to log on.not already tried that.Do you get any errors?There are no errors.
Andrus.
> 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
Hi!
psql (12.2 (Debian 12.2-2.pgdg100+1))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;
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.
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.
> 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
> 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
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.