Create users in Azure Cosmos DB for PostgreSQL
APPLIES TO: Azure Cosmos DB for PostgreSQL (powered by the Citus database extension to PostgreSQL)
The PostgreSQL engine uses roles to control access to database objects, and a newly created cluster comes with several roles pre-defined:
- The default PostgreSQL roles
Since Azure Cosmos DB for PostgreSQL is a managed PaaS service, only Microsoft can sign in with the
postgres superuser role. For limited administrative access, Azure Cosmos DB for PostgreSQL
The Citus role
Permissions for the
- Read all configuration variables, even variables normally visible only to superusers.
- Read all pg_stat_* views and use various statistics-related extensions--even views or extensions normally visible only to superusers.
- Execute monitoring functions that may take ACCESS SHARE locks on tables, potentially for a long time.
- Create PostgreSQL extensions, because
the role is a member of
citus role has some restrictions:
- Can't create roles
- Can't create databases
How to create user roles
As mentioned, the
citus admin account lacks permission to create user roles. To add a user role, use the Azure portal interface.
On your cluster page, select the Roles menu item, and on the Roles page, select Add.
Enter the role name and password. Select Save.
The user will be created on the coordinator node of the cluster,
and propagated to all the worker nodes. Roles created through the Azure
portal have the
LOGIN attribute, which means they’re true users who
can sign in to the database.
How to modify privileges for user roles
New user roles are commonly used to provide database access with restricted privileges. To modify user privileges, use standard PostgreSQL commands, using a tool such as PgAdmin or psql. For more information, see Connect to a cluster.
For example, to allow
db_user to read
mytable, grant the permission:
GRANT SELECT ON mytable TO db_user;
Azure Cosmos DB for PostgreSQL propagates single-table GRANT statements through the entire cluster, applying them on all worker nodes. It also propagates GRANTs that are system-wide (for example, for all tables in a schema):
-- applies to the coordinator node and propagates to workers GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_user;
How to delete a user role or change their password
To update a user, visit the Roles page for your cluster, and select the ellipses ... next to the user. The ellipses will open a menu to delete the user or reset their password.
citus role is privileged and can't be deleted.
Open the firewall for the IP addresses of the new users' machines to enable them to connect: Create and manage firewall rules using the Azure portal.
For more information about database user management, see PostgreSQL product documentation: