snapshot
Guides
PostgreSQL

PostgreSQL

Create a read-only role

Snaplet connects to your PostgreSQL database in order to create snapshots. We recommend that you give us read-only access to your database, and that you restrict connection to a specific set of IP addresses.

Never cry over split milk!

Connect to your database and run the following SQL statements. These creates a snaplet_readonly user with the password a very good password and gives them the snaplet_read_all_data role.

PostgreSQL v14 includes a pg_read_all_data role. Run SELECT version() in PostgreSQL to determine your version.

⚠️

Change the username and the password!


SELECT version();
-- Create a "snaplet_readonly" user and associate the "pg_read_all_data" role.
-- We give the user BYPASSRLS privileges in order to introspect the db structure.
CREATE USER snaplet_readonly WITH PASSWORD 'a very good password' BYPASSRLS;
GRANT pg_read_all_data TO snaplet_readonly;
ALTER ROLE snaplet_readonly SET statement_timeout = 0;

Grant IP address access

Snaplet uses these IPs to connect to your database:


3.67.57.100
3.68.126.236
35.158.181.77

It's a good idea to restrict all traffic to PostgreSQL, and only grant access where it's absolutely required.

Self-Signed Certificates

To make Snaplet work with servers using self-signed certificates. Please add sslmode=require to the database connection string:

>_ terminal

SNAPLET_TARGET_DATABASE_URL='postgresql://<user>:<password>@<host>:<port>/<database>?sslmode=required&ssl=true&sslmode=require' snaplet snapshot restore

During the snapshot capture process, use NODE_TLS_REJECT_UNAUTHORIZED=0:

>_ terminal

NODE_TLS_REJECT_UNAUTHORIZED=0 SNAPLET_SOURCE_DATABASE_URL='postgresql://<user>:<password>@<host>:<port>/<database>?sslmode=required&ssl=true&sslmode=require' snaplet snapshot capture