PostgreSQL projects can use direct connect to reverse engineer entire schemas in seconds. Please refer to the instructions below to configure and perform a direct connection to your PostgreSQL instance.

To establish a direct connection to PostgreSQL, you will require the following details

• Server: e.g., mydatabase.myserver.com

• Port: 5432 (default)

• Database (e.g., postgres)

• Username

• Password

Using these details, follow the highlighted steps to establish the connection (or get more details in the related articles at the end of this post):
​

1. Navigate to the Reverse Engineering screen

2. Click the "Connect to DW" button

3. Enter your connection details from the section above

4. Connect and select the schema to import

Obtaining PostgreSQL parameters

The server URL and port for your PostgreSQL instance depend on where the database is hosted. Locate them using one of the methods below.

Managed cloud services

• Amazon RDS / Aurora PostgreSQL: in the AWS console, open the "Databases" page, click your database instance, and find the Endpoint and Port under the "Connectivity & security" tab.

• Azure Database for PostgreSQL: in the Azure portal, open your server resource and find the Server name on the "Overview" page (e.g., myserver.postgres.database.azure.com).

• Google Cloud SQL: in the Google Cloud console, open the "Cloud SQL Instances" page and find the Public IP address or DNS name on the instance "Overview" page.

• Other hosted providers (e.g., Neon, Supabase, Heroku): the host and port are displayed in the connection details or connection string section of the provider's dashboard.

Self-hosted instances

If you can already connect to the database through another client (e.g., psql, DBeaver, pgAdmin), you can confirm the port by running:

SHOW port;

Alternatively, ask your database administrator for the host name and port, or check the listen_addresses and port settings in the server's postgresql.conf file.

Connection parameters are often shared as a single connection string in the following format, which contains the server, port, and database:

• postgresql://username:password@server:port/database

Required database permissions

To obtain DDL, SqlDBM direct connect runs several queries that reference metadata in the information_schema and pg_catalog. Access to the contents (data) of the tables themselves is not required.

The connecting user will require:

• CONNECT privilege on the database

• USAGE privilege on the schemas being imported

• Read access to pg_catalog and information_schema (granted by default)

• SELECT privilege on the tables being imported

For reference, the following SQL is run to obtain the metadata:

--Get schema list

SELECT      table_schema as schema_name
FROM        information_schema.tables
WHERE       table_schema not in ('pg_catalog', 'information_schema')
AND         table_catalog = @database
GROUP BY    1
ORDER BY    1
;

--------------------

--Create schema ddl

SELECT
	'CREATE SCHEMA ' || QUOTE_IDENT(nspname) ||
		CASE
		WHEN nspowner > 100
		THEN ' AUTHORIZATION ' || QUOTE_IDENT(pg_user.usename)
		ELSE ''
		END
		|| ';' AS ddl
FROM pg_catalog.pg_namespace as pg_namespace
LEFT OUTER JOIN pg_catalog.pg_user pg_user
ON pg_namespace.nspowner=pg_user.usesysid
WHERE nspname = any(@schemas)
ORDER BY nspname
;

--------------------

--Get table list

SELECT  quote_ident(t.table_schema) || '.' || quote_ident(t.table_name) as tableName
FROM    information_schema.tables t
WHERE   t.table_schema = any(@schemas)
  AND   t.table_type = 'BASE TABLE'
;

Table, column, and constraint definitions are then read from pg_catalog to reconstruct the DDL.

IP allowlisting and private networking

Corporate networking and security policies may interfere with connectivity between SqlDBM and PostgreSQL. If you are unable to connect despite following the guidelines in this article, please make sure that IP blocking or private networking is not interfering. Also confirm that your PostgreSQL server accepts remote connections (listen_addresses) and that the connecting user and SqlDBM IP range are permitted in pg_hba.conf (for self-hosted instances) or in the network/firewall rules of your cloud provider.

An easy way to diagnose this type of issue is by attempting to connect to your PostgreSQL instance from a home computer, without using a VPN or any corporate software.

For instructions on allowing SqlDBM through the firewall or private network, please see the related article at the bottom of this page.

See also:

• Reverse Engineering

• Direct DW Connection

• Direct Connect: allowlisting and private networking