Обсуждение: DROP ROLE blocked by pg_init_privs

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!

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.

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

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:

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

            regards, tom lane

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.

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

> 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