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
}

1.3 IAM database authentication (Amazon RDS / Aurora)

For PostgreSQL on Amazon RDS or Aurora, you can authenticate using IAM database authentication instead of a password. SQLTools signs a short-lived (15 min) auth token with the AWS SDK’s RDS Signer on each new pool connection so long-running sessions keep working past the token’s expiry.

In the connection form, set Use password to IAM database authentication.
Provide the AWS Region the RDS / Aurora instance is in (for example us-east-1).
Optionally provide an AWS Profile name from your shared AWS config (~/.aws/credentials or ~/.aws/config). Leave empty to use the default credential provider chain (environment variables, default profile, IMDS, SSO, etc.).
Enable SSL under pgOptions > SSL and supply the RDS CA bundle (downloadable from Using SSL/TLS to encrypt a connection to a DB instance). IAM database authentication requires SSL and will refuse to connect otherwise.
Grant IAM access to the database user: GRANT rds_iam TO <username>;.

Example stored connection:

{
  "name": "Aurora IAM",
  "driver": "PostgreSQL",
  "server": "my-cluster.cluster-xyz.us-east-1.rds.amazonaws.com",
  "port": 5432,
  "database": "app",
  "username": "dbuser",
  "useAwsIamAuth": true,
  "awsIamOptions": {
    "region": "us-east-1",
    "profile": "dev"
  },
  "pgOptions": {
    "ssl": {
      "ca": "file:///path/to/rds-global-bundle.pem"
    }
  }
}