Skip to main content

Managing PostgreSQL for 1C users

Users are created to access databases in the PostgreSQL for 1C cluster.

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

Users can only access the cluster itself—there is no access to cluster nodes since they are located on the Selectel side. By default, all users in the cluster have the same permissions.

Access to a single PostgreSQL for 1C database can be granted to multiple users, but there can be only one database owner. On database objects, users can be granted privileges.

Database owner

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

The PostgreSQL for 1C database owner is a user who inherits ownership of objects from deleted users. After deleting a user, you will not lose access to the objects they created; instead, you can manage them via the owner. Unlike a regular user, the database owner has access to all its objects and can perform operations on them.

Create a user

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

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

  1. In the control panel, in the top menu, click Products and select Cloud 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

Access to a single PostgreSQL for 1C database can be granted to multiple users.

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

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

Change database owner

The PostgreSQL for 1C database owner is assigned when it is created. The owner cannot be deleted (every database must have an owner), but you can change it to another one.

  1. In the control panel, in the top menu, click Products and select Cloud 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 control panel, in the top menu, click Products and select Cloud Databases.
  2. Open the Active tab.
  3. Open the database cluster page → Databases tab → database page.
  4. In the Have access section, 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 are the owner of this database. You can grant users a privilege (access right) to an object.

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

Grant privileges

You can grant privileges on database objects to users 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, to a table in the default schema, and to all schema tables.

Automatically, all new tables will be created with read-only access for this user.

  1. Create a user.

  2. Подключитесь к базе данных.

  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;