Managing PostgreSQL Users

Users are created in the cluster to access the databases.

After creating a cluster, you must first create a user, and then create a database. You must select an owner user when creating a database.

A database owner is the user who receives ownership of objects belonging to remote users. When you delete a user, you do not lose access to the objects created by the user, but can manage them through the user-owner. Unlike a user, a database owner has access to all objects in the database and can perform operations on them.

Several users can have access to a single database, but only one can own it.

The cluster nodes are on the Selectel side, so users cannot access them, only the cluster itself. By default, all users of the cluster have the same rights.

Creating a User

To create a user, from the Control panel:

  1. Go to the Cloud platform ⟶ Managed Databases section.
  2. Go to the desired database cluster page and open the Users tab.
  3. Click Create.
  4. Enter the user’s name and set a password. Please note that you need to save your password, as it will not be stored in the Control panel.
  5. Click Create.

After creating databases and users, you can provide users access to specific databases.

Accessing Databases

Granting Access to the Users

You can grant several users access to a database.

To grant access, from the Control panel:

  1. Go to the Cloud platform ⟶ Managed Databases section.
  2. Go to the desired database cluster page and open the Databases tab.
  3. Open the database page, and click the plus sign in the Have access block.
  4. In the list, select a user and confirm the changes.

After creation, the user can only connect to the database (CONNECT) and cannot perform any operations with the database objects. You must grant the user the necessary permissions for them to access objects.

Changing the Database Owner

The database owner is assigned when the database is created. The owner cannot be deleted (every database must have an owner), but you can change it to another.

To change the database owner, from the Control panel:

  1. Go to the Cloud platform ⟶ Managed Databases section.
  2. Go to the desired database cluster page and open the Databases tab.
  3. Open the database page, select another owner from the Database owner list and confirm the changes.

Denying Access for a User

To deny access to the database, from the Control panel:

  1. Go to the Cloud platform ⟶ Managed Databases section.
  2. Go to the desired database cluster page and open the Databases tab.
  3. Open the database page, delete the desired user in the Have access block, and confirm the changes.

Granting and Revoking Privileges

By default, users do not have access to operations on any database objects (schemas, tables, functions, etc.) that they do not own. To do this, users must be granted a privilege (the right to access) to the object.

By default, the object owners have access and all rights to the object.

Granting Privileges to a User

Privileges are granted using the GRANT command, which assigns rights (privileges) to users on database objects. The privileges can be as follows: SELECT, INSERT, DELETE, USAGE, etc.

An example of granting read access (SELECT) to the user:

GRANT SELECT ON table TO user;

For a detailed description of the command syntax refer to the PostgreSQL documentation.

An Example of Creating a Schema User with read-only Privileges

Let’s create a user with the following privileges: access to the cluster database, to the default table in the schema, and to all tables in the schema. Automatically, all new tables will be created with read-only access for this user.

To create a read-only schema user, create a user and connect to the database.

Then create the schema and the table:

CREATE SCHEMA myschema;
CREATE TABLE myschema.mytable(i int);
INSERT INTO myschema.mytable(i) values(1);

Grant privileges:

GRANT USAGE ON SCHEMA myschema TO myuser;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO myuser;

Revoking Privileges from a User

You can revoke privileges with the REVOKE command. For a detailed description of the command syntax refer to the PostgreSQL documentation.

An example of revoking a privilege from the user to a schema:

REVOKE USAGE ON SCHEMA schema FROM user;