Security

pg_ripple provides multiple layers of security: PostgreSQL's native authentication and authorization, named-graph row-level security (RLS), SQL injection prevention through dictionary encoding, and secure configuration of the pg_ripple_http companion service.

Authentication and Authorization

pg_ripple relies entirely on PostgreSQL's built-in authentication (pg_hba.conf) and role-based access control. There is no separate user database.

Minimum Privileges for SPARQL Queries

-- Create a read-only role
CREATE ROLE sparql_reader LOGIN PASSWORD 'strong_password';
GRANT USAGE ON SCHEMA pg_ripple TO sparql_reader;
GRANT USAGE ON SCHEMA _pg_ripple TO sparql_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA _pg_ripple TO sparql_reader;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pg_ripple TO sparql_reader;

Minimum Privileges for Data Loading

-- Create a writer role
CREATE ROLE sparql_writer LOGIN PASSWORD 'strong_password';
GRANT USAGE ON SCHEMA pg_ripple TO sparql_writer;
GRANT USAGE ON SCHEMA _pg_ripple TO sparql_writer;
GRANT SELECT, INSERT, DELETE ON ALL TABLES IN SCHEMA _pg_ripple TO sparql_writer;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA _pg_ripple TO sparql_writer;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pg_ripple TO sparql_writer;

ALTER DEFAULT PRIVILEGES IN SCHEMA _pg_ripple
  GRANT SELECT ON TABLES TO sparql_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA _pg_ripple
  GRANT SELECT, INSERT, DELETE ON TABLES TO sparql_writer;

Named-Graph Row-Level Security

pg_ripple supports fine-grained access control at the named-graph level using PostgreSQL's row-level security (RLS) infrastructure. This allows different users to see different subsets of the knowledge graph.

Enabling Graph RLS

-- Enable RLS on all VP tables
SELECT pg_ripple.enable_graph_rls();

This creates RLS policies on every VP table (including vp_rare) that filter rows based on the g (graph) column.

Granting Graph Access

-- Grant a role access to a specific named graph
SELECT pg_ripple.grant_graph('sparql_reader', 'http://example.org/confidential');

-- Grant access to the default graph (g = 0)
SELECT pg_ripple.grant_graph('sparql_reader', '');

-- Grant access to all graphs
SELECT pg_ripple.grant_graph('sparql_reader', '*');

Revoking Graph Access

-- Revoke access to a specific graph
SELECT pg_ripple.revoke_graph('sparql_reader', 'http://example.org/confidential');

How It Works

When graph RLS is enabled:

1. Each VP table gets an RLS policy that checks the g column against the user's allowed graph IDs
2. The dictionary encodes graph IRIs to i64 identifiers
3. An internal mapping table (_pg_ripple.graph_grants) stores (role, graph_id) pairs
4. PostgreSQL enforces the policy transparently — SPARQL queries automatically filter results

Example: Multi-Tenant Knowledge Graph

-- Create tenant roles
CREATE ROLE tenant_a LOGIN PASSWORD 'pw_a';
CREATE ROLE tenant_b LOGIN PASSWORD 'pw_b';

-- Grant base access
GRANT USAGE ON SCHEMA pg_ripple TO tenant_a, tenant_b;
GRANT USAGE ON SCHEMA _pg_ripple TO tenant_a, tenant_b;
GRANT SELECT ON ALL TABLES IN SCHEMA _pg_ripple TO tenant_a, tenant_b;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pg_ripple TO tenant_a, tenant_b;

-- Enable graph RLS
SELECT pg_ripple.enable_graph_rls();

-- Tenant A sees only their graph
SELECT pg_ripple.grant_graph('tenant_a', 'http://example.org/tenant-a');

-- Tenant B sees only their graph
SELECT pg_ripple.grant_graph('tenant_b', 'http://example.org/tenant-b');

-- Both see shared reference data
SELECT pg_ripple.grant_graph('tenant_a', 'http://example.org/shared');
SELECT pg_ripple.grant_graph('tenant_b', 'http://example.org/shared');

Now SPARQL queries run by tenant_a will only see triples in tenant-a and shared graphs, with no application-level filtering required.

SQL Injection Prevention

pg_ripple's architecture provides strong defense against SQL injection by design.

Dictionary Encoding as a Security Layer

All SPARQL queries go through a multi-step translation pipeline:

1. Parse: SPARQL text is parsed by spargebra into an abstract algebra tree
2. Encode: All bound constants (IRIs, literals) are dictionary-encoded to i64 integers before SQL generation
3. Generate: SQL is constructed using parameterized queries with integer placeholders
4. Execute: SQL runs via pgrx::SpiClient with bound parameters

Table Name Safety

VP table references use OID lookups from _pg_ripple.predicates, not string concatenation:

#![allow(unused)]
fn main() {
// Internal: table names are never interpolated from user input
let table_oid = predicates::get_table_oid(predicate_id)?;
// SQL uses the OID directly: FROM pg_class WHERE oid = $1
}

User-Facing Function Safety

Functions that accept text input (like pg_ripple.sparql()) parse the SPARQL text through spargebra, which rejects anything that is not valid SPARQL. No raw SQL is passed through.

File-Path Loaders and Superuser Requirement

Functions that read from the server's filesystem require superuser privileges:

Safe Bulk Load Pattern

-- As superuser: load the data
SELECT pg_ripple.load_turtle_file('/data/import/dataset.ttl');

-- As superuser: grant access to the app role
GRANT SELECT ON ALL TABLES IN SCHEMA _pg_ripple TO app_role;

pg_ripple_http Security

The pg_ripple_http companion service exposes a SPARQL Protocol endpoint over HTTP. Secure it appropriately.

TLS Configuration

Always run pg_ripple_http behind TLS in production:

# pg_ripple_http.toml
[server]
bind = "0.0.0.0:8443"
tls_cert = "/etc/ssl/certs/pg_ripple_http.crt"
tls_key = "/etc/ssl/private/pg_ripple_http.key"

Authentication

Configure pg_ripple_http to authenticate incoming requests:

[auth]
# HTTP Basic authentication backed by PostgreSQL roles
method = "pg_role"
# Or use a static API key
# method = "api_key"
# api_key = "your-secret-key-here"

With pg_role authentication, HTTP Basic credentials are forwarded to PostgreSQL. Graph RLS policies apply to the authenticated role.

Reverse Proxy Setup

For production, place pg_ripple_http behind a reverse proxy:

# nginx configuration
server {
    listen 443 ssl;
    server_name sparql.example.org;

    ssl_certificate /etc/ssl/certs/sparql.crt;
    ssl_certificate_key /etc/ssl/private/sparql.key;

    location /sparql {
        proxy_pass http://127.0.0.1:8080;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;

        # Rate limiting
        limit_req zone=sparql burst=20 nodelay;
    }
}

CORS Configuration

If the SPARQL endpoint is accessed from browser applications:

[cors]
allowed_origins = ["https://app.example.org"]
allowed_methods = ["GET", "POST"]
allowed_headers = ["Content-Type", "Authorization"]
max_age = 3600

Network Isolation

Production Topology

┌─────────────┐     TLS      ┌──────────────────┐     Unix socket     ┌─────────────┐
│  Clients    │ ────────────→ │  pg_ripple_http   │ ──────────────────→ │ PostgreSQL  │
│             │               │  (reverse proxy)  │                     │ (pg_ripple) │
└─────────────┘               └──────────────────┘                     └─────────────┘

Recommendations

1. PostgreSQL: bind to localhost or a private network interface only. Never expose port 5432 to the public internet.

# postgresql.conf
listen_addresses = '127.0.0.1'

2. pg_ripple_http: connect to PostgreSQL via Unix socket for lowest latency and no network exposure.

3. Firewall rules: only allow traffic on the HTTPS port (443) from expected client networks.

# iptables example
iptables -A INPUT -p tcp --dport 443 -s 10.0.0.0/8 -j ACCEPT
iptables -A INPUT -p tcp --dport 443 -j DROP
iptables -A INPUT -p tcp --dport 5432 -j DROP

4. pg_hba.conf: restrict connections by source IP and authentication method:

# TYPE  DATABASE  USER              ADDRESS        METHOD
local   all       postgres                         peer
host    mydb      pg_ripple_http    127.0.0.1/32   scram-sha-256
host    mydb      sparql_reader     10.0.0.0/8     scram-sha-256
host    all       all               0.0.0.0/0      reject

Security Checklist

Planned v1.0.0 External Audit

After v0.132.0, the project plans an independent security audit covering the Rust extension, HTTP companion, SQL migrations, Docker images, and Helm chart. The requested review scope includes OWASP ASVS level 2, PostgreSQL extension attack surfaces, SSRF, injection, authentication bypass, and privilege escalation. The engagement brief and deliverables checklist are tracked in audit/engagement-brief.md.

The audit is planned to start within four weeks of the v0.132.0 tag. Its report and remediation confirmation are release evidence for v1.0.0; this section is not a claim that the audit has completed.

Audit Logging

Enable PostgreSQL's logging to maintain an audit trail:

# postgresql.conf
log_statement = 'all'          # or 'ddl' for schema changes only
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p] %u@%d '

For fine-grained audit logging, consider the pgaudit extension alongside pg_ripple.

Log-Hook Audit and Secret Suppression (LOG-HOOK-01)

v0.76.0 audit finding: No RegisterEmitLogHook is installed to suppress secrets from PostgreSQL error messages. A defense-in-depth audit was performed to verify that no error or warning call site leaks raw secrets.

Audit Results

The following audit was performed on all pgrx::error!(), pgrx::warning!(), tracing::error!(), and tracing::warn!() call sites in the pg_ripple extension and pg_ripple_http companion service:

No RegisterEmitLogHook Required

Based on the audit, no error path in the current codebase logs raw HMAC keys, connection strings, bearer tokens, or other credentials. A RegisterEmitLogHook is therefore not required at this time.

If you add a new error path that touches secrets, follow these guidelines:

• Do not interpolate secret values (token, key, password) into pgrx::error!() or pgrx::warning!() messages.
• Use descriptive error types without values (e.g., "HMAC key error: invalid key length" not "bad key: {key_value}").
• If a future release handles user-supplied credentials in a hot path, consider installing a RegisterEmitLogHook to scrub sensitive patterns from log lines.

Connection Strings

PostgreSQL connection strings (DSN) containing passwords may appear in pg_log via log_connections = on. Use pg_hba.conf with scram-sha-256 and a connection pooler (e.g., PgBouncer) that authenticates separately so application DSNs never include inline passwords.

Supported Authentication Schemes (S13-10, v0.86.0)

pg_ripple_http accepts Bearer token authentication only.

Recommendation: rotate the Bearer token regularly. Use a 32-byte (256-bit) or larger random secret:

openssl rand -base64 32

Set the token as an environment variable or via Docker secrets — never hardcode it in docker-compose.yml or configuration files.

Metrics Port Isolation (S13-09, v0.86.0)