Can RDS Proxy support RLS patterns, with many sets of DB credentials dynamically added? Are there alternatives to this pattern for multi-tenant setups / SaaS architectures?

0

tl;dr; I want to create a separate DB user account for each tenant in a SaaS, to support multi-tenant setup for PostgreSQL db using Row Level Security (RLS). It seems this isn't possible or practical with RDS Proxy because the SDK doesn't allow for easy management of secrets / credentials associated with RDS Proxy. What am I missing? How can I achieve a multi-tenant RLS setup with RDS Proxy and PostgreSQL RLS?

I'm trying to create a SaaS with a multi-tenant DB setup. RDS Aurora Postgres. Each tenant in the database === a DB account (see: https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/).

This was going fairly well when I was in the PoC stage, because I ignorantly put off storing DB secrets in secret manager and just had a few sample accounts setup to test things out.

That said, I've recently realized that with RDS Proxy you need to actually add each database credential to the proxy in order to be able to use that credential through the proxy... and that's not something that happens instantly, it can take an unknown amount of time for RDS Proxy to be updated, and frankly I'm not sure how well this would scale adding potentially hundreds or even thousands of credentials to RDS Proxy.

I had hoped / thought maybe that using the "IAM Authentication" would solve the issue, but although it doesn't seem super well documented / clear (at least not through the AWS console), I think IAM Authentication doesn't do anything for us unless we're using SQL server:

IAM Authentication. Choose whether to require, allow, or disallow IAM authentication for connections to your proxy. The allow option is only valid for proxies for RDS for SQL Server. The choice of IAM authentication or native database authentication applies to all DB users that access this proxy.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-setup.html

If I'm misunderstanding something here I'd love to know, and would really appreciate any advice. I feel like I'm fighting a loosing battle with my current approach and would love to know if there is something I'm missing that would salvage things!

If not, then I'm left either

  1. Figure out how to programmatically add secrets / users to the DB Proxy - I think https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/clients/client-rds/interfaces/modifydbproxyrequest.html#auth is perhaps a mechanism I could use, but again it doesn't feel like it was really built for this - each time a user registered, it looks like I'd have to basically update the entire proxy, I can't "just" add a single user.
  2. Switch away from the "each user in the SaaS has a separate DB user" approach to something else, essentially putting the onus of security back on the application layer (which was my entire goal of using RLS originally).
  3. ??

Note that the AWS documentation on RDS Proxy and adding database users of course says that you can certain add DB users, this I know, the issue is adding users at scale, dynamically, via the SDK - it just doesn't feel like RDS Proxy is designed for this (for understandable reasons I might add, I realize there is probably a fair amount of complexity hidden in RDS Proxy).

1 Answer
0

RDS Proxy is incompatible with PostgreSQL RLS policies based on anything having to do with the connection (who the role/user is that's connecting or any session variable set on the connection). Why? Because RDS Proxy multiplexes (shares) the connections to the database behind the scenes. Because of this there's no guarantee that your different tenants will be handed different (isolated) connections...

The only way to use RDS Proxy with this multi-tenant RLS pattern would be to create an RDS Proxy per tenant... which sort of defeats the purpose of RDS Proxy.

You should be able to use IAM database authentication for Aurora/RDS PostgreSQL. Again, you can't use RDS Proxy. You'd create an IAM user per tenant and then create your JDBC database connection in your client with the useAwsIam property set to true and the username set to the tenant's IAM username. Make sure your RLS policies are defined by testing against current_user. Note there are limitations to IAM database authentication and limits on the number of IAM users and roles you can have in an account.

As the blog post points out along with the sample code, you can use connection session variables instead of usernames to scale your RLS policies for larger numbers of tenants.

AWS
answered 9 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions