Skip to main content

Managing PostgreSQL TimescaleDB Users

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

To create a database in the cluster, first create a user.

Users can only work with the cluster itself — they do not have access to cluster nodes because the nodes are on the Servercore side. By default, all users in the cluster have the same privileges.

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

Database owner

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

The PostgreSQL TimescaleDB database owner is the user who receives ownership of objects that belonged to deleted users. After you delete a user, you do not lose access to the objects they created — you can manage them through the owner. Unlike a regular user, the database owner has access to all of its objects and can perform operations on them.

Create a user

  1. In the control panel, in the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → the 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 a user password

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

  1. In the control panel, in the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the cluster page → the Users tab.
  4. From the menu of the user, select Change password.
  5. Enter or generate a new password and save the changes.

Configure database access

Grant access to a user

You can grant access to one PostgreSQL TimescaleDB database to multiple users.

  1. In the control panel, in the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the database cluster page → the Databases tab → the 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 operations on objects. To give the user access to objects, grant the required privileges.

Change the database owner

The PostgreSQL TimescaleDB database owner is assigned when the database is created. You cannot delete the owner (every database must have an owner), but you can change the owner to another user.

  1. In the control panel, in the top menu, click Products and select Managed Databases.
  2. Open the Active tab.
  3. Open the database cluster page → the Databases tab → the database page.
  4. In the Database owner list, select another owner.

Revoke access for a user

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

Configure user privileges

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

By default, object owners have access and all privileges on the object.

Grant privileges

You can grant privileges on database objects to users with the GRANT command. Privileges can be: SELECT, INSERT, DELETE, USAGE.

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

GRANT SELECT ON table TO user;

Create a schema user with read-only access

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

All new tables will automatically be created with read-only access for this user.

  1. Create a user.

  2. Connect to the database.

  3. Create the schema 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 privileges from a user with the REVOKE command.

Example of revoking a privilege from the user user on the schema schema:

REVOKE USAGE ON SCHEMA schema FROM user;