4 ms·

The only part of this I agree with is his comment on database permissions. Every modern SQL database has a concept of users and permissions that are divorced f

by snuxoll 10y ago

The only part of this I agree with is his comment on database permissions. Every modern SQL database has a concept of users and permissions that are divorced from your application, you're left with three options all of which are flawed. 1. Handle security inside your application. This is the worst choice if users need to get a LIST of records they have access to and it's determined by something more than a simple WHERE owner_id = :user_id. Think multi-tenant applications where records can belong to a tenant, and users have access based on a org hierarchy or other criteria. Suddenly you're having to filter a whole list of records out in your application code, and this makes implementing pagination awful (do you requery until you fill up a page, or present a partial page?). You are also taking full responsibility for security, if you modify your queries to filter records out you open up to human error where someone forgets to filter this one query. 2. You implement a method to synchronize your application users with the database, and use the database engines RLS support to handle access control. This is probably the best approach for web applications, but the caveats that come from it still suck. You have to make sure the connection is set as the user performing the action, this is doable with PostgreSQL, MSSQL and Oracle at the least (SET ROLE / EXECUTE AS) without destroying your ability to use a single connection pool - but for all the security you get out of this your web application user still has all the keys and if that account is compromised or there is a flaw that can cause your application to not switch security contexts you just failed at protecting your data. 3. Just use the database directly, it's handles authentication and you never change security contexts for a session - you can safely utilize the RLS functions of your database without any real risk since that database session is fixed to the user it is handling. Downside, you just lost connection pooling and while pooling middleware like pgPool can help you still have Y more connections since you need to maintain a separate one for each user or tenant at best. Approach 2 is by far the best we have, and you can make it safer by doing things like utilizing pg_hba.conf to limit access to the application user to the servers hosting your applications - but maybe you're using docker and IP addresses aren't fixed anymore (well, shit!). Also, how are you going to ensure the database connection is in the correct state when you make a query? Where are you going to plug that into your request pipeline? I'd really like to see modern tooling around this problem, I don't know what exactly it would look like but it would be nice to have SOME improvement in the area.

mixmastamyk 10y ago
Am looking at building a multitenant app with pg, so thanks for this comment. Know of any resources that discuss these patterns at length?
- snuxoll 10y ago
  A lot of it is technology stack dependent, unfortunately. Option 1 can be implemented when using an ORM with decent support for something like Active Record scopes, with LINQ you could have a method on your model that returns a pre-filtered IQueyrable, with JPA you'd use the criteria API. If you're doing hand written SQL then have fun adding it to all of your queries or using some query builder to add the security filters. Either way, this approach is prone to human error. Option 2 is a little simpler, assuming you understand the RLS functionality of your database. At the start of your HTTP request before you touch the database you SET ROLE to your tenant or user (you'll have to write some code to create these database users), and in some hook that is called when a connection is returned to your pool you'll RESET ROLE. Personally, I've had a hard time finding anything decent on handling security in multi-tenant applications or where security goes beyond simple ownership checks. Maybe I'll get around to writing a series of articles on my blog someday.

by snuxoll 10y ago

mixmastamyk 10y ago

Am looking at building a multitenant app with pg, so thanks for this comment. Know of any resources that discuss these patterns at length?

snuxoll 10y ago
A lot of it is technology stack dependent, unfortunately. Option 1 can be implemented when using an ORM with decent support for something like Active Record scopes, with LINQ you could have a method on your model that returns a pre-filtered IQueyrable, with JPA you'd use the criteria API. If you're doing hand written SQL then have fun adding it to all of your queries or using some query builder to add the security filters. Either way, this approach is prone to human error. Option 2 is a little simpler, assuming you understand the RLS functionality of your database. At the start of your HTTP request before you touch the database you SET ROLE to your tenant or user (you'll have to write some code to create these database users), and in some hook that is called when a connection is returned to your pool you'll RESET ROLE. Personally, I've had a hard time finding anything decent on handling security in multi-tenant applications or where security goes beyond simple ownership checks. Maybe I'll get around to writing a series of articles on my blog someday.