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 cannot use them to add or change users. For more information, see Assigning privileges and roles to 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 select the Users tab.
If you do not have the Yandex Cloud 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:
yc managed-postgresql user list \
--cluster-name <cluster_name>
You can request the cluster name with a list of clusters in the folder.
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Use the User.list method and make a request, e.g., via cURL
:curl \ --request GET \ --header "Authorization: Bearer $IAM_TOKEN" \ --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users'
You can get the cluster ID with a list of clusters in the folder.
-
View the server response to make sure the request was successful.
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Clone the cloudapi
repository:cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
Below, we assume the repository contents are stored in the
~/cloudapi/
directory. -
Use the UserService/List call and make a request, e.g., via gRPCurl
:grpcurl \ -format json \ -import-path ~/cloudapi/ \ -import-path ~/cloudapi/third_party/googleapis/ \ -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \ -rpc-header "Authorization: Bearer $IAM_TOKEN" \ -d '{ "cluster_id": "<cluster_ID>" }' \ mdb.api.cloud.yandex.net:443 \ yandex.cloud.mdb.postgresql.v1.UserService.List
You can get the cluster ID with a list of clusters in the folder.
-
View the server response to make sure the request was successful.
Creating a user
Warning
Granting the public
table create privilege to new users depends on the PostgreSQL version:
- 14 and lower: The privilege is granted automatically and cannot be revoked.
- 15 and higher: The privilege is manually granted to the user.
-
Go to the folder page and select Managed Service for PostgreSQL.
-
Click the cluster name and open the Users tab.
-
Click Create user.
-
Enter the database username and password.
Note
The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter (except the
pg_
combination), number, or underscore. The name may be up to 63 characters long.The names
mysql
,sys
,information_schema
, andperformance_schema
are reserved for Managed Service for MySQL®. You cannot create users with these names.The password must be between 8 and 128 characters.
-
Select the deletion protection type.
The possible values are:
- Same as cluster
- Enabled
- Disabled
-
Select one or more databases that the user should have access to:
- In the Database field, click
to the right of the drop-down list. - Select the database from 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 Database field, click
-
Configure the DBMS settings for the user.
-
Click Save.
If you do not have the Yandex Cloud 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:
yc managed-postgresql user create <username> \
--cluster-name <cluster_name> \
--password=<password> \
--permissions=<database_list> \
--conn-limit=<maximum_number_of_connections>
Where:
cluster-name
: Cluster name.password
: User password.permissions
: List of databases the user should have access to.conn-limit
: Maximum number of connections per user.
This command configures only the main user settings.
Note
The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter (except the pg_
combination), number, or underscore. The name may be up to 63 characters long.
The names mysql
, sys
, information_schema
, and performance_schema
are reserved for Managed Service for MySQL®. 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.
You can request the cluster name with a list of clusters in the folder.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
For a complete list of editable fields in the Managed Service for PostgreSQL cluster user configuration, see the Terraform provider documentation
. -
Add the
yandex_mdb_postgresql_user
resource:resource "yandex_mdb_postgresql_user" "<username>" { cluster_id = "<cluster_ID>" name = "<username>" password = "<password>" grants = [ "<role1>","<role2>" ] login = <DB_login_permission> conn_limit = <maximum_number_of_connections> deletion_protection = <deletion_protection> settings = { <DB_settings> } permission { database_name = "<DB_name>" } }
Where:
login
: Permission to log in to the DB,true
orfalse
.deletion_protection
: User deletion protection,true
,false
, orunspecified
(inherits the value from the cluster). Default value:unspecified
.
Note
The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter (except the
pg_
combination), number, or underscore. The name may be up to 63 characters long.The names
mysql
,sys
,information_schema
, andperformance_schema
are reserved for Managed Service for MySQL®. You cannot create users with these names.The password must be between 8 and 128 characters.
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Use the User.create method and make a request, e.g., via cURL
:curl \ --request POST \ --header "Authorization: Bearer $IAM_TOKEN" \ --header "Content-Type: application/json" \ --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users' \ --data '{ "userSpec": { "name": "<username>", "password": "<user_password>", "permissions": [ { "databaseName": "<DB_name>" } ], "connLimit": "<maximum_number_of_DB_connections>", "deletionProtection": <deletion_protection:_true_or_false> } }'
Where
userSpec
lists the new DB user settings:-
name
: Username. -
password
: User password.Note
The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter (except the
pg_
combination), number, or underscore. The name may be up to 63 characters long.The names
mysql
,sys
,information_schema
, andperformance_schema
are reserved for Managed Service for MySQL®. You cannot create users with these names.The password must be between 8 and 128 characters.
-
permissions.databaseName
: Array of databases the user should have access to. Each array element is mapped to a separate DB. -
connLimit
: Maximum number of DB connections for the user. -
deletionProtection
: DB deletion protection.
You can get the cluster ID with a list of clusters in the folder.
-
-
View the server response to make sure the request was successful.
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Clone the cloudapi
repository:cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
Below, we assume the repository contents are stored in the
~/cloudapi/
directory. -
Use the UserService/Create call and make a request, e.g., via gRPCurl
:grpcurl \ -format json \ -import-path ~/cloudapi/ \ -import-path ~/cloudapi/third_party/googleapis/ \ -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \ -rpc-header "Authorization: Bearer $IAM_TOKEN" \ -d '{ "cluster_id": "<cluster_ID>", "user_spec": { "name": "<username>", "password": "<user_password>", "permissions": [ { "database_name": "<DB_name>" } ], "conn_limit": "<maximum_number_of_DB_connections>", "deletion_protection": <deletion_protection:_true_or_false> } }' \ mdb.api.cloud.yandex.net:443 \ yandex.cloud.mdb.postgresql.v1.UserService.Create
Where
user_spec
lists the new DB user settings:-
name
: Username. -
password
: User password.Note
The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter (except the
pg_
combination), number, or underscore. The name may be up to 63 characters long.The names
mysql
,sys
,information_schema
, andperformance_schema
are reserved for Managed Service for MySQL®. You cannot create users with these names.The password must be between 8 and 128 characters.
-
permissions.database_name
: Array of databases the user should have access to. Each array element is mapped to a separate DB. -
conn_limit
: Maximum number of DB connections for the user. -
deletion_protection
: DB deletion protection.
You can get the cluster ID with a list of clusters in the folder.
-
-
View the server response to make sure the request was successful.
Note
Once created, the user only gets the CONNECT
privilege for the selected databases and cannot perform any operations on them. 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 the cluster name and open the Users tab.
- Click
and select Change password. - Set a new password and click Edit.
Note
The password must be between 8 and 128 characters.
If you do not have the Yandex Cloud 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 password, run this command:
yc managed-postgresql user update <username> \
--cluster-name=<cluster_name> \
--password=<new_password>
Note
The password must be between 8 and 128 characters.
You can request the cluster name with a list of clusters in the folder.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
For a complete list of editable fields in the Managed Service for PostgreSQL cluster user configuration, see the Terraform provider documentation
. -
Find the
yandex_mdb_postgresql_user
resource of the user you need. -
Change the value of the
password
field:resource "yandex_mdb_postgresql_user" "<username>" { ... name = "<username>" password = "<new_password>" ... }
Note
The password must be between 8 and 128 characters.
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Use the User.update method and make a request, e.g., using cURL
:Warning
This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the
updateMask
parameter (one line separated by commas).curl \ --request PATCH \ --header "Authorization: Bearer $IAM_TOKEN" \ --header "Content-Type: application/json" \ --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>' \ --data '{ "updateMask": "password", "password": "<new_password>" }'
Where:
-
updateMask
: List of parameters to update as a single string, separated by commas.In this case, only one parameter is provided.
-
password
: New password.Note
The password must be between 8 and 128 characters.
You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.
-
-
View the server response to make sure the request was successful.
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Clone the cloudapi
repository:cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
Below, we assume the repository contents are stored in the
~/cloudapi/
directory. -
Use the UserService/Update call and make a request, e.g., using gRPCurl
:Warning
This API method will assign default values to all object parameters not explicitly set in the request. To avoid this, list the settings you want to change in the
update_mask
parameter as an array ofpaths[]
strings.Format for listing settings
"update_mask": { "paths": [ "<setting_1>", "<setting_2>", ... "<setting_N>" ] }
grpcurl \ -format json \ -import-path ~/cloudapi/ \ -import-path ~/cloudapi/third_party/googleapis/ \ -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \ -rpc-header "Authorization: Bearer $IAM_TOKEN" \ -d '{ "cluster_id": "<cluster_ID>", "user_name": "<username>", "update_mask": { "paths": [ "password" ] }, "password": "<new_password>" }' \ mdb.api.cloud.yandex.net:443 \ yandex.cloud.mdb.postgresql.v1.UserService.Update
Where:
-
update_mask
: List of parameters to update as an array ofpaths[]
strings.In this case, only one parameter is provided.
-
password
: New password.Note
The password must be between 8 and 128 characters.
You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.
-
-
View the server response to make sure the request was successful.
Changing user settings
Note
The privileges and roles in PostgreSQL are not affected by these settings and are configured separately.
For information on setting up user privileges and roles, see Assigning privileges and roles to users.
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the cluster name and open the Users tab.
- Click
and select Configure. - Configure user permissions to access certain databases:
- To grant access to the required databases:
- In the Database field, click
to the right of the drop-down list. - Select the database from the drop-down list.
- Repeat the previous two steps until all the required databases are selected.
- In the Database field, click
- To revoke access to a specific database, click
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 do not have the Yandex Cloud 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:yc managed-postgresql user update <username> \ --cluster-name=<cluster_name> \ --permissions=<database_list>
Where:
cluster-name
: Cluster name.permissions
: List of databases the user should have access to.
You can request the cluster name 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:
yc 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.
You can request the cluster name with a list of clusters in the folder.
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
For a complete list of editable fields in the Managed Service for PostgreSQL cluster user configuration, see the Terraform provider documentation
. -
To grant the user permissions to access certain databases:
-
Find the
yandex_mdb_postgresql_user
resource of the user you need. -
Add
permission
sections with the appropriate DB names:resource "yandex_mdb_postgresql_user" "<username>" { ... name = "<username>" permission { database_name = "<DB_name>" } permission { database_name = "<DB_name>" } ... }
-
-
To revoke the user's permission to access a specific database, delete the
permission
section with the name of this DB from the configuration file. -
To change the PostgreSQL settings for the user, provide their parameters in the
settings
block:resource "yandex_mdb_postgresql_user" "<username>" { ... name = "<username>" settings = { <DB_settings> } ... }
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Use the User.update method and make a request, e.g., using cURL
:Warning
This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the
updateMask
parameter (one line separated by commas).curl \ --request PATCH \ --header "Authorization: Bearer $IAM_TOKEN" \ --header "Content-Type: application/json" \ --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>' \ --data '{ "updateMask": "settings", "settings": { <settings> } }'
Where:
-
updateMask
: List of parameters to update as a single string, separated by commas.In this case, only one parameter is provided.
-
settings
: New settings. See the method description and User-level settings for the list of available settings.
You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.
-
-
View the server response to make sure the request was successful.
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Clone the cloudapi
repository:cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
Below, we assume the repository contents are stored in the
~/cloudapi/
directory. -
Use the UserService/Update call and make a request, e.g., using gRPCurl
:Warning
This API method will assign default values to all object parameters not explicitly set in the request. To avoid this, list the settings you want to change in the
update_mask
parameter as an array ofpaths[]
strings.Format for listing settings
"update_mask": { "paths": [ "<setting_1>", "<setting_2>", ... "<setting_N>" ] }
grpcurl \ -format json \ -import-path ~/cloudapi/ \ -import-path ~/cloudapi/third_party/googleapis/ \ -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \ -rpc-header "Authorization: Bearer $IAM_TOKEN" \ -d '{ "cluster_id": "<cluster_ID>", "user_name": "<username>", "update_mask": { "paths": [ "settings" ] }, "settings": { <settings> } }' \ mdb.api.cloud.yandex.net:443 \ yandex.cloud.mdb.postgresql.v1.UserService.Update
Where:
-
update_mask
: List of parameters to update as an array ofpaths[]
strings.In this case, only one parameter is provided.
-
settings
: New settings. See the method description and User-level settings for the list of available settings.
You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.
-
-
View the server response to make sure the request was successful.
Configuring deletion protection
- Go to the folder page
and select Managed Service for PostgreSQL. - Click the cluster name and open the Users tab.
- Click
and select Configure. - Configure user deletion protection. To do this, select the relevant value in the Deletion protection field.
- Click Save.
-
Open the current Terraform configuration file with an infrastructure plan.
-
Find the
yandex_mdb_postgresql_user
resource of the user you need. -
Add the
deletion_protection
parameter. The possible values aretrue
,false
, orunspecified
(inherits the value from the cluster). The default value isunspecified
.resource "yandex_mdb_postgresql_user" "<username>" { ... deletion_protection = <deletion_protection> ... }
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Use the User.update method and make a request, e.g., using cURL
:Warning
This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the
updateMask
parameter (one line separated by commas).curl \ --request PATCH \ --header "Authorization: Bearer $IAM_TOKEN" \ --header "Content-Type: application/json" \ --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>' \ --data '{ "updateMask": "deletionProtection", "deletionProtection": <deletion_protection:_true_or_false> } }'
Where:
-
updateMask
: List of parameters to update as a single string, separated by commas.In this case, only one parameter is provided.
-
deletionProtection
: DB deletion protection.
You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.
-
-
View the server response to make sure the request was successful.
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Clone the cloudapi
repository:cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
Below, we assume the repository contents are stored in the
~/cloudapi/
directory. -
Use the UserService/Update call and make a request, e.g., using gRPCurl
:Warning
This API method will assign default values to all object parameters not explicitly set in the request. To avoid this, list the settings you want to change in the
update_mask
parameter as an array ofpaths[]
strings.Format for listing settings
"update_mask": { "paths": [ "<setting_1>", "<setting_2>", ... "<setting_N>" ] }
grpcurl \ -format json \ -import-path ~/cloudapi/ \ -import-path ~/cloudapi/third_party/googleapis/ \ -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \ -rpc-header "Authorization: Bearer $IAM_TOKEN" \ -d '{ "cluster_id": "<cluster_ID>", "user_name": "<username>", "update_mask": { "paths": [ "deletion_protection" ] }, "deletion_protection": <deletion_protection:_true_or_false> }' \ mdb.api.cloud.yandex.net:443 \ yandex.cloud.mdb.postgresql.v1.UserService.Update
Where:
-
update_mask
: List of parameters to update as an array ofpaths[]
strings.In this case, only one parameter is provided.
-
deletion_protection
: DB deletion protection.
You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.
-
-
View the server response to make sure the request was successful.
Warning
Deletion protection only applies at specific user level. Deleting a cluster will delete all users, including those protected from deletion.
Deleting a user
A user can be protected against deletion. To delete such a user, disable the protection first.
To delete a user:
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the cluster name and open the Users tab.
- Click
and select Delete. - Confirm the deletion.
If you do not have the Yandex Cloud 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:
yc managed-postgresql user delete <username> \
--cluster-name <cluster_name>
You can request the cluster name with a list of clusters in the folder.
To delete a user:
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about creating this file, see Creating clusters.
For a complete list of available Managed Service for PostgreSQL cluster configuration fields, see the Terraform provider documentation
. -
Delete the
yandex_mdb_postgresql_user
resource with the description of the user you need. -
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Use the User.delete method and make a request, e.g., via cURL
:curl \ --request DELETE \ --header "Authorization: Bearer $IAM_TOKEN" \ --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>'
You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.
-
View the server response to make sure the request was successful.
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
Clone the cloudapi
repository:cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
Below, we assume the repository contents are stored in the
~/cloudapi/
directory. -
Use the UserService/Delete call and make a request, e.g., via gRPCurl
:grpcurl \ -format json \ -import-path ~/cloudapi/ \ -import-path ~/cloudapi/third_party/googleapis/ \ -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \ -rpc-header "Authorization: Bearer $IAM_TOKEN" \ -d '{ "cluster_id": "<cluster_ID>", "user_name": "<username>" }' \ mdb.api.cloud.yandex.net:443 \ yandex.cloud.mdb.postgresql.v1.UserService.Delete
You can get the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.
-
View the server response to make sure the request was successful.
Examples
Add a user with read-only permissions
Alert
Do not use this example if a user is created using Terraform: subsequent changes made via Terraform may cancel the user's privileges granted through SQL.
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, SELECT ON ALL SEQUENCES IN SCHEMA myschema to user2;
-
(Optional) To change the default privileges, run this command:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO user2; ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES 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;
You can grant user privileges via Terraform only in a cluster with public hosts.
User privileges are granted via Terraform using a third-party provider, Terraform Provider for PostgreSQL
Note
Terraform Provider for PostgreSQL is not part of Managed Service for PostgreSQL. It is not covered by Yandex Cloud support and its usage is not governed by the Yandex Managed Service for PostgreSQL Terms of Use
For more information about granting privileges, see Granting a privilege to a user.
Let's say you have a cluster named mypg
with user1
as the owner. To add a new user (user2
) to this cluster with read-only access to db1
tables with the public
schema:
-
Add a
postgresql
provider to therequired_providers
section in the provider configuration file:terraform { required_providers { ... postgresql = { source = "cyrilgdn/postgresql" } ... } }
-
Open the Terraform configuration file with the infrastructure plan.
-
Add the
yandex_mdb_postgresql_user
resource:resource "yandex_mdb_postgresql_user" "user2" { cluster_id = yandex_mdb_postgresql_cluster.mypg.id name = "user2" password = "user2user2" permission { database_name = yandex_mdb_postgresql_database.db1.name } }
-
Add the
postgresql
provider and configure its access permissions todb1
:provider "postgresql" { host = yandex_mdb_postgresql_cluster.mypg.host[0].fqdn port = 6432 database = yandex_mdb_postgresql_database.db1.name username = yandex_mdb_postgresql_user.user1.name password = yandex_mdb_postgresql_user.user1.password }
-
Add the
postgresql_grant
resource with the following attributes:resource "postgresql_grant" "readonly_tables" { database = yandex_mdb_postgresql_database.db1.name role = yandex_mdb_postgresql_user.user2.name object_type = "table" privileges = ["SELECT"] schema = "public" }
-
Initialize Terraform once again:
terraform init
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
To revoke the granted privilege, remove it from the privileges
list and confirm updating the resources.