PostgreSQL Active Directory Admin - DB in inconsistent state

Question

I have put my Azure Postgres db into an inconsistent state w.r.t. the Azure Active Directory Admin from which I can't recover.
The issue is that the Portal (and az cli) report that an AD Admin is configured for the database,
but when I try to connect with that admin user the db reports psql: error: FATAL: AAD tenant has not been set. Please create an AAD admin via CLI or the portal..
I also can't delete and re-create the admin due to a borked (?) privilege setup.

This is the situation:

I can't delete the configured Azure Active Directory Admin of the Postgre SQL server,
neither via Portal nor via az cli.
The az cli simply runs for 20 minutes and then times out. If I delete via Portal, then the event request ist "accepted"
but it never "succeeds".

I can see in the server logs that this is (probably) due to some prvileges of a AD User (DB_ACC_DEV, see below),
that belong to that admin user:

2021-01-29 19:08:37 UTC azure_superuser postgres 127.0.0.1ERROR:  role "sqladmin" cannot be dropped because some objects depend on it
2021-01-29 19:08:37 UTC azure_superuser postgres 127.0.0.1DETAIL:  owner of default privileges on new sequences belonging to role sqladmin in schema public
 owner of default privileges on new relations belonging to role sqladmin in schema public
2021-01-29 19:08:37 UTC azure_superuser postgres 127.0.0.1STATEMENT:  DROP ROLE IF EXISTS "sqladmin"

sqladmin is the ad admin rolename in Postgres.

This is (probalby) because of an AD user I created.
The user is called "DB_ACC_DEV", which is mapped to an AD group of that name.
The user and its privileges were created with this SQL snippet, executed by the sqladmin:

DO
'
BEGIN
   IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles  -- SELECT list can be empty for this
      WHERE  rolname = ''DB_ACC_DEV'') THEN

      CREATE ROLE "DB_ACC_DEV" WITH LOGIN IN ROLE azure_ad_user;
   END IF;
END
';

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "DB_ACC_DEV";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "DB_ACC_DEV";

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON tables TO "DB_ACC_DEV";

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, USAGE ON sequences TO "DB_ACC_DEV";

;

The problem now is that I can't log in with the sqladmin user anymore.
This is probably due to some issues when destroying the AD admin setup with terraform.

I can now only log in with the "normal" database admin that is created when creating the database.
Importantly, this user is not a superuser, which is by design, according to the Azure docs.

Connecting with that admin account - which is called "postgres" in my case -
I think I was able to revoke the "ALL PRIVILEGES" by executing the following SQL:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "DB_ACC_DEV";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM "DB_ACC_DEV";

Unfortunately I can't revoke the default privileges, and I think this is what causes the error message in the log.
You can see below the pg_default_acl table and how it's the same before and after running the revoke:

postgres=> select * from pg_default_acl;
 defaclrole | defaclnamespace | defaclobjtype |         defaclacl
------------+-----------------+---------------+----------------------------
      17608 |            2200 | r             | {DB_ACC_DEV=arwd/sqladmin}
      17608 |            2200 | S             | {DB_ACC_DEV=rU/sqladmin}
(2 rows)

postgres=> ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE SELECT, INSERT, UPDATE, DELETE ON tables FROM "DB_ACC_DEV";
ALTER DEFAULT PRIVILEGES
postgres=> select * from pg_default_acl;
 defaclrole | defaclnamespace | defaclobjtype |         defaclacl
------------+-----------------+---------------+----------------------------
      17608 |            2200 | r             | {DB_ACC_DEV=arwd/sqladmin}
      17608 |            2200 | S             | {DB_ACC_DEV=rU/sqladmin}
(2 rows)

I'm confused that I don't get an error - psql simply says "ALTER DEFAULT PRIVILEGES", which sounds like a success to me.
I guess that this command can actually only be successfully executed by the sqladmin.

And therein lies the problem:
I can't log in with the AD admin and I can't delete (in order to re-create)
the AD admin because of some lingering permissions, which I can only revoke while being logged in as the AD admin.

I'm not a PostgreSQL expert, so there's a good chance I'm missing something, but Azure is not really helping here either.
I don't get any useful error-message and I even had to dig out the permission issue from the serverlogs.

One other thing I tried was change the AD admin to another Azure AD Group.
This succeeds on the Portal and I do see the new Group name there, and also when retrieving the info via az cli
(az postgres server ad-admin show --server-name my-db-name --resource-group xyz.
However, I do not see any changes when looking at the \du+ output - there's still the "sqladmin" user,
even if I change the group to something like "Testgroup".
When I do the same on a "vanilla" db, the "Testgroup" name show up in the \du+ table.

I'm happy to provide any further command outputs that might aid in debugging this issue
and am very grateful for any hints on this issue!

Output of \l:

postgres=> \l
                                                               List of databases
       Name        |      Owner      | Encoding |          Collate           |           Ctype            |          Access privileges
-------------------+-----------------+----------+----------------------------+----------------------------+-------------------------------------
 azure_maintenance | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 | azure_superuser=CTc/azure_superuser
 azure_sys         | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 |
 postgres          | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 |
 template0         | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 | =c/azure_superuser                 +
                   |                 |          |                            |                            | azure_superuser=CTc/azure_superuser
 template1         | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 | =c/azure_superuser                 +
                   |                 |          |                            |                            | azure_superuser=CTc/azure_superuser
(5 rows)

Output of \du+:

postgres=> \du+
                                                 List of roles
    Role name    |                         Attributes                         |    Member of     | Description
-----------------+------------------------------------------------------------+------------------+-------------
 DB_ACC_DEV      |                                                            | {azure_ad_user}  |
 azure_ad_admin  | Cannot login                                               | {azure_pg_admin} |
 azure_ad_mfa    | Cannot login                                               | {}               |
 azure_ad_user   | Cannot login                                               | {}               |
 azure_pg_admin  | Cannot login, Replication                                  | {}               |
 azure_superuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}               |
 postgres        | Create role, Create DB, Replication                        | {azure_pg_admin} |
 sqladmin        | Create role, Create DB                                     | {azure_ad_admin} |

Output of \ddp:

 postgres=> \ddp
                Default access privileges
  Owner   | Schema |   Type   |    Access privileges
----------+--------+----------+--------------------------
 sqladmin | public | sequence | DB_ACC_DEV=rU/sqladmin
 sqladmin | public | table    | DB_ACC_DEV=arwd/sqladmin
(2 rows)

Answer 1

Hello, I am currently experiencing this issue also. Azure cannot remove the old AD admin as there are hanging references to defaclacl s (all other objects were transferred)