Assigning privileges and roles to PostgreSQL users

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

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

To assign roles to a cluster user, provide the list of required roles in the --grants argument. This will completely overwrite the existing roles. To add or remove roles from the current list, you must first retrieve the user’s current roles using the yc managed-postgresql user get command.

To assign roles, run this command:

yc managed-postgresql user update <username> \
       --grants=<role_1>,<role_2> \
       --cluster-id <cluster_ID>

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

To assign roles to a cluster user:

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

   For more information about creating this file, see this guide.

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

2. Locate the user's yandex_mdb_postgresql_user resource.

3. Add the grants attribute with the list of required roles:

   resource "yandex_mdb_postgresql_user" "<username>" {
     ...
     name   = "<username>"
     grants = [ "<role_1>","<role_2>" ]
     ...
   }
   

4. Validate your configuration.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

5. Confirm resource changes.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

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

   export IAM_TOKEN="<IAM_token>"
   

2. To check the list of current roles, call the User.Get method, for instance, via the following cURL request:

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

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

   You can find the list of current roles in the grants field of the command output.

3. To change the list of roles for a user, call the User.Update method, for instance, via the following request:

   Warning

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

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

   Where:

   • updateMask: Comma-separated list of settings you want to update.

     Here, we provide only one setting.

   • grants: New roles as a string array. Each string represents an individual role. The possible values are:

     • mdb_admin
     • mdb_monitor
     • mdb_replication
     • mdb_superuser

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

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

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

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

   Below, we assume that the repository contents reside in the ~/cloudapi/ directory.

3. To check the list of current roles, call UserService.Get method, for instance, via the following gRPCurl request:

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

   You can find the list of current roles in the grants field of the command output.

4. To change the list of roles for a user, call UserService.Update method, for instance, via the following request:

   Warning

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

   Format for listing settings

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

   grpcurl \
     -format json \
     -import-path ~/cloudapi/ \
     -import-path ~/cloudapi/third_party/googleapis/ \
     -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/user_service.proto \
     -rpc-header "Authorization: Bearer $IAM_TOKEN" \
     -d '{
           "cluster_id": "<cluster_ID>",
           "user_name": "<username>",
           "update_mask": {
             "paths": [
               "grants"
             ]
           },
           "grants": [
             "role_1", "role_2", ..., "role_N"
           ]
         }' \
     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.

   • grants: New roles as a string array. Each string represents an individual role. The possible values are:

     • mdb_admin
     • mdb_monitor
     • mdb_replication
     • mdb_superuser

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

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

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

You can grant user privileges via Terraform using a third-party tool, Terraform Provider for PostgreSQL.

To grant a privilege to a cluster user:

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

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

2. Open the Terraform configuration file describing your infrastructure.

   For more information about creating this file, see this guide.

3. Add the postgresql provider and configure it to access your target database using its owner’s credentials:

   provider "postgresql" {
     host            = <host_FQDN>
     port            = 6432
     database        = <DB_name>
     username        = <DB_owner_username> 
     password        = <user_password>
   }
   

   For instructions on retrieving a host’s FQDN, see this guide.

   The full list of settings is available in the provider documentation.

4. Add the postgresql_grant resource:

   resource "postgresql_grant" "<resource_name>" {
     database    = "<DB_name>"
     role        = "<username>"
     object_type = "<object_type>"
     privileges  = ["<list_of_privileges>"]
     schema      = "<schema>"
     objects     = ["<list_of_objects>"]
     columns     = ["<list_of_columns>"]
     with_grant_option = <permission_to_grant_privileges>
   }
   

   Where:

   • <resource_name>: Name of the Terraform resource that grants the privileges. This name must be unique within the Terraform manifest.
   • database: Name of the target database for granting privileges.
   • role: Name of the user receiving the privileges.
   • object_type: Type of the target PostgreSQL object for granting privileges. The possible values are: database, schema, table, sequence, function, procedure, routine, foreign_data_wrapper, foreign_server, column.
   • privileges: Array of privileges. The possible values are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE and USAGE. For privilege descriptions see this PostgreSQL article.
   • schema: Target schema for granting privileges. You cannot use this option with the database object type.
   • objects: Array of target objects for granting privileges. This is an optional argument. If you omit it, the system will grant privileges on all objects of the specified type. You cannot use this option with database and schema object types. For the column object type, the array of target objects cannot contain more than one item.
   • columns: Array of target columns for granting privileges. This argument is required for the column object type and cannot be used with any other type.
   • with_grant_option: If true, a user with the granted privileges can grant them to other users. This is an optional argument. The default value is false.

5. Reinitialize Terraform:

   terraform init
   

6. Validate your configuration.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

7. Confirm resource changes.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

If you previously granted a privilege using Terraform:

1. Open the Terraform configuration file describing your infrastructure.

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

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

3. Validate your configuration.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

4. Confirm resource changes.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

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

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

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

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

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

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

2. Open the Terraform configuration file describing your infrastracture.

3. Add the yandex_mdb_postgresql_user resource:

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

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

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

5. Add the postgresql_grant resource with the following attributes:

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

6. Reinitialize Terraform:

   terraform init
   

7. Validate your configuration.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

8. Confirm resource changes.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

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

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

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

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

3. Validate your configuration.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

4. Confirm resource changes.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.