feat: add reassign_owned_to parameter to role ressource

[jBouyoud]

Description

This PR adds a new optional parameter reassign_owned_to to the postgresql_role resource, allowing users to specify the role to which owned objects should be reassigned when dropping a role.

Critical Fix (Jan 20, 2026): The implementation now correctly executes REASSIGN OWNED across all accessible databases, not just the current one. This fixes a critical bug where roles with objects in multiple databases couldn't be properly cleaned up.

Problem Solved

REASSIGN OWNED BY role TO target operates at the database level, not at the cluster level. The initial implementation only executed this command in the current database, causing DROP ROLE to fail when the role owned objects in other databases.

Error encountered before fix

ERROR: role "role_name" cannot be dropped because some objects depend on it
DETAIL: owner of table xyz in database other_db

Changes

Core Functionality

• Added reassign_owned_to attribute to the postgresql_role resource schema
• Modified the resourcePostgreSQLRoleDelete function to use the specified role instead of always defaulting to the current database user
• Multi-database support: Lists all accessible databases and executes REASSIGN OWNED + DROP OWNED in each one
• Added listDatabases() helper function in helpers.go with comprehensive unit tests

Testing

• Added acceptance test TestAccPostgresqlRole_ReassignOwnedTo to validate the functionality
• Added unit tests for listDatabases() function using go-sqlmock:
  • Successfully lists multiple databases
  • Handles empty database list
  • Handles query errors
  • Handles scan errors
  • Verifies template databases are excluded
  • Works with transactions

Documentation

• Added documentation for the new parameter with usage examples

Behavior

When a role is deleted:

• If reassign_owned_to is set, owned objects are reassigned to the specified role in all databases
• If reassign_owned_to is not set, the default behavior is preserved (reassign to current user in all databases)
• If skip_reassign_owned is true, the reassign_owned_to parameter is ignored
• Each database operation is performed in its own transaction for atomicity
• Template databases (template0, template1) and non-connectable databases are excluded

Implementation Details

Query used to list databases

SELECT datname 
FROM pg_database 
WHERE datallowconn = true 
  AND NOT datistemplate

Multi-database execution flow

1. List all accessible databases (excluding templates)
2. For each database:
   • Connect to the database
   • Execute REASSIGN OWNED BY role TO target
   • Execute DROP OWNED BY role
   • Commit the transaction
3. Finally, execute DROP ROLE at the cluster level

Example Usage

resource "postgresql_role" "admin_role" {
  name = "admin_role"
}

resource "postgresql_role" "temp_role" {
  name              = "temp_role"
  login             = true
  password          = "temppass"
  reassign_owned_to = postgresql_role.admin_role.name
}

When temp_role is destroyed, all objects owned by temp_role in all databases will be reassigned to admin_role before the role is dropped.

Files Modified

• postgresql/helpers.go: New listDatabases() helper function
• postgresql/helpers_test.go: Unit tests for listDatabases()
• postgresql/resource_postgresql_role.go: Multi-database REASSIGN OWNED logic
• postgresql/resource_postgresql_role_test.go: Acceptance test

Impact

• ✅ Fixes critical bug preventing role deletion in multi-database environments
• ✅ No breaking changes to existing functionality
• ✅ Improves reliability of role cleanup operations
• ✅ Better error handling with detailed error messages per database
• ✅ Comprehensive test coverage with unit and acceptance tests

Related

This change is useful when managing roles in environments where:

• The provider's connection user may not have sufficient privileges
• You want explicit control over object ownership during role deletion
• Roles own objects across multiple databases (critical fix)

[merlinofcha0s]

Really want to see that merged soon!