Securing Your PostgreSQL DB with Roles and Privileges
1–10 of 41 posts
Re: Securing Your PostgreSQL DB with Roles and Privileges
#2[flagged]
Re: Securing Your PostgreSQL DB with Roles and Privileges
#3Has 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.
Re: Securing Your PostgreSQL DB with Roles and Privileges
#4Has 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.
Why have a role per user instead of just defining the row policy with the user directly?
Re: Securing Your PostgreSQL DB with Roles and Privileges
#5Has 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.
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.
Re: Securing Your PostgreSQL DB with Roles and Privileges
#6Has 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.
Why have a role per user instead of just defining the row policy with the user directly?
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?
Re: Securing Your PostgreSQL DB with Roles and Privileges
#7The 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).
Re: Securing Your PostgreSQL DB with Roles and Privileges
#8Has 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.
Why have a role per user instead of just defining the row policy with the user directly?
In Postgres a "role" really is equivalent to a user. A user in Postgres is just a role with the ability to log in.
Re: Securing Your PostgreSQL DB with Roles and Privileges
#9Maybe 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?
Re: Securing Your PostgreSQL DB with Roles and Privileges
#10Has 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.
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.