DROP ROLE blocked by pg_init_privs

Started by immerrr again5 months ago10 messagesgeneral

immerrr@gmail.com

5 months ago

Hi everyone,

First time trying to configure a PG cluster by the book, I want to create a
role with read permissions on all current and future tables in the current
db. It looks smth like this

CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;

I've been trying out different scenarios for the future, and currently having
a problem when trying to remove "test_role" after adding an extension.

CREATE EXTENSION pg_stat_statements;
DROP ROLE test_role;

The error is as follows:

role "test_role" cannot be dropped because some objects depend on it
DETAIL: privileges for default privileges on new relations belonging
to role postgres in schema public
privileges for view pg_stat_statements_info
initial privileges for view pg_stat_statements_info
privileges for view pg_stat_statements
initial privileges for view pg_stat_statements
Time: 0.001s

I revoke all permissions I know how, but it still won't let me drop "test_role":

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM
test_role;
REVOKE SELECT ON TABLE public.pg_stat_statements_info FROM test_role;
REVOKE SELECT ON TABLE public.pg_stat_statements FROM test_role;

Expectedly, the error still mentions initial privileges:

role "test_role" cannot be dropped because some objects depend on it
DETAIL: initial privileges for view pg_stat_statements_info
initial privileges for view pg_stat_statements

I haven't been able to find much information on initial privileges and how to
manage them. I know that "REASSIGN OWNED BY" doesn't touch them, but "DROP
OWNED BY" does, but I'm a bit worried that DROP can remove other non-ACL
stuff that was actually created by the role.

I have come up with a solution that lets me clean up the initial privileges,
by repacking pg_init_privs.initprivs and and manually removing stuff from
pg_shdepends, but it feels super hacky:

UPDATE pg_init_privs
-- repacks ['postgres=arwdDxtm/postgres', 'test_role=r/postgres', '=r/postgres']
-- into ['postgres=arwdDxtm/postgres', '=r/postgres']
SET initprivs = (
SELECT array_agg(aclitem) FROM (
SELECT makeaclitem(
ip.grantee,
ip.grantor,
string_agg(ip.privilege_type, ','),
ip.is_grantable
) as aclitem
FROM aclexplode(initprivs) AS ip
WHERE ip.grantee != (SELECT oid FROM pg_roles WHERE rolname = 'test_role')
GROUP BY ip.grantor, ip.grantee, ip.is_grantable
) AS aclitems
)
WHERE privtype = 'e'
AND EXISTS (
SELECT 1
FROM aclexplode(initprivs) AS ip
WHERE ip.grantee = (SELECT oid FROM pg_roles WHERE rolname = 'test_role')
);

DELETE FROM pg_shdepend
WHERE deptype = 'i'
AND refclassid = 'pg_authid'::regclass
AND refobjid = (SELECT oid FROM pg_roles WHERE rolname = 'test_role')

So my questions are:

- Am I doing something with the initial role configuration?
- Is there a SQL command to drop the initial privileges safely?
- If not, should there be one?

Thanks!

Pavel Luzanov

p.luzanov@postgrespro.ru

5 months ago

In reply to: immerrr again (#1)

Re: DROP ROLE blocked by pg_init_privs

On 24.11.2025 18:59, immerrr again wrote:

First time trying to configure a PG cluster by the book, I want to create a
role with read permissions on all current and future tables in the current
db. It looks smth like this

CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;

I've been trying out different scenarios for the future, and currently having
a problem when trying to remove "test_role" after adding an extension.

Hm, I have checked your example, it works as expected:

postgres@postgres(16.9)=# CREATE ROLE test_role;
CREATE ROLE
postgres@postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO
test_role;
GRANT
postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_role;
ALTER DEFAULT PRIVILEGES
postgres@postgres(16.9)=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

postgres@postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA public
FROM test_role;
REVOKE
postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM test_role;
ALTER DEFAULT PRIVILEGES
postgres@postgres(16.9)=# DROP ROLE test_role;
DROP ROLE
postgres@postgres(16.9)=# DROP EXTENSION pg_stat_statements;
DROP EXTENSION

In any case, since v14 you can use the predefined role pg_read_all_data.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

immerrr again

immerrr@gmail.com

5 months ago

In reply to: Pavel Luzanov (#2)

Re: DROP ROLE blocked by pg_init_privs

Hi,

Thank you for replying. Great to know about pg_read_all_data, will have a
look at that.

Re: it works, not sure, can't make it work on my side. Here's a full repro:

[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -e
POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d
postgres:16.9
ae9fe66613867d4db6019bbc0806ef57b5bf7e8b83b10ee0dbb422c2d146d701

CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE EXTENSION
ERROR: role "test_role" cannot be dropped because some objects depend on it
DETAIL: privileges for default privileges on new relations belonging to
role postgres in schema public
privileges for view pg_stat_statements_info
privileges for view pg_stat_statements

Is there some difference in the configuration that I'm not accounting for?

Thanks

On Tue, Nov 25, 2025 at 11:49 PM Pavel Luzanov <p.luzanov@postgrespro.ru>
wrote:

Show quoted text

Hi

On 24.11.2025 18:59, immerrr again wrote:

First time trying to configure a PG cluster by the book, I want to create a
role with read permissions on all current and future tables in the current
db. It looks smth like this

CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;

I've been trying out different scenarios for the future, and currently having
a problem when trying to remove "test_role" after adding an extension.

Hm, I have checked your example, it works as expected:

postgres@postgres(16.9)=# CREATE ROLE test_role;
CREATE ROLE
postgres@postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO
test_role;
GRANT
postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT
SELECT ON TABLES TO test_role;
ALTER DEFAULT PRIVILEGES
postgres@postgres(16.9)=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

postgres@postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA public
FROM test_role;
REVOKE
postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM test_role;
ALTER DEFAULT PRIVILEGES
postgres@postgres(16.9)=# DROP ROLE test_role;
DROP ROLE
postgres@postgres(16.9)=# DROP EXTENSION pg_stat_statements;
DROP EXTENSION

In any case, since v14 you can use the predefined role pg_read_all_data.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com

immerrr again

immerrr@gmail.com

5 months ago

In reply to: immerrr again (#3)

Re: DROP ROLE blocked by pg_init_privs

Sorry, nvm, I forgot to drop the privileges in the repro. Can confirm, with
16.9 it works as expected:

[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -e
POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d
postgres:16.9
564ab91604018ff903c428f6a8659207a4071c30dbb94d206b20cb4f5e8d1635

[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555
<<EOF
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
test_role;
CREATE EXTENSION pg_stat_statements;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM
test_role;
DROP ROLE test_role;
EOF
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE EXTENSION
REVOKE
ALTER DEFAULT PRIVILEGES
DROP ROLE

The same script with postgres:17 image fails with the error I have been
describing:

[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -e
POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d
*postgres:17*
5be292dbe145375e0717f722d622f3be2b7e2764b225253fbc33ea1f9d13f3e7

[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555
<<EOF

CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
test_role;
CREATE EXTENSION pg_stat_statements;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM
test_role;
DROP ROLE test_role;
EOF
CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE EXTENSION
REVOKE
ALTER DEFAULT PRIVILEGES

*ERROR: role "test_role" cannot be dropped because some objects depend on
itDETAIL: initial privileges for view pg_stat_statements_infoinitial
privileges for view pg_stat_statements*

On Wed, Nov 26, 2025 at 12:00 AM immerrr again <immerrr@gmail.com> wrote:

Show quoted text

Hi,

Thank you for replying. Great to know about pg_read_all_data, will have a
look at that.

Re: it works, not sure, can't make it work on my side. Here's a full repro:

[nix-shell:~]$ docker run --rm -ti -p 5555:5432 -e
POSTGRES_PASSWORD=pg_test_init_privs --name pg_test_init_privs -d
postgres:16.9
ae9fe66613867d4db6019bbc0806ef57b5bf7e8b83b10ee0dbb422c2d146d701

[nix-shell:~]$ psql postgres://postgres:pg_test_init_privs@localhost:5555
<<EOF
CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
test_role;
CREATE EXTENSION pg_stat_statements;
DROP ROLE test_role;
EOF

CREATE ROLE
GRANT
ALTER DEFAULT PRIVILEGES
CREATE EXTENSION
ERROR: role "test_role" cannot be dropped because some objects depend on
it
DETAIL: privileges for default privileges on new relations belonging to
role postgres in schema public
privileges for view pg_stat_statements_info
privileges for view pg_stat_statements

Is there some difference in the configuration that I'm not accounting for?

Thanks

On Tue, Nov 25, 2025 at 11:49 PM Pavel Luzanov <p.luzanov@postgrespro.ru>
wrote:

Hi

On 24.11.2025 18:59, immerrr again wrote:

First time trying to configure a PG cluster by the book, I want to create a
role with read permissions on all current and future tables in the current
db. It looks smth like this

CREATE ROLE test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_role;

I've been trying out different scenarios for the future, and currently having
a problem when trying to remove "test_role" after adding an extension.

Hm, I have checked your example, it works as expected:

postgres@postgres(16.9)=# CREATE ROLE test_role;
CREATE ROLE
postgres@postgres(16.9)=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO
test_role;
GRANT
postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_role;
ALTER DEFAULT PRIVILEGES
postgres@postgres(16.9)=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

postgres@postgres(16.9)=# REVOKE SELECT ON ALL TABLES IN SCHEMA public
FROM test_role;
REVOKE
postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM test_role;
ALTER DEFAULT PRIVILEGES
postgres@postgres(16.9)=# DROP ROLE test_role;
DROP ROLE
postgres@postgres(16.9)=# DROP EXTENSION pg_stat_statements;
DROP EXTENSION

In any case, since v14 you can use the predefined role pg_read_all_data.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com

Tom Lane

tgl@sss.pgh.pa.us

5 months ago

In reply to: immerrr again (#4)

Re: DROP ROLE blocked by pg_init_privs

immerrr again <immerrr@gmail.com> writes:

Sorry, nvm, I forgot to drop the privileges in the repro. Can confirm, with
16.9 it works as expected:
...
The same script with postgres:17 image fails with the error I have been
describing:

The missing step here is

DROP OWNED BY test_role;

You have to use that to get rid of "initial privileges" because
neither REVOKE nor ALTER DEFAULT PRIVILEGES will touch existing
initial privileges. Pre-v17 didn't account for this properly
and would allow you to drop the role anyway, leaving dangling
entries behind in pg_init_privs. While those are harmless in
isolation, they cause problems for later dump/restore activity.

See discussion here:

/messages/by-id/1745535.1712358659@sss.pgh.pa.us

regards, tom lane

immerrr again

immerrr@gmail.com

5 months ago

In reply to: Tom Lane (#5)

Re: DROP ROLE blocked by pg_init_privs

Hi Tom,

On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

The missing step here is

DROP OWNED BY test_role;
...
See discussion here:
...

I had gone through that thread before posting, and yes, I mentioned "DROP
OWNED BY" in my original message. Is this what everyone is doing these
days? People just make sure there are no pg_depends/pg_shdepends that need
transferring, and then just blast the rest with "DROP OWNED BY"?

It just makes me uneasy to run a command with such potential for data loss
in order to remove a role. So much so that I have written a couple of
queries to manually clean up the system tables pg_init_privs/pg_shdepends
instead (see [1]). They seem to unblock dropping the role for now, but I'm
not even sure if I have missed something, or for how long they will keep
working.

I guess, my question is shouldn't there be a "REVOKE INITIAL ... FROM
<user>" command to drop just the initial privilege(-s) without potentially
nuking everything else owned by that user?

Thanks

1.
/messages/by-id/CAERznn-SBBqQ3YcdZk9U4mqVQPsVgLisi=EdFzY5Fb7hOQ4g_Q@mail.gmail.com

immerrr again

immerrr@gmail.com

5 months ago

In reply to: immerrr again (#6)

Re: DROP ROLE blocked by pg_init_privs

I have written a couple of queries to manually clean up the system tables

pg_init_privs/pg_shdepends instead (see [1])

Sorry, wrong link [1]. Should have been

1.
/messages/by-id/CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com

Show quoted text

Ron

ronljohnsonjr@gmail.com

5 months ago

In reply to: immerrr again (#7)

Re: DROP ROLE blocked by pg_init_privs

On Tue, Nov 25, 2025 at 7:36 PM immerrr again <immerrr@gmail.com> wrote:

I have written a couple of queries to manually clean up the system

tables pg_init_privs/pg_shdepends instead (see [1])

Sorry, wrong link [1]. Should have been

1.
/messages/by-id/CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com

It would be interesting to see what's missing from the
information_schema.*_privileges views:
https://www.postgresql.org/docs/14/information-schema.html

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Tom Lane

tgl@sss.pgh.pa.us

5 months ago

In reply to: immerrr again (#6)

Re: DROP ROLE blocked by pg_init_privs

immerrr again <immerrr@gmail.com> writes:

On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

The missing step here is
DROP OWNED BY test_role;

It just makes me uneasy to run a command with such potential for data loss
in order to remove a role.

Well,

(a) if you try to DROP ROLE any role that still owns objects,
it will refuse, and tell you what the role still owns.
(The crux of your problem is that access permissions are not objects.)

(b) the usual procedure is to do REASSIGN OWNED first. Anything
that remains to be dropped by DROP OWNED must be an access permission
not an object.

So much so that I have written a couple of
queries to manually clean up the system tables pg_init_privs/pg_shdepends
instead (see [1]).

Yup, that's far safer. No possibility of irretrievably hosing your
database through ill-considered manual catalog changes, for sure.

regards, tom lane

#10

immerrr again

immerrr@gmail.com

5 months ago

In reply to: Tom Lane (#9)

Re: DROP ROLE blocked by pg_init_privs

On Wed, Nov 26, 2025 at 4:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

immerrr again <immerrr@gmail.com> writes:

On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

The missing step here is
DROP OWNED BY test_role;

It just makes me uneasy to run a command with such potential for data

loss

in order to remove a role.

...
(b) the usual procedure is to do REASSIGN OWNED first. Anything
that remains to be dropped by DROP OWNED must be an access permission
not an object.

(c) you do know that DDL in Postgres is transactional, right?
You can roll it back if you don't like the results.

Being able to roll back a dropped role doesn't seem like a huge help. I
mean, if I can detect that a table/function/type is missing after the fact,
it's probably even easier to check which ones are preventing the role from
being dropped in the first place, right?

REASSIGN before DROP does help, thanks. There's still a potential for
someone to create or reassign a new object to that role just before it's
dropped, but it's not a big deal.

So much so that I have written a couple of
queries to manually clean up the system tables pg_init_privs/pg_shdepends
instead (see [1]).

Yup, that's far safer. No possibility of irretrievably hosing your
database through ill-considered manual catalog changes, for sure.

I wouldn't be discussing it here if I were happy about it.
Carpet-reassigning and -dropping didn't feel right, so I had explored an
alternative path. That one didn't feel right either. It made me wonder: if
there was no PG command for it, was there a yet another approach that was
better? I guess not, and everyone is just happy with REASSIGN+DROP, that's
fine.

Thanks