Contact UsGet started
© 2025 Direct Cursus Technology L.L.C.

User management PostgreSQL

Written by
Updated at May 5, 2025

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

  1. Navigate to the folder dashboard and select Managed Service for PostgreSQL.
  2. Click the name of the cluster you need and select the Users tab.

If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder specified when creating the CLI profile is used by default. To change the default folder, use the yc config set folder-id <folder_ID> command. 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 the list of clusters in the folder.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.list method and send the following 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 request the cluster ID with the list of clusters in the folder.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. 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.

  3. Use the UserService.List call and send the following 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 request the cluster ID with the list of clusters in the folder.

  4. 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.

  1. Navigate to the folder dashboard and select Managed Service for PostgreSQL.

  2. Click the cluster name and open the Users tab.

  3. Click Create user.

  4. Enter a database username.

    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.

    Such names as admin, repl, monitor, postgres, mdb_superuser, mdb_admin, mdb_monitor, and mdb_replication are reserved for Managed Service for PostgreSQL. You cannot create users with these names.

  5. Select how to set a password:

    • Enter manually: Enter your own password. The password must be from 8 to 128 characters long.

    • Generate: Generate a password using Connection Manager.

    To view the password, select the Users tab on the cluster page and click View password in the new user row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

  6. Select the deletion protection type.

    The possible values are:

    • Same as cluster
    • Enabled
    • Disabled

  7. Select one or more databases that the user should have access to:

    1. In the Database field, click to the right of the drop-down list.
    2. Select the database from the drop-down list.
    3. Repeat the previous two steps until all the required databases are selected.
    4. To delete a database added by mistake, click to the right of the database name.

  8. Configure the DBMS settings for the user.

  9. Click Save.

If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder specified when creating the CLI profile is used by default. To change the default folder, use the yc config set folder-id <folder_ID> command. 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. The password must be from 8 to 128 characters long.

    You can also generate a password using Connection Manager. To do this, specify --generate-password instead of --password=<password>.

    To view the password, select the cluster you need in the management console, go to the Users tab and click View password in the new user row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

  • 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.

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.

Such names as admin, repl, monitor, postgres, mdb_superuser, mdb_admin, mdb_monitor, and mdb_replication are reserved for Managed Service for PostgreSQL. You cannot create users with these names.

To customize the DBMS for the user, use the parameters described in User settings.

You can request the cluster name with the list of clusters in the folder.

  1. Open the current Terraform configuration file that defines your infrastructure.

    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.

  2. 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 or false.
    • deletion_protection: User deletion protection, true, false, or unspecified (inherits the value from the cluster). The default value is unspecified.

    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.

    Such names as admin, repl, monitor, postgres, mdb_superuser, 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 from 8 to 128 characters long.

    You can also generate a password using Connection Manager. To do this, specify generate_password = true instead of password = "<password>".

    To view the password, select the cluster you need in the management console, go to the Users tab and click View password in the new user row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.create method and send the following 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>
            }
          }'

    Where userSpec lists the new DB user settings:

    • name: Username.

      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.

      Such names as admin, repl, monitor, postgres, mdb_superuser, mdb_admin, mdb_monitor, and mdb_replication are reserved for Managed Service for PostgreSQL. You cannot create users with these names.

    • password: User password. The password must be from 8 to 128 characters long.

      You can also generate a password using Connection Manager. To do this, specify "generatePassword": true instead of "password": "<user_password>".

      To view the password, select the cluster you need in the management console, go to the Users tab, and click View password in the new user row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

    • 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: User deletion protection, true, false, or unspecified (inherits the value from the cluster). The default value is unspecified.

    You can request the cluster ID with the list of clusters in the folder.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. 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.

  3. Use the ClusterService.Create call and send the following 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>
        }
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.UserService.Create

    Where user_spec lists the new DB user settings:

    • name: Username.

      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.

      Such names as admin, repl, monitor, postgres, mdb_superuser, mdb_admin, mdb_monitor, and mdb_replication are reserved for Managed Service for PostgreSQL. You cannot create users with these names.

    • password: User password. The password must be from 8 to 128 characters long.

      You can also generate a password using Connection Manager. To do this, specify "generate_password": true instead of "password": "<user_password>".

      To view the password, select the cluster you created in the management console, go to the Users tab, and click View password in the new user row. This will open the page of the Yandex Lockbox secret that stores the password. To view passwords, you need the lockbox.payloadViewer role.

    • 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: User deletion protection, true, false, or unspecified (inherits the value from the cluster). The default value is unspecified.

    You can request the cluster ID with the list of clusters in the folder.

  4. 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

  1. Navigate to the folder dashboard and select Managed Service for PostgreSQL.

  2. Click the cluster name and open the Users tab.

  3. Click and select Change password.

  4. Select how to set a new password:

    • Enter manually: Enter your own password. The password must be from 8 to 128 characters long.

    • Generate: Generate a password using Connection Manager.

  5. Click Edit.

    To view the new password, select the Users tab on the cluster page and click View password in the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

    To view passwords, you need the lockbox.payloadViewer role.

If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder specified when creating the CLI profile is used by default. To change the default folder, use the yc config set folder-id <folder_ID> command. 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>

The password must be from 8 to 128 characters long.

You can also generate a new password using Connection Manager. To do this, specify --generate-password instead of --password=<new_password>.

To view the new password, select the cluster in the management console, go to the Users tab, and click View password in the the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

To view passwords, you need the lockbox.payloadViewer role.

You can request the cluster name with the list of clusters in the folder.

  1. Open the current Terraform configuration file that defines your infrastructure.

    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.

  2. Locate the user's yandex_mdb_postgresql_user resource.

  3. Change the value of the password field:

    resource "yandex_mdb_postgresql_user" "<username>" {
  ...
  name     = "<username>"
  password = "<new_password>"
  ...
}

    The password must be from 8 to 128 characters long.

    You can also generate a new password using Connection Manager. To do this, specify generate_password = true instead of password = "<new_password>".

    To view the new password, select the cluster in the management console, go to the Users tab, and click View password in the the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

    Note

    If the old password was generated, you cannot regenerate it using Terraform due to provider limitations.

  4. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  5. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.update method and send the following request, e.g., using cURL:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

    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. The password must be from 8 to 128 characters long.

      You can also generate a password using Connection Manager. To do this, edit the contents of the data field:

      {
  "updateMask": "generatePassword",
  "generatePassword": true
}

      To view the new password, select the cluster in the management console, go to the Users tab, and click View password in the the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

      To view passwords, you need the lockbox.payloadViewer role.

    You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. 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.

  3. Use the ClusterService.Update call and send the following request, e.g., via gRPCurl:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] 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 of paths[] strings.

      Only one parameter is provided in this case.

    • password: New password. The password must be from 8 to 128 characters long.

      You can also generate a password using Connection Manager. To do this, edit the contents of the d parameter:

      {
  "cluster_id": "<cluster_ID>",
  "user_name": "<username>",
  "update_mask": {
    "paths": [
      "generate_password"
    ]
  },
  "generate_password": true
}

      To view the new password, select the cluster in the management console, go to the Users tab, and click View password in the the user's row. This will open the page of the Yandex Lockbox secret that stores the password. The new password version is labeled as Current.

      To view passwords, you need the lockbox.payloadViewer role.

    You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  4. 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.

  1. Navigate to the folder dashboard and select Managed Service for PostgreSQL.
  2. Click the cluster name and open the Users tab.
  3. Click and select Configure.
  4. Configure user permissions to access certain databases:
    1. To grant access to the required databases:
      1. In the Database field, click to the right of the drop-down list.
      2. Select the database from the drop-down list.
      3. Repeat the previous two steps until all the required databases are selected.
    2. To revoke access to a specific database, click to the right of the database name.
  5. 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.
  6. Click Save.

If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder specified when creating the CLI profile is used by default. To change the default folder, use the yc config set folder-id <folder_ID> command. 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:

  1. 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 the 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.

  2. 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 the list of clusters in the folder.

  1. Open the current Terraform configuration file that defines your infrastructure.

    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.

  2. To grant the user permissions to access certain databases:

    1. Locate the user's yandex_mdb_postgresql_user resource.

    2. 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>"
  }
  ...
}

  3. 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.

  4. To change the PostgreSQL settings for the user, provide their parameters in the settings section:

    resource "yandex_mdb_postgresql_user" "<username>" {
  ...
  name     = "<username>"
  settings = {
    <DB_settings>
  }
  ...
}

  5. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  6. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.update method and send the following request, e.g., using cURL:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

    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.

      Only one parameter is provided in this case.

    • settings: New settings. See the method description and User-level settings for the list of available settings.

    You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. 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.

  3. Use the ClusterService.Update call and send the following request, e.g., via gRPCurl:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] 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 of paths[] strings.

      Only one parameter is provided in this case.

    • settings: New settings. See the method description and User-level settings for the list of available settings.

    You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  4. View the server response to make sure the request was successful.

Configuring deletion protection

  1. Navigate to the folder dashboard and select Managed Service for PostgreSQL.
  2. Click the cluster name and open the Users tab.
  3. Click and select Configure.
  4. Configure user deletion protection. To do this, select the relevant value in the Deletion protection field.
  5. Click Save.

  1. Open the current Terraform configuration file that defines your infrastructure.

  2. Locate the user's yandex_mdb_postgresql_user resource.

  3. Add the deletion_protection parameter. The possible values are true,false, or unspecified (inherits the value from the cluster). The default value is unspecified.

    resource "yandex_mdb_postgresql_user" "<username>" {
  ...
  deletion_protection = <deletion_protection>
  ...
}

  4. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  5. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.update method and send the following request, e.g., using cURL:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.

    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>
            }
          }'

    Where:

    • updateMask: List of parameters to update as a single string, separated by commas.

      Only one parameter is provided in this case.

    • deletionProtection: User deletion protection, true, false, or unspecified (inherits the value from the cluster). The default value is unspecified.

    You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. 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.

  3. Use the ClusterService.Update call and send the following request, e.g., via gRPCurl:

    Warning

    The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] 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>
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.UserService.Update

    Where:

    • update_mask: List of parameters to update as an array of paths[] strings.

      Only one parameter is provided in this case.

    • deletion_protection: User deletion protection, true, false, or unspecified (inherits the value from the cluster). The default value is unspecified.

    You can request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  4. 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:

  1. Navigate to the folder dashboard and select Managed Service for PostgreSQL.
  2. Click the cluster name and open the Users tab.
  3. Click and select Delete.
  4. Confirm the deletion.

If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder specified when creating the CLI profile is used by default. To change the default folder, use the yc config set folder-id <folder_ID> command. 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 the list of clusters in the folder.

To delete a user:

  1. Open the current Terraform configuration file that defines your infrastructure.

    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.

  2. Delete the yandex_mdb_postgresql_user resource with the user's description.

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the User.delete method and send the following 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 request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. 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.

  3. Use the ClusterService.Delete call and send the following 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 request the cluster ID with the list of clusters in the folder and the username, with the list of users in the cluster.

  4. 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:

  1. Create a user named user2. Select the databases that the user should have access to.

  2. Connect to the db1 database under the database owner account.

  3. Grant user2 the required permissions.

    Examples:

    • Grant access only to the Products table in the default public schema:

      GRANT SELECT ON public.Products TO user2;

    • Grant access to objects in myschema:

      GRANT USAGE ON SCHEMA myschema TO user2;

    • Grant access to all tables and sequences in myschema:

      GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO user2;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA myschema to user2;

    • Allow calling my_function in myschema:

      GRANT EXECUTE ON FUNCTION myschema.my_function TO user2;

    • Change the default privileges for tables and sequences in myschema:

      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;

      The ALTER DEFAULT PRIVILEGES commands allow you to change access permissions for new objects you will create later (in this case, myschema tables and sequences) but do not affect permissions granted for existing objects.

      To update privileges for existing objects, use the GRANT and REVOKE commands.

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:

  1. Add the postgresql provider to the required_providers section in the provider configuration file:

    terraform {
  required_providers {
    ...
    postgresql = {
      source   = "cyrilgdn/postgresql"
    }
    ...
  }
}

  2. Open the Terraform configuration file with the infrastructure plan.

  3. 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
  }
}

  4. Add the postgresql provider and configure its access permissions to db1:

    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
}

  5. 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"
}

  6. Initialize Terraform once again:

    terraform init

  7. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  8. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

Revoking permissions

  1. Connect to the db1 database under the database owner account.

  2. Revoke the relevant access permissions from user2.

    Examples:

    1. Revoke all privileges for tables in the myschema schema:

      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema FROM user2;

    2. Revoke access for the Products table in the default public schema:

      REVOKE SELECT ON public.Products FROM user2;

    3. Revoke access to all tables in myschema:

      REVOKE SELECT ON ALL TABLES IN SCHEMA myschema FROM user2;

    4. Revoke access for objects in the myschema schema:

      REVOKE USAGE ON SCHEMA myschema FROM user2;

  1. Open the Terraform configuration file you used to grant privileges.

  2. In the postgresql_grant section, remove the privilege you want to revoke from the privileges parameter.

    To revoke all privileges, leave the privileges array empty or completely remove the postgresql_grant resource.

    resource "postgresql_grant" "readonly_tables" {
  database          = yandex_mdb_postgresql_database.db1.name
  role              = yandex_mdb_postgresql_user.user2.name
  object_type       = "table"
  privileges        = []
  schema            = "public"
}

  3. Make sure the settings are correct.

    1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.

    2. Run this command:

      terraform validate

      Terraform will show any errors found in your configuration files.

  4. Confirm updating the resources.

    1. Run this command to view the planned changes:

      terraform plan

      If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.

    2. If everything looks correct, apply the changes:

      1. Run this command:

        terraform apply

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

Previous
Managing databases
Next
Assigning privileges and roles