Skip to content

Connecting to a database

pgNimbus opens the connection dialog on launch.

The connection dialog, with saved profiles on the left and the paste-anything import box at the top

Supported PostgreSQL versions

The test suite passes on PostgreSQL 12 to 18 (the official postgres:12 to postgres:18 images), with no failures. Two groups of tests skip on older servers because they need newer syntax: the per-membership NOINHERIT roles test needs 16 (skipped on 12 to 15), and the four multirange tests need 14 (skipped on 12 and 13). The function list reads a catalog column from 14 and falls back below it. PostgreSQL 19 was not tested. PostgreSQL's own policy ends support for a major release five years after it ships.

Paste anything

The box at the top of the dialog accepts whatever form of connection string you already have, and fills the form out for you. All of these work:

postgres://alice:s3cret@db.example.com:5433/appdb?sslmode=require
jdbc:postgresql://db.example.com:5433/appdb?user=alice&ssl=true
Host=db.example.com;Port=5433;Database=appdb;Username=alice;Password=s3cret
host=db.example.com port=5433 dbname=appdb user=alice sslmode=require
PGPASSWORD=s3cret psql -h db.example.com -p 5433 -U alice appdb

That last one means you can copy a psql command straight out of a runbook or a hosting provider's dashboard and paste it in.

A password with /, ?, # or @ in it doesn't have to be percent-encoded in a postgres:// URI: everything before the last @ is read as the user name and password.

The copy button next to the box copies the form as a postgres:// URI without the password, because clipboard history and clipboard managers keep what you copy. To copy it with the password, right-click the button and choose Copy With Password. pgNimbus then asks Windows clipboard history (and clipboard managers on macOS and Linux that honor the hint) not to keep it, and clears the clipboard after 30 seconds if the string is still there.

Where your password goes

Database and SSH passwords are kept separately from the connection profile: DPAPI-encrypted files on Windows, Keychain on macOS, and Secret Service through libsecret on Linux. A password is written there a moment after you stop typing it. Clearing the field removes the stored one. These stores can persist protected data on disk.

Linux requires libsecret-1.so.0 and a running Secret Service provider such as GNOME Keyring. If storage is unavailable or access is denied, the dialog warns and keeps entered passwords in memory for this app session. Unlock/configure the OS store and edit the password again to retry. On macOS, Keychain access must be available without an interactive authorization prompt; resolve restrictions in Keychain Access. Builds that are signed ad hoc change identity with every version, so after an update the Keychain can refuse the passwords an earlier version saved. The macOS installation notes have the fix.

On macOS and Linux, older versions kept passwords in unencrypted base64 .cred files. When the connection dialog first opens, pgNimbus moves all of them into the OS store at once. It deletes an old file only after reading the saved password back from the OS store. Files it can't move stay where they are, and the dialog shows one warning with their count. If a different OS-store value already exists, it takes precedence; editing the password resolves the old copy. Passwords that the OS store refused are kept in memory only until the last window using that connection closes. Deleting a profile attempts to remove database and SSH credentials from both locations and reports failures.

A password file (.pgpass)

Leave the Password field empty and pgNimbus connects with no password of its own. The server then gets the one in your PostgreSQL password file, the same file psql reads: the file named by PGPASSFILE, otherwise ~/.pgpass (%APPDATA%\postgresql\pgpass.conf on Windows). Each line is host:port:database:username:password, and * matches anything. Backups and restores read the same file. A password typed in the field wins over the file.

Credential protection does not cover your queries. Three files keep SQL text on disk, unencrypted, in <appdata>/pgNimbus/ (on Windows, %AppData%\pgNimbus; see where pgNimbus keeps its files):

  • history.json keeps the text of every statement you run, with the values in it. A query that looks up a customer by email keeps the email address. Each entry also keeps its result line: the row count, or the server's error message. A statement that held a password keeps no result line, because an error message can quote the password where masking can't find it.
  • workspace.json keeps the text of your open tabs, so the next session can reopen them.
  • saved-queries.json keeps the queries you save to the Saved Queries list, exactly as you saved them.

Before the first two files are written, pgNimbus masks passwords it finds in the SQL: PASSWORD '…' in CREATE ROLE and ALTER ROLE, including inside a DO block, an EXECUTE string or a comment, and password=… in a connection string such as CREATE SUBSCRIPTION … CONNECTION '…' or dblink_connect('…'). The password is replaced with '<redacted>'::redacted, so a restored tab or a history entry shows that instead. If you run such a statement again, PostgreSQL rejects it as a syntax error rather than setting the password to the placeholder. Other values are kept as you typed them. A tab opened from a .sql file that holds a password is not copied at all: next time it opens from the file itself, so any changes you had not saved to that file are gone.

Masking needs a word next to the secret that says what it is. A key passed as an ordinary argument, such as pgp_sym_encrypt(data, 'key'), or a password kept in a variable inside a DO block, is stored as you typed it.

To stop recording history, turn off Record query history in Settings, on the General tab. Queries you run after that are not written anywhere, and the history list says that history is off. Entries already recorded stay until you right-click the list and choose Clear History; pinned entries survive that, so unpin them first.

On macOS and Linux, pgNimbus makes its data folder and every file in it readable by your user account only, including files an older version left open to other users.

That is a design rule rather than a setting. The profile record has no field to put a password in, so a profile file cannot leak one even if you copy it somewhere.

Saved profiles

There is no Save button. Start typing into an empty form, or paste a connection string, and the connection appears in the list on the left of the dialog. Every later change to a selected profile is kept as you make it, whether you connect or not. Press New for a blank form. Each row shows who connects where, as user@host/database, so two profiles on the same server are told apart without clicking either.

The connection you used last is already selected when the dialog opens, with the password loaded, so launching pgNimbus and pressing Enter reconnects to it. Pick another with the arrow keys and press Enter, or double-click any profile to connect to that one.

Right-click a profile for Connect, Duplicate and Delete. Duplicate is the fast way to add a second database on the same server: it copies the host, SSL mode, SSH settings and password, and you change only what differs.

Give production a colour. Each profile carries an accent colour, picked from the round swatch next to the name field. It shows as a dot in the main window's command bar and runs through the window's chrome. Making production red and staging green is the cheapest possible guard against running the right query against the wrong server.

The main window's title bar names the connection after its profile, followed by the database. Hover over it to see where that is, as user@host:port/database and the SSH hop, if any. A profile you never named shows its host instead.

Environments

Set Environment on a profile to say what it points at: Development, Staging or Production. The profile list shows the label next to the name, and the main window shows it next to the connection name before anything runs. Dev and staging are labels only.

Production also changes what the window can do. Every window on a production profile opens with writes off, enforced by the server in the same way as a read-only connection: INSERT, UPDATE, DELETE, COPY FROM and DDL are refused from the editor, the results grid, an import and a schema or security action alike. Cancel query and Terminate in Server Activity are off too.

When you mean to change data, click the red production label (or run Allow writes in this window from the command palette) and confirm. The label fills red and reads production · writes on. Only that window can write: other windows on the same profile, a new window and the next connect all start with writes off again. Click the label again (or run Turn writes off in this window) to turn writes off. pgNimbus refuses that while a transaction you started with Begin is open, because its connection can still write; commit or roll back first. Rows already in a grid stay read-only until you run the query again.

If Read-only session is also on, the profile stays read-only and no window can allow writes.

What this protects, and what it doesn't

The environment and the read-only session are safeguards against mistakes in pgNimbus, not permissions. They make an accidental write fail, and they make the write state visible before you run anything. They don't stop a statement from running SET default_transaction_read_only = off for its own session, and they don't apply to psql or any other client using the same login.

What a role may do is decided by PostgreSQL: its grants, ownership and role memberships. To make writes to production impossible rather than deliberate, connect as a role without write privileges. Roles and permissions in the command palette shows what a role can do and why.

Read-only connections

Switch on Read-only session for a profile you only mean to read from, such as production. Every session then starts with default_transaction_read_only on, so the server itself refuses INSERT, UPDATE, DELETE, COPY FROM and DDL. It refuses them from the editor, the results grid, an import and a schema action alike, because the check isn't in pgNimbus. The profile gets a lock in the list.

The main window shows read-only with a lock next to the connection name and database, and the results grid doesn't offer editing. pgNimbus asks the server when the window opens, so the mark also appears when the server makes the session read-only on its own: a role or database with default_transaction_read_only set, or a standby replica.

A connection pooler can drop the read-only option before it reaches the server. PgBouncer does this when ignore_startup_parameters includes options. If the profile asks for read-only and the server still accepts writes, the mark turns amber and reads read-only not applied. The results grid stays read-only, but SQL you run can change data, so don't count on the profile's protection for that session.

This guards against mistakes. It isn't a permission. A statement can still switch it off for its own session with SET default_transaction_read_only = off. To make writes impossible, connect as a role that has no write privileges.

Timeouts

Open Advanced under the form to give a profile a Statement Timeout and a Lock Timeout, in seconds. Every session the profile opens starts with PostgreSQL's statement_timeout and lock_timeout set to them, so the server cancels a statement that runs longer, or that waits longer for a lock another session holds. It applies to everything the window sends: your queries, the grid's edits, schema actions and pgNimbus's own reads. A statement cut short reports canceling statement due to statement timeout (or lock timeout); it is an ordinary error, and the connection stays usable.

Leave a field empty, or enter 0, for no timeout from pgNimbus. The server's own setting then applies, including one set for your role with ALTER ROLE … SET statement_timeout. A value in the profile overrides those.

Three long operations are not cut short by the statement timeout, because you start them on purpose and can stop them: exporting every row of a table, an import, and backups and restores. The lock timeout still applies to an import. EXPLAIN ANALYZE runs the statement, so the timeout applies to it as it would to a run.

The timeouts travel as startup options, like read-only. A connection pooler that drops startup options (PgBouncer with options in ignore_startup_parameters) drops them too, and nothing in the window says so.

Encryption (TLS)

SSL Mode decides whether the connection is encrypted and what pgNimbus checks about the server. The list describes each mode. In short:

Mode Encrypted Checks the certificate Checks the host name
Disable No No No
Allow Only if the server insists No No
Prefer If the server offers it No No
Require Yes No No
Verify CA Yes Yes No
Verify full Yes Yes Yes

New profiles start at Require for a server on the network. It always encrypts, but it does not check who is on the other end: anyone who can intercept the connection can present their own certificate and read everything, the password included. Prefer and Allow are weaker still, because an attacker can make them fall back to plaintext. Use Verify full for any server you reach over a network you do not control. It is marked as recommended in the list.

A server on this machine (localhost, 127.0.0.1, ::1) starts at Prefer instead: there is no network path to intercept, and a local Postgres, such as the postgres Docker image, has no TLS unless you set it up. The mode follows the host you type until you pick one yourself. If Require meets a server without TLS, the connection fails with a message saying so.

Root certificate

Verify CA and Verify full trust the certificate authorities your operating system trusts. Managed Postgres services often sign their servers with their own CA, which no operating system trusts, so verification fails until you point pgNimbus at that CA. Choosing Verify CA or Verify full shows a Root Certificate field. Give it the provider's CA file, with Browse… or by typing the path:

  • Amazon RDS and Aurora: the global bundle, global-bundle.pem, from https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem.
  • Google Cloud SQL: the server CA certificate (server-ca.pem) from the instance's Connections page, under Security. If the instance's certificate does not name the host you connect to, use Verify CA.
  • Supabase: the certificate from the project's database settings, under SSL Configuration.

Leave the field empty to use the operating system's store. The path is saved with the profile. It is not a secret. A pasted connection string fills it from sslrootcert= (libpq, URIs, JDBC) or Root Certificate= (Npgsql).

Client certificate

Some servers sign you in with a certificate instead of a password, or ask for both: a cert line in pg_hba.conf, or clientcert=verify-full. Open Advanced under the form and give Client Certificate and Client Key the two files, with Browse… or by typing the paths. The paths are saved with the profile, are not secrets, and appear in the connection string preview as sslcert= and sslkey=. A pasted string fills them from sslcert= and sslkey= (libpq, URIs, JDBC), SSL Certificate= and SSL Key= (Npgsql), or PGSSLCERT= and PGSSLKEY= in front of a psql command. A path on another machine (\\server\share\…, a URL) is not taken from a paste.

Left empty, pgNimbus does what psql does: it uses PGSSLCERT and PGSSLKEY, or ~/.postgresql/postgresql.crt and postgresql.key (%APPDATA%\postgresql\ on Windows), if they exist. Backups and restores get the same certificate.

pgNimbus does not support a key protected by a passphrase, and says so before it connects. Save a copy without the passphrase (openssl pkey -in client.key -out client-plain.key) and make it readable only by you. On Linux and macOS, pg_dump and pg_restore refuse a key file that other users can read (chmod 600).

Through an SSH tunnel

Verify full works through an SSH tunnel. The tunnel connects pgNimbus to a local port, but the certificate is still checked against the database host you typed in the form, which is the name the server's certificate carries.

SSH tunnels

A profile can carry SSH tunnel settings, so a database that is only reachable from a bastion host connects like any other. The tunnel is owned by the window, so closing the window tears it down.

Pick how pgNimbus signs in to the SSH host with Auth Method:

  • SSH agent uses the keys your agent already holds. On Windows that is the OpenSSH Authentication Agent service; on macOS and Linux it is the agent SSH_AUTH_SOCK points at. A key with a passphrase works without typing it here, as long as you have added it with ssh-add. Nothing is stored.
  • Key file reads a private key from disk. Leave the path blank to use the same defaults ssh tries (~/.ssh/id_ed25519, then id_ecdsa, then id_rsa). If the key has a passphrase, type it below; it is stored like a password.
  • Password signs in with the SSH user's password.

For the database host and port, give them as the SSH host sees them. A Postgres that only listens locally on the server is 127.0.0.1 and 5432.

Host keys

Before it signs in, pgNimbus checks that the SSH host is the server it claims to be, the same way ssh does. It looks the host's key up in your own ~/.ssh/known_hosts first (hashed entries, [host]:port entries and @revoked lines included) and then in its own list at <appdata>/pgNimbus/known_hosts. It never writes to your ~/.ssh/known_hosts.

  • A known key connects with no question.
  • An unknown host opens a dialog with the host, the key type and the key's SHA256: fingerprint, written the way ssh and ssh-keygen -l write it. Compare it with the fingerprint the server's administrator gave you. Accept connects and adds the key to pgNimbus's list; Cancel does not connect. Enter does not accept, so a key is never trusted by a stray key press.
  • A changed key stops the connection. The message names the host, the stored key's fingerprint, the new one, and the file and line that hold the old key. A server that was reinstalled or had its keys regenerated causes this, and so does someone intercepting the connection. If you know the key really changed, remove that line (ssh-keygen -R with -f pointing at that file does it for you) and connect again to be asked about the new key.
  • A key of a different type for a host already in either file stops the connection too. pgNimbus asks the server only for the key types the files know the host by, as ssh does, so a server that suddenly offers only a new type of key is refused rather than treated as a new host. If its keys really changed, remove its lines and connect again.
  • A revoked key (an @revoked line in either file) always stops the connection.

Take your time with the dialog: if reading the fingerprint outlasts the connection's timeout, pgNimbus connects again once you accept, without asking a second time.

The Test button runs the same check, so a key you accept there is already known when you connect. SSH host certificates (@cert-authority lines) are not supported yet: a host that relies on one is treated as unknown and asks about its key.

Several connections at once

Two different things, for two different needs.

Open a connection in a new window (Ctrl+Shift+N) gives that connection a fully independent window: its own connection pool, LISTEN/NOTIFY listener, SSH tunnel, and workspace of tabs. Use this to put dev and prod side by side.

Switch connection (Ctrl+Shift+O) repoints the current window at a different server without restarting the app.

Both are also in the menu behind the ☰ button and in the command palette.

Another database on the same server

You don't need a profile for each database on a server. Click the database name in the title bar (the part after ›), or run Switch database… from the command palette. The list shows the databases your role may connect to; the current one has a check mark. Type to narrow the list, then press Enter or click a database to open it in this window.

pgNimbus connects with the same profile, password and SSH tunnel, with only the database changed. The profile itself is not changed. If the connection fails (for example, your role has no CONNECT privilege), the window stays where it was and the status line shows the error. When it succeeds, the window works exactly as after Switch connection: the tabs of the old database, unsaved text included, are saved for it and come back when you return to it. On a production profile, writes are off again in the new window.

If the window has staged changes, an open transaction or a running query, pgNimbus asks first and says what will be lost: staged changes are discarded, the transaction is rolled back and the query is cancelled. Choose Cancel to stay where you are. Switch connection asks the same before its dialog opens, and so does closing the window (see Closing a window).

To keep the current database open as well, right-click a database in the list and choose Open in New Window.

If the connection drops

A connection dropped by laptop sleep, a network blip or an SSH tunnel hiccup is reopened quietly on your next run. Before it sends a statement, pgNimbus asks the server to describe it, which runs nothing. If that fails because the connection is dead, pgNimbus flushes the pool and opens a fresh connection, so you usually will not notice.

pgNimbus never sends a statement a second time on its own. If the connection drops while a statement is running, whether the network went away or a DBA terminated your session, the run ends with an error that says the statement was not run again and may or may not have taken effect. Check before you run it again. The next statement reconnects by itself. A statement that has already started on the server is out of the client's hands: an UPDATE that was halfway through when the socket died keeps running there and commits, and running it again would apply it twice.

The other case pgNimbus deliberately does not paper over is an open explicit transaction. A transaction lives on one held connection; if that connection dies, the transaction is gone and nothing in it committed. Rather than silently starting a new one and leaving you to guess what happened, pgNimbus surfaces a clear "connection lost, nothing committed" error.

Skipping the dialog

Set the PGNIMBUS_CONN environment variable to any of the formats above and pgNimbus connects straight to it, skipping the dialog. Handy for scripted or repeated local testing:

export PGNIMBUS_CONN="postgres://postgres:secret@localhost:5432/mydb"

The example above puts the password in the environment. Any other process running as you can read it (/proc/<pid>/environ on Linux, a process inspector on Windows or macOS), and if you type the export line directly at a shell, it usually lands in shell history too. Prefer a connection string with no password and let pgNimbus prompt, or keep this variable to a throwaway local database.

For everyday use there is a switch on the General tab of Settings, Open the last connection on startup, which goes straight to whatever you connected to last. The dialog stays one Switch connection away, and a connect that fails lands back in it with the error.

Next

Get to know the SQL editor