Skip to main content

Managing PostgreSQL TimescaleDB Users

Last update:

Users are created to access databases in a PostgreSQL TimescaleDB cluster.

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

Users can only access the cluster itself; they do not have access to cluster nodes, as these are managed by Selectel. By default, all users in a cluster have the same permissions.

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

Database owner

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

The PostgreSQL TimescaleDB database owner is the user to whom ownership rights of objects created by deleted users are transferred. When a user is deleted, you will not lose access to the objects they created; instead, you will be able to 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, click Products in the top menu and select Managed Databases.
  2. Open the Active tab.
  3. Open the database cluster page → Users tab.
  4. Click Create User.
  5. Enter a username and password. Save the password, as it will not be stored in the dashboard.
  6. Click Save.

Change user password

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

  1. In the Dashboard, click Products in the top menu 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 the changes.

Configure database access

Grant access to a user

Multiple users can be granted access to a single PostgreSQL TimescaleDB database.

  1. In the Dashboard, click Products in the top menu and select Managed Databases.
  2. Open the Active tab.
  3. Open the database cluster page → Databases tab → database page.
  4. In the Has Access block, click Add and select a user.

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

Change database owner

The PostgreSQL TimescaleDB database owner is assigned at the time of its creation. The owner cannot be deleted (every database must have an owner), but you can change it to another user.

  1. In the Dashboard, click Products in the top menu 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 another owner.

Remove user access

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

Configure user privileges

By default, a user does not have access to operations on any database objects (schemas, tables, functions) unless they are the owner of that database. You can grant a user a privilege (access right) for an object.

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

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 the 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, a table in the default schema, and all tables in the 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 privileges from a user using the REVOKE command.

Example of revoking privileges from user user on schema schema:

REVOKE USAGE ON SCHEMA schema FROM user;