Skip to main content
PostgreSQL user management
Last update:

PostgreSQL user management

Users are created to access databases in a PostgreSQL cluster.

To create a database in a cluster, you must first create a user.

Only the cluster itself is available for users to work with — there is no access to the cluster nodes, as they are located on the Selectel side. By default, all users in the cluster have the same permissions.

A single PostgreSQL database can be accessed by multiple users, but there can only be one database owner. You can grant privileges to database objects to users.

Database owner⁠

When you create a PostgreSQL database, you need to select the owner user.

A PostgreSQL database owner is a user to whom ownership of objects of deleted users is transferred. After deleting a user, you will not lose access to the objects he created, but will be able to manage them through the owner. Unlike a user, a database owner has access to all database objects and can perform operations on them.

Create user⁠

  1. In the Control panel, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the cluster page → Users tab.
  4. Click Create User.
  5. Enter a name and password. Save the password — it will not be stored in the control panel.
  6. Click Save.

Change user password⁠

After the cluster is created, the user password can be changed. Remember to change the password in your application.

  1. In the Control panel, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the cluster page → Users tab.
  4. From the user's menu, select Change Password.
  5. Enter or generate a new password and save the changes.

Configure database access⁠

Grant access to a user⁠

You can give multiple users access to a single PostgreSQL database.

  1. In the Control panel, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the Database Cluster page → Databases tab → Database page.
  4. In the Have access block, click Add and select the user.

The user can only connect to the database (CONNECT) and cannot perform operations on objects. To give the user access to objects, grant the user the necessary privileges.

Change the owner of the database⁠

The owner of a PostgreSQL database is assigned when the database is created. The owner cannot be deleted (every database must have an owner), but it can be changed to another one.

  1. In the Control panel, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the Database Cluster page → Databases tab → Database page.
  4. In the Base Owner list, select a different owner.

Remove access for a user⁠

  1. In the Control panel, on the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the Database Cluster page → Databases tab → Database page.
  4. In the Have access block, delete the user.

Customize user privileges⁠

A database owner has access to operations on any database objects (schemas, tables, functions). By default, object owners have access and all rights to the object. Other users can be granted privilege (access rights) to the object.

In PostgreSQL 14 and lower databases, users are granted the CREATE privilege on the public schema by default. In PostgreSQL 15, only the CONNECT privilege is granted, the options are described in the Usage Patterns section of  the PostgreSQL documentation.

Grant privileges⁠

You can grant users privileges on database objects using the GRANT command. Privileges can be as follows: SELECT, INSERT, DELETE``, USAGE.

Example of granting read access (SELECT) to table table to user user:

GRANT SELECT ON table TO user;

Create a schema user with read-only permissions⁠

You can create a user with access to the cluster database, the default table in the schema, and all tables in the schema.

Automatically all new tables will be created with read-only access for this user.

  1. Create a user.

  2. Connect to the database.

  3. Create a schema schema and a table table:

    CREATE SCHEMA schema;
    CREATE TABLE schema.table(i int);
    INSERT INTO schema.table(i) values(1);

  4. Grant privileges to the user user:

    GRANT USAGE ON SCHEMA schema TO user;
    GRANT SELECT ON ALL TABLES IN SCHEMA schema TO user;
    ALTER DEFAULT PRIVILEGES IN SCHEMA schema GRANT SELECT ON TABLES TO user;

Revoke privileges⁠

You can revoke privileges from a user by using the REVOKE command.

Example of revoking privilege from user user to schema schema schema:

REVOKE USAGE ON SCHEMA schema FROM user;