pgvector is a PostgreSQL extension that adds vector similarity search to PostgreSQL. Gateco treats pgvector as a Tier 1 connector — supporting full ingestion, retroactive registration, and RAG access control. This guide covers identity-aware retrieval setup for self-hosted and cloud deployments, adding vector database RBAC via dedicated pgvector access control permissions.
Step 1 — Enable the pgvector extension
Connect to your target database as a superuser and run:
sql
CREATE EXTENSION IF NOT EXISTS vector;
-- Verify the version
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';pgvector must be installed on the PostgreSQL server before running CREATE EXTENSION. On Ubuntu/Debian: apt install postgresql-16-pgvector. On RHEL/Amazon Linux: dnf install pgvector_16. For cloud providers (RDS, Cloud SQL), enable the extension from the console.
Step 2 — Create a dedicated database user
sql
-- Create the user
CREATE USER gateco WITH PASSWORD 'your-strong-password';
-- Allow connection to the database
GRANT CONNECT ON DATABASE your_database TO gateco;
-- Allow usage of the public schema
GRANT USAGE ON SCHEMA public TO gateco;
-- For ingestion (insert + read)
GRANT SELECT, INSERT, UPDATE, DELETE ON your_vector_table TO gateco;
-- For retrieval-only mode (read access only)
-- GRANT SELECT ON your_vector_table TO gateco;
-- Required for sequence access if using serial/bigserial IDs
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO gateco;Step 3 — Add the connector in Gateco
- Navigate to Connectors → Add connector → pgvector.
- Enter your PostgreSQL connection string.
- Click Test connection. Gateco introspects your schema and lists available vector tables.
- Click Save.
text
postgresql://gateco:password@your-host:5432/your_database?sslmode=requireStep 4 — Configure search settings
- In Connectors, click your pgvector connector → Search config.
- Set the Vector table name, Embedding column, and Content column.
- For hybrid search, also set text_search_config (default: english).
- Save.
Search configuration reference
| Field | Example | Description |
|---|---|---|
table_name | embeddings | Table containing vector data |
embedding_column | embedding | Column of type vector(n) |
content_column | content | Text column for keyword/hybrid search |
id_column | id | Primary key (default: id) |
text_search_config | english | PostgreSQL text search config for keyword/hybrid mode |
metadata_columns | ["doc_id","labels"] | Columns to expose as policy metadata |
Troubleshooting
| Error | Cause | Fix |
|---|---|---|
could not load library "vector" | pgvector not installed on the server | Install the pgvector package for your PostgreSQL version and run CREATE EXTENSION |
permission denied | Insufficient grants | Run the GRANT statements from Step 2 for the gateco user |
connection refused | Firewall or pg_hba.conf restriction | Add Gateco's IP to pg_hba.conf and allow port 5432 in your firewall rules |