Managing PostgreSQL users
You can add and remove users, as well as manage their individual settings.
Warning
You can use SQL commands to assign privileges to users, but you can't use them to add or change users. For more information, see Assigning privileges and roles to PostgreSQL users.
Getting a list of users
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the name of the cluster you need and then select the Users tab.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To get a list of cluster users, run the following command:
ncp managed-postgresql user list
--cluster-name <cluster name>
The cluster name can be requested with a list of clusters in the folder.
Adding a user
Warning
New users are automatically granted public
table create privileges. They cannot be revoked.
-
Go to the folder page and select Managed Service for PostgreSQL.
-
Click on the name of the cluster you need and select the Users tab.
-
Click Add.
-
Enter the database username and password.
Note
A username may contain letters, numbers, hyphens and underscores, but must start with a letter (other than
pg_
), number or underscore. The maximum name length is 63 characters.The names
admin
,repl
,monitor
,postgres
,mdb_admin
,mdb_monitor
andmdb_replication
are reserved for Managed Service for PostgreSQL. You cannot create users with these names.The password must be between 8 and 128 characters.
-
Select one or more databases that the user should have access to:
- Select the database from the Database drop-down list.
- Click Add to the right of the drop-down list.
- Repeat the previous two steps until all the required databases are selected.
- To delete a database added by mistake, click
to the right of the database name in the Rights list.
-
Configure the DBMS settings for the user.
-
Click Add.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To create a user in a cluster, run the command:
ncp managed-postgresql user create <username>
--cluster-name <cluster name>
--password=<user password>
--permissions=<list of DBs user can access>
--conn-limit=<maximum number of connections per user>
This command configures only the main user settings.
Note
A username may contain letters, numbers, hyphens and underscores, but must start with a letter (other than pg_
), number or underscore. The maximum name length is 63 characters.
The names admin
, repl
, monitor
, postgres
, mdb_admin
, mdb_monitor
and mdb_replication
are reserved for Managed Service for PostgreSQL. You cannot create users with these names.
The password must be between 8 and 128 characters.
To customize the DBMS for the user, use the parameters described in User settings.
The cluster name can be requested with a list of clusters in the folder.
Note
When created, the user only gets the CONNECT
privilege for the selected databases and can't perform any operations with the databases. To give the user access to the database, assign them the required privileges or roles.
Changing a password
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the Users tab.
- Click the
- Set a new password and click Edit.
Note
The password must be between 8 and 128 characters.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To change the user's password, run the command:
ncp managed-postgresql user update <username>
--cluster-name=<cluster name>
--password=<new password>
Note
The password must be between 8 and 128 characters.
The cluster name can be requested with a list of clusters in the folder.
Changing user settings
Note
The privileges and roles in PostgreSQL are not affected by these settings and are configured separately.
For information about setting up user privileges and roles, see Assigning privileges and roles to PostgreSQL users.
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the Users tab.
- Click
- Set up user permissions to access certain databases:
- To grant access to the required databases:
- Select the database from the Database drop-down list.
- Click Add to the right of the drop-down list.
- Repeat the previous two steps until all the required databases are selected.
- To revoke access to a specific database, delete it from the Rights list by clicking
to the right of the database name.
- To grant access to the required databases:
- Click DBMS settings to change the maximum allowed number of connections for the user (Conn limit), enable/disable the user to connect to a cluster (Login), or update other PostgreSQL settings.
- Click Save.
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
You can change the user settings from the command line interface:
-
To set up the user's permissions to access certain databases, run the command, listing the database names in the
--permissions
parameter:ncp managed-postgresql user update <username> --cluster-name=<cluster name> --permissions=<list of databases to grant a user access to>
The cluster name can be requested with a list of clusters in the folder.
This command grants the user access rights to the databases listed.
To revoke access to a specific database, remove its name from the list and send the updated list to the command.
-
To change the PostgreSQL settings for the user, pass their parameters in the command:
ncp managed-postgresql user update <username> --cluster-name=<cluster name> --<setting 1>=<value 1> --<setting 2>=<value 2> --<setting 3>=<list of values> ...
You can change the connection limit for the user via the
--conn-limit
parameter.The cluster name can be requested with a list of clusters in the folder.
Deleting a user
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the Users tab.
- Click
If you don't have the Nebius AI command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To remove a user, run:
ncp managed-postgresql user delete <username>
--cluster-name <cluster name>
The cluster name can be requested with a list of clusters in the folder.
Examples
Add a user with read-only permissions
To add a new user user2
to an existing cluster with read-only access to the db1
database:
-
Create a user named
user2
. Select the databases that the user should have access to. -
Connect to the
db1
database under the account of the database owner. -
To only grant access rights to the
Products
table, in the defaultpublic
schema, run the command:GRANT SELECT ON public.Products TO user2;
-
To grant access to all the
myschema
schema tables, run the command:GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO user2; GRANT USAGE ON SCHEMA myschema TO user2;
To revoke the granted privileges, run the commands:
REVOKE SELECT ON public.Products FROM user2;
REVOKE SELECT ON ALL TABLES IN SCHEMA myschema FROM user2;
REVOKE USAGE ON SCHEMA myschema FROM user2;