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

Someone was feeling very clever when they came up with this idea.

It makes a lot of sense, especially since in the SQL standard, you can grant privileges to "users" or "roles."

Might as well simplify the mental model and make them the same.

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.

To me, the comment you are replying to is saying that you should^ DIFFERENTIATE roles by service, not ‘end user’.

> 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

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.

> 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.

`SET ROLE`[1] changes the "the current user identifier of the current SQL session"; after running it "permissions checking for SQL commands is carried out as though the named role were the one that had logged in originally".

Whilst it changes the "current user" it doesn't change the current "session user", and this is what determines which roles you can switch to.

The docs also note that:

> SQL does not allow this command during a transaction; PostgreSQL does not make this restriction because there is no reason to.

[1]: https://www.postgresql.org/docs/16/sql-set-role.html

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.

I don't think Stack Overflow is a good place for good documentation. I think the Postgres documentation should be better.