Managing PostgreSQL users

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To get a list of cluster users, run the following command:

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

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

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

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.list method and make a request, e.g., via cURL:

   curl \
     --request GET \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users'
   

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

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

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

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/List call and make a request, e.g., via gRPCurl:

   grpcurl \
     -format json \
     -import-path ~/cloudapi/ \
     -import-path ~/cloudapi/third_party/googleapis/ \
     -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
     -rpc-header "Authorization: Bearer $IAM_TOKEN" \
     -d '{
           "cluster_id": "<cluster_ID>"
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.UserService.List
   

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

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

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To create a user in a cluster, run the command:

yc managed-postgresql user create <username> \
   --cluster-name <cluster_name> \
   --password=<password> \
   --permissions=<database_list> \
   --conn-limit=<maximum_number_of_connections>

Where:

• cluster-name: Cluster name.
• password: User password.
• permissions: List of databases the user should have access to.
• conn-limit: Maximum number of connections per user.

This command configures only the main user settings.

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

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

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

   For more information about creating this file, see Creating clusters.

   For a complete list of editable fields in the Managed Service for PostgreSQL cluster user configuration, see the Terraform provider documentation.

2. Add the yandex_mdb_postgresql_user resource:

   resource "yandex_mdb_postgresql_user" "<username>" {
     cluster_id = "<cluster_ID>"
     name       = "<username>"
     password   = "<password>"
     grants     = [ "<role1>","<role2>" ]
     login      = <DB_login_permission>
     conn_limit = <maximum_number_of_connections>
     deletion_protection = <deletion_protection>
     settings   = {
       <DB_settings>
     }
     permission {
       database_name = "<DB_name>"
     }
   }
   

   Where:

   • login: Permission to log in to the DB, true or false.
   • deletion_protection: User deletion protection, true, false, or unspecified (inherits the value from the cluster). The default value is unspecified.

   Note

   The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter (except the pg_ combination), number, or underscore. The name may be up to 63 characters long.

   The names mysql, sys, information_schema, and performance_schema are reserved for Managed Service for MySQL®. You cannot create users with these names.

   The password must be between 8 and 128 characters.

3. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

4. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

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

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.create method and make a request, e.g., via cURL:

   curl \
     --request POST \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users' \
     --data '{
               "userSpec": {
                 "name": "<username>",
                 "password": "<user_password>",
                 "permissions": [
                   {
                     "databaseName": "<DB_name>"
                   }
                 ],
                 "connLimit": "<maximum_number_of_DB_connections>",
                 "deletionProtection": <deletion_protection:_true_or_false>
               }
             }'
   

   Where userSpec lists the new DB user settings:

   • name: Username.

   • password: User password.

     Note

     The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter (except the pg_ combination), number, or underscore. The name may be up to 63 characters long.

     The names mysql, sys, information_schema, and performance_schema are reserved for Managed Service for MySQL®. You cannot create users with these names.

     The password must be between 8 and 128 characters.

   • permissions.databaseName: Array of databases the user should have access to. Each array element is mapped to a separate DB.

   • connLimit: Maximum number of DB connections for the user.

   • deletionProtection: DB deletion protection.

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

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

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

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/Create call and make a request, e.g., via gRPCurl:

   grpcurl \
     -format json \
     -import-path ~/cloudapi/ \
     -import-path ~/cloudapi/third_party/googleapis/ \
     -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
     -rpc-header "Authorization: Bearer $IAM_TOKEN" \
     -d '{
           "cluster_id": "<cluster_ID>",
           "user_spec": {
             "name": "<username>",
             "password": "<user_password>",
             "permissions": [
               {
                 "database_name": "<DB_name>"
               }
             ],
             "conn_limit": "<maximum_number_of_DB_connections>",
             "deletion_protection": <deletion_protection:_true_or_false>
           }
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.UserService.Create
   

   Where user_spec lists the new DB user settings:

   • name: Username.

   • password: User password.

     Note

     The username may contain Latin letters, numbers, hyphens, and underscores but must begin with a letter (except the pg_ combination), number, or underscore. The name may be up to 63 characters long.

     The names mysql, sys, information_schema, and performance_schema are reserved for Managed Service for MySQL®. You cannot create users with these names.

     The password must be between 8 and 128 characters.

   • permissions.database_name: Array of databases the user should have access to. Each array element is mapped to a separate DB.

   • conn_limit: Maximum number of DB connections for the user.

   • deletion_protection: DB deletion protection.

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

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

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To change the user password, run this command:

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

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

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

   For more information about creating this file, see Creating clusters.

   For a complete list of editable fields in the Managed Service for PostgreSQL cluster user configuration, see the Terraform provider documentation.

2. Locate the user's yandex_mdb_postgresql_user resource.

3. Change the value of the password field:

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

   Note

   The password must be between 8 and 128 characters.

4. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

5. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

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

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.update method and make a request, e.g., using cURL:

   Warning

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

   curl \
     --request PATCH \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>' \
     --data '{
               "updateMask": "password",
               "password": "<new_password>"
             }'
   

   Where:

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

     In this case, only one parameter is provided.

   • password: New password.

     Note

     The password must be between 8 and 128 characters.

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

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

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

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/Update call and make a request, e.g., using gRPCurl:

   Warning

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

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   grpcurl \
     -format json \
     -import-path ~/cloudapi/ \
     -import-path ~/cloudapi/third_party/googleapis/ \
     -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
     -rpc-header "Authorization: Bearer $IAM_TOKEN" \
     -d '{
           "cluster_id": "<cluster_ID>",
           "user_name": "<username>",
           "update_mask": {
             "paths": [
               "password"
             ]
           },
           "password": "<new_password>"
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.UserService.Update
   

   Where:

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

     In this case, only one parameter is provided.

   • password: New password.

     Note

     The password must be between 8 and 128 characters.

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

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

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

You can change the user settings from the command line interface:

1. To set up the user's permissions to access certain databases, run the command, listing the database names in the --permissions parameter:

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

   Where:

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

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

   This command grants the user access rights to the databases listed.

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

2. To change the PostgreSQL settings for the user, pass their parameters in the command:

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

   You can change the connection limit for the user via the --conn-limit parameter.

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

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

   For more information about creating this file, see Creating clusters.

   For a complete list of editable fields in the Managed Service for PostgreSQL cluster user configuration, see the Terraform provider documentation.

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

   1. Locate the user's yandex_mdb_postgresql_user resource.

   2. Add permission sections with the appropriate DB names:

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

3. To revoke the user's permission to access a specific database, delete the permission section with the name of this DB from the configuration file.

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

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

5. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

6. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

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

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.update method and make a request, e.g., using cURL:

   Warning

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

   curl \
     --request PATCH \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>' \
     --data '{
               "updateMask": "settings",
               "settings": {
                 <settings>
               }
             }'
   

   Where:

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

     In this case, only one parameter is provided.

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

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

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

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

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/Update call and make a request, e.g., using gRPCurl:

   Warning

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

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   grpcurl \
     -format json \
     -import-path ~/cloudapi/ \
     -import-path ~/cloudapi/third_party/googleapis/ \
     -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
     -rpc-header "Authorization: Bearer $IAM_TOKEN" \
     -d '{
           "cluster_id": "<cluster_ID>",
           "user_name": "<username>",
           "update_mask": {
             "paths": [
               "settings"
             ]
           },
           "settings": {
             <settings>
           }
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.UserService.Update
   

   Where:

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

     In this case, only one parameter is provided.

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

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

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

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

2. Locate the user's yandex_mdb_postgresql_user resource.

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

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

4. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

5. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

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

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.update method and make a request, e.g., using cURL:

   Warning

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

   curl \
     --request PATCH \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --header "Content-Type: application/json" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>' \
     --data '{
               "updateMask": "deletionProtection",
               "deletionProtection": <deletion_protection:_true_or_false>
               }
             }'
   

   Where:

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

     In this case, only one parameter is provided.

   • deletionProtection: DB deletion protection.

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

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

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

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/Update call and make a request, e.g., using gRPCurl:

   Warning

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

   Format for listing settings

   "update_mask": {
       "paths": [
           "<setting_1>",
           "<setting_2>",
           ...
           "<setting_N>"
       ]
   }
   

   grpcurl \
     -format json \
     -import-path ~/cloudapi/ \
     -import-path ~/cloudapi/third_party/googleapis/ \
     -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
     -rpc-header "Authorization: Bearer $IAM_TOKEN" \
     -d '{
           "cluster_id": "<cluster_ID>",
           "user_name": "<username>",
           "update_mask": {
             "paths": [
               "deletion_protection"
             ]
           },
           "deletion_protection": <deletion_protection:_true_or_false>
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.UserService.Update
   

   Where:

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

     In this case, only one parameter is provided.

   • deletion_protection: DB deletion protection.

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

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

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To remove a user, run:

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

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

To delete a user:

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

   For more information about creating this file, see Creating clusters.

   For a complete list of available Managed Service for PostgreSQL cluster configuration fields, see the Terraform provider documentation.

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

3. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

4. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

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

   export IAM_TOKEN="<IAM_token>"
   

2. Use the User.delete method and make a request, e.g., via cURL:

   curl \
     --request DELETE \
     --header "Authorization: Bearer $IAM_TOKEN" \
     --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/users/<username>'
   

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

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

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

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

   cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
   

   Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

3. Use the UserService/Delete call and make a request, e.g., via gRPCurl:

   grpcurl \
     -format json \
     -import-path ~/cloudapi/ \
     -import-path ~/cloudapi/third_party/googleapis/ \
     -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
     -rpc-header "Authorization: Bearer $IAM_TOKEN" \
     -d '{
           "cluster_id": "<cluster_ID>",
           "user_name": "<username>"
         }' \
     mdb.api.cloud.yandex.net:443 \
     yandex.cloud.mdb.postgresql.v1.UserService.Delete
   

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

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

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

User privileges are granted via Terraform using a third-party provider, Terraform Provider for PostgreSQL.

For more information about granting privileges, see Granting a privilege to a user.

Let's say you have a cluster named mypg with user1 as the owner. To add a new user (user2) to this cluster with read-only access to db1 tables with the public schema:

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

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

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

3. Add the yandex_mdb_postgresql_user resource:

   resource "yandex_mdb_postgresql_user" "user2" {
     cluster_id      = yandex_mdb_postgresql_cluster.mypg.id
     name            = "user2"
     password        = "user2user2"
     permission {
       database_name = yandex_mdb_postgresql_database.db1.name
     }
   }
   

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

   provider "postgresql" {
     host            = yandex_mdb_postgresql_cluster.mypg.host[0].fqdn
     port            = 6432
     database        = yandex_mdb_postgresql_database.db1.name
     username        = yandex_mdb_postgresql_user.user1.name
     password        = yandex_mdb_postgresql_user.user1.password
   }
   

5. Add the postgresql_grant resource with the following attributes:

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

6. Initialize Terraform once again:

   terraform init
   

7. Make sure the settings are correct.

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

8. Confirm updating the resources.

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

To revoke the granted privilege, remove it from the privileges list and confirm updating the resources.