PostgreSQL 16 Flexible Server: How to remove automatic azuresu-granted creator role memberships?

Question

PostgreSQL 16 Flexible Server: How to remove automatic azuresu-granted creator role memberships?

Taufiq ALblooshi 0

We need guidance on a PostgreSQL 16 role-provisioning issue in a non-production Azure Database for PostgreSQL Flexible Server environment.

Environment:

Azure Database for PostgreSQL Flexible Server
PostgreSQL 16
Customer administrator: bemtechadmin, member of azure_pg_admin

We intend to create two ordinary non-superuser application roles:

bemtech_schema_owner: NOLOGIN role used as database/schema/object owner
bemtech_app_runtime: restricted LOGIN runtime role

PostgreSQL 16 automatically creates creator membership in each newly created role with:

member = bemtechadmin grantor = azuresu ADMIN OPTION = true INHERIT OPTION = false SET OPTION = false

Our security requirement is that after bootstrap, bemtechadmin must have zero residual membership in either application role, regardless of grantor or membership options.

Our investigation indicates that an ordinary REVOKE by bemtechadmin may not remove the automatic membership granted by azuresu, while the customer administrator cannot act as azuresu.

What is the officially supported method on Azure Database for PostgreSQL Flexible Server / PostgreSQL 16 to:

Create these roles without the automatic azuresu-granted creator membership; or
Remove that membership afterward; or
Use another supported provisioning pattern that leaves bemtechadmin with zero membership while retaining a NOLOGIN owner role and restricted LOGIN runtime role?

We are not requesting superuser access or broader Azure RBAC.

Please provide the supported SQL commands or Azure procedure, required privileges, and a verification query showing that no residual membership remains.We need guidance on a PostgreSQL 16 role-provisioning issue in a non-production Azure Database for PostgreSQL Flexible Server environment.

Environment:

Azure Database for PostgreSQL Flexible Server
PostgreSQL 16
Customer administrator: bemtechadmin, member of azure_pg_admin

We intend to create two ordinary non-superuser application roles:

bemtech_schema_owner: NOLOGIN role used as database/schema/object owner
bemtech_app_runtime: restricted LOGIN runtime role

PostgreSQL 16 automatically creates creator membership in each newly created role with:

member = bemtechadmin
grantor = azuresu
ADMIN OPTION = true
INHERIT OPTION = false
SET OPTION = false

Our security requirement is that after bootstrap, bemtechadmin must have zero residual membership in either application role, regardless of grantor or membership options.

Our investigation indicates that an ordinary REVOKE by bemtechadmin may not remove the automatic membership granted by azuresu, while the customer administrator cannot act as azuresu.

What is the officially supported method on Azure Database for PostgreSQL Flexible Server / PostgreSQL 16 to:

Create these roles without the automatic azuresu-granted creator membership; or
Remove that membership afterward; or
Use another supported provisioning pattern that leaves bemtechadmin with zero membership while retaining a NOLOGIN owner role and restricted LOGIN runtime role?

We are not requesting superuser access or broader Azure RBAC.

Please provide the supported SQL commands or Azure procedure, required privileges, and a verification query showing that no residual membership remains.

0 comments

1 answer

Your answer

Answer 1

Allan Solomon Mejia 9,085

Hi @Taufiq ALblooshi

What you're seeing is expected PostgreSQL 16 role-management behavior, rather than an Azure RBAC assignment.

In PostgreSQL 16, when a non-superuser with CREATEROLE creates another role, the creator implicitly receives ADMIN OPTION on that role. PostgreSQL records this automatic grant as coming from the bootstrap superuser.

That explains the membership you're seeing:

member = bemtechadmin

grantor = azuresu

ADMIN = true

INHERIT = false

SET = false

Importantly, PostgreSQL's documentation states that the CREATEROLE user can't remove or modify this bootstrap-superuser grant; only a superuser can.

On Azure Database for PostgreSQL Flexible Server, customers aren't given the azuresu superuser role. Microsoft reserves that role for managed-service/control-plane operations, while the server administrator is NOSUPERUSER with CREATEROLE and membership in azure_pg_admin.

So I don't see a supported customer-side SQL command that lets bemtechadmin remove this particular azuresu-granted creator membership while remaining the role creator.

One useful distinction, though, is that the membership shown here has:

INHERIT = false

SET = false

Therefore, bemtechadmin does not automatically inherit the application's privileges and cannot SET ROLE to that role through this membership. ADMIN OPTION gives it role-administration capability, but isn't equivalent to inheriting or assuming the application's runtime permissions. PostgreSQL 16 separates these membership options explicitly.

You can verify the exact relationship with:

SELECT
    r.rolname AS role_name,
    m.rolname AS member,
    g.rolname AS grantor,
    am.admin_option,
    am.inherit_option,
    am.set_option
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
JOIN pg_roles g ON g.oid = am.grantor
WHERE r.rolname IN ('bemtech_schema_owner',
                    'bemtech_app_runtime');

pg_auth_members is the PostgreSQL catalog containing the grantor and the three membership-option states.

If your compliance requirement literally requires zero rows in pg_auth_members connecting bemtechadmin to those roles, rather than simply preventing privilege inheritance/impersonation, raise this with Azure Database for PostgreSQL Support. The PostgreSQL 16 bootstrap grant, plus Azure's restriction on customer superuser access, means a normal REVOKE by bemtechadmin can't satisfy that requirement.

References:

Access management in Azure Database for PostgreSQL Flexible Server

Manage users in Azure Database for PostgreSQL Flexible Server

PostgreSQL 16 - CREATE ROLE

PostgreSQL 16 - Role membership

PostgreSQL 16 – pg_auth_members

Help make this community better for everyone: If this answer helped or resolved your issue, please accept it or upvote it. If not, share more details in a comment so we can continue the discussion and find the right solution. Thank you.

Taufiq ALblooshi 0 Reputation points

2026-09-17T16:50:27.02+00:00
Thank you for the clarification regarding PostgreSQL 16 role memberships. That resolved our previous blocker.

We have now reached a related Azure Flexible Server security question regarding credential provisioning.

Our server administrator (bemtechadmin, member of azure_pg_admin) needs to assign an initial password to a restricted LOGIN role (bemtech_app_runtime).

We require the plaintext password and its SCRAM-SHA-256 verifier never to appear in PostgreSQL/Azure logs.

In an authenticated PostgreSQL 16 session we confirmed:

log_statement = none

log_min_error_statement = error

log_parameter_max_length = -1

log_parameter_max_length_on_error = 0

password_encryption = scram-sha-256

However, bemtechadmin receives SQLSTATE 42501 when attempting to change the relevant SUSET logging parameters, including log_min_error_statement, even at session/local scope.

ALTER ROLE ... PASSWORD $1 also cannot use an ordinary bind parameter for the password clause. Supplying either a plaintext password literal or a client-generated SCRAM verifier in the utility statement therefore appears capable of exposing sensitive material if that statement errors and is logged.

What is the Microsoft-supported method on Azure Database for PostgreSQL Flexible Server / PostgreSQL 16 for securely assigning or rotating a role password without exposing either the plaintext password or SCRAM verifier in PostgreSQL/Azure diagnostic logs?

Specifically, is there:

An Azure-managed/control-plane password-setting mechanism for ordinary PostgreSQL roles;

A supported secure SQL/protocol mechanism that avoids credential-bearing statement text;

A supported way for the Flexible Server administrator to suppress credential-bearing error-statement logging for this operation; or

Another Microsoft-recommended credential-provisioning pattern?

We are not requesting PostgreSQL superuser access and do not want to weaken server-wide logging permanently.

Please provide the exact supported procedure and required privileges.Thank you for the clarification regarding PostgreSQL 16 role memberships. That resolved our previous blocker.

We have now reached a related Azure Flexible Server security question regarding credential provisioning.

Our server administrator (bemtechadmin, member of azure_pg_admin) needs to assign an initial password to a restricted LOGIN role (bemtech_app_runtime).

We require the plaintext password and its SCRAM-SHA-256 verifier never to appear in PostgreSQL/Azure logs.

In an authenticated PostgreSQL 16 session we confirmed:

log_statement = none

log_min_error_statement = error

log_parameter_max_length = -1

log_parameter_max_length_on_error = 0

password_encryption = scram-sha-256

However, bemtechadmin receives SQLSTATE 42501 when attempting to change the relevant SUSET logging parameters, including log_min_error_statement, even at session/local scope.

ALTER ROLE ... PASSWORD $1 also cannot use an ordinary bind parameter for the password clause. Supplying either a plaintext password literal or a client-generated SCRAM verifier in the utility statement therefore appears capable of exposing sensitive material if that statement errors and is logged.

What is the Microsoft-supported method on Azure Database for PostgreSQL Flexible Server / PostgreSQL 16 for securely assigning or rotating a role password without exposing either the plaintext password or SCRAM verifier in PostgreSQL/Azure diagnostic logs?

Specifically, is there:

An Azure-managed/control-plane password-setting mechanism for ordinary PostgreSQL roles;

A supported secure SQL/protocol mechanism that avoids credential-bearing statement text;

A supported way for the Flexible Server administrator to suppress credential-bearing error-statement logging for this operation; or

Another Microsoft-recommended credential-provisioning pattern?

We are not requesting PostgreSQL superuser access and do not want to weaken server-wide logging permanently.

Please provide the exact supported procedure and required privileges.
Allan Solomon Mejia 9,085 Reputation points

2026-09-17T18:39:07.2733333+00:00
Hi @Taufiq ALblooshi

Thanks for the clarification. For this credential-provisioning requirement, avoid sending:

ALTER ROLE bemtech_app_runtime PASSWORD 'plaintext-password';

as a literal SQL statement.

PostgreSQL specifically recommends using the psql \password command when you need to change a role password safely. Unlike putting the password directly in an ALTER ROLE ... PASSWORD statement, \password prompts for the password and avoids exposing the cleartext password in the command history or server log.

From an authenticated psql session, use:

\password bemtech_app_runtime

You will be prompted to enter and confirm the new password rather than embedding it in the SQL text.

This is particularly relevant to your logging concern because CREATE ROLE/USER ... PASSWORD ... and ALTER ROLE/USER ... PASSWORD ... can expose the password in PostgreSQL logs when log_statement is set to DDL or ALL. I recommend pgaudit for auditing role operations: pgaudit.log=ROLE can log CREATE/ALTER ROLE activity while redacting the password.

Therefore, use \password for interactive password provisioning/rotation, rather than trying to change log_min_error_statement at session level. Your 42501 result is consistent with these logging parameters being controlled/restricted in the managed Flexible Server environment; Azure exposes server parameters through the Flexible Server configuration surface rather than granting PostgreSQL superuser access.

If this needs to be fully automated rather than interactive, be more cautious. I don't see a documented Azure Flexible Server control-plane operation for setting arbitrary ordinary PostgreSQL-role passwords. The Azure control plane exposes password management for the server administrator, while ordinary local database roles are managed inside PostgreSQL.

Another supported design worth considering, if the application permits it, is Microsoft Entra authentication instead of maintaining a PostgreSQL password. Flexible Server supports Entra users, groups, service principals, and managed identities, with an access token used for authentication. That eliminates long-lived PostgreSQL passwords and the need to rotate them.

So for the four possibilities you listed: \password is the PostgreSQL-supported approach for interactive local-role password assignment; Entra authentication is the Azure-native passwordless alternative. I don't recommend weakening server-wide logging just to accommodate password rotation.

References:

PostgreSQL 16 - CREATE ROLE and password handling

Microsoft - Audit logging in Azure Database for PostgreSQL Flexible Server

Microsoft - Entra authentication for PostgreSQL Flexible Server

Help make this community better for everyone: If this answer helped or resolved your issue, please accept it or upvote it. If not, share more details in a comment so we can continue the discussion and find the right solution. Thank you.