| title | redirect_from | |
|---|---|---|
PostgreSQL |
|
This page covers connecting to PostgreSQL as a data warehouse. For using PostgreSQL as Metabase's application database, see Configuring the Metabase application database.
To add a database connection, click the grid icon in the top right, and navigate to Admin > Databases > Add a database.
Fill out the fields for that database, and click Save changes at the bottom.
Metabase supports the oldest supported version of PostgreSQL through the latest stable version. See PostgreSQL versions.
To connect to a Supabase database, select PostgreSQL. For more details, check out the Supabase docs.
After connecting to a database, you'll see the "Connection and sync" section that displays the current connection status and options to manage your database connection.
Here you can sync the database schema and rescan field values, and edit connection details.
You can edit these settings at any time. Just remember to save your changes.
Paste a connection string here to pre-fill the remaining fields below.
The display name for the database in the Metabase interface.
Your database's IP address, or its domain name (e.g., esc.mydatabase.com).
The database port. E.g., 5432.
The name of the database you're connecting to.
The database username for the account that you want to use to connect to your database. You can set up multiple connections to the same database using different user accounts to connect to the same database, each with different sets of privileges.
The password for the username that you use to connect to the database.
{% include plans-blockquote.html feature="Authenticating with a provider" %}
Instead of a password, you can authenticate with a supported provider.
Only for self-hosted Pro and Enterprise plans.
To use Azure Managed Identity, you'll need to input your client ID.
To use Oauth as a provider, you'll need to input your:
- Auth token URL
- Auth token request headers (a JSON map)
To connect to Amazon RDS instances using IAM authentication instead of a password, see IAM authentication for AWS RDS.
You can specify which schemas you want to sync and scan. Options are:
- All
- Only these...
- All except...
For the Only these and All except options, you can input a comma-separated list of values to tell Metabase which schemas you want to include (or exclude). For example:
foo,bar,baz
You can use the * wildcard to match multiple schemas.
Let's say you have three schemas: foo, bar, and baz.
- If you have Only these... set, and enter the string
b*, you'll sync with bar and baz. - If you have All except... set, and enter the string
b*, you'll just sync foo.
Note that only the * wildcard is supported; you can't use other special characters or regexes.
Metabase automatically tries to connect to databases with SSL first, then without if that doesn't work. If it's possible to connect to your database with an SSL connection, Metabase will make that the default setting for your database. If you prefer to connect without this layer of security, you can always change this setting later, but we recommend keeping SSL turned on to keep your data secure.
PostgreSQL databases support different levels of security with their connections, with different levels of overhead.
SSL Mode options include:
- allow
- prefer
- require
- verify-ca
- verify-full
See the PostgreSQL docs for a table about the different SSL Modes, and select the option that works for you.
If you set the SSL Mode to either "verify-ca" or "verify-full", you'll need to specify a root certificate (PEM). You have the option of using a Local file path or an Uploaded file path. If you're on Metabase Cloud, you'll need to select Uploaded file path and upload your certificate.
Toggle on to bring up client certificate options.
You have the option of using a Local file path or an Uploaded file path. If you're on Metabase Cloud, you'll need to select Uploaded file path and upload your certificate.
Again, you have the option of using a Local file path or an Uploaded file path. If you're on Metabase Cloud, you'll need to select Uploaded file path and upload your certificate. You'll also need to input your SSL Client Key Password.
The private key must be PKCS8 and stored in DER format.
If you instead have a PEM SSL client key, you can convert that key to the PKCS-8/DER format using openssl. The command would look something like:
openssl pkcs8 -topk8 -inform PEM -outform DER -in client-key.pem -out client-key.der -nocrypt
Note: if you're using GCP and you managed to issue client certificates, everything will be given in PEM format, you only need to transform the client-key.pem into a client-key.der for the "SSL Client Key"
See our guide to SSH tunneling.
For PostgreSQL databases, Metabase can unfold JSON columns into component fields to yield a table where each JSON key becomes a column. JSON unfolding is on by default, but you can turn off JSON unfolding if performance is slow.
If you turn on JSON unfolding, you can also toggle the unfolding for individual columns in table metadata.
You can append options to the connection string that Metabase uses to connect to your database. Use the format:
options=-c%20key=value
PostgreSQL connection URIs expect percent-encoding for whitespaces and symbols.
Turn this option OFF if people want to click Run (the play button) before applying any Summarize or filter selections.
By default, Metabase will execute a query as soon as you choose an grouping option from the Summarize menu or a filter condition from the drill-through menu. If your database is slow, you may want to disable re-running to avoid loading data on each click.
See syncs and scans.
Periodic refingerprinting will increase the load on your database.
Turn this option ON to scan a sample of values every time Metabase runs a sync.
A fingerprinting query examines the first 10,000 rows from each column and uses that data to guesstimate how many unique values each column has, what the minimum and maximum values are for numeric and timestamp columns, and so on. If you leave this option OFF, Metabase will only fingerprint your columns once during setup.
Choose whether to enable features related to Metabase models. These features will often require that the database user account, the one you use to connect to your database, has both read and write privileges.
Turn this setting on to allow actions from models created from this data to be run. Actions can read, write, and delete data. Your database user will need write permissions.
We'll create tables with model data and refresh them on a schedule you define. To enable model persistence, you need to grant this connection's credentials read and write permissions on a schema Metabase provides.
Turn this setting ON to enable editing of table data directly within Metabase. When enabled, Admins can create, update, and delete records in your tables through Metabase's interface.
Your database connection will need Write permissions to enable this feature. Meaning: the database user account that you use to connect Metabase to your database must have appropriate privileges to modify data in the tables you want to make editable.
See privileges.
With database routing, an admin can build a question once using one database, and the question will run its query against a different database with the same schema depending on who is viewing the question.
See Database routing.
See Danger zone.