7 ms·

Securing Your PostgreSQL DB with Roles and Privileges

117 points by rlopezc 3y ago

mistrial9 3y ago
[flagged]
pphysch 3y ago
Has anyone run into issues with too many roles? Like if you want to use RLS and have a role per application-user, with millions of users.
- programmarchy 3y ago
  Why have a role per user instead of just defining the row policy with the user directly?
  - pphysch 3y ago
    How does that work? With per "user" roles, I can SET ROLE "user-1000" and enter their authz context without changing any of my queries. How would this work without per-user roles?
  - hn_throwaway_99 3y ago
    In Postgres a "role" really is equivalent to a user. A user in Postgres is just a role with the ability to log in.
    - programmarchy 3y ago
      Yes but I’m presuming the person I was responding to meant “role” in the context you’re talking about, but by “user” they meant a row in some “users” or “customers” table corresponding to their application. Questioning the need to “create role” for every application user.
- paulryanrogers 3y ago
  Roles/users are backed by PG tables, so I imagine storing them should be no more difficult. (Assuming partitioning and other native features are available.) Yet I'm not sure how well it would scale all the row checks for read and write access, especially if you also use column level security. Proxying connections for so many different users would be awkward too, though some proxies apparently can take on a different role for the session and revert when client-side disconnects.
- anarazel 3y ago
  FWIW, you don't need to use database roles if you want to use RLS. You can instead have some other context indicating the current "application user" and use that in your RLS policies.
  - pphysch 3y ago
    Do I have to add that context to every query, or is it something I can set per cursor/transaction?
    - anarazel 3y ago
      Either. What the best approach is depends a bit on your needs / security model. You can e.g. something like storing the session "application user" in a configuration variable (SET myapp.rls_user =...). But if the user can influence the SQL and that's part of the threat model, you need to do more, because that could be changed by further SQL. Another solution is to just have a session level temp table indicating the current application user.
      - pphysch 3y ago
        Oh sweet. That approach makes a lot more sense. Access would be through a server-side ORM so users would not be able to run arbitrary SQL. Thanks!
        edmundsauto 3y ago
        Supabase has pretty good docs and a nice Ui to play around with this, btw.
- aeonsky 3y ago
  Yes, my team had a direct issue with this on Aurora Postgres, at least. This is PG9 but then kept happening all the way into PG12 until we got rid of all but like 5 roles. Above like 4000 roles we experienced a significant lag on every query, sometimes on the order of seconds. At scaled somewhat linearly. I even wrote to Tom Lane and he said that area of Postgres is poorly optimized.
  - pphysch 3y ago
    Interesting. Why so many roles initially, and how did you safely consolidate to 5?
hn_throwaway_99 3y ago
The article starts at the top by saying "To become SOC2 compliant, we needed to remove global access and fine-tune who has access to what schemas and tables." I've had to go through this SOC2 certification process as well, and I think a much better approach (with a lot of other benefits) is to use client side encryption to encode sensitive data like PII or PHI (personal health info) before you insert it into the DB. That way it's easy to give all of your developers read-only access to essentially the entire DB for things like debugging support while still maintaining SOC2 and other compliance (e.g. HIPAA). Not saying there isn't also good use cases for roles and privileges (and it's a lot harder to add client-side encryption after the fact), but using client side encryption/decryption is a better approach to this issue IMO (you get more security benefits, and the compliance benefits really just are a consequence of that).
- snagg 3y ago
  We are big proponent of app-layer encryption as well. We wrote extensively about how we do it for our specific use case: https://www.slashid.dev/blog/app-layer-encryption/ https://www.slashid.dev/blog/app-layer-encryption/
- lastofus 3y ago
  Doesn't encrypting your data before insertion make your data unable to be indexed/searched easily?
  - webstrand 3y ago
    For some kinds of data and queries, it doesn't matter if the data in the index is encrypted. For other kinds of data, you could build the index on an expression that produces decrypted or anonymized values. Sadly postgres doesn't have per-index permissions, so you can't prevent a user with access to the table from using all of it's indexes.
  - hn_throwaway_99 3y ago
    For indexing/searching on encrypted fields we use a blind index (lots of good resources if you search for that term). On the other hand, sorting on encrypted fields has proven to be a difficult challenge. There are some possible approaches but they lower the security of your encryption.
louwrentius 3y ago
Maybe I’m totally out of it, but creating an actual database user for each account of your application sounds like you can rely on database security and don’t run the risk of application bugs causing security vulnerabilities. This means a more complex database level of roles and privileges, which may be it’s own can of worms, but if you have to choose between problems to have, what would you select?
- warent 3y ago
  I don't think this literally means each user of your app gets their own DB user, rather that you create different db users for different aspects of your app. What you're describing is what RLS (row-level security) is for, where you log into a generic global "app_user" user with certain permissions that don't include things like admin tables etc, and then define the specific user that is using the session via session variables.
  - louwrentius 3y ago
    I've seen blog posts decades ago from DB admins that actually advocated for a DB account per user account. They stated the DB was totally build for that, but people just don't know. They use what they understand, as in: app people may know too little about database security and privileges and just decide to solve it in code.
  - patmorgan23 3y ago
    There are applications that are built to where literally every user gets a database login. For example Dynamics SL (formerly Solomon) is built this way with MS SQL server.
    - TheNewsIsHere 3y ago
      We use an application in my company that’s designed to run in the vendor cloud or on-prem. They’re pretty transparent that every cloud customer is running in their own specific database, and they’re just clustering all of those databases. The SaaS build of the app even exposes this in the URLs. Everyone is on the same codebase, and there’s just some additional database logic to connect the right deployments to the right databases.

117 points by rlopezc 3y ago

[flagged]

Has anyone run into issues with too many roles? Like if you want to use RLS and have a role per application-user, with millions of users.

programmarchy 3y ago
Why have a role per user instead of just defining the row policy with the user directly?
- pphysch 3y ago
  How does that work? With per "user" roles, I can SET ROLE "user-1000" and enter their authz context without changing any of my queries. How would this work without per-user roles?
- hn_throwaway_99 3y ago
  In Postgres a "role" really is equivalent to a user. A user in Postgres is just a role with the ability to log in.
  - programmarchy 3y ago
    Yes but I’m presuming the person I was responding to meant “role” in the context you’re talking about, but by “user” they meant a row in some “users” or “customers” table corresponding to their application. Questioning the need to “create role” for every application user.
paulryanrogers 3y ago
Roles/users are backed by PG tables, so I imagine storing them should be no more difficult. (Assuming partitioning and other native features are available.) Yet I'm not sure how well it would scale all the row checks for read and write access, especially if you also use column level security. Proxying connections for so many different users would be awkward too, though some proxies apparently can take on a different role for the session and revert when client-side disconnects.
anarazel 3y ago
FWIW, you don't need to use database roles if you want to use RLS. You can instead have some other context indicating the current "application user" and use that in your RLS policies.
- pphysch 3y ago
  Do I have to add that context to every query, or is it something I can set per cursor/transaction?
  - anarazel 3y ago
    Either. What the best approach is depends a bit on your needs / security model. You can e.g. something like storing the session "application user" in a configuration variable (SET myapp.rls_user =...). But if the user can influence the SQL and that's part of the threat model, you need to do more, because that could be changed by further SQL. Another solution is to just have a session level temp table indicating the current application user.
    - pphysch 3y ago
      Oh sweet. That approach makes a lot more sense. Access would be through a server-side ORM so users would not be able to run arbitrary SQL. Thanks!
      - edmundsauto 3y ago
        Supabase has pretty good docs and a nice Ui to play around with this, btw.
aeonsky 3y ago
Yes, my team had a direct issue with this on Aurora Postgres, at least. This is PG9 but then kept happening all the way into PG12 until we got rid of all but like 5 roles. Above like 4000 roles we experienced a significant lag on every query, sometimes on the order of seconds. At scaled somewhat linearly. I even wrote to Tom Lane and he said that area of Postgres is poorly optimized.
- pphysch 3y ago
  Interesting. Why so many roles initially, and how did you safely consolidate to 5?

hn_throwaway_99 3y ago

The article starts at the top by saying "To become SOC2 compliant, we needed to remove global access and fine-tune who has access to what schemas and tables." I've had to go through this SOC2 certification process as well, and I think a much better approach (with a lot of other benefits) is to use client side encryption to encode sensitive data like PII or PHI (personal health info) before you insert it into the DB. That way it's easy to give all of your developers read-only access to essentially the entire DB for things like debugging support while still maintaining SOC2 and other compliance (e.g. HIPAA). Not saying there isn't also good use cases for roles and privileges (and it's a lot harder to add client-side encryption after the fact), but using client side encryption/decryption is a better approach to this issue IMO (you get more security benefits, and the compliance benefits really just are a consequence of that).

snagg 3y ago
We are big proponent of app-layer encryption as well. We wrote extensively about how we do it for our specific use case: https://www.slashid.dev/blog/app-layer-encryption/ https://www.slashid.dev/blog/app-layer-encryption/
lastofus 3y ago
Doesn't encrypting your data before insertion make your data unable to be indexed/searched easily?
- webstrand 3y ago
  For some kinds of data and queries, it doesn't matter if the data in the index is encrypted. For other kinds of data, you could build the index on an expression that produces decrypted or anonymized values. Sadly postgres doesn't have per-index permissions, so you can't prevent a user with access to the table from using all of it's indexes.
- hn_throwaway_99 3y ago
  For indexing/searching on encrypted fields we use a blind index (lots of good resources if you search for that term). On the other hand, sorting on encrypted fields has proven to be a difficult challenge. There are some possible approaches but they lower the security of your encryption.

louwrentius 3y ago

Maybe I’m totally out of it, but creating an actual database user for each account of your application sounds like you can rely on database security and don’t run the risk of application bugs causing security vulnerabilities. This means a more complex database level of roles and privileges, which may be it’s own can of worms, but if you have to choose between problems to have, what would you select?

warent 3y ago
I don't think this literally means each user of your app gets their own DB user, rather that you create different db users for different aspects of your app. What you're describing is what RLS (row-level security) is for, where you log into a generic global "app_user" user with certain permissions that don't include things like admin tables etc, and then define the specific user that is using the session via session variables.
- louwrentius 3y ago
  I've seen blog posts decades ago from DB admins that actually advocated for a DB account per user account. They stated the DB was totally build for that, but people just don't know. They use what they understand, as in: app people may know too little about database security and privileges and just decide to solve it in code.
- patmorgan23 3y ago
  There are applications that are built to where literally every user gets a database login. For example Dynamics SL (formerly Solomon) is built this way with MS SQL server.
  - TheNewsIsHere 3y ago
    We use an application in my company that’s designed to run in the vendor cloud or on-prem. They’re pretty transparent that every cloud customer is running in their own specific database, and they’re just clustering all of those databases. The SaaS build of the app even exposes this in the URLs. Everyone is on the same codebase, and there’s just some additional database logic to connect the right deployments to the right databases.