Create users in Azure Database for PostgreSQL - Single Server

APPLIES TO: Azure Database for PostgreSQL - Single Server

This article describes how you can create users within an Azure Database for PostgreSQL server.

If you would like to learn about how to create and manage Azure subscription users and their privileges, you can visit the Azure role-based access control (Azure RBAC) article or review how to customize roles.

The server admin account

When you first created your Azure Database for PostgreSQL, you provided a server admin user name and password. For more information, you can follow the Quickstart to see the step-by-step approach. Since the server admin user name is a custom name, you can locate the chosen server admin user name from the Azure portal.

The Azure Database for PostgreSQL server is created with the 3 default roles defined. You can see these roles by running the command: SELECT rolname FROM pg_roles;

• azure_pg_admin
• azure_superuser
• your server admin user

Your server admin user is a member of the azure_pg_admin role. However, the server admin account is not part of the azure_superuser role. Since this service is a managed PaaS service, only Microsoft is part of the super user role.

The PostgreSQL engine uses privileges to control access to database objects, as discussed in the PostgreSQL product documentation. In Azure Database for PostgreSQL, the server admin user is granted these privileges: LOGIN, NOSUPERUSER, INHERIT, CREATEDB, CREATEROLE, REPLICATION

The server admin user account can be used to create additional users and grant those users into the azure_pg_admin role. Also, the server admin account can be used to create less privileged users and roles that have access to individual databases and schemas.

How to create additional admin users in Azure Database for PostgreSQL

1. Get the connection information and admin user name. To connect to your database server, you need the full server name and admin sign-in credentials. You can easily find the server name and sign-in information from the server Overview page or the Properties page in the Azure portal.

2. Use the admin account and password to connect to your database server. Use your preferred client tool, such as pgAdmin or psql. If you are unsure of how to connect, see the quickstart

3. Edit and run the following SQL code. Replace your new user name for the placeholder value <new_user>, and replace the placeholder password with your own strong password.

   CREATE USER <new_user> CREATEDB CREATEROLE PASSWORD '<StrongPassword!>';
   
   GRANT azure_pg_admin TO <new_user>;
   

How to create database users in Azure Database for PostgreSQL

1. Get the connection information and admin user name. To connect to your database server, you need the full server name and admin sign-in credentials. You can easily find the server name and sign-in information from the server Overview page or the Properties page in the Azure portal.

2. Use the admin account and password to connect to your database server. Use your preferred client tool, such as pgAdmin or psql.

3. Edit and run the following SQL code. Replace the placeholder value <db_user> with your intended new user name, and placeholder value <newdb> with your own database name. Replace the placeholder password with your own strong password.

   This sql code syntax creates a new database named testdb, for example purposes. Then it creates a new user in the PostgreSQL service, and grants connect privileges to the new database for that user.

   CREATE DATABASE <newdb>;
   
   CREATE USER <db_user> PASSWORD '<StrongPassword!>';
   
   GRANT CONNECT ON DATABASE <newdb> TO <db_user>;
   

4. Using an admin account, you may need to grant additional privileges to secure the objects in the database. Refer to the PostgreSQL documentation for further details on database roles and privileges. For example:

   GRANT ALL PRIVILEGES ON DATABASE <newdb> TO <db_user>;
   

   If a user creates a table "role," the table belongs to that user. If another user needs access to the table, you must grant privileges to the other user on the table level.

   For example:

   GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <db_user>;
   

5. Log in to your server, specifying the designated database, using the new user name and password. This example shows the psql command line. With this command, you are prompted for the password for the user name. Replace your own server name, database name, and user name.

   psql --host=mydemoserver.postgres.database.chinacloudapi.cn --port=5432 --username=db_user@mydemoserver --dbname=newdb
   

Next steps

Open the firewall for the IP addresses of the new users' machines to enable them to connect: Create and manage Azure Database for PostgreSQL firewall rules by using the Azure portal or Azure CLI.

For more information regarding user account management, see PostgreSQL product documentation for Database Roles and Privileges, GRANT Syntax, and Privileges.