3

Objective

I am creating a Proof of Concept multi-tenant system that will eventually run on RDS based on the following guide:

aws rds multi tenant data isolation

Summary

According to the aws article there are two ways to achieve this:

  1. Creating a database user per tenant
  2. using a runtime parameter on connection

Approach

I would like to explore option 2 fully before making any decisions. I can see that it is possible to pass runtime configuration parameters in the connection string based on this merge request and assume this is the Options parameter from the connection string parameters doc.

Questions

This leads me to the following questions:

  • Will I be able to achieve this using the Options parameter?
  • Will connection pooling work? and if so will it have the effect of pooling connections for a single tenant?
CorporateActionMan
  • 59
  • 4
  • Which parameter were you thinking of providing via the "PGOPTIONS" mechanism, and how do you plan using it to enforce row-level security? – Shay Rojansky Dec 16 '21 at 13:08
  • Hi Shay, following the aws article I was planning to use a custom variable called app.current_tenant_id, then adding custom sql in the migration: ```c# migrationBuilder.Sql("ALTER TABLE Tenants ENABLE ROW LEVEL SECURITY;"); migrationBuilder.Sql( "CREATE POLICY tenant_isolation_policy ON Tenants USING (Id = current_setting('app.current_tenant_id')::bigint);"); ``` – CorporateActionMan Dec 16 '21 at 14:38
  • In that case yeah, that should work, and each tenant should get their own connection pool, since the "Options" component is part of the connection string. However, you must be very careful to disallow any sort of raw SQL access that would allow users to modify their current_tenant_id. – Shay Rojansky Dec 16 '21 at 16:54
  • maybe I'm doing this wrong: `Host=localhost;Database=somedb;Username=someuser;Password=somepassword;Options=app.current_tenant_id=1` I am receiving: `42601: invalid command-line argument for server process: app.current_tenant_id=1` but `show app.current_tenant_id` works against the DB – CorporateActionMan Dec 17 '21 at 11:46
  • Yes, there's a tiny almost invisible error in your options string. It's a missing `-c ` in front of `app.current_tenant_id=1`. Your full connection string would be the following: `Host=localhost;Database=somedb;Username=someuser;Password=somepassword;Options=-c app.current_tenant_id=1` – Brar Dec 19 '21 at 17:45
  • The reason for this comes from the fact that the options are really what gets passed to the backend process that will be started (or forked) to handle your connection. If you type `postgres -?` into a console window you can see all the possible options and will find out that `-c NAME=VALUE set run-time parameter` is just a command line option to set a runtime parameter in a backend. You can also set other command line options (like `-d 1-5 debugging level`) by specifying them in the connection string. – Brar Dec 19 '21 at 17:46
  • BTW: You can also quickly test this via psql. `psql "host=localhost dbname=somedb user=someuser password=somepassword options='app.current_tenant_id=1'"` will fail with `FATAL: invalid command-line argument for server process: app.current_tenant_id=1` whereas `psql "host=localhost dbname=somedb user=someuser password=somepassword options='-c app.current_tenant_id=1'"` will succeed and `SHOW app.current_tenant_id;` will return `1`. – Brar Dec 19 '21 at 17:46
  • You can set multiple options and even values that contain blanks or backslashes although the syntax is pretty much undocumented. Have a look at the following Npgsql test to see an example: https://github.com/npgsql/npgsql/blob/075312bdec24b7d90cda3f79a2488ad90dc4d8d6/test/Npgsql.Tests/ConnectionTests.cs#L929 – Brar Dec 19 '21 at 17:50
  • @ShayRojansky regarding "each tenant should get their own connection pool", I've had a difficult time finding documentation for this behavior in the npgsql docs (perhaps I've overlooked something obvious). I see you've also described this behavior here https://stackoverflow.com/a/44272654/3570474. If this is missing from the npgsql docs, I'd be happy to open an issue/PR for it. – Ben Longo May 18 '23 at 19:47
  • Yeah, a doc page on Npgsql's pooling is long overdue... I'll try to get around to it in the coming months. – Shay Rojansky May 19 '23 at 12:41

0 Answers0