[Django] #34200: Allow setting postgres role during connection setup
Django
-------------------------------------+-------------------------------------
Reporter: mcrute | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: 4.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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
[[https://www.hashicorp.com/products/vault|Hashicorp Vault]] which create
ephemeral single-use roles for applications since those roles will, by
definition, never match the creating 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
[[https://www.postgresql.org/docs/8.4/sql-set-role.html|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 postgres-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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34200>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: mcrute | Owner: nobody
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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
> [[https://www.hashicorp.com/products/vault|Hashicorp Vault]] which create
> ephemeral single-use roles for applications since those roles will, by
> definition, never match the creating 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
> [[https://www.postgresql.org/docs/8.4/sql-set-role.html|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 postgres-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.
New description:
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
[[https://www.hashicorp.com/products/vault|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
[[https://www.postgresql.org/docs/8.4/sql-set-role.html|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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: mcrute | Owner: nobody
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: New feature => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by mcrute):
* owner: nobody => mcrute
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:3>
Django
-------------------------------------+-------------------------------------
| Crute
Type: New feature | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Florian Apolloner (added)
* status: assigned => closed
* resolution: => needsinfo
* type: Bug => New feature
Comment:
> 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
[[https://www.postgresql.org/docs/8.4/sql-set-role.html|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
[https://docs.djangoproject.com/en/stable/ref/settings/#std-setting-
OPTIONS docs])
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mike Crute):
Replying to [comment:4 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 juggling should be done by a DBA or whomever is
responsible for setting up 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 a
DBA to force an application to assume a role by default in postgres. It
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 (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 [https://docs.djangoproject.com/en/stable/ref/settings/#std-setting-
OPTIONS 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mike Crute):
I've submitted a [[https://github.com/django/django/pull/16360 pull
request]] for this.
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Replying to [comment:5 Mike Crute]:
> > Also, is it not already possible to `SET ROLE` in
`DATABASE["OPTIONS"]`? (see
[https://docs.djangoproject.com/en/stable/ref/settings/#std-setting-
OPTIONS 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",
},
},
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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)
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mike Crute):
Replying to [comment:8 Mariusz Felisiak]:
> Is it not possible to set `ROLE` in the same way?
Unfortunately this does not work for roles, the
[[https://www.postgresql.org/docs/8.0/runtime-config.html|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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mike Crute):
Replying to [comment:7 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.
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mike Crute):
Replying to [comment:12 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`
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* stage: Accepted => Ready for checkin
Comment:
[https://github.com/django/django/pull/16360 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Component: Database layer | Version: 4.1
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"0b78ac3fc7bd9f0c57518d0c1a153582318edd59" 0b78ac3f]:
{{{
#!CommitTicketReference repository=""
revision="0b78ac3fc7bd9f0c57518d0c1a153582318edd59"
Fixed #34200 -- Made the session role configurable on PostgreSQL.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Mike Crute | Owner: Mike
| Crute
Type: New feature | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
In [changeset:"18d79033b90902a6d6b615b42051191fd1b37892" 18d79033]:
{{{#!CommitTicketReference repository=""
revision="18d79033b90902a6d6b615b42051191fd1b37892"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34200#comment:16>