the right place to SET a session variable when using gorm with row level security for postgresql

Question

We are trying to find the go / gorm equivalent of the following snippet of java code

// Every time the app asks the data source for a connection
// set the PostgreSQL session variable to the current tenant
// to enforce data isolation.
@Override
public Connection getConnection() throws SQLException {
    Connection connection = super.getConnection();
    try (Statement sql = connection.createStatement()) {
        sql.execute("SET app.current_tenant = '" + TenantContext.getTenant() + "'");
    }
    return connection;
}

This is from https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/

Our requirement is to set this value for every new incoming request, ie the app.current_tenant value can be different for every incoming request even though they would need to fetch data from the same database / dsn.

On the gorm side, I could find

https://gorm.io/docs/connecting_to_the_database.html#PostgreSQL

Which does

import (      
 "gorm.io/driver/postgres"
 "gorm.io/gorm"
)

dsn := "host=localhost user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai"
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

So we get a db that is of type DB within gorm.

We can now run the query

db.Exec(fmt.Sprintf("SET myapp.current_tenant_id = '%s'", currentTenantValue).Session(&gorm.Session{})

That's for the first time. However, next time, we may have a different currentTenantValue

So, do we gorm.Open() and then create a new Session for every request?

We are not able to find out how to distinguish between the connection (which requires a dsn) and the session (that is for the same dsn / connection), because both gorm.Open() and .Session() return the same datatype.

It's probably something like

getConnection() - this would return the actual *DB via the dsn and we do this only once, followed by

calling

.Session() on it with the query in place

Is that the right thing to do? Lastly we understand that we don't need to close the connection created by gorm.Open() manually. However what about the session, do we do a session close once we are done? How do we do that the original gorm.Open() connection doesn't close when we close the session if it has to be closed manually?

It would help greatly if any of you have done this before and can share insights