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:
Warning
New users are automatically granted public
table create privileges. They cannot be revoked.
Updating the list of user roles
To assign a role to a user, use the Nebius AI interfaces: the roles assigned by the GRANT
query are canceled during the next database operation.
With Managed Service for PostgreSQL, you cannot access predefinedmdb_admin
role. For more information, see Assigning PostgreSQL roles.
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the Users tab.
- In the line with the name of the desired user, click
- Expand the DBMS settings list and select the roles you want to assign to the user in the Grants field.
- Click Save.
If you don't have the Nebius AI 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, 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 ncp managed-postgresql user get
command.
To assign roles, run the command:
ncp managed-postgresql user update <username> \
--grants=<role1,role2> \
--cluster-id <cluster ID>
You can query the cluster name with the list of clusters in the folder and the username with the list of users.
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 .
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 .
Examples
Add a user with read-only permissions
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 ON SCHEMA myschema 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;