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

11–20 of 41 posts

Re: Securing Your PostgreSQL DB with Roles and Privileges

#11

Post by lastofus »

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?

Re: Securing Your PostgreSQL DB with Roles and Privileges

#12

Post by warent »

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

Re: Securing Your PostgreSQL DB with Roles and Privileges

#13

Post by [deleted] »

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

[deleted]

Re: Securing Your PostgreSQL DB with Roles and Privileges

#14

Post by pphysch »

anarazel wrote: post #10
pphysch wrote: post #3

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.

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.

Do I have to add that context to every query, or is it something I can set per cursor/transaction?

Re: Securing Your PostgreSQL DB with Roles and Privileges

#15

Post by rudasn »

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?

Re: Securing Your PostgreSQL DB with Roles and Privileges

#16

Post by anarazel »

pphysch wrote: post #14
anarazel wrote: post #10

Earlier quoted context omitted.

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.

Do I have to add that context to every query, or is it something I can set per cursor/transaction?

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.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#17

Post by pphysch »

anarazel wrote: post #16
pphysch wrote: post #14

Earlier quoted context omitted.

Do I have to add that context to every query, or is it something I can set per cursor/transaction?

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!

Re: Securing Your PostgreSQL DB with Roles and Privileges

#18

Post by aeonsky »

pphysch wrote: post #3

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.

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.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#19

Post by _jal »

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.

Re: Securing Your PostgreSQL DB with Roles and Privileges

#20

Post by pphysch »

aeonsky wrote: post #18
pphysch wrote: post #3

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.

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.

Interesting. Why so many roles initially, and how did you safely consolidate to 5?
Post reply on HN