Managing database users
You can add and remove users, as well as manage their individual settings.
Getting a list of users
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and then select the Users tab.
If you don't have the Yandex.Cloud command line interface yet, install 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 command:
$ yc managed-postgresql user list
--cluster-name=<cluster name>
The cluster name can be requested with a list of clusters in the folder.
Add a user
Once created, the user only gets the CONNECT privilege and can't perform any database operations. To give the user access to the database, grant them the required privileges or roles.
Note
The user assigned as database owner when creating the DB gets the OWNER privilege. It lets them make changes to the DB.
When adding a user, Managed Service for PostgreSQL reserves 50 connections to the PostgreSQL cluster (the connlimit parameter) by default. The minimum number of connections per user is 10.
Warning
The total number of connections reserved for users must not exceed the value of the parameter max_connections. Keep in mind that Managed Service for PostgreSQL reserves 15 connections for service users per PostgreSQL host. For example, if the cluster has the setting "max_connections": 100 you can reserve a maximum of 85 connections for cluster users.
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the tab Users.
- Click Add.
- Enter the database username and password (from 8 to 128 characters).
If you don't have the Yandex.Cloud command line interface yet, install 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:
$ yc managed-postgresql user create <username>
--cluster-name=<cluster name>
--password=<DB user password>
--permissions=<list of DBs the user can access>
--conn-limit=<maximum number of connections per user>
The cluster name can be requested with a list of clusters in the folder.
Changing users
For the user, you can change:
- Their name and password.
- The list of databases the user can access.
- The limit on the number of database connections and other settings specific to PostgreSQL.
For information setting up user privileges and roles, see Granting user permissions and roles.
In the management console, you can only change the password of a database user:
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the tab Users.
- Click
and select Change password.
If you don't have the Yandex.Cloud command line interface yet, install 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 or the list of databases available to the user, run:
$ yc managed-postgresql user update <username>
--cluster-name=<cluster name>
--password=<DB user password>
--permissions=<list of DBs the user can access>
--conn-limit=<maximum number of connections per user>
The cluster name can be requested with a list of clusters in the folder.
Deleting users
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the tab Users.
- Click and select Delete.
If you don't have the Yandex.Cloud command line interface yet, install 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:
$ yc managed-postgresql user delete <username>
--cluster-name=<cluster name>
The cluster name can be requested with a list of clusters in the folder.