Skip to main content
Managing PostgreSQL TimescaleDB users
Last update:

Managing PostgreSQL TimescaleDB users

Users are created to access the databases in the PostgreSQL TimescaleDB 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 TimescaleDB database can be accessed by multiple users, but database owner can be only one. Database objects can be accessed by users privilege.

Database owner⁠

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

A PostgreSQL TimescaleDB 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 its objects and can perform operations on them.

Create a user⁠

  1. В control panels go to Cloud platform → Databases.
  2. Open the Database Cluster page → tab Users.
  3. Click Create a user.
  4. Enter a name and password. Save the password — it will not be stored in the control panel.
  5. 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. В control panels go to Cloud platform → Databases.
  2. Open the cluster page → tab Users.
  3. On the menu. user select Change password.
  4. Enter or generate a new password and save the changes.

Configure database access⁠

Grant access to a user⁠

A single PostgreSQL TimescaleDB database can be granted access to multiple users.

  1. В control panels go to Cloud platform → Databases.
  2. Open the Database Cluster page → tab Databases → database page.
  3. In the block Have access click Add and select a user.

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

Change the owner of the database⁠

The owner of a PostgreSQL TimescaleDB 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. В control panels go to Cloud platform → Databases.
  2. Open the Database Cluster page → tab Databases → database page.
  3. On the list Base owner choose another owner.

Remove access for a user⁠

  1. В control panels go to Cloud platform → Databases.
  2. Open the Database Cluster page → tab Databases → database page.
  3. In the block Have access delete the user.

Customize user privileges⁠

By default, a user does not have access to operations on any database objects (schemas, tables, functions) unless he or she owns the database. You can grant users a privilege (access right) to an object.

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

Grant privileges⁠

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

Example of granting read access (SELECT) to the table table 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 schematic schema and the 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 a user's privileges with the command REVOKE.

Example of revoking a user's privilege user on the diagram schema:

REVOKE USAGE ON SCHEMA schema FROM user;