Securing Your PostgreSQL DB with Roles and Privileges
rlopzc.com
rlopzc.com
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).
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.
Your advice isn't simply security theater - It's wrong and dangerous. It leads to companies treating this data, which is still sensitive, as nonsensitive and storing it insecurely, particularly when data teams export it to third-party tools.
[1] https://www.transportation.gov/gis/national-address-database
The indexes are created with a secure salt. They're only crackable if you know the salt.
Send a public key to the client (say in a secrets input page), your browser encrypts field content with that key, and you receive the ciphertext on the server. You can then decrypt it, discard the sealed box keys, and persist the data however you need. (Presumably something that sensitive would get encrypted with a different key before going into the database, but you could keep the keys around and have each piece of data protected by a different key. This has pros and cons.)
Github Actions secrets are protected in transit to Github using sealed boxes.
That said, you can also use the SubtleCrypto API in the browser to encrypt data before it is even sent to the server.
If you want to use the "R" in RDBMS, though, or report on your data, or use indexes, or anything else that makes it worth running complex DBs instead of a file system, you're stuck using a database as a database.
I do think there’s an argument to be made for the idea that, to put it colloquially, “somewhere the Social Security Administration needs a database that just has every SSN in plaintext”, but that’s not _exactly_ an honest everyday use case.
This is a huge reason why SOC2 isn't a very useful certification. Your SOC2 and my SOC2 can be wildly different.
I’d much sooner agree that the flexibility is in implementation. As long as you can hit a control in a reasonable and articulable manner that can be measured and evidenced, you have much flexibility. I see that as the benefit of SOC2. Others see it as an issue.
To your point, last time I led a company through a SOC2 Type 1-2 engagement, we had some standards sourced from NIST that were ahead of industry for the time, and published NIST standards were an authority that the auditing firm was comfortable accepting as compensatory for a control that otherwise would have been absent or out of compliance. So that control was ultimately accepted as “No exceptions during the audit period, but see our notes annex”.
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?
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.
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.
The postgres roles in particular were a recurring mess until we built provisioning that stuff in to our onboarding automation. If you are going to have individual named users, I highly recommend taking humans out of the process, we make too many mistakes.
And yes, there are multiple advantages to enforcing access control as close to the data as possible. I tend to think of databases as "data structures that can defend themselves" - not just security, but type and data validation, relational references, etc. all can both save you from bugs and help you find them earlier.
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.
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.