PostgreSQL
How to connect PostgreSQL to Count
Last updated
How to connect PostgreSQL to Count
Last updated
To connect your database, you'll need to enter:
Host
Port
Database name
Username
Password
You'll also need to ensure that your firewall accepts requests from the following IP addresses:
Connections between Count and your database are encrypted by SSL (TLS). Your database must be configured to use SSL in order for Count to connect.
For additional security, you may optionally provide the certificate authority, public client certificate and private client key (in PEM format).
Once you hit Connect you will see your connection details including a full list of tables accessible with this connection.
To connect your Heroku-hosted PostgreSQL database to Count, there a few additional things to consider.
Some Heroku systems do not support verifiable certificates, making Count's preferred method of connection impossible. (see the Why don't I need to upload the CA when connecting to other products? section below for more details).
To enable secure Heroku connections in Count there are two options:
Heroku users could use mutual TLS for a more secure connection, but this is a paid service. (see more here)
Enable the 'Trust Server Certificate' button upon connecting to Count (see the What does "Trust server certificate" mean? section below for more details)
Count now supports SSH connections to your PostgreSQL database, via a bastion host.
This feature is in beta. Please contact Count support to request access to this feature.
To connect via SSH you must have
a bastion host in your VPC which can connect to your private database
whitelisted Count's server IPs so that we can securely connect to your bastion host VM
details for how to connect to your bastion host, including the host, port, user and a private SSH key.
Details for how to set up a bastion host can be found in your cloud service provider's documentation. On AWS, the documentation for connecting to an RDS instance via a bastion host can be found here.
On Count, you can additionally secure your traffic by specifying an SSH host and the algorithm used to generate it. Our servers will check this value against that provided by your VM on connection, helping to verify its identity. This information can be obtained when generating the SSH key pair. On AWS, this information is available in your EC2 instance System logs; you should see a entry in the log with a list of SSH host key keys
Select an algorithm and verification key pair and enter into the details in the above form.
The PostgreSQL connection in Count supports a number of advanced settings:
Max concurrent connections - Set the maximum number of simultaneous connections between Count and your database. If your database has a lower limit than the value set here, then the lower limit will take precedence.
Query timeout - queries executed from Count will be automatically aborted after this length of time.
If you're having trouble connecting, check the error message returned at the top of the Connection page against the table below for a possible fix.
Error snippet
Possible resolution
GETADDRINFO ENOTFOUND
Is the server running on
Connection time out
Connection refused
Could not connect to server
Failed to establish a connection
ECONNREFUSED
Check your Host and Port settings and that your database server is open to accept connections from the whitelisted Count IP addresses
Password authentication failed for user
Password authentication failed; please check Username, Password, and Database name settings
No PG_HBA.CONF entry for host
SETUSERID: USER
Check the Username with these credentials is authorised to connect to the database from the whitelisted Count IP addresses
Server does not support SSL connections
Self signed certificate
Check that your database server supports SSL connections. If your database server has self-signed certificates, does not support verifiable certificates, or your host is an IP address you may need to add the Certificate Authority or "trust" the incoming server certificate (see SSL certificate errors section below)
If you see a connection error complaining about "self signed certificates", it's likely that there's an issue verifying the SSL certificates issued by your database server against the host parameter of your sever details. This can happen for several reasons
You are using manually created self signed certificates, in which case it's not possible to verify the identity of your server
The database server does support verifiable certificates (e.g. some Heroku systems)
The certificates are issued by the provider of your managed database server (e.g. Digital Ocean, GCP) and the host parameter is an IP address
If any one of these is the case, the connection issue can usually be resolved either by
downloading your database server Certificate Authority (CA) file, usually given in PEM format, and uploading to the "Advanced" section of the connection setup
activating the "Trust server certificate" in the "Advanced" section of the connection setup
You may be wondering why you're able to connect automatically with clients such as psql or other web-based BI tools, but Count asks to upload the CA certificate.
In the SSL Support section of the PostgreSQL documentation, it clarifies that there are 6 modes of SSL support.
By default, Count communicates with your database server using the most secure method of communication, "verify-full", which provides full protection against eavesdropping and MITM attacks. This method requires full verification of the CA , which may not be possible if the server does not issue a root certificate, or if the domain of the certificate and the database server host address do not match (as is usually the case where the host parameter is an IP address). In that case, uploading the CA file to Count allows the communication to happen safely with the mode "verify-ca", using your uploaded certificate to ensure maximal protection against eavesdropping and MITM attacks.
If you are unable to obtain a copy of your database CA (e.g. some Heroku systems), the only way to initiate encrypted communication with your database is to trust that the certificate sent by the server is correct (SSL mode "require"). This ensures full protection against eavesdropping, but not against MITM attacks. Count will only use this mode of communication if you activate the "Trust server certificate" in the "Advanced" section of the connection setup.
You should consult your database administrator to confirm that this setting is appropriate for your system.
If another product is able to connect automatically without the above considerations of server certificates, it may be worth checking what SSL mode they are using for communication, and whether this is sufficient for your needs.
If you are still having trouble connecting, reach out to us to schedule a help session.
