Assigning privileges and roles to 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 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 request the cluster name with the list of clusters in the folder and the username, with the list of users.

To assign roles to a cluster 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 editable fields in the Managed Service for PostgreSQL cluster user configuration, see the Terraform provider documentation.

2. Find the yandex_mdb_postgresql_user resource of the user you need.

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. 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. To check the list of current roles, use the User.get method and execute this 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/<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.

   The list of current roles is in the grants parameter in the command output.

3. To change the list of roles for a user, use the User.update method and execute this 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: List of parameters to update as a single string, separated by commas.

     In this case, only one parameter is provided.

   • grants: Array of strings with new roles. Each row corresponds to a separate role. The possible values are:

     • mdb_admin
     • mdb_monitor
     • mdb_replication
     • mdb_superuser

4. 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. To check the list of current roles, use the UserService/Get call and execute this 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.Get
   

   The list of current roles is in the grants parameter in the command output.

4. To change the list of roles for a user, use the UserService/Update call and execute this 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 parameters to update as an array of paths[] strings.

     In this case, only one parameter is provided.

   • grants: Array of strings with new roles. Each row corresponds to a separate role. The possible values are:

     • mdb_admin
     • mdb_monitor
     • mdb_replication
     • mdb_superuser

   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.

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

You can grant privileges to your users via Terraform using the third-party 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 with the infrastructure plan.

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

3. Add the postgresql provider and enable it to access the database you need as its owner:

   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.

   For a full list of settings, see 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 with privileges. It must be unique within the Terraform manifest.
   • database: Name of the database for which the privileges are granted.
   • role: Name of the user the privileges are granted to.
   • 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. You can find the descriptions of privileges in the PostgreSQL documentation.
   • schema: Schema for which you are granting privileges. You cannot specify it for database objects.
   • (Optional) objects: Array of objects for which privileges are granted. If you omit this parameter, privileges will be granted for all objects of the specified type. You cannot specify it for database or schema objects. If the object type is column, the array can contain only one value.
   • columns: Array of columns for which privileges are granted. This is a required parameter for column objects. You cannot specify it for any object type other than column.
   • (Optional) with_grant_option: If true, a user with the privileges can grant them to other users. The default value is false.

5. Initialize Terraform once again:

   terraform init
   

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

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

If you previously granted a privilege using Terraform:

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

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.

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.

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.