PostgreSQL

Guide to set up your PostgreSQL database with Velvet

In this guide, we outline the steps required to securely connect your PostgreSQL database to your Velvet workspace. You may be able to skip some of these steps if you've previously connected your database to other services.

Depending on whether you're starting a new project from scratch, or connecting a database that's already configured for a third-party tool - this will take between 10 minutes and 2 hours.

  1. Create a read-only user
  2. Set up a static IP address (guide here)
  3. Identify and copy your connection string
  4. Configure which database tables you want to connect with Velvet

1. Create a read-only user

It's best practice to create a user with read-only permissions for products like Velvet. Create a user with read-only permissions in Postgres.

Connect to your database and run the following SQL statements. These statements create a velvet_readonly user with the password a very good password and gives them the velvet_read_all_data role.

PostgreSQL v14 includes a pg_read_all_data role. Run SELECT version() in PostgreSQL to determine your version.

Read-only user configuration:

-- Create a new user with read-only access to the database
-- Recommend to run each step one by one, as some steps may
-- fail if the user does not have the necessary privileges.

-- Step 1: Create the user
CREATE USER velvet_readonly WITH LOGIN PASSWORD 'your_secure_password';

-- Step 2: Grant connect privilege to the database 
-- (replace your_database_name with the actual name - on supabase it's postgres)
GRANT CONNECT ON DATABASE postgres TO velvet_readonly;

-- Step 3: Grant usage on schemas. This allows the user to see the schemas
GRANT USAGE ON SCHEMA public TO velvet_readonly;
GRANT USAGE ON SCHEMA auth TO velvet_readonly; -- optional

-- Step 4: Grant select on all tables and views in the schemas
GRANT SELECT ON ALL TABLES IN SCHEMA public TO velvet_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA auth TO velvet_readonly;

-- Step 5: Ensure future tables and views in these schemas are also accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO velvet_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT SELECT ON TABLES TO velvet_readonly;

-- [OPTIONAL] Step 6: Bypass RLS policies
ALTER ROLE velvet_readonly BYPASSRLS;

Click Run and execute query.

Once you've created a read-only user, move on to the next step.

2. Set up a static IP address

Set up a static IP using our static IP configuration guide.

3. Identify and copy your connection string

Find your database connection string to paste it into your Velvet workspace config. If you already have a connection string, jump ahead to section #4.

Or, follow the guide below for building your connection string for the first time.

Build a connection string

Read our general guide on URI construction here. The PostgreSQL-specific overview is below.

As you build your connection string ensure all characters that require encoding are converted to their percent encoded counterparts.

Characters to percent-encode:

  • %%25
  • &%26
  • /%2F
  • :%3A
  • =%3D
  • ?%3F
  • @%40
  • [%5B
  • ]%5D

When building your PostgreSQL connection string, you have the flexibility to choose between two schema identifiers: postgresql or postgres.

postgresql://

postgres://

Following the schema identifier is the userspec, an optional component. The userspec allows you to include the username and password in the following format:

postgresql://username:password@

If you prefer to specify only the username, you can use:

postgresql://username@

The hostspec, also optional, is comprised of the host (a name resolved by an external name system like DNS, an IP address or other direct address) and an optional port (port number on which the database server is running).

For a single host connection, the format is:

postgresql://username:password@host:port

If you need to connect to multiple hosts, you can use a comma-separated list, like so:

postgresql://username:password@host1:port1,host2:port2

The database name is required and is provided after the hostspec. The database name begins with a forward slash.

postgresql://username:password@host:port/database

You can also include additional parameters to customize the connection. These parameters are appended to the connection string and start with a question mark (?).

postgresql://username:password@host:port/database?sslmode=required

Common parameters:

  • sslmode
  • timezone
  • application_name
  • connect_timeout
  • keepalives
  • options

Example URIs

  1. Single Host with Username and Password

    postgresql://your_username:your_password@localhost:5432/your_database

  2. Single Host with Only Username

    postgresql://your_username@localhost:5432/your_database

  3. Multiple Hosts with Username and Password

    postgresql://your_username:your_password@host1:5432,host2:5432/your_database

  4. Multiple Additional Parameters

    postgresql://your_username:your_password@localhost:5432/your_database?sslmode=require&timezone=UTC&application_name=my_app&connect_timeout=10

Once done, copy your connection string to paste it into Velvet.

4. Configure your database and tables on Velvet

Configure PostgreSQL as a remote database connection and select which tables you want connected.

  1. Tap into a workspace in your Velvet dashboard
  2. Tap "Add a data source"
  3. Select type "Remote database"
  4. Name your database
  5. Paste in your database URI. Note: After providing your database credentials for the first time, we manage the connection using ClickHouse. If required, you can add ClickHouse's Static IPs to an allowlist - Read their API documentation here.
  1. Select and configure the table you want to connect as a source.
  1. Name your table and create the source.
  1. Repeat steps 6 and 7 to add each table you want included as a data source.
  2. You can now view, search, and filter data inside your database source.

Great job, you’ve connected your internal database as a data source. We’ll capture data from your connected tables so you can query them in your Velvet workspace.

Email [email protected] for support or feedback.