postgresqlazureazure-postgresql

Azure Postgres flexible server - How to delete a user?

Using my "globalAdmin" account (which is not a pg superuser) I create a user via
CREATE ROLE myDatabase_IntegrationServicesAccount LOGIN WITH PASSWORD 'test@12345'
and if I SELECT * FROM pg_user I can see that account listed!

but if I run DROP USER myDatabase_IntegrationServicesAccount; I get an error saying that the user does not exists!

what am I doing wrong here?

EDIT1
I think it has something to do with the casing... apparently the command on the server is being lower-cased before execution, so anything upper-cased is not being found

Solution

  • Thank you for pointing out the root of the issue @Lukasz Szozda and @Frank Heikens

    In PostgreSQL, unquoted identifiers are automatically folded to lowercase. So, this command:

    CREATE ROLE myDatabase_IntegrationServicesAccount LOGIN WITH PASSWORD 'test@12345';

    is actually interpreted as :

    CREATE ROLE mydatabase_integrationservicesaccount LOGIN WITH PASSWORD 'test@12345';

    However, if you explicitly create the role with double quotes, then PostgreSQL preserves the exact case, making the role name case-sensitive.

    DROP USER "myDatabase_IntegrationServicesAccount";

    This will correctly identify and drop the role with exact casing.