We are implementing session variables in postgresql to take advantage of row level security (RLS) in postgresql.
For this purpose, we need
request 1 - has the session variable value set to TENANT1
request 2 - has the session variable set to TENANT2
Both requests can be concurrent.
So we require that after opening the connection using gorm.Open(), it should be possible to obtain two separate connections from the underlying connection pool and both should work independent of each other such that one has session variable value set to TENANT1 and the other has session variable set to TENANT2
However, we note by running
GORM_DIALECT=postgres go test
that the output is
2023/12/18 12:26:51 testing postgres...
FATA-010 DB Ptr is [0xc0003b1410]
FATA-020 & DB Ptr is [0x125a140]
2023/12/18 12:26:51 /home/alok/github.com/alkuma/gorm-connection-pool/db.go:147
[0.081ms] [rows:0] SET myapp.current_tenant_id = 'TENANT1'
FATA-030 DB session 1 is [0xc0004e8000]
FATA-040 &DB session 1 is [0xc0004bc240]
2023/12/18 12:26:51 /home/alok/github.com/alkuma/gorm-connection-pool/db.go:124
[0.155ms] [rows:1] SELECT current_setting('myapp.current_tenant_id')
FATA-050 dbPtr1 tenantId is [TENANT1]
2023/12/18 12:26:51 /home/alok/github.com/alkuma/gorm-connection-pool/db.go:128
[0.061ms] [rows:1] SELECT current_setting('myapp.current_tenant_id')
FATA-060 from1Session1 tenantId is [TENANT1]
2023/12/18 12:26:51 /home/alok/github.com/alkuma/gorm-connection-pool/db.go:147
[0.054ms] [rows:0] SET myapp.current_tenant_id = 'TENANT2'
FATA-070 DB session 2 is [0xc0004e8270]
FATA-080 &DB session 2 is [0xc0004bc278]
2023/12/18 12:26:51 /home/alok/github.com/alkuma/gorm-connection-pool/db.go:136
[0.057ms] [rows:1] SELECT current_setting('myapp.current_tenant_id')
FATA-120 from1Session1 tenantId is [TENANT2]
2023/12/18 12:26:51 /home/alok/github.com/alkuma/gorm-connection-pool/db.go:140
[0.058ms] [rows:1] SELECT current_setting('myapp.current_tenant_id')
FATA-130 from1Session2 tenantId is [TENANT2]
2023/12/18 12:26:51 /home/alok/github.com/alkuma/gorm-connection-pool/main_test.go:14
[0.832ms] [rows:1] INSERT INTO "users" ("created_at","updated_at","deleted_at","name","age","birthday","company_id","manager_id","active") VALUES ('2023-12-18 12:26:51.536','2023-12-18 12:26:51.536',NULL,'jinzhu',0,NULL,NULL,NULL,false) RETURNING "id"
2023/12/18 12:26:51 /home/alok/github.com/alkuma/gorm-connection-pool/main_test.go:17
[0.359ms] [rows:1] SELECT * FROM "users" WHERE "users"."id" = 1 AND "users"."deleted_at" IS NULL ORDER BY "users"."id" LIMIT 1
PASS
ok gorm.io/playground 0.047s
As can be seen, FATA-050 and FATA-060 have the value set to TENANT1
However, the moment we set the value for the second session to TENANT2, the value is updated for both the first session as well as the second session, which means they are both one and the same.
So the question is? How do we obtain two distinct sessions / connections (or whatever is the terminology gorm uses) that can perform operations in parallel?
A very simple solution is to just create a new database connection using the dsn and gorm.Open() every time, but then there is no connection pooling - is our understanding.
The other solutions seems to be to create a new connection whenever a new tenant is encountered and return the correct *gorm.DB based on the tenant Id. This means that the number of connection pools will be equal to the number of tenants - that's a better solution than the first one but we are still not able to use connections from the same pool for multiple tenant requests.
This is raised as an issue at https://github.com/go-gorm/gorm/issues/6753 and this is the underlying code example
Explain your user case and expected results
We are implementing session variables in postgresql to take advantage of row level security (RLS) in postgresql.
For this purpose, we need
Both requests can be concurrent.
So we require that after opening the connection using
gorm.Open(), it should be possible to obtain two separate connections from the underlying connection pool and both should work independent of each other such that one has session variable value set to TENANT1 and the other has session variable set to TENANT2However, we note by running
that the output is
As can be seen,
FATA-050andFATA-060have the value set toTENANT1However, the moment we set the value for the second session to
TENANT2, the value is updated for both the first session as well as the second session, which means they are both one and the same.So the question is? How do we obtain two distinct sessions / connections (or whatever is the terminology gorm uses) that can perform operations in parallel?
A very simple solution is to just create a new database connection using the dsn and
gorm.Open()every time, but then there is no connection pooling - is our understanding.The other solutions seems to be to create a new connection whenever a new tenant is encountered and return the correct *gorm.DB based on the tenant Id. This means that the number of connection pools will be equal to the number of tenants - that's a better solution than the first one but we are still not able to use connections from the same pool for multiple tenant requests.
So how do we do this in an efficient manner?
This is raised as an issue at https://github.com/go-gorm/gorm/issues/6753 and this is the underlying code example