Azure PostgresSQL: Want to create NOSUPERUSER role, getting "ERROR: must be superuser to create superusers"

Marius Hornung 21 Reputation points
2020-11-21T09:40:00.097+00:00

Hi everybody,

I´m getting crazy ;) especially because I already did this several times. Maybe I´m blind at the moment. I already tried several new deployments and version. I want to create a flexible server, but I tried with the Single Server as well:

  • I´m creating a postgres server
  • I´m connecting to the server with the admin user created by Azure
  • I want to create a NOSUPERUSER-User/Role
  • I´m getting the error "ERROR: must be superuser to create superusers"

I want to emphasize: I want to have NOSUPERUSER-User - no superuser.

psql "host=XXXXXXXX.postgres.database.azure.com port=5432 dbname=XXXXX user=XXXX password=XXXX sslmode=require"
psql (12.2, server 12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> CREATE USER xxxx ENCRYPTED PASSWORD 'xxxx' NOSUPERUSER NOCREATEDB NOCREATEROLE;
ERROR: must be superuser to create superusers
postgres=> CREATE ROLE xxxx ENCRYPTED PASSWORD 'xxxx' NOSUPERUSER NOCREATEDB NOCREATEROLE;
ERROR: must be superuser to create superusers

To check, if I´m just too stupid and blind or something, I created a postgres in AWS and tried it out. As far as I know the user created by AWS RDS has no superuser rights as well - but here the creation of the user works like a charme:

psql "host=xxxxxx.eu-central-1.rds.amazonaws.com port=5432 dbname=xxxxx user=xxxx password=xxxxx sslmode=require"
psql (12.2, server 12.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> CREATE ROLE xxxxxx ENCRYPTED PASSWORD 'xxxxxx' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE ROLE

Does anyone have a hint, what I´m missing here?

Thanks
Marius

Azure Database for PostgreSQL
Accepted answer
  1. Anurag Sharma 17,586 Reputation points
    2020-11-23T12:36:41.427+00:00

    Hi @Marius Hornung , welcome to Microsoft Q&A.

    I was trying the same scenario as mentioned by creating Azure PostgreSQL Database - Flexible Server and faced the same issue when creating the role as below: 

    postgres=> CREATE USER test3 ENCRYPTED PASSWORD 'password@123' NOCREATEDB NOCREATEROLE NOSUPERUSER;  
ERROR:  must be superuser to create superusers

    Then I changed the query by removing the NOSUPERUSER keyword and it worked fine: 

    postgres=> CREATE USER test3 ENCRYPTED PASSWORD 'password@123' NOCREATEDB NOCREATEROLE;  
CREATE ROLE

    And I was able to login using the same user then.

    But the error is definitely something related to 'Flexible Server' as the same statement worked perfectly fine in 'Single Server'.

    Can you please try the same and let me know if this works with you? Or else we can discuss further on the same.

    ----------

    If the answer helps, please select 'Accept Answer' as it could help other community members looking for similar queries.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Marius Hornung 21 Reputation points
    2020-11-24T08:10:31.01+00:00

    Hi AnuragSharma-MSFT,

    Thanks for trying it out - at least I´m not stupid :D.

    It worked for me as well after I left out NOSUPERUSER. I don´t understand whats happening under the hood, but it works now.

    Have tried it out on a completely new single server as well? I tried it there as well, did not work. Or maybe I mixed up the connection strings and was conntected to the not working flexible server...

    Thanks
    Marius

    0 comments