public inbox for [email protected]  

help / color / mirror / Atom feed

From: immerrr again <[email protected]>
To: [email protected]
Subject: DROP ROLE blocked by pg_init_privs
Date: Mon, 24 Nov 2025 16:59:14 +0100
Message-ID: <CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com> (raw)

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!

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: DROP ROLE blocked by pg_init_privs
  In-Reply-To: <CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox