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 user.

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

Multiple users can be granted access to a single PostgreSQL TimescaleDB database, but database-owner can only be a single user. You can grant privileges to users on database objects.

Database owner⁠

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

The PostgreSQL TimescaleDB database owner is the user to whom ownership of remote users' objects is transferred. Once you delete a user, you will not lose access to the objects they created, but will be able to manage them through the owner. Unlike a user, the owner of a database has access to all its objects and can perform operations on them.

Create user⁠

  1. In Control Panel, go to Cloud Platform → Databases.
  2. Open the Database Cluster page → Users tab.
  3. Click Create User.
  4. Enter your 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. Don't forget to change the password in your app.

  1. In Control Panel, go to Cloud Platform → Databases.
  2. Open the cluster page → Users tab.
  3. From the menu () of the user, select Change Password.
  4. Enter or generate a new password and save the changes.

Configure access to the database⁠

Grant access to the user⁠

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

  1. In Control Panel, go to Cloud Platform → Databases.
  2. Open the Database Cluster page → Databases tab → Database page.
  3. In the Accessed box, click Add and select the user.

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

Change database owner⁠

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

  1. In Control Panel, go to Cloud Platform → Databases.
  2. Open the Database Cluster page → Databases tab → Database page.
  3. In the Base Owner list, select a different owner.

Remove access for user⁠

  1. In Control Panel, go to Cloud Platform → Databases.
  2. Open the Database Cluster page → Databases tab → Database page.
  3. In the Have access block, delete the user.

Configure user privileges⁠

By default, a user does not have access to operations on any database objects (schemas, tables, functions) unless they own that 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 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 privileges⁠

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 user.

  2. Connect to 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:

REVOKE USAGE ON SCHEMA schema FROM user;