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 parameter. This will completely overwrite the existing roles. To add or remove roles, first, run the yc managed-postgresql user get command to get the list of current roles together with the user info.

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 information on how to create this file, see Creating a cluster.

   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. Make sure the settings are correct.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

5. Confirm updating the resources.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

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

   export IAM_TOKEN="<IAM_token>"
   

2. To check the list of current roles, call the User.Get method, e.g., 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, use the User.Update method to execute 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 set it as an environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Clone the cloudapi repository:

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

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

3. To check the list of current roles, call UserService.Get via gRPCurl or another preferred tool:

   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 as follows:

   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 with the list of clusters in the folder, and the username, with the list of users in the cluster.

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 information on how to create this file, see Creating a cluster.

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

   To learn how to get a host 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 PostgreSQL object for which the privileges are granted. Possible values: database, schema, table, sequence, function, procedure, routine, foreign_data_wrapper, foreign_server, column.
   • privileges: Array of privileges to grant. Possible values: 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 parameter with the database object type.
   • objects: Array of target objects for granting privileges. This is an optional parameter. If you omit it, the system will grant privileges on all objects of the specified type. You cannot use this parameter 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 parameter 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 parameter. The default value is false.

5. Reinitialize Terraform:

   terraform init
   

6. Make sure the settings are correct.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

7. Confirm updating the resources.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

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. Make sure the settings are correct.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

4. Confirm updating the resources.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

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 the postgresql provider to the required_providers section in the provider configuration file:

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

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

3. Add the yandex_mdb_postgresql_user resource:

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

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

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

5. Add the postgresql_grant resource with the following attributes:

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

6. Initialize Terraform once again:

   terraform init
   

7. Make sure the settings are correct.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

8. Confirm updating the resources.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.

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

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

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

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

3. Make sure the settings are correct.

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

   2. Run this command:

      terraform validate
      

      Terraform will show any errors found in your configuration files.

4. Confirm updating the resources.

   1. Run this command to view the planned changes:

      terraform plan
      

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

   2. If everything looks correct, apply the changes:

      1. Run this command:

         terraform apply
         

      2. Confirm updating the resources.

      3. Wait for the operation to complete.