I like Postgres, a lot.
Most of the things in this article are avoidable, and good to keep an eye out for.
But let's be clear we're not talking about the worst part of Postgres: roles. There is a ton of power there, it would be amazing to use it. Making it work feels like black magic. Every bit of the interface around it just seems like esoteric incantations that may or may not do what you expect. It's a terrible way to manage something so important.
THe manual for this section is, thin. It gives you an idea of how things should work, maybe, in a narrow use case. The problem is when they dont your going to spend time doing a lot of trial and error to figure out what you did wrong, and likely not have a clue as to how to do it right. And may god have mercy on your soul if you want to migrate a db with complex user permissions.
I need to sit down with it for a month and write "cookbook". If one person uses it and goes to bed that night without crying them selves to sleep it will have been worth it.
I’d read that. Role management for me often involves a lot of guessing and too often roles end up with too many permissions because of that.
Hah, role management for us is "create a role for migrations, and a role to do db things, and enforce auth entirely in the web app"
I suspect we aren't alone
This is a fairly sane place to be in terms of bang for your buck. It's easy to find yourself in a place where authorization data and logic span multiple services and at that point having everything deeply siloed into Postgres might be a doozy. That being said, there are plenty of times that'll never be the case and you should try to lean on the abstractions that work best for you.
Roles are for services not for users. If you have a read-only Web api then it makes sense to use a read-only role regardless of which user is using it.
Everything in PostgreSQL is a role.
It's just named such that when a ROLE allows `login` it's considered a user
This is part of what many people find so confusing. In most systems “role” is a group (or something closely resembling a group), not a user. The weird terminology confuses beginners
It's a bit confusing and legacy.
All roles function like you would expect groups to function
A role that is not allowed to login is a `group`.
While the CREATE USER and CREATE GROUP commands still exist, they are simply aliases for CREATE ROLE.
Someone was feeling very clever when they came up with this idea.
To me, the comment you are replying to is saying that you should^ DIFFERENTIATE roles by service, not ‘end user’.
Honestly I'd be happy to spend the time learning the ins and outs of PostgreSQL IAM stuff, but there's two very good reasons why I won't use it:
1. Still need the "role"/user across other services, so I don't save anything by doing ACL inside the DB.
2. I've no idea how to temporarily drop privileges for a single transaction. Connecting as the correct user on each incoming HTTP request is way too slow.
Here's your cookbook: https://stackoverflow.com/questions/78401179/how-do-postgres...
Oh it's really worrying that you post a link to a SO page as the recommended 'cookbook' for something that's referred to as a powerful feature. And both of the answers only have a single up vote each. Seems like there's a serious gap here.
You can help out by posting answer / upvoting good answer.
I agree with the sentiment that IAM in PostgreSQL is complex.
What makes it complex is that there are 3 layers of objects (Database, Schema, Tables) and also implicit grants given to DB object owners
To be able to select from a table you need:
* CONNECT on the Database
* USAGE on the Schema (Given implicitly to schema owner)
* SELECT on the Table (Given implicitly to table owner)
To see these privileges we need to understand acl entries of this format
`grantee=privilege-abbreviation[]/grantor:`
* Use \l+ to see privileges of Database
* Use \dn+ to see privileges of Schemas
* Use \dp+ to see privileges of Tables
Privileges are seen [here](https://www.postgresql.org/docs/current/ddl-priv.html)
e.g. in the following example user has been given all permissions by postgres role
`user=arwdDxt/postgres`
If the “grantee” column is empty for a given object, it means the object has default owner privileges (all privileges) or it can mean privileges to PUBLIC role (every role that exists)
`=r/postgres`
Also it's confusing when Public schema is used. You have CREATE permission on schema so when the tables are created with the same user you select data with and you have owner permissions out of the box.
I've seen funny things happen when developers think their GRANT is doing what ALTER DEFAULT PRIVILEGES does, and a day later break production because their user doesn't have access to the new tables.
What do you mean exactly?
If roles have INHERIT, then doing the following works, no?
* Role A creates table * GRANT A TO B; * ROLE B can read from table just like A can.
Also if Role A creates new table, Role B can read that too no?
Remove any "if" from your comment and it seems like you understood what would fail! There's more ways to make misunderstanding grant vs default privileges will create issues as well.
Then I would argue for trying to centrally ensure that all roles have INHERIT in your DBs. :thinking:
Doing ALTER DEFAULT PRIVILEGES could be another future footgun of it's own.
I think you probably don’t want the application user to inherit from the superuser you may use for migrations since some migrations require a superuser.
Isn't LOGIN (https://www.postgresql.org/docs/16/role-attributes.html) also needed?
I recently had to rename an app I was developing. That included the linux user account under which the service ran, and therefore the postgres user.
I don't remember all the details, but I had to also rename the postgres user and role, which seemed a simple thing to do. But for some reason renaming the user didn't include the permissions on the database. I was left with a very confusing state of working table access and denied record access. I decided to backup the data, dropp the database and do an import that didn't include any permissions.
That simple thing turned out a complete shit show and I blame Postgres for making something so simple so complex.
I never use system accounts for Postgres auth. It feels like coupling that won’t do anything good for most people most of the time, and will only bite you in the ass when you don’t expect it to.
Can confirm. Last year I implemented a simple postgREST server with rowlevel security. (The postgREST logs are really good. With cookbook and all)
The path there was somewhat difficult, but once it worked, it was truly magical. And the mechanisms involved were quite simple even.
Please do. I'd be happy to pay ~$20 for it.
I have seen what you have seen (did several "serious" things using postgrest).
edit: i mean yes, we need that cookbook real bad
Even the docs of postgrest, which relies on roles for auth, seems to be not very detailed: https://postgrest.org/en/v12/explanations/db_authz.html
Interesting. If you are serious about writing a cookbook for postgres roles, and open something like a kickstarter, I'll be one of the first to pledge!