Render

Guide to set up your Render database with Velvet

In this guide, we outline the steps required to securely connect your Render 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 read-only user
  2. Set up static IP
  3. Add connection string
  4. Configure tables

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 Render.

Navigate to your Render account dashboard and identify your database, click the three dots to the right and click on “Settings.

From your database dashboard, click connect. Switch to the external connection tab and copy the “PSQL Command.” You will paste this in your terminal. This will open up a psql session to your database.

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

For PostgreSQL v14 and above

-- Create a "velvet_readonly" user and associate the "pg_read_all_data" role.
-- We give the user BYPASSRLS privileges in order to introspect the db structure.
CREATE USER velvet_readonly WITH PASSWORD 'a very good password' BYPASSRLS;
GRANT pg_read_all_data TO velvet_readonly;
ALTER ROLE velvet_readonly SET statement_timeout = 0;

For PostgreSQL v13 and below

-- Create a "velvet_read_all_data" role, find all the user generated schemas,
-- and associate the correct grants.
CREATE ROLE velvet_read_all_data;
DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema'
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO velvet_read_all_data $$, sch);
        EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO velvet_read_all_data $$, sch);
        EXECUTE format($$ GRANT SELECT ON ALL SEQUENCES IN SCHEMA %I TO velvet_read_all_data $$, sch);
        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO velvet_read_all_data $$, sch);
        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON SEQUENCES TO velvet_read_all_data $$, sch);
    END LOOP;
END;
$do$;
-- Create a "velvet_readonly" user and associate the "velvet_read_all_data" role.
-- We give the user BYPASSRLS privileges in order to introspect the db structure.
CREATE USER velvet_readonly WITH PASSWORD 'a very good password' BYPASSRLS;
GRANT velvet_read_all_data TO velvet_readonly;
ALTER ROLE velvet_readonly SET statement_timeout = 0;

Alternate solution: Create a read replica

Instead of creating a read-only user, you can create a read replica. You must be on Render's standard database plan (or above) to unlock this functionality.

Navigate to your Render account dashboard and identify your database, click the three dots to the right and click on “Settings."

Under “General” settings, click on “Add Read Replica.” You can now use this connection string to connect to Velvet.

2. Set up static IP

Configure network restrictions to allow Velvet's static IP addresses. Follow our static IP configuration guide.

3. Identify and copy your connection string

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

If you already have a connection string - copy it and skip to step #4.

Build a connection string

Navigate to your Render account dashboard and identify your database. Click the three dots to the right and click on “Settings"

From your database dashboard, click "connect". Switch to the external connection tab and copy the “External Database URL.”

Additionally, you'll need to add an options object to the ssl param at the end of your External Database URL. The connection object:

{
	"rejectUnauthorized":false, 
	"servername":"dpg-xxxxxxxxxxxxxxxxxxxxx-a.oregon-postgres.render.com"
}

The syntax results in: External connection URL, ssl param, connection object.

Example connection url:

postgres://user:[email protected]/mydatabase?ssl={"rejectUnauthorized":false, "servername":"dpg-xxxxxxxxxxxxxxxxxxxxx-a.oregon-postgres.render.com"}

4. Configure your database and tables on Velvet

Configure Render 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.