Get started
Yandex Managed Service for PostgreSQL

Managing database users

You can add and remove users, as well as manage their individual settings.

Getting a list of users

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Click on the name of the cluster you need and then select the Users tab.

If you don't have the Yandex.Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To get a list of cluster users, run the command:

$ yc managed-postgresql user list
     --cluster-name=<cluster name>

The cluster name can be requested with a list of clusters in the folder.

Add a user

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Click on the name of the cluster you need and select the tab Users.
  3. Click Add.
  4. Enter the database username and password (from 8 to 128 characters).
  5. Select one or more databases that the user should have access to:
    1. Select the database from the Database drop-down list.
    2. Click Add to the right of the drop-down list.
    3. Repeat the previous two steps until all the required databases are selected.
    4. To delete a database that was added by mistake, click to the right of the database name in the Permissions list.
  6. Configure the DBMS settings for the user.
  7. Click Add.

If you don't have the Yandex.Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To create a user in a cluster, run the command:

$ yc managed-postgresql user create <username>
     --cluster-name=<cluster name>
     --password=<DB user password>
     --permissions=<list of DBs the user can access>
     --conn-limit=<maximum number of connections per user>

This command configures only the main user settings.

To set the DBMS settings for the user, use the parameters described in DBMS settings.

The cluster name can be requested with a list of clusters in the folder.

Note

When created, the user only gets the CONNECT privilege for the selected databases and can't perform any operations with the databases. To provide the user with access to the database, grant them the required privileges or roles.

Change password

To change the user's password:

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Click on the name of the cluster you need and select the tab Users.
  3. Click the icon and select Change password.
  4. Set a new password and click Edit.

If you don't have the Yandex.Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To change the user's password, run the command:

$ yc managed-postgresql user update <update>
     --cluster-name=<cluster name>
     --password=<new password>

The cluster name can be requested with a list of clusters in the folder.

Change user settings

Note

The privileges and roles in PostgreSQL are not affected by these settings and are configured separately.

For information on setting up user privileges and roles, see Granting user permissions and roles.

To change the user settings:

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Click on the name of the cluster you need and select the tab Users.
  3. Click and select Settings.
  4. Set up the user's access rights to certain databases:
    1. To grant access to the required databases:
      1. Select the database from the Database drop-down list.
      2. Click Add to the right of the drop-down list.
      3. Repeat the previous two steps until all the required databases are selected.
    2. To revoke access to a specific database, remove it from the Permissions list by clicking to the right of the database name.
  5. Change the PostgreSQL settings for the user under DBMS settings.
  6. Click Save.

If you don't have the Yandex.Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

You can change the user settings from the command line interface:

  1. To set up the user's access rights to certain databases, run the following command, listing the database names in the --permissions parameter:

    $ yc managed-postgresql user update <update>
     --cluster-name=<cluster name>
     --permissions=<list of DBs the user can access>

    The cluster name can be requested with a list of folder clusters.

    This command grants the user access rights to the databases listed.

    To revoke access to a specific database, remove its name from the list and pass the updated list to the command.

  2. To change the PostgreSQL settings for the user, pass their parameters in the command:

    $ yc managed-postgresql user update <update>
     --cluster-name=<cluster name>
     --<setting 1>=<value 1>
     --<setting 2>=<value 2>
     --<setting 3>=<list of values>
     ...

    The cluster name can be requested with a list of folder clusters.

Deleting users

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Click on the name of the cluster you need and select the tab Users.
  3. Click and select Delete.

If you don't have the Yandex.Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To remove a user, run:

$ yc managed-postgresql user delete <username>
     --cluster-name <cluster name>

The cluster name can be requested with a list of clusters in the folder.

Additional settings

These settings affect the behavior of PostgreSQL when handling user queries.

You can use the following settings:

  • Conn limit: The maximum number of connections per user.

    In session pooling, this setting limits the number of user connections to each host in a PostgreSQL cluster.
    When using this type of pooling, make sure the setting value is not less than the sum of all connections that can be opened by the service backends using PostgreSQL. Note that each open server connection slows PostgreSQL down a little for the OLTP (Online Transaction Processing) workload.

    In transaction pooling, this setting limits the number of simultaneous user connections per transaction. When using this type of pooling, the user can open thousands of connections, but only N connections can run concurrently (where N is the setting value).

    Everything described below is true for session pooling:

    • When adding a user, Managed Service for PostgreSQL reserves 50 connections per host in a PostgreSQL cluster by default. The minimum number of connections per user is 10.
    • The total number of connections reserved for users must not exceed the max_connections parameter value that was specified when creating the cluster. Keep in mind that Managed Service for PostgreSQL reserves 15 connections for service users per PostgreSQL host. For example, if the cluster has the setting "max_connections": 100, you can reserve a maximum of 85 connections per cluster host for users.
    • We recommend distributing different services that use PostgreSQL among different users and setting the desired value for each user.
      This way, if problems occur in a certain service when a large number of connections start being created, it won't affect other services and they will be able to create connections to PostgreSQL.

  • Default transaction isolation: The isolation level is set for each transaction in SQL. This setting determines the default isolation level to set for new SQL transactions.

    Acceptable values:

    • read committed (by default): A query only sees the strings that were committed before it is run.

    • read uncommitted: The behavior of this isolation level in PostgreSQL is identical to read committed.

    • repeatable read: All queries in the current transaction only see the strings that were committed before the first query to select and update data that was executed in this transaction.

    • serializable: The strictest isolation level of all those mentioned.

      All queries in the current transaction only see the strings that were committed before the first query to select and update data that was executed in this transaction. If the overlap of read and write operations of parallel serializable transactions can lead to a situation that is impossible when executed sequentially, one of the transactions is rolled back with the "serialization failure" error.

    For more information about isolation levels, see the documentation for PostgreSQL.

  • Synchronous commit: This setting determines whether the DBMS will perform a transaction commit synchronously.

    Synchronous execution means that a cluster will wait for synchronous operations that guarantee different levels of data persistence and visibility in the cluster before committing the transaction to the client.

    Acceptable values:

    • off: A transaction is committed even if its data is not yet in the WAL (Write Ahead Log). There is no synchronous write, and transaction data may be lost as a result of a disk subsystem failure.
    • on (default): A transaction is committed if the WAL is written to the master disk and synchronous replica disk.
    • local: A transaction is committed if the WAL is written to the master disk.
    • remote_apply: A transaction is committed if the WAL is written to the master disk, and the synchronous replica accepted the WAL and applied the changes from it.
    • remote_write: A transaction is committed if the WAL is written to the master disk, and the synchronous replica accepted the WAL and passed it to the OS for writing to the disk. If the master disk system is lost and the OS on the replica fails, transaction data with this level of synchronization may be lost.

    For more information, see the documentation for PostgreSQL.

  • Lock timeout: The maximum waiting time (in milliseconds) for any statement to get a lock on a table, index, row, or other DB object. If the timeout expires, the statement is aborted.

    The minimum and default value is 0 (time monitoring is disabled and there is no lock timeout).

  • Log statement: A filter for SQL statements to be logged for the specified user:

    • none (default): The filter is disabled and SQL statements aren't logged.
    • ddl: SQL statements that let you change data definitions are logged (such as CREATE, ALTER, and DROP).
    • mod: SQL statements that fall under the ddl filter and statements that let you change data (such as INSERT and UPDATE) are logged.
    • all: All SQL statements are logged.

    For more information, see the documentation for PostgreSQL.

  • Log min duration statement: Configures logging of SQL statement duration.

    Logs the execution duration of all statements whose processing time is equal to or greater than the number of milliseconds specified in the setting value. For example, if this setting's value is 500, a statement that took 300 milliseconds to complete isn't logged, while the one that took 2000 milliseconds to complete is logged.

    If 0, the duration of all statements is logged.

    The default value is -1. It disables statement duration logging.

    We recommend determining what is considered slow query execution for each service and its corresponding user and setting the appropriate value to log "heavy" queries from the service. For example, for a web service, a query is considered slow if it runs for more than one second, while for the reporting service, a query that runs for more than 10 minutes is slow.

    For more information, see the documentation for PostgreSQL.

  • Temp file limit: The maximum amount of disk space (KB) that a single process can use for temporary files. A transaction that tries to exceed this limit will be canceled.

    Large queries, due to their size, are executed in the disk space rather than in RAM. Queries that are too large can overload the disk and prevent other quieres from being executed. This setting prevents queries that can greatly affect performance by limiting the size of temporary files.

    The default value is -1 (no limitations).

    For more information, see the documentation for PostgreSQL.

You can use the following settings:

  • --сonn-limit: The maximum number of connections per user.

    In session pooling, this setting limits the number of user connections to each host in a PostgreSQL cluster.
    When using this type of pooling, make sure the setting value is not less than the sum of all connections that can be opened by the service backends using PostgreSQL. Note that each open server connection slows PostgreSQL down a little for the OLTP (Online Transaction Processing) workload.

    In transaction pooling, this setting limits the number of simultaneous user connections per transaction. When using this type of pooling, the user can open thousands of connections, but only N connections can run concurrently (where N is the setting value).

    Everything described below is true for session pooling:

    • When adding a user, Managed Service for PostgreSQL reserves 50 connections per host in a PostgreSQL cluster by default. The minimum number of connections per user is 10.
    • The total number of connections reserved for users must not exceed the max_connections parameter value that was specified when creating the cluster. Keep in mind that Managed Service for PostgreSQL reserves 15 connections for service users per PostgreSQL host. For example, if the cluster has the setting "max_connections": 100, you can reserve a maximum of 85 connections per cluster host for users.
    • We recommend distributing different services that use PostgreSQL among different users and setting the desired value for each user.
      This way, if problems occur in a certain service when a large number of connections start being created, it won't affect other services and they will be able to create connections to PostgreSQL.

  • --default-transaction-isolation: The isolation level is set for each transaction in SQL. This setting determines the default isolation level to set for new SQL transactions.

    Acceptable values:

    • transaction-isolation-read-committed (by default): A query only sees the strings that were committed before it is run.

    • transaction-isolation-read-uncommitted: The behavior of this isolation level in PostgreSQL is identical to that of transaction-isolation-read-committed.

    • transaction-isolation-repeatable-read: All queries in the current transaction only see those strings that were committed before the first query to select or update data executed in this transaction.

    • transaction-isolation-serializable: The strictest isolation level of all those mentioned.

      All queries in the current transaction only see the strings that were committed before the first query to select and update data that was executed in this transaction. If the overlap of read and write operations of parallel serializable transactions can lead to a situation that is impossible when executed sequentially, one of the transactions is rolled back with the "serialization failure" error.

    For more information about isolation levels, see the documentation for PostgreSQL.

  • --login: Enables/disables user login to a PostgreSQL cluster.

    Default: true (the user is allowed to log in to the cluster).

  • --lock-timeout: The maximum waiting time (in milliseconds) for any statement to get a lock on a table, index, row, or other DB object. If the timeout expires, the statement is aborted.

    The minimum and default value is 0 (time monitoring is disabled and there is no lock timeout).

  • --log-statement: A filter for SQL statements to be logged for the specified user:

    • log-statement-none (default): The filter is disabled and SQL statements aren't logged.
    • log-statement-ddl: SQL statements that let you change data definitions are logged (such as CREATE, ALTER, and DROP).
    • log-statement-mod: SQL statements that fall under the log-statement-ddl filter and statements that let you change data (such as INSERT and UPDATE) are logged.
    • log-statement-all: All SQL statements are logged.

    For more information, see the documentation for PostgreSQL.

  • --log-min-duration-statement: Configures logging of SQL statement duration.

    Logs the execution duration of all statements whose processing time is equal to or greater than the number of milliseconds specified in the setting value. For example, if this setting's value is 500, a statement that took 300 milliseconds to complete isn't logged, while the one that took 2000 milliseconds to complete is logged.

    If 0, the duration of all statements is logged.

    The default value is -1. It disables statement duration logging.

    We recommend determining what is considered slow query execution for each service and its corresponding user and setting the appropriate value to log "heavy" queries from the service. For example, for a web service, a query is considered slow if it runs for more than one second, while for the reporting service, a query that runs for more than 10 minutes is slow.

    For more information, see the documentation for PostgreSQL.

  • --synchronous-commit: This setting determines whether the DBMS will perform a transaction commit synchronously.

    Synchronous execution means that a cluster will wait for synchronous operations that guarantee different levels of data persistence and visibility in the cluster before committing the transaction to the client.

    Acceptable values:

    • synchronous-commit-off: A transaction is committed even if its data is not yet in the WAL (Write Ahead Log). There is no synchronous write, and transaction data may be lost as a result of a disk subsystem failure.
    • synchronous-commit-on (default): A transaction is committed if the WAL is written to the master disk and synchronous replica disk.
    • synchronous-commit-local: A transaction is committed if the WAL is written to the master disk.
    • synchronous-commit-remote-apply: A transaction is committed if the WAL is written to the master disk, and the synchronous replica accepted the WAL and applied the changes from it.
    • synchronous-commit-remote-write: A transaction is committed if the WAL is written to the master disk, and the synchronous replica accepted the WAL and passed it to the OS for writing to the disk. If the master disk system is lost and the OS on the replica fails, transaction data with this level of synchronization may be lost.

    For more information, see the documentation for PostgreSQL.

  • --temp-file-limit: The maximum amount of disk space (KB) that a single process can use for temporary files. A transaction that tries to exceed this limit will be canceled.

    Large queries, due to their size, are executed in the disk space rather than in RAM. Queries that are too large can overload the disk and prevent other quieres from being executed. This setting prevents queries that can greatly affect performance by limiting the size of temporary files.

    The default value is -1 (no limitations).

    For more information, see the documentation for PostgreSQL.

В этой статье: