Skip to main content

Connecting to Postgres

The following guide shows how to connect Veezoo to a PostgreSQL database using a dedicated read-only user.

Prerequisites​

Make sure you have the following details ready:

  • PostgreSQL host name or IP address
  • Port number (default: 5432)
  • Database name you want to expose to Veezoo
  • Credentials for a PostgreSQL user with read-only access (see next section)

Creating a Read-Only PostgreSQL User​

We recommend creating a dedicated user for Veezoo with the minimum permissions needed to read data. Run the commands below from psql or similar tool as a user with permissions to create users (for example, postgres). Replace the placeholders (your_database, your_schema etc) with the values that match your environment.

Save the credentials for the user in a password manager so it's stored securely. 

CREATE ROLE veezoo_readonly WITH LOGIN PASSWORD 'choose_a_strong_password';

-- Allow the user to connect to the database the data lives in.
GRANT CONNECT ON DATABASE your_database TO veezoo_readonly;

-- Switch to the target database to grant schema-level privileges. If not using psql, switch database in the preferred way of your tool.
\c your_database

-- Allow usage of the schema that contains the tables you want to expose.
GRANT USAGE ON SCHEMA your_schema TO veezoo_readonly;

-- Grant read access to existing tables.
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO veezoo_readonly;

-- Ensure future tables become readable automatically.
ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema
    GRANT SELECT ON TABLES TO veezoo_readonly;

If you need to expose multiple schemas, repeat the GRANT and ALTER DEFAULT PRIVILEGES statements for each schema, or create a different user.

Configuring the Connection in Veezoo​

Follow the UI steps described in Connect to a SQL database. Use the host, port and the read-only credentials created above when filling out the form.