Connecting PostgreSQL to elvex

This guide provides specific instructions for preparing a PostgreSQL database for connection to elvex. For general principles and best practices, please refer to the Preparing Your Database for Connection to elvex guide.

Before You Begin

Before connecting your PostgreSQL database to elvex, you should:

Have administrator access to your PostgreSQL database or work with someone who does
Understand which tables and data you want to make accessible
Identify which users or groups will need access to this data through elvex
Consider any data security or compliance requirements that apply to your data

Step 1: Create a Dedicated Service Account

For optimal security, create a dedicated service account that elvex will use to connect to your PostgreSQL database:

CREATE USER elvex_service WITH PASSWORD 'strong_password_here';

Step 2: Set Appropriate Permissions

Restrict the service account to only the permissions it needs:

-- Grant read-only access to specific tables
GRANT SELECT ON table_name TO elvex_service;
GRANT SELECT ON another_table TO elvex_service;

-- Grant read-only access to all tables in a specific schema
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO elvex_service;

Step 3: Create Views for Complex Data Models (Optional)

If your data model is complex or you want to simplify access, consider creating views:

-- Create a view to summarize customer information
CREATE VIEW customer_summary AS
SELECT
    c.customer_id,
    c.name,
    c.email,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.order_id) AS order_count
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id, c.name, c.email;

-- Grant read-only access to the view
GRANT SELECT ON customer_summary TO elvex_service;

Step 4: Add Descriptions to Tables and Columns (Recommended)

Adding descriptions to your tables and columns will help elvex's AI better understand your data:

-- Add a comment to the 'customers' table
COMMENT ON TABLE customers IS 'Customer information including contact details';

-- Add comments to specific columns in the 'customers' table
COMMENT ON COLUMN customers.customer_id IS 'Unique identifier for each customer';
COMMENT ON COLUMN customers.name IS 'Customer full name';

Step 5: Prepare Connection Information

Gather the following information which you'll need when setting up the connection in elvex:

Host name or IP address
Port (default is 5432)
Database name
Username (service account)
Password

Step 6: Test the Connection (Optional)

Before configuring elvex, test that your service account can connect to the database and access the intended tables:

psql -h hostname -p 5432 -d database_name -U elvex_service -c "SELECT * FROM table_name LIMIT 5;"

Step 7: Configure the Connection in elvex

Once you've completed the preparation steps, you're ready to configure the PostgreSQL connection in elvex:

In elvex, navigate to the Datasources section
Click "Create New Datasource"
Select "Database Connection" as the datasource type
Choose "PostgreSQL" as the database type
Enter the connection details you gathered in Step 6
Select the tables you want to include in your datasource
Review the schema and add any additional descriptions if needed
Configure access permissions for the datasource
Save the datasource

PostgreSQL-Specific Considerations

Materialized Views

PostgreSQL supports materialized views, which can be useful for caching complex query results. elvex can query materialized views just like regular tables:

-- Create a materialized view to summarize sales information
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_revenue
FROM
    sales
GROUP BY
    product_id;

-- Grant read-only access to the materialized view
GRANT SELECT ON sales_summary TO elvex_service;

JSON Data

If your PostgreSQL database contains JSON data, elvex can query it using PostgreSQL's JSON functions. Make sure to add clear descriptions to these columns to help the AI understand the structure of the JSON data.

Connection Pooling

For production environments with many users, consider using connection pooling (like PgBouncer) to manage database connections efficiently.

Security Best Practices for PostgreSQL

In addition to the general security best practices, consider these PostgreSQL-specific recommendations:

Use SSL/TLS encryption for connections by configuring ssl = on in your postgresql.conf file
Consider using client certificate authentication for additional security
Implement row-level security policies if you need fine-grained access control
Regularly audit your PostgreSQL logs for unexpected query patterns

Troubleshooting PostgreSQL Connections

Common Issues

Connection refused errors: Check that your firewall allows connections from elvex's IP addresses
Authentication failures: Verify the service account username and password
Permission errors: Ensure the service account has SELECT permissions on all required tables
Schema not found: Verify the schema name is correct and the service account has usage permissions on the schema

Checking Permissions

To verify the permissions of your service account:

-- Check table permissions for 'elvex_service'
SELECT
    grantee,
    privilege_type,
    table_name
FROM
    information_schema.table_privileges
WHERE
    grantee = 'elvex_service';

-- Check schema permissions for 'elvex_service'
SELECT
    nspname,
    privilege_type,
    grantee
FROM
    pg_namespace n
JOIN
    information_schema.usage_privileges u ON n.nspname = u.object_name
WHERE
    u.grantee = 'elvex_service';

Next Steps

After successfully connecting your PostgreSQL database to elvex, you can:

Create assistants that use this datasource
Test natural language queries against your database
Share the assistants with appropriate users in your organization

How to connect to PostgreSQL