Feature/Support for: Better Postgresql Flexible Server role creation options.

[leonrob]

Is there an existing issue for this?

• [X] I have searched the existing issues

Community Note

• Please vote on this issue by adding a :thumbsup: reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment and review the contribution guide to help.

Postgres Flexible does not have any out of the box options for roles without using Hashicorp Vault. If a setup uses vault_generic_secret and things like vault_database_secret_backend_role users are able to work around this by injecting Postgresql create statements to run for a creation_statement

This would force any newly created roles to be attached to actual vault roles for the database, not database roles.

Currently

resource "azurerm_postgresql_flexible_server" allows the valuesadministrator_loginandadministrator_password`

That part is fine. Using dynamic setup keeps those credentials safe.

Request:

Add an azurerm_postgres_flexible feature that creates user roles on the database. This would bypass hashicorp vault - which for this setup is fine.

Something similar to how the setup below possibly?

resource "azurerm_cosmosdb_postgresql_role" "example" { name = "examplerole" cluster_id = azurerm_cosmosdb_postgresql_cluster.example.id password = "H@Sh1CoR3!" }

Maybe something like

resource "azurerm_postgresql_role" "example_role" { server_name = azurerm_postgresql_server.example.name resource_group_name = azurerm_resource_group.example.name name = "my_role" login = false }

Then

resource "azurerm_postgresql_role_permissions" "example_grant" { server_name = azurerm_postgresql_server.example.name resource_group_name = azurerm_resource_group.example.name database_name = "my_database" schema_name = "public" object_type = "TABLE" object_name = "my_table" role_name = azurerm_postgresql_role.example_role.name permissions = ["SELECT", "INSERT", "UPDATE", "DELETE"] }

It would not have to be exactly like this - but something that is workable and can be used. This would be extremely helpful to my entire organization.

Description

With the introduction of Azure Postgres Flexible Server Version 16 role permissions have become more difficult.

More information:

New or Affected Resource(s)/Data Source(s)

azurerm_all

Potential Terraform Configuration

`
resource "azurerm_postgresql_role" "example_role" {
  server_name         = azurerm_postgresql_server.example.name
  resource_group_name = azurerm_resource_group.example.name
  name                = "my_role"
  login               = false
}
`

Then

`
resource "azurerm_postgresql_role_permissions" "example_grant" {
  server_name         = azurerm_postgresql_server.example.name
  resource_group_name = azurerm_resource_group.example.name
  database_name       = "my_database"
  schema_name         = "public"
  object_type         = "TABLE"
  object_name         = "my_table"
  role_name           = azurerm_postgresql_role.example_role.name
  permissions         = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}
`

References

No response

[leonrob]

I really think this would help all clients who utilize Postgres. Especially if Postgres is here to stay. I'd be willing to chat on a call with anyone about this if there are any questions.

[NicklasWallgren]

We would benefit from this as well.

[leonrob]

bump

[mjanschek]

Might be worth considering to add Entra ID authentication for this new role creation, see https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-manage-azure-ad-users#create-a-role-using-azure-ad-object-identifier

[leonrob]

Might be worth considering to add Entra ID authentication for this new role creation, see https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-manage-azure-ad-users#create-a-role-using-azure-ad-object-identifier

Thanks for this I will read through. Might be an option I can engineer into our stuff later on

I feel like the feature in question i'm asking for shouldn't be too difficult (i'm hoping). And would solve SO many issues