logoReverse Migrations

PostgreSQL Service Account

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 as a superuser (for example postgres) and run:

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

CREATE USER is an alias for CREATE ROLE … LOGIN in PostgreSQL.

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.

-- 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;

Why pg_catalog?
pg_catalog contains PostgreSQL's system views such as pg_class, pg_attribute, pg_constraint, and pg_index, which are required for complete table, column, index, and constraint discovery.

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 Reverse Migrations dashboard when you add your PostgreSQL database.

References

Back to Documentation