adding support for postgres SET ROLE?

[sv3ndk]

Hi,

I'm considering using dbmate for my current project using Postgres, although to make it work for my setup I need the ability to switch role after the db connection is established.

why ?

My Postgres users are configured to have (almost) no permissions themselves, but are allowed to switch temporarily to some specific roles depending on the tasks, similarly to the sudo linux logic.

Expressed in Postgres lingo: those users are granted some roles but do not inherit automatically their permissions.

A simplified version of the setup looks as follows

-- (PG 15 syntax used below)

-- those roles act as "groups"
CREATE ROLE my_read_only NOLOGIN;
CREATE ROLE my_read_write NOLOGIN;

-- "group" schema level permission:
GRANT ALL ON SCHEMA my_schema TO my_read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON TABLES TO my_read_write;
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO my_read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO my_read_only;

-- actual users: 
--   they don't have any table nor schema level permission 
--   they don't automatically have the permissions of the roles they are part of:
CREATE ROLE alice LOGIN NOINHERIT;
CREATE ROLE bob LOGIN NOINHERIT;

-- assign users to "groups":
GRANT my_read_only TO alice;
GRANT my_read_write TO alice;
GRANT my_read_only TO bob;

The way this works is that users first connect with their credentials:

postgres://alice:somepassword@somehost:5432/my_db?sslmode=disable&search_path=my_schema

Then depending on what they need to do, they can switch to read-only or read-write role (if they're allowed to):

SET ROLE my_read_write

Common GUI tools like DBeaver support this pattern, allowing to specify an additional role to switch to as part of the connection details.

How

dbmate does not support to automatically SET ROLE at the moment, although it seems pretty straightforward to add it. I created a quick proof of concept here with hardcoded values, and I confirm it works with the setup I describe above:

https://github.com/sv3ndk/dbmate/pull/1/files

Next steps?

Would this be a feature you'd be open to? I'm happy to work on this and re-work my proof into a workable solution.

If so, let me know your thought on how to approach this. I have in mind to extend the Driver struct to contain an additional role field, although I'm happy to align on any direction you give me in that regard.

Looking forward to your thoughts!

[dossy]

A more general solution might be to introduce a new command-line argument named something like --init-sql that can be specified zero or more times, and the string args would be executed one at a time after connecting to the database before applying any other SQL statements.

I would be more in favor of this than a Postgres-specific change that's limited to only invoking a SET ROLE statement.

The one trap that your PR does illuminate is that when we use external executables for certain functionality, such as dumping the schema, we may not have a way of passing along these initialization SQL statements to the external program to invoke before it does its work.

There's a separate discussion #547 around why there's no mechanism for supplying additional command line arguments to the dump command that dbmate invokes, so these two changes (init-sql, dump options) combined would solve your problem in a very general way, and allow for a lot of other problems to be solved with no additional changes to dbmate.

[dossy]

#596 wants to invoke select pg_advisory_xact_lock(48372615) before performing migrations to prevent multiple instances from applying migrations concurrently, and --init-sql could be used to provide this functionality without having to implement a specific migration lock feature.

I'll continue to accumulate actual use cases for --init-sql that I come across here, in anticipation of putting together a PR.