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.

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.

warning

Change the username and the password!

PostgresQL v14

SELECT version();-- Create a "snaplet_readonly" user and associate the "pg_read_all_data" role.CREATE USER snaplet_readonly WITH PASSWORD 'a very good password';GRANT pg_read_all_data TO snaplet_readonly;

PostgresQL v13 and below

SELECT version();CREATE ROLE snaplet_read_all_data;DO $do$DECLARE    sch text;BEGIN    FOR sch IN SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema'    LOOP        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO snaplet_read_all_data $$, sch);        EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO snaplet_read_all_data $$, sch);        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO snaplet_read_all_data $$, sch);    END LOOP;END;$do$;-- Create a "snaplet_readonly" user and associate the "snaplet_read_all_data" role.CREATE USER snaplet_readonly WITH PASSWORD 'a very good password';GRANT snaplet_read_all_data TO snaplet_readonly;