pgviewctl

pgviewctl is a CLI tool for managing PostgreSQL access by auto-generating SQL to create views that safely expose only selected tables and columns. It leverages the underlying capability of PostgreSQL views to securely present just the data you want to external users and never your raw tables. This is especially useful when you need to provide analytics tools like Hex, Metabase, and others with access to your database, while keeping their access strictly limited to sanitized, read-only views you define.

Overview

pgviewctl generates idempotent SQL that:

1. Creates a schema for views
2. Creates standard views exposing only specific columns from source tables
3. Creates a database role with a password
4. Revokes all access from source schemas (least privilege)
5. Grants SELECT-only access on the views

All generated SQL is safe to run multiple times - great for CI/CD pipelines.

Installation

go build -o pgviewctl .

Configuration

Create an access.yaml file to define what data to expose and to whom.

Configuration Reference

Field	Required	Description
target_schema	Yes	Target schema where all views will be created
user_env	Yes	Environment variable name containing the username for the role to create
password_env	Yes	Environment variable name containing the password for the role to create
tables	Yes	List of table configurations (at least one required)
tables[].name	Yes	Name for the view in the target schema
tables[].source	Yes	Fully qualified source table (schema.table)
tables[].columns	Yes	List of columns to expose (at least one required)

Field Details

• target_schema: The namespace where views are created. The external client role created will only have access to this schema, not the source schemas.

• user_env / password_env: Names of environment variables (not the actual values). Must be uppercase. This keeps secrets out of config files so they can be safely committed to Git.

• tables[].source: Can reference tables in any schema (public.customers, analytics.events, reporting.sales, etc.). All views are created in the single target schema regardless of where source tables live.

• tables[].columns: Only these columns are exposed in the view. Any column not listed is completely inaccessible to the external client.

Example Configuration

# Target schema where all views will be created
target_schema: api

# Environment variables for the role credentials
# Set before running: export DB_SERVICE_USER=external_service
#                     export DB_SERVICE_PASSWORD=secure_password
user_env: DB_SERVICE_USER
password_env: DB_SERVICE_PASSWORD

tables:
  # Simple case - lowercase identifiers
  - name: customers
    source: public.customers
    columns: [id, name, city, created_at]

  # Mixed-case table and column names (automatically quoted)
  - name: orders
    source: public.Orders
    columns: [id, customerId, totalAmount, orderDate]

  # Source from a different schema
  - name: user_profiles
    source: analytics.UserProfiles
    columns: [UserId, Email, CreatedAt]

Usage

Preview SQL (dry run)

export DB_SERVICE_USER=external_service
export DB_SERVICE_PASSWORD=secretpassword

pgviewctl --config access.yaml --dry-run

Apply to database

Using a connection URL:

pgviewctl --config access.yaml --database-url $DATABASE_URL

Using individual parameters:

pgviewctl --config access.yaml \
  --host my-primary.rds.amazonaws.com \
  --port 5432 \
  --database mydb \
  --user admin \
  --password $ADMIN_PASSWORD

CLI Flags

Flag	Description
--config, -c	Path to access.yaml (required)
--dry-run	Print SQL without executing
--drop-existing-views	Drop all existing views in target schema before creating (default: false)
--database-url	PostgreSQL connection string
--host	Database host
--port	Database port (default: 5432)
--database	Database name
--user	Admin user to run migrations
--password	Admin password

Warning: The --drop-existing-views flag will delete ALL views in the target schema, including views not managed by pgviewctl. Use with caution.

Generated SQL Example

-- 1. Create schema (IF NOT EXISTS = idempotent)
CREATE SCHEMA IF NOT EXISTS api;

-- 2. Create views (OR REPLACE = idempotent)
CREATE OR REPLACE VIEW api.customers AS
SELECT id, name, city, created_at
FROM public.customers;

CREATE OR REPLACE VIEW api.orders AS
SELECT id, "customerId", "totalAmount", "orderDate"
FROM public."Orders";

-- 3. Create role (IF NOT EXISTS check = idempotent)
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'external_service') THEN
    CREATE ROLE external_service LOGIN PASSWORD 'xxx';
  END IF;
END $$;

-- 4. Revoke all from source schemas (idempotent)
REVOKE ALL ON SCHEMA public FROM external_service;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM external_service;

-- 5. Grant access (idempotent)
GRANT CONNECT ON DATABASE current_database() TO external_service;
GRANT USAGE ON SCHEMA api TO external_service;
GRANT SELECT ON api.customers TO external_service;
GRANT SELECT ON api.orders TO external_service;

Workflow

1. Edit access.yaml
2. Commit to Git (audit trail)
3. Run: pgviewctl --config access.yaml --database-url $PRIMARY_URL
4. Views + role + grants created on primary
5. Replicated to read replica automatically
6. Give external service the replica connection string

Adding a new column

tables:
  - name: customers
    source: public.customers
    columns: [id, name, city, created_at, email]  # added email

pgviewctl --config access.yaml --database-url $PRIMARY_URL
# View is replaced, external service now sees email column

Identifier Quoting

The tool automatically handles mixed-case PostgreSQL identifiers:

• Lowercase identifiers: used as-is (customers)
• Mixed-case identifiers: quoted ("customerId", "Orders")

This ensures the generated SQL works correctly with any naming convention.

Least Privilege

The tool follows the principle of least privilege:

1. Revokes ALL access from source schemas first
2. Grants only USAGE on the view schema
3. Grants only SELECT on specific views
4. No access to underlying tables

The external service can only read the columns you explicitly expose.

Running Tests

The project includes end-to-end tests that use testcontainers to spin up a real PostgreSQL instance.

# Run all tests
go test ./...

# Run e2e tests with verbose output
go test -v ./e2e/...

# Run tests without cache
go test -count=1 ./e2e/...

Note: Docker must be running for the e2e tests to work.