Changes between Initial Version and Version 1 of Ticket #34200
- Timestamp:
- Dec 5, 2022, 9:43:42 PM (17 months ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Ticket #34200 – Description
initial v1 1 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".1 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". 2 2 3 3 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. 4 4 5 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.5 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. 6 6 7 7 I have a patch for this that I'm testing and will submit soon.