Connect SPSync to PostgreSQL

This guide covers PostgreSQL you run yourself and the cloud-provided databases built on it: Azure Database for PostgreSQL, Supabase, Neon, Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, and Heroku Postgres. SPSync uses one destination kind, postgres, for all of them. Only the host, the firewall and the TLS settings differ.

Before you start

A PostgreSQL server you can reach. SPSync uses the standard Postgres driver, which needs nothing extra on the machine that runs SPSync.
Superuser access, or an admin role on the cloud service, to create a role and a database.

Create the database and a least-privilege user

The script is from the PostgreSQL documentation and has not been run here against a test server. Run it as a superuser or admin role.

CREATE ROLE spsync_loader LOGIN PASSWORD 'use-a-long-random-password';
CREATE DATABASE spsync OWNER spsync_loader;

spsync_loader owns the database, so it can create the schema SPSync loads into (the dataset_name, for example sync) and the staging schema SPSync uses while it merges a round. If you cannot hand over ownership of a whole database, create the schemas yourself and grant only those. This block is from vendor docs, not run here:

CREATE SCHEMA sync AUTHORIZATION spsync_loader;
CREATE SCHEMA sync_staging AUTHORIZATION spsync_loader;
GRANT CONNECT ON DATABASE spsync TO spsync_loader;

Reporting views are optional. They need CREATE on the schema, which owning it already gives the role. SPSync drops and recreates the views each round.

Allow SPSync to reach it

Postgres listens on TCP 5432 unless the service says otherwise. On the desktop app and a self-hosted server the connection starts from that machine, so allow that machine’s address. For the Azure managed application, allow the address it connects out from, or put the database on the same virtual network, instead of opening it to the internet.

Self-hosted: allow the SPSync address in pg_hba.conf with hostssl, and in the host firewall.
Azure Database for PostgreSQL (Flexible Server): in the server’s Networking page, add a firewall rule for the address, or use a private endpoint or virtual network access and run SPSync inside it.
Amazon RDS and Aurora: add an inbound rule for port 5432 to the instance’s security group, with the source written as /32. For Aurora, use the cluster (writer) endpoint.
Google Cloud SQL: under Connections, add the address as an authorized network.
Google AlloyDB: connect through the AlloyDB Auth Proxy, or from inside the peered VPC.
Supabase: under Settings, Database, Network restrictions, allow the address if restrictions are on. See the Supabase section below for which host to use.
Neon: under Settings, IP Allow, add the address if the allow-list is on.
Heroku Postgres: there is no firewall rule to add on the standard plans. Use heroku pg:credentials:create to make a dedicated credential for SPSync instead of sharing the default one.

Encrypt the connection

Put sslmode=require in the string. That encrypts the connection but does not check the server’s identity. To check it, use sslmode=verify-full with sslrootcert=/path/to/ca.pem for a CA your host does not already trust.

Azure Database for PostgreSQL requires TLS and presents a certificate that chains to a public root, so sslmode=require works and verify-full needs no CA file.
Amazon RDS and Aurora allow plain connections unless you require TLS. Set sslmode=require yourself. For verify-full, download the regional RDS CA bundle.
Google Cloud SQL needs either the Cloud SQL Auth Proxy (connect to 127.0.0.1:5432 with no sslmode, because the proxy handles TLS) or a direct connection with sslmode=verify-ca and the client certificate files from the instance’s Connections page.
AlloyDB through the Auth Proxy also connects to 127.0.0.1:5432 with no sslmode.
Heroku Postgres requires sslmode=require.

Authentication SPSync supports today

A role and password in the connection string. Microsoft Entra ID tokens for Azure, and IAM database tokens on AWS or Google Cloud, are not supported. The Cloud SQL and AlloyDB proxies use Google credentials to reach the instance, but the credential SPSync passes to Postgres stays a user name and password.

Connect from SPSync

HOST is your server’s name.

postgresql://spsync_loader:PASSWORD@HOST:5432/spsync?sslmode=require

Desktop app: open the Destination screen, choose Postgres / Supabase, paste the string into Connection string and choose Test connection.

CLI:

export SPSYNC_DSN='postgresql://spsync_loader:PASSWORD@HOST:5432/spsync?sslmode=require'
spsync export-config --output replication.yaml \
  --site-url https://contoso.sharepoint.com/sites/x --list Projects \
  --pipeline-name spsync_dev --prefix dev \
  --destination-kind postgres --connection-string-env SPSYNC_DSN --dataset-name sync \
  --client-id <entra-app-client-id>

AI assistant: with the admin MCP server connected, ask for the destination requirements, then let the assistant create the destination. You enter the string on the secure entry page.

Supabase

Supabase offers three endpoints for the same database: the direct connection, the session pooler and the transaction pooler. The direct connection is IPv6-only unless you buy the IPv4 add-on. The transaction pooler works over IPv4 on every plan and is what the Supabase connection string in Project Settings, Database gives you. SPSync always sets sslmode=require when it builds a Supabase string, but Supabase does not force TLS on its side until you turn on “Enforce SSL on incoming connections”. Switch that on.

No one has run the transaction pooler end to end with SPSync, so it is untested. If a sync fails there, try the session pooler or the direct connection.

Neon

Neon suspends idle compute. The first connection after a pause can take a few seconds, so use a connection timeout of ten seconds or more. Paste the connection string from the Neon dashboard as it is; for the pooled string it already carries what the pooler needs.

Heroku Postgres

Heroku gives you a DATABASE_URL that starts postgres://. SPSync accepts that scheme. Add ?sslmode=require. Small plans cap the number of connections low, so a sync alongside your application can hit the limit; use a pooler or a larger plan.

Azure, RDS, Aurora, Cloud SQL and AlloyDB

Nothing beyond the firewall and TLS notes above. Use the writer endpoint on Aurora.

What Test connection and doctor check

Both open a connection and run SELECT 1. A pass means the host is reachable, the firewall and TLS are right and the password is accepted. It does not prove the role can create schemas or tables. spsync doctor reports the same probe as destination-connectivity.

Common errors

What you see	What it means and the fix
`connection refused` or `timeout expired`	A firewall, security group or allow-list is blocking port 5432, or the host is wrong.
`no pg_hba.conf entry for host ... SSL off`	The server demands TLS. Add `sslmode=require`.
`password authentication failed for user`	Wrong password, or on a pooler the user name needs the project suffix the host’s page shows.
`permission denied for database` or `permission denied to create schema`	The role cannot create schemas. Use the owner setup above, or create `sync` and `sync_staging` yourself.
`SSL error: certificate verify failed`	With `verify-full`, the CA file is missing or the host name does not match.
`remaining connection slots are reserved`	The service’s connection cap is full. Close other clients, or use a pooler.