Connecting to PostgreSQL clusters

• Is the cluster accessible from inside Yandex Cloud?

• Can I connect to the cluster from a Docker container?

• How do I obtain an SSL certificate to connect to Managed Service for PostgreSQL on Windows?

• What do I do if I get the revocation check error when using PowerShell to obtain a new SSL certificate?

• How do I install an SSL certificate to connect Power BI to Managed Service for PostgreSQL using psql?

• What do I do if I get the SSL is required error?

• Can I connect to cluster hosts via SSH or get superuser permissions on hosts?

• What is the maximum allowed number of concurrent connections to a single host in Managed Service for PostgreSQL?

• What is the allowed number of connections per user?

• Why do I get an error when trying to connect to a database?

• Why does an error occur when trying to connect to a database from Looker Studio?

• Why does a connection fail?

Can I access a cluster from within Yandex Cloud?Can I access a cluster from within Yandex Cloud?

You can connect to Managed Service for PostgreSQL cluster hosts:

• Over the internet, if you configured public access for the appropriate host. You can only connect to such hosts over an SSL connection.
• From Yandex Cloud virtual machines located in the same cloud network. If the host is not publicly accessible, there is no need to use SSL for connections from such virtual machines.
• From the container in Yandex Serverless Containers. If the host is not publicly accessible, the container must be located in the same cloud network.

For more information, see the service documentation.

Can I connect to a cluster from a Docker container?Can I connect to a cluster from a Docker container?

Yes, you can. To do this, configure the Dockerfile.

You can find an example of connection from a container in Yandex Serverless Containers in this tutorial.

How do I obtain an SSL certificate to connect to Managed Service for PostgreSQL on Windows?How do I obtain an SSL certificate to connect to Managed Service for PostgreSQL on Windows?

You can obtain an SSL certificate using PowerShell:

mkdir $HOME\.postgresql; curl.exe --output $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem

The certificate will be saved to the $HOME\.postgresql\root.crt file.

For more information about obtaining a certificate and connecting to a database, see the service documentation.

What do I do if I get the revocation check error when using PowerShell to obtain an SSL certificate?What do I do if I get the revocation check error when using PowerShell to obtain an SSL certificate?

Here is the full text of the error:

curl: (35) schannel: next InitializeSecurityContext failed: Unknown error (0x80092012)
The revocation function was unable to check revocation for the certificate

This means, when connecting to the website, the service failed to check whether or not the website’s certificate is on the list of revoked certificates.

To fix this error:

• Make sure the corporate network settings do not block the check.

• Run the command with the --ssl-no-revoke parameter.

  mkdir $HOME\.postgresql; curl.exe --ssl-no-revoke -o $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem
  

How do I install an SSL certificate to connect Power BI to Managed Service for PostgreSQL via psql?How do I install an SSL certificate to connect Power BI to Managed Service for PostgreSQL via psql?

1. Install Windows Subsystem for Linux (WSL) and run the following command in the terminal:

   mkdir /mnt/c/temp && \
   curl "https://storage.yandexcloud.net/cloud-certs/CA.pem" --output /mnt/c/temp/CA.pem && \
   openssl pkcs12 -export -out /mnt/c/temp/CA.pfx -nokeys -in /mnt/c/temp/CA.pem
   

   The certificate will be available at C:\temp\CA.pfx.

2. Place the obtained certificate in the Windows certificate store.

What must I do if I get the SSL is required error when connecting?What must I do if I get the SSL is required error when connecting?

The error occurs because you are trying to connect to the cluster with a public host. These hosts only support connections with an SSL certificate. You can:

• Obtain an SSL certificate and add it to the application you are using to connect to the cluster.
• Disable public access to hosts and connect to the cluster from a VM located in the same cloud network.

Can I connect to cluster hosts via SSH or get superuser permissions on hosts?Can I connect to cluster hosts via SSH or get superuser permissions on hosts?

You cannot connect to hosts via SSH, nor can you get superuser permissions. This is done for the sake of security and user cluster fault tolerance because direct changes inside a host can render it completely inoperable.

What is the maximum allowed number of concurrent connections to a single host in Managed Service for PostgreSQL?What is the maximum allowed number of concurrent connections to a single host in Managed Service for PostgreSQL?

The number of concurrent connections is specified at the cluster level in the Max connections setting. By default, the maximum value is set, which is calculated by the following formula:

200 × <number_of_vCPUs_per_host>

For information about how to update the PostgreSQL settings at the cluster level, see our documentation.

What is the allowed number of connections per user?What is the allowed number of connections per user?

By default, a cluster reserves 50 connections to each host per user. You can change this number in the Conn limit setting.

If the connection limit per user is reached, any attempt to establish a new connection will fail with the following error:

too many active clients for user (pool_size for user <username> reached <limit_value>)

To learn how to update the PostgreSQL settings at the user level, see our documentation.

Why do I get an error when trying to connect to a database?Why do I get an error when trying to connect to a database?

Connecting to a database may fail with an error like:

ERROR: odyssey: ce3ea075f4ffa: route for 'dbname.username' is not found

The error means that the connection parameters contain an invalid database name.

Why do I get an error when trying to connect to a database from Looker Studio?Why do I get an error when trying to connect to a database from Looker Studio?

To connect from Looker Studio, be sure to generate a client certificate file and a private key and specify them in the connection settings. For more information about how to do this, see Connecting from Looker Studio.

Why does a connection terminate with an error?Why does a connection terminate with an error?

A Managed Service for PostgreSQL cluster connection may be terminated with the following message:

FATAL: terminating connection due to administrator command

This message does not indicate an error, it means that the session/transaction duration has exceeded the Session duration timeout setting value (default value: 12 hours).