Use 1Password to securely authenticate the PostgreSQL CLI

The PostgreSQL shell plugin allows you to use 1Password to securely authenticate psql with your fingerprint, Apple Watch, or system authentication, rather than storing your credentials in plaintext.

Follow the instructions to configure your default credentials and source the plugins.sh file, then you'll be prompted to authenticate psql with biometrics.

Requirements

1. Sign up for 1Password.
2. Install and sign in to 1Password for Mac or Linux.
3. Install 1Password CLI 2.9.0 or later.
   If you've already installed 1Password CLI, learn how to update your installation.
4. Integrate 1Password CLI with the 1Password app.
5. Install psql.

The following shells are supported:

• Bash
• Zsh
• fish

Additional executables

You can also use the PostgreSQL shell plugin with:

• pg_dump
• pg_restore
• pgcli

Run op plugin init <plugin-executable> with the executable you want to configure, then follow the steps to select your credentials.

Step 1: Configure your default credentials

To get started with the PostgreSQL shell plugin:

1. Sign in to the 1Password account you want to use with the PostgreSQL plugin:
2. If you only want to configure the plugin in a specific directory, change to that directory.
3. Run the command to set up the plugin:

You'll be prompted to import your PostgreSQL credentials into 1Password or select an existing 1Password item where your credentials are saved, then configure when the credentials should be used.

Step 1.1: Import or select an item

Import a new item

If you haven't saved your PostgreSQL credentials in 1Password yet, select Import into 1Password. Enter your credentials, choose a name for the new 1Password item, and select the vault where you want to save it.

If 1Password detects your credentials in your local development environment, you'll be prompted to import them automatically.

Select an existing item

If you've already saved your PostgreSQL credentials in 1Password, select Search in 1Password.

You'll see a list of related items and the vaults where they're saved. If you don't see your credentials, select Expand search to browse all items in your account.

Step 1.2: Set default credential scope

After you select or import your credentials, you'll be prompted to configure when to use the item to authenticate PostgreSQL.

• "Prompt me for each new terminal session" will only configure the credentials for the duration of the current terminal session. Once you exit the terminal, the default will be removed.
• "Use automatically when in this directory or subdirectories" will make the credentials the default in the current directory and all of its subdirectories, as long as no other directory-specific defaults are set in them. A terminal-session default takes precedence over a directory-specific one.
• "Use as global default on my system" will set the credentials as the default in all terminal sessions and directories. A directory-specific default takes precedence over a global one.

Step 2: Source the plugins.sh file

To make the plugin available, source your plugins.sh file. For example:

The file path for your op folder may vary depending on your configuration directory. op plugin init will output a source command with the correct file path.

If this is your first time installing a shell plugin, you'll also need to add the source command to your RC file or shell profile to persist the plugin beyond the current terminal session. For example:

Bash

Zsh

fish

Step 3: Use the CLI

The next time you enter a command with PostgreSQL, you'll be prompted to authenticate with biometrics or system authentication.

Step 4: Remove imported credentials from disk

After saving your PostgreSQL credentials in 1Password, you can remove all local copies you previously had stored on disk.

Next steps

1Password Shell Plugins support more than 60 third-party CLIs. To see a list of supported CLIs:

To choose another plugin to get started with:

To use shell plugins for seamless context switching, learn how to configure a plugin in multiple environments or with multiple accounts.

Get help

Inspect your configuration

To inspect your current PostgreSQL configuration:

1Password CLI will return a list of the credentials you've configured to use with PostgreSQL and their default scopes, as well as a list of aliases configured for PostgreSQL.

Clear your credentials

To reset the credentials used with PostgreSQL:

You can clear one configuration at a time, in this order of precedence:

1. Terminal session default
2. Directory default, from the current directory to $HOME
3. Global default

For example, if you're in the directory $HOME/projects/awesomeProject and you have a terminal session default, directory defaults for $HOME and $HOME/projects/awesomeProject, and a global default credential configured, you would need to run op plugin clear psql four times to clear all of your defaults.

To clear your global default credentials, terminal session default, and the defaults for your current directory at the same time, run op plugin clear psql --all.

Reference

1Password authenticates with PostgreSQL by injecting environment variables with the credentials required by the plugin commands directly from your 1Password account.

If you saved your PostgreSQL credentials in 1Password manually rather than using op plugin to import a new item, make sure that your field names match the table below.

If the item doesn't contain a field with the required name, you'll be prompted to rename one of the existing fields.

1Password field names	Environment variables
Server	PGHOST
Port (optional)	PGPORT
Username	PGUSER
Password (optional)	PGPASSWORD
Database (optional)	PGDATABASE

Learn more

• Use shell plugins to switch between multiple environments
• Use shell plugins with multiple accounts
• Build your own shell plugins