Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Securing Your PostgreSQL DB with Roles and Privileges

rlopzc.com

Post reply on HN
Print view View original

21–30 of 41 posts

Re: Securing Your PostgreSQL DB with Roles and Privileges

#21

Post by _jal »

louwrentius wrote: post #9

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?

We are heavy postgres users, and we do both SOC2 and a more stringent audit demanded by our corporate parent. Lots of roles, lots of RLS, encrypted media, additional column-level encryption for some specific things for not great reasons).

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.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#22

Post by louwrentius »

warent wrote: post #12
louwrentius wrote: post #9

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?

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…

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.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#23

Post by louwrentius »

_jal wrote: post #21
louwrentius wrote: post #9

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?

We are heavy postgres users, and we do both SOC2 and a more stringent audit demanded by our corporate parent. Lots of roles, lots of RLS, encrypted media, additional column-level encryption for some specific things for not great reasons). 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 hig…

Yes, as an armchair "expert" that's what I was thinking. But managing swats of SQL: a developer friend said that this is difficult too. I don't know. But if I had to choose, I would make the SQL / DB side rock solid, even if it required some business logic in SQL.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#24

Post by candiddevmike »

rudasn wrote: post #15
hn_throwaway_99 wrote: post #7

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

How can you do client side encryption with web apps though? While keeping the key on the client, I assume, and allowing multiple browser sessions for the same user?

Envelope encryption, where you encrypt a data encryption key (typically symmetric with AES) with other keys (typically asymmetric with RSA). This is how most password safes like bitwarden work.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#25

Post by hn_throwaway_99 »

_jal wrote: post #19
hn_throwaway_99 wrote: post #7

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's fine if you want a bucket of bits instead of a database. You can even make it easier by making one big table with an ID and blob, and just serialize | encrypt state to the DB. Easy-peasy. 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.

This is wrong and unnecessarily snarky. I don't pre-encrypt all data, just PII/PHI. Doing this, or tokenization with a vaulting servjce, is pretty much standard recommended practice for storing sensitive data.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#26

Post by webstrand »

lastofus wrote: post #11
hn_throwaway_99 wrote: post #7

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

Doesn't encrypting your data before insertion make your data unable to be indexed/searched easily?

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.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#27

Post by hn_throwaway_99 »

lastofus wrote: post #11
hn_throwaway_99 wrote: post #7

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

Doesn't encrypting your data before insertion make your data unable to be indexed/searched easily?

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.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#28

Post by hn_throwaway_99 »

rudasn wrote: post #15
hn_throwaway_99 wrote: post #7

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

How can you do client side encryption with web apps though? While keeping the key on the client, I assume, and allowing multiple browser sessions for the same user?

When I say "client side encryption", I'm referring to the database client, which in most web apps is actually code running on a private server (i.e. browser code makes API calls to a server running something like Python, Node or Java, and that server code makes calls to the DB - it's on the server where PII is encrypted).

That said, you can also use the SubtleCrypto API in the browser to encrypt data before it is even sent to the server.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#29

Post by GauntletWizard »

hn_throwaway_99 wrote: post #27
lastofus wrote: post #11

Earlier quoted context omitted.

Doesn't encrypting your data before insertion make your data unable to be indexed/searched easily?

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.

Blind indexes are useless when working with limited address spaces like Social Security Numbers, and even US Addresses[1]. It would take under an hour to reverse these on my current home PC.

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

Re: Securing Your PostgreSQL DB with Roles and Privileges

#30

Post by edmundsauto »

pphysch wrote: post #17
anarazel wrote: post #16

Earlier quoted context omitted.

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 c…

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!

Supabase has pretty good docs and a nice Ui to play around with this, btw.
Post reply on HN