Contact UsTry it for free
© 2026 Direct Cursus Technology L.L.C.

Managing PostgreSQL users

Written by
Updated at December 25, 2025

You can add users, remove users, and manage their individual settings.

Warning

While you can use SQL commands to assign user privileges, you cannot use them to add or modify users. For more information, see Assigning privileges and roles to users.

Getting a list of users

  1. Go to Managed Service for PostgreSQL.
  2. Click the name of your cluster and open the Users tab.

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

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command 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 get the cluster name from the folder’s cluster list.

  1. Get an IAM token for API authentication and place it in an environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Call the User.List method, for instance, via the following cURL request:

    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 from the folder’s cluster list.

  3. Check the server response to make sure your request was successful.

  1. Get an IAM token for API authentication and place it in an 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. Call the UserService.List method, for instance, via the following gRPCurl request:

    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 from the folder’s cluster list.

  4. Check the server response to make sure your request was successful.

Creating a user

Warning

For new users, the privilege to create tables in the public schema depends on the PostgreSQL version:

  • Version 14 and below: The privilege is granted automatically and cannot be revoked.
  • 15 and above: The privilege must be granted to the user manually.

  1. Go to Managed Service for PostgreSQL.

  2. Click the name of your cluster and select the Users tab.

  3. Click Create user.

  4. Specify the database user’s name.

    The username may contain Latin letters, numbers, hyphens, and underscores. It must start with a letter, number, or underscore, but cannot begin with a pg_ prefix. The maximum name length is 63 characters.

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

  5. Select the method for setting a password:

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

    • Generate: Generate a password using Connection Manager.

    To view the password, navigate to the cluster page, select the Users tab, and click View password for the new user. This will open the page of the Yandex Lockbox secret containing the password. To view passwords, you need the lockbox.payloadViewer role.

  6. Select the deletion protection option.

    Possible values:

    • Same as cluster
    • Enabled
    • Disabled

  7. Choose which databases the user can access:

    1. In the Database field, click next to the drop-down list.
    2. Select a database from the drop-down list.
    3. Repeat these two steps to select all required databases.
    4. To delete a mistakenly added database, click next to its name.

  8. Configure the DBMS settings for the user.

  9. Click Save.

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

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

To create a cluster user, run this 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 8-128 characters long.

    You can also generate a password using Connection Manager. To do this, replace --password=<password> with --generate-password.

    To view the password, select your cluster in the management console, navigate to the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. Viewing passwords requires the lockbox.payloadViewer role.

  • permissions: List of databases to which the user must have access.

  • conn-limit: Maximum number of connections per user.

This command only configures the user’s basic settings.

The username may contain Latin letters, numbers, hyphens, and underscores. It must start with a letter, number, or underscore, but cannot begin with a pg_ prefix. The maximum name length is 63 characters.

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

To configure the user’s DBMS settings, use the parameters described in User settings.

You can get the cluster name from the folder’s cluster list.

  1. Open the current Terraform configuration file describing your infrastructure.

    To learn how to create this file, see Creating clusters.

    For a complete list of configurable fields of Managed Service for PostgreSQL cluster user accounts, refer to the Terraform provider guides.

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

    The username may contain Latin letters, numbers, hyphens, and underscores. It must start with a letter, number, or underscore, but cannot begin with a pg_ prefix. The maximum name length is 63 characters.

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

    The password must be 8-128 characters long.

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

    To view the password, select your cluster in the management console, navigate to the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. Viewing passwords requires the lockbox.payloadViewer role.

  3. Validate your configuration.

    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 resource changes.

    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 place it in an environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Call the User.Create method, for instance, via the following cURL request:

    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 are the settings for the new database user:

    • name: Username.

      The username may contain Latin letters, numbers, hyphens, and underscores. It must start with a letter, number, or underscore, but cannot begin with a pg_ prefix. The maximum name length is 63 characters.

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

    • password: Password. The password must be 8-128 characters long.

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

      To view the password, select your cluster in the management console, navigate to the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. Viewing passwords requires the lockbox.payloadViewer role.

    • permissions.databaseName: Array of databases to which the user must have access. Each array item represents a single database.

    • connLimit: Maximum number of database connections per user.

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

    You can get the cluster ID from the folder’s cluster list.

  3. Check the server response to make sure your request was successful.

  1. Get an IAM token for API authentication and place it in an 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. Call the ClusterService.Create method, for instance, via the following gRPCurl request:

    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 are the settings for the new database user:

    • name: Username.

      The username may contain Latin letters, numbers, hyphens, and underscores. It must start with a letter, number, or underscore, but cannot begin with a pg_ prefix. The maximum name length is 63 characters.

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

    • password: Password. The password must be 8-128 characters long.

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

      To view the password, select your cluster in the management console, navigate to the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. Viewing passwords requires the lockbox.payloadViewer role.

    • permissions.database_name: Array of databases to which the user must have access. Each array item represents a single database.

    • conn_limit: Maximum number of database connections per user.

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

    You can get the cluster ID from the folder’s cluster list.

  4. Check the server response to make sure your request was successful.

Note

Newly created users only get the CONNECT privilege for selected databases and cannot perform any operations on them. To give a user access to databases, assign them the required privileges or roles.

Changing a password

  1. Go to Managed Service for PostgreSQL.

  2. Click the name of your cluster and select the Users tab.

  3. Click and select Change password.

  4. Select a method for setting a new password:

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

    • Generate: Generate a password using Connection Manager.

  5. Click Edit.

    To view the new password, navigate to the cluster page, select the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. The new password version is marked as Current.

    Viewing passwords requires the lockbox.payloadViewer role.

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

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

To change a user’s password, run this command:

yc managed-postgresql user update <username> \
     --cluster-name=<cluster_name> \
     --password=<new_password>

The password must be 8-128 characters long.

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

To view the new password, select your cluster in the management console, navigate to the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. The new password version is marked as Current.

Viewing passwords requires the lockbox.payloadViewer role.

You can get the cluster name from the folder’s cluster list.

  1. Open the current Terraform configuration file describing your infrastructure.

    To learn how to create this file, see Creating clusters.

    For a complete list of configurable fields of Managed Service for PostgreSQL cluster user accounts, refer to the Terraform provider guides.

  2. Locate the yandex_mdb_postgresql_user resource for the required user.

  3. Update the password field:

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

    The password must be 8-128 characters long.

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

    To view the new password, select your cluster in the management console, navigate to the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. The new password version is marked as Current.

    Note

    If the current password has been automatically generated, you cannot regenerate it using Terraform due to provider limitations.

  4. Validate your configuration.

    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 resource changes.

    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 place it in an environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Call the User.Update method, for instance, via the following cURL request:

    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: Comma-separated list of settings you want to update.

      Here, we provide only one setting.

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

      You can also generate a password using Connection Manager. To do this, update the data field value:

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

      To view the new password, select your cluster in the management console, navigate to the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. The new password version is marked as Current.

      Viewing passwords requires the lockbox.payloadViewer role.

    You can get the cluster ID from the folder’s cluster list, and the username from the cluster’s user list.

  3. Check the server response to make sure your request was successful.

  1. Get an IAM token for API authentication and place it in an 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. Call the UserService.Update method, for instance, via the following gRPCurl request:

    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 settings you want to update as an array of strings (paths[]).

      Here, we provide only one setting.

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

      You can also generate a password using Connection Manager. To do this, modify the d argument:

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

      To view the new password, select your cluster in the management console, navigate to the Users tab, and click View password for the relevant user. This will open the page of the Yandex Lockbox secret containing the password. The new password version is marked as Current.

      Viewing passwords requires the lockbox.payloadViewer role.

    You can get the cluster ID from the folder’s cluster list, and the username from the list of cluster users.

  4. Check the server response to make sure your request was successful.

Changing user settings

Note

These settings do not affect PostgreSQL privileges and roles, which are configured separately.

To learn how to assign privileges and roles to a user, see this article.

  1. Go to Managed Service for PostgreSQL.
  2. Click the name of your cluster and select the Users tab.
  3. Click and select Configure.
  4. Configure user permissions for access to specific databases:
    1. To grant user access to the required databases:
      1. In the Database field, click next to the drop-down list.
      2. Select a database from the drop-down list.
      3. Repeat these two steps to select all required databases.
    2. To revoke user access to a specific database, click next to its name.
  5. Click DBMS settings to change the user’s maximum connections (Conn limit), cluster login permissions (Login), or other PostgreSQL settings.
  6. Click Save.

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

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

You can change user settings from the command line interface:

  1. To configure user access to specific databases, run this command with a list of database names specified in the --permissions option:

    yc managed-postgresql user update <username> \
     --cluster-name=<cluster_name> \
     --permissions=<database_list>

    Where:

    • cluster-name: Cluster name.
    • permissions: List of databases to which the user must have access.

    You can get the cluster name from the folder’s cluster list.

    This command grants the user access to the listed databases.

    To revoke user access to a specific database, remove its name from the list and run the command with the updated list.

  2. To change PostgreSQL user settings, run this command with the relevant arguments:

    yc managed-postgresql user update <username> \
     --cluster-name=<cluster_name> \
     --<setting_1>=<value_1> \
     --<setting_2>=<value_2> \
     --<setting_3>=<list_of_values> \
     ...

    To change a user’s connection limit, use the --conn-limit option.

    You can get the cluster name from the folder’s cluster list.

  1. Open the current Terraform configuration file describing your infrastructure.

    To learn how to create this file, see Creating clusters.

    For a complete list of configurable fields of Managed Service for PostgreSQL cluster user accounts, refer to the Terraform provider guides.

  2. To configure user permissions for access to specific databases:

    1. Locate the yandex_mdb_postgresql_user resource for the required user.

    2. Add permission sections with the relevant database names:

      resource "yandex_mdb_postgresql_user" "<username>" {
  ...
  name = "<username>"
  permission {
    database_name = "<DB_name>"
  }
  permission {
    database_name = "<DB_name>"
  }
  ...
}

  3. To revoke user access to a specific database, delete the permission section with its name from the configuration file.

  4. To change PostgreSQL user settings, specify the relevant arguments in the settings section:

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

  5. Validate your configuration.

    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 resource changes.

    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 place it in an environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Call the User.Update method, for instance, via the following cURL request:

    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: Comma-separated list of settings you want to update.

      Here, we provide only one setting.

    • settings: New settings. For the list of available settings, refer to the method description and User-level settings.

    You can get the cluster ID from the folder’s cluster list, and the username from the list of cluster users.

  3. Check the server response to make sure your request was successful.

  1. Get an IAM token for API authentication and place it in an 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. Call the UserService.Update method, for instance, via the following gRPCurl request:

    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 settings you want to update as an array of strings (paths[]).

      Here, we provide only one setting.

    • settings: New settings. For the list of available settings, refer to the method description and User-level settings.

    You can get the cluster ID from the folder’s cluster list, and the username from the list of cluster users.

  4. Check the server response to make sure your request was successful.

Configuring deletion protection

  1. Go to Managed Service for PostgreSQL.
  2. Click the name of your cluster and select the Users tab.
  3. Click and select Configure.
  4. Configure user deletion protection by selecting the relevant value in the Deletion protection field.
  5. Click Save.

  1. Open the current Terraform configuration file describing your infrastructure.

  2. Locate the yandex_mdb_postgresql_user resource for the required user.

  3. Add the deletion_protection attribute. Possible values: true, false, or unspecified (inherits cluster setting). The default value is unspecified.

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

  4. Validate your configuration.

    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 resource changes.

    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 place it in an environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Call the User.Update method, for instance, via the following cURL request:

    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: Comma-separated list of settings you want to update.

      Here, we provide only one setting.

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

    You can get the cluster ID from the folder’s cluster list, and the username from the list of cluster users.

  3. Check the server response to make sure your request was successful.

  1. Get an IAM token for API authentication and place it in an 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. Call the UserService.Update method, for instance, via the following gRPCurl request:

    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 settings you want to update as an array of strings (paths[]).

      Here, we provide only one setting.

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

    You can get the cluster ID from the folder’s cluster list, and the username from the list of cluster users.

  4. Check the server response to make sure your request was successful.

Warning

Deletion protection only applies to individual users. Deleting a cluster will permanently remove all its users, including those with deletion protection.

Deleting a user

A user account can have deletion protection enabled. To delete it, disable deletion protection first.

To delete a user:

  1. Go to Managed Service for PostgreSQL.
  2. Click the name of your cluster and select the Users tab.
  3. Click and select Delete.
  4. Confirm the deletion.

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

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

To delete a user, run this command:

yc managed-postgresql user delete <username> \
     --cluster-name <cluster_name>

You can get the cluster name from the folder’s cluster list.

To delete a user:

  1. Open the current Terraform configuration file describing your infrastructure.

    To learn how to create this file, see Creating clusters.

    For a complete list of configurable Managed Service for PostgreSQL cluster fields, refer to the Terraform provider guides.

  2. Delete the yandex_mdb_postgresql_user resource describing the target user.

  3. Validate your configuration.

    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 resource changes.

    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 place it in an environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Call the User.Delete method, for instance, via the following cURL request:

    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 from the folder’s cluster list, and the username from the list of cluster users.

  3. Check the server response to make sure your request was successful.

  1. Get an IAM token for API authentication and place it in an 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. Call the UserService.Delete method, for instance, via the following gRPCurl request:

    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 from the folder’s cluster list, and the username from the list of cluster users.

  4. Check the server response to make sure your request was successful.

Examples

Add a user with read-only permissions

Alert

Do not use this example for users created with Terraform as subsequent changes made via Terraform may overwrite privileges granted via SQL.

To add a new user2 account with read-only access for the db1 database to an existing cluster:

  1. Create a user named user2. While creating the user, specify which databases they can access.

  2. Connect to the db1 database as the owner.

  3. Grant user2 the required permissions.

    Examples:

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

      GRANT SELECT ON public.Products TO user2;

    • Grant access to all 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;

    • Grant execution permission for 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 the default access privileges for future objects, without affecting existing ones. In the example above, we change default privileges for new myschema tables and sequences.

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

You can only grant user privileges via Terraform in a cluster with publicly accessible hosts.

You can grant user privileges via Terraform using the third-party Terraform Provider for PostgreSQL.

Note

Terraform Provider for PostgreSQL is not part of Managed Service for PostgreSQL. Therefore, it is not maintained by Yandex Cloud support and development teams and its use falls outside the scope of the Yandex Managed Service for PostgreSQL Terms of Use.

To learn more about granting privileges, see Granting a privilege to a user.

Suppose you have a cluster named mypg with a user named user1 as its owner. To add a new cluster account user2 with read-only access to the tables in the public schema of the db1 database, do the following:

  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 describing your infrastracture.

  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 the db1 database:

    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. Reinitialize Terraform:

    terraform init

  7. Validate your configuration.

    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 resource changes.

    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 privileges

  1. Connect to the db1 database as the owner.

  2. Revoke access privileges from user2.

    Examples:

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

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

    2. Revoke access to 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 to all objects in myschema:

      REVOKE USAGE ON SCHEMA myschema FROM user2;

  1. Open the Terraform configuration file used for granting privileges.

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

    To revoke all privileges, leave the privileges array empty or remove the entire postgresql_grant section.

    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. Validate your configuration.

    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 resource changes.

    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