Granting user permissions and roles
The user created with a Managed Service for PostgreSQL cluster is automatically assigned the
OWNER role for the first database in the cluster. After that, you can create other users and configure their permissions as you wish.
Atomic permissions in PostgreSQL are called privileges and permission groups are called roles. For more information about access permissions, see the PostgreSQL documentation.
To grant a privilege to a particular user, create a user via the Yandex.Cloud management console, CLI, or API and run the
GRANT command on behalf of the DB owner. For example:
GRANT SELECT ON DATABASE db1 TO user2;
To assign a user a role, use the Yandex.Cloud CLI or API: role assignment from a
GRANT request is canceled when the next database operation is performed.
Updating the list of user roles
To add a role, request the list of available roles with user information, add the desired role to the list, and send the updated list in a request. Similarly, to revoke a role, remove it from the list of roles and send the updated list in a request.
If you don't 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
To assign roles to a cluster user, pass the list of required roles in the
--grants parameter. This completely overwrites existing roles: if you want to extend or reduce the available list, first request the current roles with user information by running the
yc managed-postgresql user get command.
To assign roles, run the command:
$ yc managed-postgresql user update <username> --cluster-name <cluster name> --grants="<role1, role2>"
Creating a read-only user
To create a user who can access data in the database but not change it:
Create a user in the cluster.
On behalf of the DB owner, grant the new user
SELECTand, if necessary,
USAGEprivileges for the appropriate database or table. For example:
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO user2; GRANT USAGE ON SCHEMA myschema TO user2;