[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for PostgreSQL](../index.md) > [Step-by-step guides](index.md) > PostgreSQL users > Managing database users

# Managing PostgreSQL users


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

{% note 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](grant.md).

{% endnote %}

## Getting a list of users {#list-users}

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to **Managed Service for PostgreSQL**.
  1. Click the name of your cluster and select the **Users** tab.

- CLI {#cli}

  If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

  The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

  To get a list of cluster users, run this command:

  ```
  yc managed-postgresql user list \
       --cluster-name <cluster_name>
  ```

  You can get the cluster name with the [list of clusters in the folder](cluster-list.md).

- REST API {#api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Call the [User.List](../api-ref/User/list.md) method, e.g., via the following [cURL](https://curl.se/) request:

     ```bash
     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](cluster-list.md#list-clusters).

  1. Check the [server response](../api-ref/User/list.md#yandex.cloud.mdb.postgresql.v1.ListUsersResponse) to make sure your request was successful.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [UserService.List](../api-ref/grpc/User/list.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

     ```bash
     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 the [list of clusters in the folder](cluster-list.md#list-clusters).

  1. Check the [server response](../api-ref/grpc/User/list.md#yandex.cloud.mdb.postgresql.v1.ListUsersResponse) to make sure your request was successful.

{% endlist %}

## Creating a user {#adduser}

{% note 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](grant.md#user-readonly).

{% endnote %}

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
  1. Click the name of your cluster and select the **Users** tab.
  1. Click **Create user**.

  
  1. Select the authentication method:

     * **Password**: Authentication by username and password.

       1. Specify the database user 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.

       
       1. 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](cluster-create.md#conn-man).

           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.


     * **IAM**: Authentication with a [Yandex account](../../iam/concepts/users/accounts.md#passport), [federated account](../../iam/concepts/users/accounts.md#saml-federation), or [local user](../../iam/concepts/users/accounts.md#local).

       In the **User** field, open the list of available user accounts and select the one you need. To find the account, use the search bar above the list.


  1. Select the password verification algorithm. The possible values are:
  
     * **MD5**
     * **SCRAM-SHA-256**
       
     If you do not select the algorithm, the value of the cluster-level `Password Encryption` [DBMS setting](../concepts/settings-list.md#dbms-cluster-settings) applies. The default setting value is `MD5`.
  
  1. Select the deletion protection option.

     Possible values:
     - **Same as cluster**
     - **Enabled**
     - **Disabled**
  1. Select which databases the user should have access to:
     1. In the **Database** field, click ![image](../../_assets/console-icons/plus.svg) next to the drop-down list.
     1. Select the database from the drop-down list.
     1. Repeat these two steps to select all required databases.
     1. To delete a database added by mistake, click ![image](../../_assets/console-icons/xmark.svg) next to its name.
  1. Configure the [DBMS settings](../concepts/settings-list.md#dbms-user-settings) for the user.
  1. Click **Save**.

- CLI {#cli}

  If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

  The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

  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 from 8 to 128 characters long.

      
      You can also generate a password using [Connection Manager](cluster-create.md#conn-man). Do it by specifying `--generate-password` instead of `--password=<password>`.

      To view the password, select your cluster in the [management console](https://console.yandex.cloud), navigate to 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.


  * `permissions`: List of databases the user should have access to.
  * `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](../concepts/settings-list.md#dbms-user-settings).

  You can get the cluster name from the [list of clusters in your folder](cluster-list.md).

- Terraform {#tf}

  1. Open the current Terraform configuration file with the infrastructure plan.

     To learn how to create this file, see [Creating a cluster](cluster-create.md).

     For the complete list of adjustable configuration fields for Managed Service for PostgreSQL cluster users, see [this Terraform provider guide](../../terraform/resources/mdb_postgresql_user.md).

  1. Add the `yandex_mdb_postgresql_user` resource:

      ```hcl
      resource "yandex_mdb_postgresql_user" "<username>" {
        cluster_id = "<cluster_ID>"
        name       = "<username>"
        password   = "<password>"
        grants     = [ "<role_1>","<role_2>" ]
        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. 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 from 8 to 128 characters long.

      
      You can also generate a password using Connection Manager. Do it by specifying `generate_password = true` instead of `password = "<password>"`.

      To view the password, select your cluster in the [management console](https://console.yandex.cloud), navigate to 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.


  1. 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.
     1. Run this command:
     
        ```bash
        terraform validate
        ```
     
        Terraform will show any errors found in your configuration files.

  1. Confirm updating the resources.

      1. Run this command to view the planned changes:
      
         ```bash
         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.
      
      1. If everything looks correct, apply the changes:
         1. Run this command:
      
            ```bash
            terraform apply
            ```
      
         1. Confirm updating the resources.
         1. Wait for the operation to complete.

- REST API {#api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Call the [User.Create](../api-ref/User/create.md) method, e.g., via the following [cURL](https://curl.se/) request:

     ```bash
     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>,
                   "userPasswordEncryption": <password_verification_algorithm>
                 }
               }'
     ```

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

        
        You can also generate a password using [Connection Manager](cluster-create.md#conn-man). Do it by specifying `"generatePassword": true` instead of `"password": "<user_password>"`.

        To view the password, select your cluster in the [management console](https://console.yandex.cloud), navigate to 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.


     * `permissions.databaseName`: Array of databases the user should have access to. Each array element is for a separate database.
     * `connLimit`: Maximum number of database connections per user.
     * `deletionProtection`: User deletion protection, `true`, `false`, or `unspecified` (inherits the value from the cluster). The default value is `unspecified`. 
     * `userPasswordEncryption`: Password verification algorithm. The possible values are:
            
       * `USER_PASSWORD_ENCRYPTION_MD5`
       * `USER_PASSWORD_ENCRYPTION_SCRAM_SHA_256`
     
       If you do not provide the algorithm, the value of the cluster-level `password_encryption` [DBMS setting](../concepts/settings-list.md#dbms-cluster-settings) applies. The default setting value is `USER_PASSWORD_ENCRYPTION_MD5`.

     You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters).

  1. Check the [server response](../api-ref/User/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [UserService.Create](../api-ref/grpc/User/create.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

     ```bash
     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>,
               "user_password_encryption": <password_verification_algorithm>
             }
           }' \
       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`: User password. The password must be from 8 to 128 characters long.

        
        You can also generate a password using Connection Manager. Do it by specifying `"generate_password": true` instead of `"password": "<user_password>"`.

        To view the password, select the cluster you created in the [management console](https://console.yandex.cloud), go to the **Users** tab, and click **View password** in the new user row. This will open the page of the Yandex Lockbox secret containing 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 for a separate database.
     * `conn_limit`: Maximum number of database connections per user.
     * `deletion_protection`: User deletion protection, `true`, `false`, or `unspecified` (inherits the value from the cluster). The default value is `unspecified`.
     * `user_password_encryption`: Password verification algorithm. The possible values are:
            
       * `USER_PASSWORD_ENCRYPTION_MD5`
       * `USER_PASSWORD_ENCRYPTION_SCRAM_SHA_256`
     
       If you do not provide the algorithm, the value of the cluster-level `password_encryption` [DBMS setting](../concepts/settings-list.md#dbms-cluster-settings) applies. The default setting value is `USER_PASSWORD_ENCRYPTION_MD5`.

     You can get the cluster ID with the [list of clusters in the folder](cluster-list.md#list-clusters).

  1. Check the [server response](../api-ref/grpc/User/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

{% note info %}

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](grant.md) them the required privileges or roles.

{% endnote %}

## Changing a password {#updateuser}

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
  1. Click the name of your cluster and select the **Users** tab.
  1. Click ![image](../../_assets/console-icons/ellipsis.svg) and select **Change password**.

  
  1. Select the 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](cluster-create.md#conn-man).


  1. Optionally, select the password verification algorithm. The possible values are:
  
     * **MD5**
     * **SCRAM-SHA-256**

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

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


- CLI {#cli}

  If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

  The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

  To change a 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](cluster-create.md#conn-man). To do this, specify `--generate-password` instead of `--password=<new_password>`.

    To view the new password, select your cluster in the [management console](https://console.yandex.cloud), 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**.

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


  You can get the cluster name from the [list of clusters in your folder](cluster-list.md).

- Terraform {#tf}

  1. Open the current Terraform configuration file with the infrastructure plan.

      To learn how to create this file, see [Creating a cluster](cluster-create.md).

      For the complete list of adjustable configuration fields for Managed Service for PostgreSQL cluster users, see [this Terraform provider guide](../../terraform/resources/mdb_postgresql_user.md).

  1. Locate the `yandex_mdb_postgresql_user` resource for the user in question.
  1. Edit the `password` field value:

      ```hcl
      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](cluster-create.md#conn-man). To do this, specify `generate_password = true` instead of `password = "<new_password>"`.

      To view the new password, select your cluster in the [management console](https://console.yandex.cloud), 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 info %}

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

      {% endnote %}


  1. 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.
      1. Run this command:
      
         ```bash
         terraform validate
         ```
      
         Terraform will show any errors found in your configuration files.

  1. Confirm updating the resources.

      1. Run this command to view the planned changes:
      
         ```bash
         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.
      
      1. If everything looks correct, apply the changes:
         1. Run this command:
      
            ```bash
            terraform apply
            ```
      
         1. Confirm updating the resources.
         1. Wait for the operation to complete.

- REST API {#api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Call the [User.Update](../api-ref/User/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

     {% note 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.
     
     {% endnote %}

     ```bash
     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": "<list_of_parameters_to_update>",
                 "password": "<new_password>",
                 "userPasswordEncryption": <password_verification_algorithm>
               }'
     ```

     Where:

     * `updateMask`: Comma-separated string of settings you want to update.

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

       
       You can also generate a password using [Connection Manager](cluster-create.md#conn-man). To do this, edit the `data` field as follows:

          ```bash
          {
            "updateMask": "<list_of_parameters_to_update>",
            "generatePassword": true,
            "userPasswordEncryption": <password_verification_algorithm>
          }
          ```

       To view the new password, select your cluster in the [management console](https://console.yandex.cloud), 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**.

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


     * `userPasswordEncryption`: Password verification algorithm. This is an optional parameter. The possible values are:
            
       * `USER_PASSWORD_ENCRYPTION_MD5`
       * `USER_PASSWORD_ENCRYPTION_SCRAM_SHA_256`
     
       If you do not provide the algorithm, its value does not change when changing the password.
     
     You can get the cluster ID from the [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/User/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [UserService.Update](../api-ref/grpc/User/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

     {% note 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.
     
     {% cut "Format for listing settings" %}
     
     ```yaml
     "update_mask": {
         "paths": [
             "<setting_1>",
             "<setting_2>",
             ...
             "<setting_N>"
         ]
     }
     ```
     
     {% endcut %}
     
     {% endnote %}

     ```bash
     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": [
                 <list_of_parameters_to_update>
               ]
             },
             "password": "<new_password>",
             "user_password_encryption": <password_verification_algorithm>
           }' \
       mdb.api.cloud.yandex.net:443 \
       yandex.cloud.mdb.postgresql.v1.UserService.Update
     ```

     Where:

     * `update_mask`: List of settings to update as an array of strings (`paths[]`).

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

       
       You can also generate a password using [Connection Manager](cluster-create.md#conn-man). To do this, edit the `d` parameter as follows:

          ```bash
          {
            "cluster_id": "<cluster_ID>",
            "user_name": "<username>",
            "update_mask": {
              "paths": [
                <list_of_parameters_to_update>
              ]
            },
            "generate_password": true,
            "user_password_encryption": <password_verification_algorithm>
          }
          ```

       To view the new password, select your cluster in the [management console](https://console.yandex.cloud), 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**.

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


     * `user_password_encryption`: Password verification algorithm. This is an optional parameter. The possible values are:
            
       * `USER_PASSWORD_ENCRYPTION_MD5`
       * `USER_PASSWORD_ENCRYPTION_SCRAM_SHA_256`
     
       If you do not provide the algorithm, its value does not change when changing the password. 

     You can get the cluster ID from the [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/grpc/User/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

## Changing user settings {#update-settings}

{% note info %}

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](grant.md).

{% endnote %}

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
  1. Click the name of your cluster and select the **Users** tab.
  1. Click ![image](../../_assets/console-icons/ellipsis.svg) and select **Configure**.
  1. Configure user access to specific databases:
     1. To grant access to databases:
        1. In the **Database** field, click ![image](../../_assets/console-icons/plus.svg) next to the drop-down list.
        1. Select the database from the drop-down list.
        1. Repeat these two steps to select all required databases.
     1. To revoke user access to a database, click ![image](../../_assets/console-icons/xmark.svg) next to its name.
  1. Click **DBMS settings** to change the user’s maximum connections (**Conn limit**), cluster login permissions (**Login**), or other [PostgreSQL settings](../concepts/settings-list.md#dbms-user-settings).
  1. Click **Save**.

- CLI {#cli}

  If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

  The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

  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 the user should have access to.

     You can get the cluster name with the [list of clusters in the folder](#list-clusters).

     This command grants the user access to the listed databases.

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

  1. To change [PostgreSQL user settings](../concepts/settings-list.md#dbms-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 with the [list of clusters in the folder](#list-clusters).

- Terraform {#tf}

    1. Open the current Terraform configuration file with the infrastructure plan.

        To learn how to create this file, see [Creating a cluster](cluster-create.md).

        For the complete list of adjustable configuration fields for Managed Service for PostgreSQL cluster users, see [this Terraform provider guide](../../terraform/resources/mdb_postgresql_user.md).

    1. To configure user permissions for access to specific databases:
        1. Locate the `yandex_mdb_postgresql_user` resource for the user in question.
        1. Add `permission` sections with the relevant database names:

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

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

    1. To change [PostgreSQL user settings](../concepts/settings-list.md#dbms-user-settings), specify the relevant arguments in the `settings` section:

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

    1. 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.
        1. Run this command:
        
           ```bash
           terraform validate
           ```
        
           Terraform will show any errors found in your configuration files.

    1. Confirm updating the resources.

        1. Run this command to view the planned changes:
        
           ```bash
           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.
        
        1. If everything looks correct, apply the changes:
           1. Run this command:
        
              ```bash
              terraform apply
              ```
        
           1. Confirm updating the resources.
           1. Wait for the operation to complete.

- REST API {#api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Call the [User.Update](../api-ref/User/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

     {% note 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.
     
     {% endnote %}

     ```bash
     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 string of settings to update.

       Here, we provide only one setting.

     * `settings`: New settings. For the list of available settings, refer to the [method description](../api-ref/User/update.md#yandex.cloud.mdb.postgresql.v1.UpdateUserRequest) and [User-level settings](../concepts/settings-list.md#dbms-user-settings).

     You can get the cluster ID from the [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/User/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [UserService.Update](../api-ref/grpc/User/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

     {% note 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.
     
     {% cut "Format for listing settings" %}
     
     ```yaml
     "update_mask": {
         "paths": [
             "<setting_1>",
             "<setting_2>",
             ...
             "<setting_N>"
         ]
     }
     ```
     
     {% endcut %}
     
     {% endnote %}

     ```bash
     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](../api-ref/grpc/User/get.md#yandex.cloud.mdb.postgresql.v1.UserSettings) and [User-level settings](../concepts/settings-list.md#dbms-user-settings).

     You can get the cluster ID from the [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/grpc/User/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

## Configuring deletion protection {#update-user-deletion-protection}

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
  1. Click the name of your cluster and select the **Users** tab.
  1. Click ![image](../../_assets/console-icons/ellipsis.svg) and select **Configure**.
  1. Configure user deletion protection by selecting the relevant value in the **Deletion protection** field.
  1. Click **Save**.

- Terraform {#tf}

    1. Open the current Terraform configuration file with the infrastructure plan.

    1. Locate the `yandex_mdb_postgresql_user` resource for the user in question.

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

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

    1. 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.
        1. Run this command:
        
           ```bash
           terraform validate
           ```
        
           Terraform will show any errors found in your configuration files.

    1. Confirm updating the resources.

        1. Run this command to view the planned changes:
        
           ```bash
           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.
        
        1. If everything looks correct, apply the changes:
           1. Run this command:
        
              ```bash
              terraform apply
              ```
        
           1. Confirm updating the resources.
           1. Wait for the operation to complete.

- REST API {#api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Call the [User.Update](../api-ref/User/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

     {% note 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.
     
     {% endnote %}

     ```bash
     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 string of settings to update.

       Here, we provide only one setting.

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

     You can get the cluster ID from the [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/User/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [UserService.Update](../api-ref/grpc/User/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

     {% note 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.
     
     {% cut "Format for listing settings" %}
     
     ```yaml
     "update_mask": {
         "paths": [
             "<setting_1>",
             "<setting_2>",
             ...
             "<setting_N>"
         ]
     }
     ```
     
     {% endcut %}
     
     {% endnote %}

     ```bash
     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 the value from the cluster). The default value is `unspecified`.

     You can get the cluster ID from the [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/grpc/User/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

{% note warning %}

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

{% endnote %}

## Deleting a user {#removeuser}

A user account can have deletion protection enabled. To delete such a user, [disable the protection](#update-user-deletion-protection) first.

{% list tabs group=instructions %}

- Management console {#console}

  To delete a user:

  1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
  1. Click the name of your cluster and select the **Users** tab.
  1. Click ![image](../../_assets/console-icons/ellipsis.svg) and select **Delete**.
  1. Confirm the deletion.

- CLI {#cli}

  If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

  The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

  To delete a user, run this command:

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

  You can get the cluster name with the [list of clusters in the folder](cluster-list.md).

- Terraform {#tf}

  To delete a user:

  1. Open the current Terraform configuration file with the infrastructure plan.

     To learn how to create this file, see [Creating a cluster](cluster-create.md).

     For the complete list of configurable Managed Service for PostgreSQL cluster fields, see [this Terraform provider guide](../../terraform/resources/mdb_postgresql_cluster.md).

  1. Delete the `yandex_mdb_postgresql_user` resource with the target user’s description.

  1. 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.
     1. Run this command:
     
        ```bash
        terraform validate
        ```
     
        Terraform will show any errors found in your configuration files.

  1. Confirm updating the resources.

     1. Run this command to view the planned changes:
     
        ```bash
        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.
     
     1. If everything looks correct, apply the changes:
        1. Run this command:
     
           ```bash
           terraform apply
           ```
     
        1. Confirm updating the resources.
        1. Wait for the operation to complete.

- REST API {#api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Call the [User.Delete](../api-ref/User/delete.md) method, e.g., via the following [cURL](https://curl.se/) request:

     ```bash
     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 [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/User/delete.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

  1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

     ```bash
     export IAM_TOKEN="<IAM_token>"
     ```

  1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
     
     ```bash
     cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
     ```
     
     Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
  1. Call the [UserService.Delete](../api-ref/grpc/User/delete.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

     ```bash
     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 [list of clusters in your folder](cluster-list.md#list-clusters), and the username from the [list of cluster users](#list-users).

  1. Check the [server response](../api-ref/grpc/User/delete.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

## Examples {#examples}

### Add a user with read-only permissions {#user-readonly}

{% list tabs group=instructions %}

- SQL {#sql}

    {% note 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.

    {% endnote %}

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

    1. [Create a user](cluster-users.md#adduser) named `user2`. While creating the user, specify which databases they can access.
    1. [Connect](connect/code-examples.md) to the `db1` database as the owner.
    1. Grant `user2` the required permissions.

        Examples:

        * Grant access to the `Products` table in the default `public` schema:

            ```sql
            GRANT SELECT ON public.Products TO user2;
            ```

        * Grant access to all objects in `myschema`:

            ```sql
            GRANT USAGE ON SCHEMA myschema TO user2;
            ```

        * Grant access to all tables and sequences in `myschema`:

            ```sql
            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`:

            ```sql
            GRANT EXECUTE ON FUNCTION myschema.my_function TO user2;
            ```

        * Change the default privileges for tables and sequences in `myschema`:

            ```sql
            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.

- Terraform {#tf}

    {% note warning %}

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

    {% endnote %}

    You can grant user privileges via Terraform using the third-party [Terraform Provider for PostgreSQL](https://github.com/cyrilgdn/terraform-provider-postgresql).

    {% note info %}
    
    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](https://yandex.ru/legal/cloud_termsofuse/?lang=en).
    
    {% endnote %}

    To learn more about granting privileges, see [Granting a privilege to a user](grant.md#grant-privilege).

    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:

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

    1. Open the Terraform configuration file describing your infrastructure.
    1. Add the `yandex_mdb_postgresql_user` resource:

        ```hcl
        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
          }
        }
        ```

    1. Add the `postgresql` provider and configure its access permissions to the `db1` database:

        ```hcl
        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
        }
        ```

    1. Add the `postgresql_grant` resource with the following attributes:

        ```hcl
        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"
        }
        ```

    1. Reinitialize Terraform:

        ```bash
        terraform init
        ```

    1. 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.
        1. Run this command:
        
           ```bash
           terraform validate
           ```
        
           Terraform will show any errors found in your configuration files.

    1. Confirm updating the resources.

        1. Run this command to view the planned changes:
        
           ```bash
           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.
        
        1. If everything looks correct, apply the changes:
           1. Run this command:
        
              ```bash
              terraform apply
              ```
        
           1. Confirm updating the resources.
           1. Wait for the operation to complete.

{% endlist %}

### Revoking privileges {#revoke-access}

{% list tabs group=instructions %}

- SQL {#sql}

    1. [Connect](connect/code-examples.md) to the `db1` database as the owner.
    1. Revoke access privileges from `user2`.

        Here are some examples:

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

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

        1. Revoke access to the `Products` table in the default `public` schema:

            ```sql
            REVOKE SELECT ON public.Products FROM user2;
            ```

        1. Revoke access to all tables in `myschema`:

            ```sql
            REVOKE SELECT ON ALL TABLES IN SCHEMA myschema FROM user2;
            ```

        1. Revoke access to all objects in `myschema`:

            ```sql
            REVOKE USAGE ON SCHEMA myschema FROM user2;
            ```

- Terraform {#tf}

    1. Open the Terraform configuration file used for [granting privileges](#user-readonly).

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

        ```hcl
        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"
        }
        ```

    1. 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.
        1. Run this command:
        
           ```bash
           terraform validate
           ```
        
           Terraform will show any errors found in your configuration files.

    1. Confirm updating the resources.

        1. Run this command to view the planned changes:
        
           ```bash
           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.
        
        1. If everything looks correct, apply the changes:
           1. Run this command:
        
              ```bash
              terraform apply
              ```
        
           1. Confirm updating the resources.
           1. Wait for the operation to complete.

{% endlist %}