public inbox for [email protected]help / color / mirror / Atom feed
Re: DROP ROLE blocked by pg_init_privs 5+ messages / 3 participants [nested] [flat]
* Re: DROP ROLE blocked by pg_init_privs @ 2025-11-26 00:33 immerrr again <[email protected]> 2025-11-26 00:35 ` Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]> 2025-11-26 03:45 ` Re: DROP ROLE blocked by pg_init_privs Tom Lane <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: immerrr again @ 2025-11-26 00:33 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; [email protected] Hi Tom, On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <[email protected]> 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. https://www.postgresql.org/message-id/CAERznn-SBBqQ3YcdZk9U4mqVQPsVgLisi%3DEdFzY5Fb7hOQ4g_Q%40mail.g... ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP ROLE blocked by pg_init_privs 2025-11-26 00:33 Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]> @ 2025-11-26 00:35 ` immerrr again <[email protected]> 2025-11-26 00:39 ` Re: DROP ROLE blocked by pg_init_privs Ron Johnson <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: immerrr again @ 2025-11-26 00:35 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; [email protected] > 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. https://www.postgresql.org/message-id/CAERznn-QWVpAvqnyF%3DrZfiuxkeDG0tym_rY%2BRuEkSPWvzgi67Q%40mail... > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP ROLE blocked by pg_init_privs 2025-11-26 00:33 Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]> 2025-11-26 00:35 ` Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]> @ 2025-11-26 00:39 ` Ron Johnson <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Ron Johnson @ 2025-11-26 00:39 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> On Tue, Nov 25, 2025 at 7:36 PM immerrr again <[email protected]> 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. > https://www.postgresql.org/message-id/CAERznn-QWVpAvqnyF%3DrZfiuxkeDG0tym_rY%2BRuEkSPWvzgi67Q%40mail... > >> 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! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP ROLE blocked by pg_init_privs 2025-11-26 00:33 Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]> @ 2025-11-26 03:45 ` Tom Lane <[email protected]> 2025-11-26 10:38 ` Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Tom Lane @ 2025-11-26 03:45 UTC (permalink / raw) To: immerrr again <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; [email protected] immerrr again <[email protected]> writes: > On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DROP ROLE blocked by pg_init_privs 2025-11-26 00:33 Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]> 2025-11-26 03:45 ` Re: DROP ROLE blocked by pg_init_privs Tom Lane <[email protected]> @ 2025-11-26 10:38 ` immerrr again <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: immerrr again @ 2025-11-26 10:38 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; [email protected] On Wed, Nov 26, 2025 at 4:45 AM Tom Lane <[email protected]> wrote: > immerrr again <[email protected]> writes: > > On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-11-26 10:38 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-11-26 00:33 Re: DROP ROLE blocked by pg_init_privs immerrr again <[email protected]> 2025-11-26 00:35 ` immerrr again <[email protected]> 2025-11-26 00:39 ` Ron Johnson <[email protected]> 2025-11-26 03:45 ` Tom Lane <[email protected]> 2025-11-26 10:38 ` immerrr again <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox