Assigning privileges and roles to PostgreSQL users
Atomic permissions in PostgreSQL are called privileges and permission groups are called roles. For more information about access permissions, see the PostgreSQL documentation
The user created with a Managed Service for PostgreSQL cluster is the owner of the first database in the cluster. You can create other users and configure their permissions as you wish:
- Updating the list of roles for a user.
- Granting a privilege to a user.
- Revoking a privilege from a user.
Warning
Granting the public
table create privilege to new users depends on the PostgreSQL version:
- 14 and lower: The privilege is granted automatically and cannot be revoked.
- 15 and higher: The privilege is manually granted to the user.
Updating the list of roles for a user
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.
With Managed Service for PostgreSQL, you cannot access predefined
mdb_admin
mdb_monitor
mdb_replication
Users with the mdb_admin
role have the highest privileges for working with clusters. For more information, see Assigning roles.
Note
You cannot create custom roles in Managed Service for PostgreSQL. User permissions depend on a set of privileges the user is granted.
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the cluster name and open the Users tab.
- In the user name row, click
and select Configure. - Expand the DBMS settings list and select the roles you want to assign to the user in the Grants field.
- Click Save.
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=<role1,role2> \
--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:
-
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
. -
Find the
yandex_mdb_postgresql_user
resource of the user you need. -
Add the
grants
attribute with the list of required roles:resource "yandex_mdb_postgresql_user" "<username>" { ... name = "<username>" grants = [ "<role1>","<role2>" ] ... }
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
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. -
To change the list of roles for a user, use the User.update method and execute this request:
Warning
This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the
updateMask
parameter (one line separated by commas).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
-
-
View the server response to make sure the request was successful.
-
Get an IAM token for API authentication and place it in the environment variable:
export IAM_TOKEN="<IAM_token>"
-
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. -
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. -
To change the list of roles for a user, use the UserService/Update call and execute this request:
Warning
This API method will assign default values to all object parameters not explicitly set in the request. To avoid this, list the settings you want to change in the
update_mask
parameter as an array ofpaths[]
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 ofpaths[]
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.
-
-
View the server response to make sure the request was successful.
Granting a privilege to a user
- Connect to the database under the database owner's account.
- Run the
GRANT
command. For a detailed description of the command syntax, see the PostgreSQL documentation .
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
Note
Terraform Provider for PostgreSQL is not part of Managed Service for PostgreSQL. It is not covered by Yandex Cloud support and its usage is not governed by the Yandex Managed Service for PostgreSQL Terms of Use
To grant a privilege to a cluster user:
-
Add the
postgresql
provider to therequired_providers
section in the provider configuration file:terraform { required_providers { ... postgresql = { source = "cyrilgdn/postgresql" } ... } }
-
Open the Terraform configuration file with the infrastructure plan.
For more information about creating this file, see Creating clusters.
-
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
. -
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
, andUSAGE
. You can find the descriptions of privileges in the PostgreSQL documentation .schema
: Schema for which you are granting privileges. You cannot specify it fordatabase
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 fordatabase
orschema
objects. If the object type iscolumn
, the array can contain only one value. columns
: Array of columns for which privileges are granted. This is a required parameter forcolumn
objects. You cannot specify it for any object type other thancolumn
.- (Optional)
with_grant_option
: Iftrue
, a user with the privileges can grant them to other users. The default value isfalse
.
-
Initialize Terraform once again:
terraform init
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
Revoking a privilege from a user
- Connect to the database under the database owner's account.
- Run the
REVOKE
command. For a detailed description of the command syntax, see the PostgreSQL documentation .
If you previously granted a privilege using Terraform:
-
Open the Terraform configuration file with the infrastructure plan.
-
In the
postgresql_grant
section, remove the privilege you want to revoke from theprivileges
parameter.To revoke all privileges, leave the
privileges
array empty or completely remove thepostgresql_grant
resource. -
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
Examples
Add a user with read-only permissions
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.
To add a new user (user2
) to an existing cluster with read-only access to the db1
database:
-
Create a user named
user2
. Select the databases that the user should have access to. -
Connect to the
db1
database under the account of the database owner. -
To only grant access rights to the
Products
table, in the defaultpublic
schema, run the command:GRANT SELECT ON public.Products TO user2;
-
To grant access to all the
myschema
schema tables, run the command:GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO user2; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA myschema to user2;
-
(Optional) To change the default privileges, run this command:
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;
To revoke the granted privileges, run the commands:
REVOKE SELECT ON public.Products FROM user2;
REVOKE SELECT ON ALL TABLES IN SCHEMA myschema FROM user2;
REVOKE USAGE ON SCHEMA myschema FROM user2;
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
Note
Terraform Provider for PostgreSQL is not part of Managed Service for PostgreSQL. It is not covered by Yandex Cloud support and its usage is not governed by the Yandex Managed Service for PostgreSQL Terms of Use
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:
-
Add a
postgresql
provider to therequired_providers
section in the provider configuration file:terraform { required_providers { ... postgresql = { source = "cyrilgdn/postgresql" } ... } }
-
Open the Terraform configuration file with the infrastructure plan.
-
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 } }
-
Add the
postgresql
provider and configure its access permissions todb1
: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 }
-
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" }
-
Initialize Terraform once again:
terraform init
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
To revoke the granted privilege, remove it from the privileges
list and confirm updating the resources.