> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dualentry.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Integration: Export Credentials Setup

> Register PostgreSQL as a DualEntry export destination - validate connectivity, store credentials in AWS Secrets Manager, and link an export integration.

In DualEntry, [PostgreSQL](https://www.postgresql.org/) isn't a general-ledger sync like Stripe or Brex - it's an **export destination**. You provide PostgreSQL connectivity so downstream AWS-based export jobs (for example, AWS Glue pipelines configured for your organization) can read credentials from AWS Secrets Manager and write data into your database. DualEntry validates the connection, then stores the full credential payload in AWS Secrets Manager. Passwords and secrets are never persisted in the DualEntry database - only non-sensitive metadata such as the engine, host, port, and database name.

## Prerequisites

Confirm the following before connecting:

* A reachable PostgreSQL host and port, a database name, and a username and password for a user that's allowed to connect from DualEntry's network. If your database sits behind a firewall or VPC, your platform team may need to allow DualEntry's egress IPs on the PostgreSQL security group or network ACL (the connection log records the egress IP that was attempted, so you can confirm the value with your platform team during failed attempts).
* TLS configured on the database. DualEntry defaults to `sslmode=require` and supports `disable`, `require`, `verify-ca`, and `verify-full`. Production destinations should use `require` or stricter.
* Permission in DualEntry to create export destinations.
* Awareness that one active PostgreSQL export integration is allowed per organization. The same single-active rule applies to other export types (Snowflake, Qlik, Power BI).

## How to connect

The connect flow is the same for every export destination: provide credentials, DualEntry validates them in the background, and on success the credentials land in AWS Secrets Manager and the integration shows as **Connected**.

1. In DualEntry, navigate to **Settings → Integrations → PostgreSQL**.
2. Provide a display name for the destination, the integration type (`postgresql`), and the credential fields from Prerequisites.
3. Choose **Save**. DualEntry returns immediately with the destination in **Pending** status - credential validation runs in the background.
4. Wait for the destination to move from **Pending** to **Testing** to **Validated**. While testing, DualEntry opens a connection to PostgreSQL using your credentials with a 10-second connect timeout, then closes it. A successful open/close counts as validation.
5. On success, the destination shows **Validated**, the linked integration shows **Connected**, and the credentials are written to AWS Secrets Manager. On failure, the destination shows **Failed** with an error message and the linked integration is set to **Error**.

If validation fails, fix the underlying cause (see [Troubleshoot connection errors](#troubleshoot-connection-errors)) and create a new destination - the failed row is kept for audit but does not retry automatically.

## Credential schema

DualEntry validates the credential payload against a fixed schema before attempting to connect. PostgreSQL reuses the generic database credentials schema; `engine` must be `postgres`.

| Field      | Required | Notes                                                                                                         |
| ---------- | -------- | ------------------------------------------------------------------------------------------------------------- |
| `type`     | Yes      | Must be the literal `"database"`.                                                                             |
| `engine`   | Yes      | Use `postgres` for PostgreSQL. (The same schema also covers `redshift`, `mysql`, `mariadb`, and `sqlserver`.) |
| `host`     | Yes      | Non-empty hostname or IP reachable from DualEntry.                                                            |
| `port`     | Yes      | TCP port in the range 1-65535. PostgreSQL's default is `5432`.                                                |
| `database` | Yes      | Non-empty database name.                                                                                      |
| `username` | Yes      | Non-empty user.                                                                                               |
| `password` | Yes      | Non-empty password.                                                                                           |
| `ssl_mode` | No       | One of `disable`, `require` (default), `verify-ca`, `verify-full`.                                            |

The schema doesn't collect a PostgreSQL schema name (namespace); downstream export jobs use the default search path for the given database and user, or pin the schema in their own configuration.

<Warning>
  DualEntry stores only `engine`, `host`, `port`, and `database` in its relational database. Username and password live exclusively in AWS Secrets Manager and are never returned by DualEntry's APIs after creation. Treat any rotation or reset as a full re-create flow - there's no in-place credential edit.
</Warning>

## Disconnect

Disconnecting a PostgreSQL destination archives the export-secret row, marks the integration as **Disconnected**, and schedules deletion of the AWS Secrets Manager entry (subject to AWS's secret recovery window). One blocking condition: you can't disconnect while an AWS Glue export job tied to the integration is in a **Running** state - the disconnect call returns an error with the active job ID. Wait for the job to complete (or cancel it if appropriate), then retry the disconnect.

## Troubleshoot connection errors

Validation runs in the background, so symptoms surface in the destination's status field and the linked integration's error message.

| Symptom                                                 | Likely cause                                                                                                                                                        | Resolution                                                                                                                                                                                                                 |
| ------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `400` "integration already exists" on create            | Another PostgreSQL export integration is already active for this organization.                                                                                      | List existing destinations, archive or disconnect the old one, then re-create. The single-active rule is enforced at the API level.                                                                                        |
| Destination stuck in **Failed** with a credential error | Wrong host, wrong port, wrong username or password, or the role is missing `CONNECT` on the database.                                                               | Re-verify the host and port with `psql -h <host> -p <port> -U <user> -d <database>` from a trusted machine; reset the password in PostgreSQL; grant `CONNECT` on the database to the role.                                 |
| `connection refused` or timeout                         | The PostgreSQL instance is unreachable from DualEntry's worker network, or the 10-second `connect_timeout` is too aggressive for a cold or scaled-to-zero instance. | Check service status; confirm the network path (VPC peering, security group, firewall); allow DualEntry's egress IP on the security group; warm up serverless PostgreSQL so the first connection doesn't cold-start.       |
| SSL/TLS handshake failure                               | `ssl_mode` is stricter than the server's certificate supports, or the server doesn't accept TLS.                                                                    | Start with `ssl_mode=require` and move to `verify-ca` or `verify-full` only once the server presents a trusted certificate. For a server that truly cannot do TLS (not recommended in production), set `ssl_mode=disable`. |
| `500` on create                                         | AWS Secrets Manager error or an internal failure.                                                                                                                   | Surface the error message to platform engineering - the `export_secret_provisioning_error` log key in the maintainer block below names the diagnostic.                                                                     |
| Cannot disconnect                                       | An AWS Glue export job tied to the integration is currently running.                                                                                                | Wait for the Glue job to finish (or cancel it), then retry the disconnect call.                                                                                                                                            |

## FAQ

### Does DualEntry sync data into PostgreSQL automatically as part of this flow?

No. The connect flow registers and tests credentials, then stores them so that downstream export infrastructure (typically AWS Glue jobs) can write data into your database. The table layout, job cadence, and SQL for specific datasets are owned by your deployment's export configuration, not by this integration.

### Where are passwords stored?

In AWS Secrets Manager, under a secret name that's keyed to your organization and the destination's identifier. The password never lands in DualEntry's relational database.

### Can I use this for Amazon Redshift or an RDS for PostgreSQL instance?

Yes. Amazon RDS for PostgreSQL and Aurora PostgreSQL work with `engine=postgres`. Amazon Redshift uses `engine=redshift` on the same credential schema; the validation path is identical (psycopg2 connect with `connect_timeout=10`), so the operational notes above apply directly.

### What other export destinations work the same way?

Snowflake, Qlik, and Power BI export destinations all share this connect flow, the same single-active-per-organization rule, and the same AWS Secrets Manager storage pattern. Only the credential schema differs per destination type.

## For maintainers

The details below describe the connect-flow plumbing, not user-facing features.

* API endpoints (internal admin):
  * `POST /api/integrations/exports/secrets/` to create a destination.
  * `GET /api/integrations/exports/secrets/{secret_id}/` to poll status.
  * `POST /api/integrations/exports/secrets/{secret_id}/disconnect/` to disconnect.
* Credential schema class: `ExportDatabaseCredentialsSchema` (shared across PostgreSQL, Redshift, MySQL, MariaDB, and SQL Server; dispatch is by the `engine` field). The schema's `metadata` property exposes `engine`, `host`, `port`, and `database` as the non-sensitive fields stored on the export-secret row's `destination_metadata`.
* Background validation: the `provision_export_secret` Celery task parses credentials, calls `psycopg2.connect` with `host`, `port`, `database`, `user`, `password`, `sslmode`, and `connect_timeout=10`, then opens and closes the connection. A clean open/close counts as validation. Allowed `sslmode` values: `disable`, `require`, `verify-ca`, `verify-full`.
* AWS Secrets Manager naming: secrets are stored at `dualentry/exports/org_{organization_id}/{name}_{secret_id}` (built by `ExportSecret.build_secret_qual_name`). If AWS reports the secret already exists, the task reuses the existing ARN and continues.
* Disconnect uses AWS Secrets Manager's scheduled deletion with the standard recovery window; reusing the same name within the recovery window will collide.
* Diagnostic log keys: `export_secret_provisioning_error` (creation/validation failures), `export_secret_ip_check` (the egress IP DualEntry attempted from). Confirm with your platform team whether the IP-check path applies to your deployment.
* Other export integration types (`snowflake`, `qlik`, `power_bi`) follow the same lifecycle and share the single-active-per-organization rule.

## Result

After a successful connect, your organization has a validated PostgreSQL export destination and a linked PostgreSQL integration that shows as **Connected** in the integrations list. Downstream AWS export jobs (for example, AWS Glue pipelines) can now read the PostgreSQL credentials from AWS Secrets Manager and write data into your database. For a warehouse-oriented destination with the same connect flow, see [Snowflake](./snowflake). To connect additional systems, return to [Integrations](./index). For PostgreSQL's own connection reference, see the [PostgreSQL libpq connection strings](https://www.postgresql.org/docs/current/libpq-connect.html) and [SSL support](https://www.postgresql.org/docs/current/libpq-ssl.html) docs.