Allow setting postgres role during connection setup

[Mike Crute]

When attempting to mutate database objects (tables, enumerations, etc...) Postgres requires that the logged-in role match the owning role for those objects. Typically the owning role of an object is the role that created it. This is a problem when using systems such as ​Hashicorp Vault which create ephemeral single-use roles for applications since those roles will, by definition, never match the creating/owning role. This results in permission errors during structural changes such as migrations. These errors are of the general form "sequence must have same owner as table it is linked to".

The solution to this problem is pretty straightforward. First create a grouping role for the application (roles do not distinguish between being a user or a group) that can become the owner of the database objects. Then create the temporary roles generated by the credential management system as members of this grouping role. Finally, assume that grouping role before performing actions on the database using the ​SET ROLE statement during connection setup. This will cause all of the temporary roles to act as the grouping role which has ownership of all of these objects and eliminate the permission issues.

I propose adding a PostgreSQL-specific driver option in the settings called role which, if present, will cause the connection setup code to issue the appropriate SET ROLE statement at connection creation.

I have a patch for this that I'm testing and will submit soon.

[Mariusz Felisiak]

The solution to this problem is pretty straightforward. First create a grouping role for the application (roles do not distinguish between being a user or a group) that can become the owner of the database objects. Then create the temporary roles generated by the credential management system as members of this grouping role. Finally, assume that grouping role before performing actions on the database using the ​SET ROLE statement during connection setup. This will cause all of the temporary roles to act as the grouping role which has ownership of all of these objects and eliminate the permission issues.

Thanks for the ticket, however I don't see anything "straightforward" about this solution, it seems complicated and quite niche. All the role-juggling looks like something the DBA should do, not something that framework is responsible for. Also, is it not already possible to SET ROLE in DATABASE["OPTIONS"]? (see ​docs)

[Mike Crute]

Replying to Mariusz Felisiak:

Thanks for the ticket, however I don't see anything "straightforward" about this solution, it seems complicated and quite niche. All the role-juggling looks like something the DBA should do, not something that framework is responsible for.

Agreed that the role setup should be done by whomever is responsible for managing the database but I do think it's the job of the framework to assume the correct role at runtime. There's no way for an administrator to force an application to assume a role by default in postgres. They can assign a role to a role but it's up to the connection owner to actually assume that role, which has to be done by executing a statement on the newly opened connection.

As for niche: in my experience, using ephemeral credentials that are leased from a (often proprietary) credential management system is pretty common in really large companies that have strong controls around accounts and security. Also this is definitely a problem for anyone using Hashicorp Vault for credential management which I would suggest is not particularly niche.

Also, is it not already possible to SET ROLE in DATABASE["OPTIONS"]? (see ​docs)

Perhaps I'm missing something but I do not see how this is possible in the current settings. The connection setup logic must execute SET ROLE <role> against a cursor to set the role. It's not a client option that can just be passed through as far as I'm aware.

[Mike Crute]

I've submitted a ​pull request for this.

[Florian Apolloner]

I think this makes sense and is hard to achieve otherwise short of overriding the backend. I am wondering if explicitly supporting role makes sense though. Would a more generic approach like init_command in mysql help? Then again we never had the need for this in postgresql so I rather explicitly support role (or assume_role maybe) -- this way we can easily shuffle it around in the backend code if needed.

[Mariusz Felisiak]

Replying to Mike Crute:

Also, is it not already possible to SET ROLE in DATABASE["OPTIONS"]? (see ​docs)

Perhaps I'm missing something but I do not see how this is possible in the current settings. The connection setup logic must execute SET ROLE <role> against a cursor to set the role. It's not a client option that can just be passed through as far as I'm aware.

It's possible to set e.g. search_path via DATABASE["OPTIONS"]:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "OPTIONS": {
            "options": "-c search_path=custom_path",
        },
    },

Is it not possible to set ROLE in the same way?

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "OPTIONS": {
            "options": "-c role=custom_role",
        },
    },

[Florian Apolloner]

Ha, I didn't know of options either. To be honest I find it rather ugly, but if it indeed fixes the problem let's add some docs about how to set the role with it and accept the doc fix as a fix for the ticket? On a related note; I am not sure how well -c role=my_role plays with connection poolers, might be worth to investigate (but if that is broken, setting the search_path like that is broken as well I guess)

[Mike Crute]

Replying to Mariusz Felisiak:

Is it not possible to set ROLE in the same way?

Unfortunately this does not work for roles, the ​postgres docs list all of the parameters supported for this configuration method and roles are not one of them. I'm pretty certain the only way to assume a role is to issue a SET ROLE statement on the connection.

[Mike Crute]

Replying to Florian Apolloner:

I am wondering if explicitly supporting role makes sense though. Would a more generic approach like init_command in mysql help? Then again we never had the need for this in postgresql so I rather explicitly support role (or assume_role maybe) -- this way we can easily shuffle it around in the backend code if needed.

I don't think this is a general concept, it's pretty specific to the postgres security model. I'm happy to call it either role or assume_role if you have a preference (I do not). Given that it's pretty postgres specific that would mean it should stay in OPTIONS right?

[Florian Apolloner]

Yes it would probably stay in OPTIONS and I'd slightly prefer assume_role since one can login into roles normally and as such might confuse it with USER.

[Mike Crute]

Replying to Florian Apolloner:

I'd slightly prefer assume_role since one can login into roles normally and as such might confuse it with USER.

I've updated my PR to use assume_role

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 0b78ac3f:

Fixed #34200 -- Made the session role configurable on PostgreSQL.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 18d79033:

Refs #34200 -- Removed unnecessary check in DatabaseWrapper.ensure_role() on PostgreSQL.

ensure_role() is only called in init_connection_state() where a new
connection is established.