[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for PostgreSQL](../index.md) > [Step-by-step guides](index.md) > PostgreSQL users > Assigning privileges and roles

# Assigning privileges and roles to PostgreSQL users

PostgreSQL manages database access permissions using _roles_. Roles can own database objects and have _privileges_.

In PostgreSQL, a user is a role that can log in to the database. The user created for a new Managed Service for PostgreSQL cluster is the owner of its first database. 

You can [create more users](cluster-users.md#adduser) and configure their permissions as needed:

- [Editing user’s roles](#grant-role).
- [Granting a privilege to a user](#grant-privilege).
- [Revoking a privilege from a user](#revoke-privilege).

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

For more information about [creating users](https://www.postgresql.org/docs/current/sql-createuser.html) and [setting up access permissions](https://www.postgresql.org/docs/current/user-manag.html), see the PostgreSQL guide.

## Editing user’s roles {#grant-role}

To assign a role to a user, use the Yandex Cloud interfaces: the roles assigned by the `GRANT` request are canceled during the next database operation.

{% note info %}

PostgreSQL supports nested roles. A user, i.e., a role allowed to authenticate in a database, may be a member of one or multiple other roles and inherit their permissions. [Learn more about role membership](https://www.postgresql.org/docs/current/sql-grant.html).

{% endnote %}

Managed Service for PostgreSQL does not allow access to [predefined roles](https://www.postgresql.org/docs/current/predefined-roles.html), including the superuser role. You can only assign the following managed service roles to a user:

* `mdb_admin`
* `mdb_monitor`
* `mdb_replication`
* `mdb_superuser`

{% note info %}

You cannot create custom roles in Managed Service for PostgreSQL. A user’s permissions are determined by the combination of granted privileges.

{% endnote %}

{% 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.
  1. Find the user you want to update in the list, click ![image](../../_assets/console-icons/ellipsis.svg) in their row, and select **Configure**.
  1. Expand the **DBMS settings** list and select the roles you want to assign to the user in the **Grants** field.
  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 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:

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

- Terraform {#tf}

  To assign roles to a cluster user:
  
    1. Open the current Terraform configuration file with the infrastructure plan.
  
        For more on how to create this file, see [Creating a cluster](cluster-create.md).

        For a complete list of configurable fields of Managed Service for PostgreSQL cluster user accounts, refer to the [Terraform provider guides](../../terraform/resources/mdb_postgresql_user.md).

    1. Locate the `yandex_mdb_postgresql_user` resource for the user in question.
    1. Add the `grants` attribute with the list of required roles:
  
        ```hcl
        resource "yandex_mdb_postgresql_user" "<username>" {
          ...
          name   = "<username>"
          grants = [ "<role_1>","<role_2>" ]
          ...
        }
        ```

    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. To check the list of current roles, call the [User.Get](../api-ref/User/get.md) method, for instance, 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/<username>'
     ```

     You can get the cluster ID from the [folder’s cluster list](cluster-list.md#list-clusters), and the username from the [cluster’s user list](cluster-users.md#list-users).

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

  1. To change the list of roles for a user, call the [User.Update](../api-ref/User/update.md) method, for instance, via the following 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": "grants",
                 "grants": [
                   "role_1", "role_2", ..., "role_N"
                 ]
               }'
     ```

     Where:

     * `updateMask`: Comma-separated string of settings 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`

  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. To check the list of current roles, call [UserService.Get](../api-ref/grpc/User/get.md) method, for instance, 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.Get
     ```

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

  1. To change the list of roles for a user, call [UserService.Update](../api-ref/grpc/User/update.md) method, for instance, via the following 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": [
                 "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](cluster-list.md#list-clusters), and the username from the [list of cluster users](cluster-users.md#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 %}

## Granting a privilege to a user {#grant-privilege}

{% list tabs group=instructions %}

- SQL

    1. [Connect](connect/index.md) to the database using the owner's account.
    1. Run the `GRANT` command. For full command syntax, see [this PostgreSQL guide](https://www.postgresql.org/docs/current/sql-grant.html).

- 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 a third-party tool, [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 grant a privilege to a cluster user:
  
    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.
  
        For more on how to create this file, see [Creating a cluster](cluster-create.md).

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

        ```hcl
        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](connect/fqdn.md).

        For a full list of settings, see [this provider guide](https://registry.terraform.io/providers/cyrilgdn/postgresql/latest/docs).

    1. Add the `postgresql_grant` resource:

        ```hcl
        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. The possible values are `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER`, `CREATE`, `CONNECT`, `TEMPORARY`, `EXECUTE`, and `USAGE`. For privilege descriptions see [this PostgreSQL article](https://www.postgresql.org/docs/current/ddl-priv.html).
        * `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`.

    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 resource changes.
  
        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 a privilege from a user {#revoke-privilege}

{% list tabs group=instructions %}

- SQL

    1. [Connect](connect/index.md) to the database using the owner's account.
    1. Run the `REVOKE` command. For full command syntax, see [this PostgreSQL guide](https://www.postgresql.org/docs/current/sql-revoke.html).

- Terraform {#tf}

    If you previously granted a privilege using Terraform:

    1. Open the Terraform configuration file describing your infrastructure.
    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.

    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 resource changes.
  
        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 %}

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

        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. Validate your configuration.

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