PostgreSQL
Guide to connect PostgreSQL to Velvet
Connect your PostgreSQL database to your Velvet workspace. You can skip some steps if you've previously connected to other services.
1. Create a read-only user
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.
2. Set static IPs
Configure network restrictions to allow Velvet's static IP addresses. Follow our static IP configuration guide.
3. Build your connection string
Read our general guide on URI construction here. The Postgres-specific overview is below.
If you already have a connection string - copy it and skip to step #4.
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
-
Single Host with Username and Password
postgresql://your_username:your_password@localhost:5432/your_database
-
Single Host with Only Username
postgresql://your_username@localhost:5432/your_database
-
Multiple Hosts with Username and Password
postgresql://your_username:your_password@host1:5432,host2:5432/your_database
-
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. Connect your database
Connect your database to Velvet using your connection string.
- Tap into a workspace in your Velvet dashboard
- Tap "Add a data source"
- Select type "Remote database"
- Name your database
- Paste in your database URI
- Press create
If you run into any errors, schedule a call or email [email protected].
Once your database is connected, you're ready to use the AI SQL editor. Close out of the modal to start using the editor on top of your database.
Schema mapping is optional, and only required if you want to query multiple sources at the same time.
5. [optional] Schema mapping for unified sources
This configuration is only required to query multiple data sources at the same time. For example - two databases at the same time, or stripe webhooks alongside your database tables.
You can set up this unified mapping any time.
Add schema mapping context
After you connect your database, you'll be prompted to configure the schema for each table you set up.
Name your table to create this source.
Repeat these schema mapping steps for each table you want included as a unified data source.
Get help
Email [email protected] if you need additional support.
Updated 18 days ago