Edit

Manage users for Azure HorizonDB (Preview)

This article describes how you can create users within an Azure HorizonDB instance.

Suppose you want to learn how to create and manage Azure subscription users and their privileges. In that case, 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 HorizonDB cluster, you provided a cluster administrator login name and password. For more information, see Create an Azure HorizonDB cluster to see the step-by-step approach. Since the cluster administrator login is a custom name, you can locate the chosen server admin user name from the Azure portal.

The Azure HorizonDB instance is created with the three default roles defined. You can see these roles by running the command: SELECT rolname FROM pg_roles;

  • azure_pg_admin
  • azuresu
  • your server admin user

Your administrator login is a member of the azure_pg_admin role. However, the admin account isn't part of the azuresu 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 HorizonDB, the server admin user is granted these privileges:

  • Sign in, NOSUPERUSER, INHERIT, CREATEDB, CREATEROLE

The cluster administrator login can be used to create more users and grant those users into the azure_pg_admin role. Also, the cluster administrator login can be used to create less privileged users and roles that have access to individual databases and schemas.

How to create more admin users in Azure HorizonDB

  1. Get the connection information and admin user name. You need the full server name and admin sign-in credentials to connect to your Azure HorizonDB instance. 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 Azure HorizonDB instance. Use your preferred client tool, such as pgAdmin or psql. If you're unsure of how to connect, see Create an Azure HorizonDB cluster.

  3. Edit and run the following SQL code. Replace your new user name with 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 HorizonDB

  1. Get the connection information and admin user name. You need the full server name and admin sign-in credentials to connect to your Azure HorizonDB instance. 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 Azure HorizonDB instance. 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 creates a new database, then it creates a new user in the Azure HorizonDB instance and grants connect privilege 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 might need to grant other privileges to secure the objects in the database. For more information, 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. Sign in to your server, specifying the designated database, using the new username and password. This example shows the psql command line. With this command, you're prompted for the password for the user name. Replace your own server name, database name, and user name.

    psql --host="{clustername}.{clusteridentifier}.{region}.horizondb.azure.com" --port=5432 --username=db_user --dbname=newdb

Related content

  • Last updated on