No way to use non-login group roles for resource operations

[Thomas-Vos00]

Problem

The provider cannot operate as a non-login group role. PostgreSQL's standard mechanism for this is SET ROLE, but there is no way to configure the provider to execute SET ROLE before performing operations.

This means if your privilege model uses non-login group roles (a fundamental PostgreSQL pattern), you cannot use this provider to create resources owned by those roles.

Background: How PostgreSQL Group Roles Work

PostgreSQL separates authentication from authorization through login and non-login roles:

├── cluster_superuser (NOLOGIN)     ← Holds privileges, cannot authenticate
│   └── terraform_user (LOGIN)      ← Authenticates, inherits membership

When terraform_user connects and needs to act with cluster_superuser privileges (e.g., creating objects owned by that role), the standard approach is:

SET ROLE cluster_superuser;
CREATE ROLE some_role;        -- Created as cluster_superuser
CREATE SCHEMA app;            -- Owned by cluster_superuser
RESET ROLE;

This is not an exotic pattern—it's how PostgreSQL is designed to be used. Non-login group roles exist specifically for this purpose.

Note: Inhertiance isn't very important here, since special privileges, like the ability to create roles, are never inherited.

Why This Matters

Organizations use non-login group roles because:

• Security: Login credentials shouldn't carry elevated privileges directly
• Shared ownership: Multiple team members can create objects owned by a shared role, preventing orphaned objects
• Managed databases: AWS RDS, GCP Cloud SQL, and Azure Database don't provide true superuser access—group roles are the only way to organize elevated privileges
• Audit clarity: Logs show who authenticated (login role) while ownership reflects logical responsibility (group role)

Without SET ROLE support, teams following these practices cannot use Terraform to manage their PostgreSQL infrastructure.

The assume_role Attribute Does Not Solve This

The existing assume_role attribute on postgresql_role resources is unrelated to this problem. It configures what role the newly created role should assume when it logs in—it has no effect on how the provider performs operations:

resource "postgresql_role" "example" {
  name        = "my_role"
  assume_role = "some_group"  # This sets my_role's login behavior
                               # It does NOT make the provider use SET ROLE
}

Note: the documentation should probably be more clear on this behavior, the current description of assume_role reads as if it does what is described above.

No Viable Workaround Exists

Approach	Why It Doesn't Work
Multiple provider aliases	Each alias still requires a login role. You cannot connect as a non-login role, which is the entire point of group roles.
Grant LOGIN to group roles	This violates the security model and defeats the purpose of separating authentication from authorization.
createrole_self_grant (PG 16+)	Only addresses role creation permission grants. Doesn't help with schemas, databases, or other object types. Not available pre-PG16.
local-exec provisioner	Abandons declarative infrastructure management. Breaks state tracking. HashiCorp explicitly discourages this.
Direct privileges on login role	Violates principle of least privilege. Objects become owned by individual users rather than logical group roles.

There is currently no way to use this provider while following PostgreSQL's intended privilege model.

Current Impact

Users must choose between:

1. Abandoning PostgreSQL group role best practices to use Terraform
2. Abandoning Terraform to follow PostgreSQL best practices
3. Accepting that Terraform-managed objects will be owned by login users rather than appropriate group roles

[Bafff]

@Thomas-Vos00 Oh, I see, you want the provider to operate as another non-login group role.

I'm having different case that I'm creating DB / user account and then assign user account to:

ALTER ROLE "name" SET ROLE TO "assumed_role" 

and change ownership on the DB/schema