PostgreSQL

PostgreSQL Start Guide

1. Connections

Connection example:

{
  "name": "PGSQL",
  "server": "localhost",
  "driver": "PostgreSQL",
  "port": 5433,
  "database": "test_db",
  "username": "root",
  "askForPassword": false,
  "password": "root",
  "connectionTimeout": 15
}

1.1 Specific Options

PostgreSQL driver specific options can be passed using pgOptions settings.

{
  "name": "PGSQL",
  "server": "localhost",
  "driver": "PostgreSQL",
  "port": 5433,
  "database": "test_db",
  "username": "root",
  "askForPassword": false,
  "password": "root",
  "connectionTimeout": 15,
  "pgOptions": {
    ... // options
  }
}
Option keyDefault ValueDescription
statement_timeoutNumber of milliseconds before a statement in query will time out. Default is no timeout
query_timeoutNumber of milliseconds before a query call will timeout. Default is no timeout
connectionTimeoutMillisNumber of milliseconds to wait before timing out when connecting a new client. By default this is 0 which means no timeout
idleTimeoutMillis10000Number of milliseconds a client must sit idle in the pool and not be checked out before it is disconnected from the backend and discarded. Default is 10000 (10 seconds) - set to 0 to disable auto-disconnection of idle clients
max10Maximum number of clients the pool should contain. By default this is set to 10.
sslPassed directly to node.TLSSocket. Check more here: https://nodejs.org/api/tls.html#tls_new_tls_tlssocket_socket_options

You can use any options defined in https://node-postgres.com/features/connecting#programmatic in pgOptions.

They will be passed to the pool constructor directly. See https://github.com/mtxr/vscode-sqltools/blob/master/packages/core/driver/pgsql/index.ts .

SSL Options

Option keyDefault ValueDescription
rejectUnauthorizedtrueIf true the server will reject any connection which is not authorized with the list of supplied CAs. This option only has an effect if requestCert is true. Default: true
requestCertfalseIf true the server will request a certificate from clients that connect and attempt to verify that certificate. Default: false
caFile path. Optionally override the trusted CA certificates. Default is to trust the well-known CAs curated by Mozilla. Mozilla’s CAs are completely replaced when CAs are explicitly specified using this option
keyFile path. Private keys in PEM format. PEM allows the option of private keys being encrypted. Encrypted keys will be decrypted with options.passphrase
certFile path. Cert chains in PEM format. One cert chain should be provided per private key. Each cert chain should consist of the PEM formatted certificate for a provided private key, followed by the PEM formatted intermediate certificates (if any), in order, and not including the root CA (the root CA must be pre-known to the peer, see ca
pfxFile path. PFX or PKCS12 encoded private key and certificate chain. pfx is an alternative to providing key and cert individually. PFX is usually encrypted, if it is, passphrase will be used to decrypt it
passphraseShared passphrase used for a single private key and/or a PFX

Example: Azure Postgres

This example enables SSL for connecting to an Azure Postgres instance.

{
    "name": "PGSQL",
    "server": "HOSTNAME.postgres.database.azure.com",
    "driver": "PostgreSQL",
    "port": 5432,
    "database": "dbnamehere",
    "username": "username@hostname",
    "askForPassword": false,
    "password": "password",
    "connectionTimeout": 15,
    "pgOptions": {
        "ssl": "true"
    }
}

You can get more information about SSL connections here: https://node-postgres.com/features/ssl. Also try to edit your connection using the connection assitant, it has a lot of descriptions for the properties you may need.

1.2 Alternative Connection Strings

ConnectionStrings or connectionURIs are supported as defined in node-postgres library. See Connection URI for more information.

Using a connectionURI for the same configurations from the previous example:

{
  "name": "PGSQL",
  "server": "localhost",
  "driver": "PostgreSQL",
  "connectString": "postgresql://root:root@localhost:5433/test_db",
  "askForPassword": false,
  "connectionTimeout": 15
}