Skip to main content

PostgreSQL

Overview​

PostgreSQL is an open-source object-relational database system known for its reliability, feature robustness, and performance. With support for advanced data types, full ACID compliance for transactional integrity, and an extensible architecture, PostgreSQL provides a highly scalable environment for managing diverse datasets, ranging from small applications to large-scale data warehouses. Its extensive SQL compliance, support for various programming languages, and strong community backing make it a versatile choice for a wide range of business intelligence and analytical applications. You can connect to and read from PostgreSQL databases using either a supported connection string or connection URI syntax.

Authentication Methods​

To connect to PostgreSQL, you need to provide database connection credentials. Rill supports two approaches:

  1. Use Individual Parameters (recommended for clarity)
  2. Use Connection String/URI (alternative format)

When you add data from PostgreSQL through the Rill UI, the process follows two steps:

  1. Configure Authentication - Set up your PostgreSQL connector with database credentials
  2. Configure Data Model - Define which table or query to execute

This two-step flow ensures your credentials are securely stored in the connector configuration, while your data model references remain clean and portable.

Using individual parameters provides clear, readable configuration for your PostgreSQL connection.

Using the UI​

  1. Click Add Data in your Rill project
  2. Select PostgreSQL as the data source type
  3. In the authentication step:
    • Enter your host (e.g., localhost or your database server address)
    • Enter the port (default: 5432)
    • Enter your database name
    • Enter your username
    • Enter your password
  4. In the data model configuration step, enter your SQL query
  5. Click Create to finalize

After the model YAML is generated, you can add additional model settings directly to the file.

Manual Configuration​

If you prefer to configure manually:

Step 1: Create connector configuration

Create connectors/postgres.yaml:

type: connector
driver: postgres

host: "localhost"
port: "5432"
user: "postgres"
password: "{{ .env.POSTGRES_PASSWORD }}"
dbname: "my_database"

Step 2: Add credentials to .env

POSTGRES_PASSWORD=your_password_here
Did you know?

If this project has already been deployed to Rill Cloud and credentials have been set for this connector, you can use rill env pull to pull these cloud credentials locally (into your local .env file). Please note that this may override any credentials you have set locally for this source.

Then, create your first model.

Method 2: Connection String/URI​

You can also use a connection string or URI format to configure your PostgreSQL connection.

Connection String Format​

host=localhost port=5432 dbname=postgres_db user=postgres_user password=postgres_pass

Connection URI Format​

postgresql://postgres_user:postgres_pass@localhost:5432/postgres_db

Manual Configuration​

Step 1: Create connector configuration

Create connectors/postgres.yaml:

type: connector
driver: postgres

dsn: "{{ .env.POSTGRES_DSN }}"

Step 2: Add credentials to .env

POSTGRES_DSN=postgresql://postgres_user:postgres_pass@localhost:5432/postgres_db

Then, create your first model.

TLS certificates in Rill Cloud​

Use certificate contents to connect to PostgreSQL with a private CA or mutual TLS. In the PostgreSQL connector form, choose Enter parameters, select verify-full for SSL mode, and upload the CA certificate, client certificate, and client private key under the advanced settings. The form stores file contents as base64-encoded secret variables and writes variable references into the connector YAML.

For manual configuration:

type: connector
driver: postgres
host: db.example.com
port: "5432"
dbname: analytics
user: rill
password: "{{ .env.POSTGRES_PASSWORD }}" # If required by the server
sslmode: verify-full
sslrootcert_pem: "{{ .env.POSTGRES_SSLROOTCERT }}"
sslcert_pem: "{{ .env.POSTGRES_SSLCERT }}"
sslkey_pem: "{{ .env.POSTGRES_SSLKEY }}"

Set the variables through your Cloud project credentials or use rill env push to upload variables configured locally. Values can contain raw PEM text or standard base64-encoded PEM.

  • sslrootcert_pem: Contents of root.crt, used to verify the server certificate. Omit this to use the system trust roots.
  • sslcert_pem: Contents of postgresql.crt, including any intermediate certificates.
  • sslkey_pem: Contents of the matching, unencrypted postgresql.key. Client certificate and private key must be provided together; omit both if the server does not require client certificates.

Certificate contents require individual connection parameters and sslmode: verify-full. If omitted, SSL mode defaults to verify-full when certificate contents are supplied. The server certificate must match the configured host. These options cannot be combined with dsn or database_url.

Keep the private key out of Git. Store credentials using project variables, which follow the existing project credential access controls, including authorized rill env pull access. Base64 is an encoding, not encryption. Rotate credentials by updating the corresponding project variables.

For local or self-hosted deployments, a DSN can instead specify sslrootcert, sslcert, and sslkey file paths. Those paths must be readable by the Rill process; local certificate files are not automatically provisioned into Rill Cloud.

Create Your First Model​

Once your connector is configured using any method above, create a model to define what data to pull.

Create models/postgres_data.yaml:

type: model
connector: postgres
dev:
  sql: SELECT * FROM my_table limit 10000

sql: SELECT * FROM my_table

After creating the model, you can add additional model settings directly to the file.

Separating Dev and Prod Environments​

When ingesting data locally, consider setting parameters in your connector file to limit how much data is retrieved, since costs can scale with the data source. This also helps other developers clone the project and iterate quickly by reducing ingestion time.

For more details, see our Dev/Prod setup docs.

Deploy to Rill Cloud​

When deploying a project to Rill Cloud, Rill requires you to explicitly provide the PostgreSQL connection credentials with access to PostgreSQL used in your project. Please refer to our connector YAML reference docs for more information.

If you subsequently add sources that require new credentials (or if you simply entered the wrong credentials during the initial deploy), you can update the credentials by pushing the Deploy button to update your project or by running the following command in the CLI:

rill env push
Did you know?

If you've already configured credentials locally (in your <RILL_PROJECT_DIRECTORY>/.env file), you can use rill env push to push these credentials to your Rill Cloud project. This will allow other users to retrieve and reuse the same credentials automatically by running rill env pull.