Client be like:
Pls, could you give the new Postgres user the same perms as this one other user?

Uh... Sure.

Then I find out that, for whatever reason, all of their user accounts have disabled inheritance... So, wtf.

Postgres doesn't really allow you to *copy* perms of a role A to role B. You can only grant role A to role B, but for the perms of A to carry over, B has to have inheritance allowed... Which... It doesn't.

So... After a bit of manual GRANT bla ON DATABASE foo TO user, I ping back that it is done and breath a sigh of relief.

Oooooonly... They ping back like -- Could you also copy the perms of A on all the existing objects in the schema to B???

Ugh. More work. Lets see... List all permissions in a schema and... Holy shit! That's thousands of tables and sequences, how tf am I ever gonna copy over all that???

Maybe I could... Disable the pager of psql, and pipe the list into a file, parse it by the magic of regex... And somehow generate a fuckload of GRANT statements? Uuuugh, but that'd kill so much time. Not to mention I'd need to find out what the individual permission letters in the output mean... And... Ugh, ye, no, too much work. Lets see if SO knows a solution!

And, surprise surprise, it did! The easiest, simplest to understand way, was to make a schema-only dump of the database, grep it for user A, substitute their name with B, and then input it back.

What I didn't expect is for the resulting filtered and altered grant list to be over 6800 LINES LONG. WHAT THE FUCK.

...And, shortly after I apply the insane number of grants... I get another ping. Turns out the customer's already figured out a way to grant all the necessary perms themselves, and I... No longer have to do anything :|

Joy. Utter, indescribable joy.

Is there any actual security reason for disabling inheritance in Postgres? (14.x) I'd think that if an account got compromised, it doesn't matter if it has the perms inherited or not, cuz you can just SET ROLE yourself to the granted role with the actual perms and go ham...

  • 0
    Sounds like automation would be nice
  • 1
    Actually there is. You want to be sure that the user inheriting those roles with grants are well defined and can't do potential harm to your database content. Imagine that the user A is actually been granted * on * .... And then B inherits this power and fucks everything up...

    It would not be the first time I see production databases with very permissive grants just so it's "easy for everyone"....
  • 0
    @NeatNerdPrime understandable, but this was A - a dev DB, and B - we, as the MSP company, provide support, debug and solutions to what the customer needs. They didn't, as far as I know, try to refine and/or revise the role perms. They just downright asked us to disable inheritance... Giving us more work down the line.
Add Comment