Connect to Supabase Postgres from your Mac

What you need

• The database password you set when you created the project. If you’ve lost it, set a new one on the project’s Database Settings page.
• The connection string from the Connect button at the top of your project page.
• Inlet, or psql for the command line.

Find your connection details

Click Connect at the top of your project page in the Supabase dashboard and pick a connection method. Supabase offers four, and the right one depends mostly on your network:

The database is called postgres in every case. The shared pooler is Supavisor; the dedicated pooler is PgBouncer and runs in transaction mode only.

• From a Mac, start with the session pooler. Supabase recommends it for database GUIs on IPv4 networks, and it keeps session state, so prepared statements and SET work as usual.
• The direct connection needs IPv6 unless you’ve bought the IPv4 add-on. On a network without IPv6 it won’t connect at all.
• The transaction pooler (port 6543) is for serverless functions with many short connections. It doesn’t support prepared statements, and session state is lost between transactions.

On the pooler, a custom role signs in as <role>.<project-ref>, not plain <role>.

If you’ve set network restrictions (Database Settings), only the listed IP ranges can reach Postgres and the pooler. Until you add one, every address is allowed. If your direct connection resolves to IPv6, the allowlist needs your IPv6 range as well as your IPv4 one.

Connection string

The session pooler string from the Connect panel looks like this:

postgresql://postgres.<project-ref>:<password>@aws-<index>-<region>.pooler.supabase.com:5432/postgres

Add ?sslmode=require (or verify-full, see below) so the client refuses to connect without encryption. Percent-encode reserved characters in the password: &, #, ?, spaces and the like. See special characters in passwords and the PostgreSQL connection string format.

TLS

Supabase accepts unencrypted connections unless you turn on Enforce SSL on incoming connections under SSL Configuration on the Database Settings page. Enforcement covers Postgres, Supavisor and the dedicated pooler, and changing it restarts the database briefly.

Supabase signs its server certificates with its own certificate authority, which macOS doesn’t trust out of the box. To check the server’s identity, download the certificate from the same SSL Configuration section (the file is prod-ca-2021.crt) and connect with sslmode=verify-full pointing at it. Supabase’s own psql example does this through the session pooler.

Connect with Inlet

1. In the Connect panel, copy the session pooler string and paste it into Inlet. Inlet fills in the connection form: host, port 5432, user postgres.<project-ref>, database postgres. Or choose New Connection and type the fields yourself.
2. Under TLS, choose verify-full and select the prod-ca-2021.crt file you downloaded as the CA file. (require encrypts the connection without checking who’s at the other end.)
3. Save the password in the Keychain, or have Inlet ask for it every time.
4. Tag the connection’s environment. A connection tagged production opens read-only: the server refuses writes until you unlock it, and unlocking lasts ten minutes.

Known limit with the transaction poolers. Inlet’s read-only mode is a session setting. In transaction mode (port 6543, and the dedicated pooler) each transaction can run on a different server connection, so the setting isn’t guaranteed to hold. (PgBouncer, which runs the dedicated pooler, also skips its reset query in transaction mode, so a SET can stay on a server connection that another client uses next.) For production, use the session pooler or the direct connection. To make a role read-only whichever way it connects, set it on the server:

CREATE ROLE reader LOGIN PASSWORD '<password>';
GRANT USAGE ON SCHEMA public TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;
ALTER ROLE reader SET default_transaction_read_only = on;

Connect through the pooler as reader.<project-ref>. The ALTER ROLE … SET line applies to every session the role opens, pooled or not, but it’s a default: a session can still run SET default_transaction_read_only = off. Granting only SELECT is what actually stops writes. (ON ALL TABLES covers the tables that exist now; grant again for new ones.)

Connect from the command line

Based on Supabase’s own example, through the session pooler with certificate checks:

psql "sslmode=verify-full sslrootcert=$HOME/Downloads/prod-ca-2021.crt host=aws-<index>-<region>.pooler.supabase.com port=5432 dbname=postgres user=postgres.<project-ref>"

psql asks for the password. If you don’t have psql, Homebrew’s libpq package includes it; it’s keg-only, so run /opt/homebrew/opt/libpq/bin/psql or add that folder to your PATH.

Troubleshooting

• password authentication failed: on the pooler, the user must include the project reference (postgres.<project-ref>). Check the password, reset it in Database Settings if you need to, and percent-encode special characters in a URL.
• The direct host won’t connect from home or the office: your network probably has no IPv6. Switch to the session pooler.
• Connection refused or timed out: check the network restrictions in Database Settings include your current IP address.
• Too many connections: connect through the pooler rather than directly.
• Errors about prepared statements: you’re on the transaction pooler (port 6543). Use the session pooler for tools that prepare statements.