Subject: DROP ROLE blocked by pg_init_privs

From: immerrr again <[email-masked]>
Date: Mon, 24 Nov 2025 16:59:14 +0100

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!

---

From: Pavel Luzanov <[email-masked]>
Date: Wed, 26 Nov 2025 01:49:15 +0300

Hi

> 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

---

From: immerrr again <[email-masked]>
Date: Wed, 26 Nov 2025 00:00:02 +0100

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 <[email-masked]>
wrote:

> postgres@postgres(16.9)=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT
> SELECT ON TABLES TO test_role;
> ALTER DEFAULT PRIVILEGES
> Postgres Professional: https://postgrespro.com
>
>
>

---

From: immerrr again <[email-masked]>
Date: Wed, 26 Nov 2025 00:06:39 +0100

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

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_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

CREATE ROLE test_role;
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_role;

*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*

---

From: Tom Lane <[email-masked]>
Date: Tue, 25 Nov 2025 19:08:05 -0500

immerrr again <[email-masked]> writes:
> ...

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:

https://www.postgresql.org/message-id/flat/1745535.1712358659%40sss.pgh.pa.us

			regards, tom lane