Connect PostgreSQL

Reverse Migrations only needs to read schema metadata - tables, columns, data types, constraints, and indexes. It never reads the actual rows in your tables.

The steps below create a dedicated user that can inspect the schema but cannot query your data.

1. Create the service account

Connect to your database with the psql client as a superuser (for example postgres) and run:

CREATE USER reverse_migrations WITH PASSWORD 'a-strong-password';

2. Grant schema introspection privileges

Run the following commands for each database you want to introspect. Replace your_database and public with your real database and schema names.

The \c command switches databases inside psql. If you use another SQL client, reconnect to the target database instead of running that line.

-- Allow connection to the target database
GRANT CONNECT ON DATABASE your_database TO reverse_migrations;
 
-- Connect to the target database and allow access to the schema
\c your_database
GRANT USAGE ON SCHEMA public TO reverse_migrations;
 
-- Allow reading the system catalog that holds schema metadata
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO reverse_migrations;

3. Verify the user cannot read table data

Open a new session as reverse_migrations and try to query a user table. It should fail:

SELECT * FROM some_user_table;
-- ERROR:  permission denied for table some_user_table

At the same time, schema queries should succeed:

SELECT relname FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace;

4. Use this credential in the dashboard

Copy the username (reverse_migrations) and password into the workspace when you select Add database and choose PostgreSQL.

References

• PostgreSQL CREATE USER
• PostgreSQL Privileges
• PostgreSQL System Catalogs