5 ms·

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

• jzelinskie 3y ago
  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.
  • ludston 3y ago
    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.
    • _jhqp 3y ago
      Everything in PostgreSQL is a role. It's just named such that when a ROLE allows `login` it's considered a user
      • skissane 3y ago
        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
        • _jhqp 3y ago
          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.
      • orthoxerox 3y ago
        Someone was feeling very clever when they came up with this idea.
        • paulddraper 3y ago
          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.
      • cqqxo4zV46cp 3y ago
        To me, the comment you are replying to is saying that you should^ DIFFERENTIATE roles by service, not ‘end user’.
• lelanthran 3y ago
  > 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.
  • jamessb 2y ago
    > 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 https://www.postgresql.org/docs/16/sql-set-role.html