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 key | Default Value | Description |
---|---|---|
statement_timeout | Number of milliseconds before a statement in query will time out. Default is no timeout | |
query_timeout | Number of milliseconds before a query call will timeout. Default is no timeout | |
connectionTimeoutMillis | Number of milliseconds to wait before timing out when connecting a new client. By default this is 0 which means no timeout | |
idleTimeoutMillis | 10000 | Number 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 |
max | 10 | Maximum number of clients the pool should contain. By default this is set to 10. |
ssl | Passed 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 key | Default Value | Description |
---|---|---|
rejectUnauthorized | true | If 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 |
requestCert | false | If true the server will request a certificate from clients that connect and attempt to verify that certificate. Default: false |
ca | File 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 | |
key | File path. Private keys in PEM format. PEM allows the option of private keys being encrypted. Encrypted keys will be decrypted with options.passphrase | |
cert | File 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 | |
pfx | File 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 | |
passphrase | Shared 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
}