Setting a default postgres schema for db connection

[rash-pro]

Hello!,

I would like to know if there is a way to set a default postgres schema per connection after the database connection has been declared

Let me explain a little bit how I'm doing things right now,
I have an AWS Lambda function that processes some reports for an enterprise database, i decided to use Sequel for it's
speed, this database uses multiple schemas for "tenant" database encapsulation.

Each time a request is processed i know which schema is needed, but as i know only this information at "execution" time
i can't declare the de database connection as suggested on the documentation (DB = Sequel.connect...)

At the moment what i do is declare a global variable named $db but i understand this not a good programming practice.

Here is hoping someone can help me shedding some light on a path to take with this.

Thanks for reading, and sorry for my english, and if my question is no clear enough.

[jeremyevans]

You can specify the :search_path Database option, which sets the default search path for the connection. Changing the search_path for a connection at runtime is possible but a very bad idea if you don't exactly understand what you are doing.

You are probably better off making sure your queries use properly qualified schemas:

DB.from{schema[:database][:table]}.sql
# SELECT * FROM schema.database.table

If you want to get fancy, it is possible to get the schema delayed until runtime:

ds = DB.from(Sequel.delay{Sequel[Thread.current[:sequel_schema] || :default_schema][:database][:table]})
ds.sql
# SELECT * FROM default_schema.database.table
Thread.current[:sequel_schema] = :foo
ds.sql
# SELECT * FROM foo.database.table

[rgalanakis]

Would it be possible to open a new connection via Sequel.connect for each tenant, and set the search path on that connection? A few extra connections shouldn't hurt. I would just make sure that provides the right degree of client config isolation (I think it does but am not sure if something is shared by Sequel across Database instances or something).

Also I am not sure of the implications of querying the wrong data, but I can imagine it would be very easy to accidentally select the wrong tenant when processing one tenant- the schemas don't really provide isolation. In addition to the 'db connection per-tenant' you could also use PG users/roles who only have access to the tenant's expected schemas/tables, so queries against their data could not hit another tenant's. Just a thought.

[jeremyevans]

Would it be possible to open a new connection via Sequel.connect for each tenant, and set the search path on that connection? A few extra connections shouldn't hurt. I would just make sure that provides the right degree of client config isolation (I think it does but am not sure if something is shared by Sequel across Database instances or something).

This is possible if you are using sharding support. However, I wouldn't use this approach unless you had a small number of tenants that were also regularly using the application, or you were using the arbitrary_servers extension to connect/disconnect per request.

Also I am not sure of the implications of querying the wrong data, but I can imagine it would be very easy to accidentally select the wrong tenant when processing one tenant- the schemas don't really provide isolation. In addition to the 'db connection per-tenant' you could also use PG users/roles who only have access to the tenant's expected schemas/tables, so queries against their data could not hit another tenant's. Just a thought.

I agree that this approach is better for security. I use separate database users (occasionally with separate schemas) to enforce isolation in most of my production applications, and it does work well, as long as you don't have a ton of different database users. I think my largest app has about 10-12 database users to enforce isolation.

In case anyone is interested in defense-in-depth techniques for Ruby web applications, I gave a presentation a few years ago regarding some of the techniques I use (some modifications since, but much of the content is still good): http://code.jeremyevans.net/presentations/rubyhack2018/index.html

[rash-pro]

The database is for a SasS, so the tenants change regularly, so an approach to add a custom connection for each one seems unlikely,
also as this is an AWS Lambda function the connection only lasts the invocation of the function, to avoid leaking to other tenants i execute
everything inside a connection block like so:

Sequel.connect(url) do |db|
    $db = db
    Sequel.extension :core_extensions
    $db.extension :pg_array, :pg_json, :pg_row
    Sequel.extension :pg_json_ops

    ...# Code to invoke reports
end

and then i use $db on the other files to query the db, the url there includes the search_path, but i know that looks ugly, but solves the problem right now, that's why i was looking for a way to improve the definition of the connection.

[jeremyevans]

As long as you aren't using threads to handle multiple connections, and you aren't using models, that seems reasonable, since it will disconnect after the block exits. You could use the arbitrary_servers extension to avoid loading the other extensions each time the block is called, but it's probably not worth the complexity unless it is causing a performance issue, or you are using models.

[rash-pro]

Yeah no threads, and no models, since each report is very specific i didn't see a good reason to include them.

thanks for the feedback!