# PostgreSQL Connection Strings

> The PostgreSQL connection strings API Maker takes - the parts it reads, TLS with sslmode and your own certificate authority, examples, how to add one to an instance, and the common errors.

Source: https://docs.apimaker.dev/v4/docs/Database-connection-string/postgres-connection-strings.html

## The format

```text
postgresql://user:password@host:port/database?sslmode=require
```

| Part | What it is |
|---|---|
| `postgresql://` | `postgres://` works too. |
| `user:password@` | The user and its password. Write the password as it is, even with `@` or `:` in it : API Maker encodes it before it connects. Do not encode it yourself. |
| `host:port` | `5432` when no port is given. |
| `/database` | The database a connection starts in. |
| `?options` | The TLS options below. API Maker reads no other option. |

## TLS

A connection string which says nothing of TLS connects without it.

| Option | Effect |
|---|---|
| `sslmode=require`, `verify-ca` or `verify-full`, or `ssl=true` | TLS, and the certificate of the server is checked : its authority and its name. |
| `sslrootcert=/path/ca.pem` | Trust this authority in place of the public ones : the file a managed service gives you, or your own. |
| `sslcert=/path/client.pem&sslkey=/path/client.key` | A certificate of the client, when the server asks for one. |
| `sslmode=no-verify` | TLS without the check. For tests only. |
| `sslmode=disable` | No TLS. |

`require` checks the certificate, as the `pg` package of Node.js does. Add `uselibpqcompat=true` for the meaning of libpq, where `require` encrypts without checking.

## Examples

```text
# 1. PostgreSQL on the same server
postgresql://postgres:password@127.0.0.1:5432/postgres

# 2. A database of an application
postgresql://app:password@10.0.0.5:5432/inventory

# 3. A managed service, its certificate checked
postgresql://app:password@db.example.com:5432/inventory?sslmode=require

# 4. Your own certificate authority
postgresql://app:password@db.internal:5432/inventory?sslmode=verify-full&sslrootcert=/root/certs/ca.pem

# 5. A password with special characters, written as it is
postgresql://app:p@ss:w0rd!@10.0.0.5:5432/inventory
```

## Add it to API Maker

An instance never holds the connection string itself : it names a key of your [default secret](https://docs.apimaker.dev/v4/docs/secrets/secrets.html), where the string stays encrypted, out of Git.

1. **Put the string in the secret** **Security → Secret Management** → `Default`. Add it under `common.connectionString`, with a name of your choice, and save.
2. **Create the instance** **APIs & Logic → Instance API (DB API)** → add an instance : pick the database type, give it a name, and pick the key of the secret under **Connection**.
3. **Test it** **Test Instance** connects with the string. An instance which can not reach its database is not saved, and the form says why.

**Connection String Sandbox** is optional : a second key, for code which runs in a sandbox and reaches the database by another address than API Maker does.

## Common errors

| What you see | What to check |
|---|---|
| `password authentication failed for user "app"` | The user and the password. |
| `no pg_hba.conf entry for host "10.0.0.2"` | Add the address of API Maker to `pg_hba.conf`, then reload PostgreSQL. |
| A timeout, or the connection refused | `listen_addresses` in `postgresql.conf`, the firewall, the port. |
| `self-signed certificate` or `unable to verify the first certificate` | Give the authority with `sslrootcert`. `sslmode=no-verify` skips the check, for tests only. |

## Related

- [Secret Management](https://docs.apimaker.dev/v4/docs/secrets/secrets.html) · [Execute plain query](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) · [Connection strings in the PostgreSQL manual](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING)