Skip to main content

PostgreSQL user management

Last update:

Users are created to access databases in a PostgreSQL cluster.

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

Users only have access to the cluster itself; they cannot access cluster nodes because they are managed by Selectel. By default, all users in a cluster have identical permissions.

You can grant access to a single PostgreSQL database to multiple users, but there can be only one database owner. You can grant privileges to users for database objects.

Database owner

When creating a PostgreSQL database, you must select an owner user.

A PostgreSQL database owner is a user who inherits ownership of objects created by deleted users. After a user is deleted, you will not lose access to the objects they created; instead, you can manage them through the owner. Unlike a standard user, the database owner has access to all its objects and can perform operations on them.

Create a user

  1. In the Dashboard, from the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Users tab.
  4. Click Create user.
  5. Enter the name and password. Save the password—it will not be stored in the dashboard.
  6. Click Save.

Change user password

After a cluster is created, you can change the user password. Do not forget to update the password in your application.

  1. In the Dashboard, from the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → Users tab.
  4. In the user’s menu, select Change password.
  5. Enter or generate a new password and save your changes.

Configure database access

Grant access to a user

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

  1. In the Dashboard, from the top menu, click Products and select Managed 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 a user.

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

Change database owner

A PostgreSQL database owner is assigned during creation. You cannot delete an owner (every database must have one), but you can change the owner to a different user.

  1. In the Dashboard, from the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the database cluster page → Databases tab → database page.
  4. In the Database owner list, select a different owner.

Revoke user access

  1. In the Dashboard, from the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the database cluster page → Databases tab → database page.
  4. In the Have access block, remove the user.

Configure user privileges

The database owner has access to operations on any database objects (schemas, tables, functions). By default, object owners have full access and all rights to an object. You can grant privileges (access rights) to an object to other users.

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

Grant privileges

You can grant privileges on database objects to users using the GRANT command. Available privileges include: SELECT, INSERT, DELETE, USAGE.

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

GRANT SELECT ON table TO user;

Create a schema user with read-only permissions

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

Automatically, all new tables will be created with read-only access (read-only) 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 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 user privileges using the REVOKE command.

Example of revoking a privilege from user user for schema schema:

REVOKE USAGE ON SCHEMA schema FROM user;