PostgreSQL — Dynamic Credentials Engine

Generates temporary PostgreSQL users with scoped privileges and automatic revocation.

Overview

The PostgreSQL engine creates short-lived database credentials on demand. When an application requests credentials, the engine creates a PostgreSQL role with specific privileges (SELECT, INSERT, etc.) and a VALID UNTIL expiry. When the lease expires or is revoked, the engine drops the role and reassigns any owned objects.

The plugin communicates with Arcan core via JSON over stdin/stdout. It does NOT connect to PostgreSQL directly -- the core holds the root DSN and executes SQL on the plugin's behalf via ctx.SQL / ctx.SQLExec host functions.

External system: PostgreSQL 12+

Capabilities

engine:dynamic_credentials -- create and revoke temporary database roles
engine:root_rotation -- rotate the root admin password

Engine Descriptor

{
  "name": "postgres",
  "version": "0.1.0",
  "display_name": "PostgreSQL",
  "description": "Dynamic credentials for PostgreSQL databases",
  "sdk_version": 1,
  "min_core_version": "0.1.0",
  "capabilities": [
    "host:sql",
    "host:store:read",
    "host:store:write",
    "host:audit",
    "engine:dynamic_credentials",
    "engine:root_rotation"
  ],
  "tier": "official",
  "config_schema": {
    "type": "object",
    "properties": {
      "dsn": {
        "type": "string",
        "description": "PostgreSQL connection string",
        "format": "uri",
        "example": "postgres://arcan_admin:[email protected]:5432/myapp?sslmode=require"
      },
      "max_ttl": {
        "type": "string",
        "description": "Maximum lease TTL (Go duration)",
        "default": "24h"
      },
      "default_ttl": {
        "type": "string",
        "description": "Default lease TTL if not specified",
        "default": "1h"
      },
      "admin_role": {
        "type": "string",
        "description": "PostgreSQL role that owns generated roles",
        "default": "arcan_admin"
      },
      "max_connections": {
        "type": "integer",
        "description": "Max concurrent leased credentials",
        "default": 50
      }
    },
    "required": ["dsn"]
  },
  "default_roles": [
    { "name": "readonly", "config": { "grants": "SELECT" } },
    { "name": "readwrite", "config": { "grants": "SELECT, INSERT, UPDATE, DELETE" } },
    { "name": "admin", "config": { "grants": "ALL PRIVILEGES" } }
  ]
}

Configuration

What the admin provides during arcan plugin setup postgres:

Parameter	Required	Description	Example
`dsn`	Yes	PostgreSQL connection string	`postgres://arcan_admin:pass@db:5432/myapp?sslmode=require`
`max_ttl`	No	Maximum lease duration (default: 24h)	`72h`
`default_ttl`	No	Default lease duration (default: 1h)	`4h`
`admin_role`	No	Owner role for generated roles (default: arcan_admin)	`arcan_admin`
`max_connections`	No	Max concurrent leased credentials (default: 50)	`100`

Bootstrap

During setup, the core connects to PostgreSQL using the provided DSN and verifies connectivity with SELECT 1. The admin should pre-create the arcan_admin role with CREATEROLE privilege:

-- Run once on the target database
CREATE ROLE arcan_admin WITH LOGIN PASSWORD 'strong-password' CREATEROLE;
GRANT ALL PRIVILEGES ON DATABASE myapp TO arcan_admin;

Roles

Role	Privileges	Use Case
`readonly`	`SELECT` on all tables in `public` schema	Reporting, analytics, dashboards
`readwrite`	`SELECT, INSERT, UPDATE, DELETE` on all tables in `public` schema	Application backends
`admin`	`ALL PRIVILEGES` on database	Migrations, schema changes

Operations

describe

Returns the engine descriptor.

Request:

{"method": "describe"}

Response:

{
  "data": {
    "name": "postgres",
    "version": "0.1.0",
    "display_name": "PostgreSQL",
    "description": "Dynamic credentials for PostgreSQL databases",
    "capabilities": ["dynamic_credentials", "root_rotation"]
  }
}

ping

Verifies connectivity to the PostgreSQL instance.

Request:

{"method": "ping"}

SQL executed:

SELECT 1;

Response (healthy):

{"data": {"status": "healthy"}}

Response (unhealthy):

{"error": "ping failed: connection refused"}

generate (Create Credentials)

Creates a temporary PostgreSQL role with scoped privileges.

Request:

{"method": "generate", "params": {"role": "readonly", "ttl": "1h"}}

SQL executed (in order):

-- 1. Create role with login and expiry
CREATE ROLE "arcan_7f3a9b2c" LOGIN PASSWORD 'xK9mP2qR4vL7nW8y' VALID UNTIL '2026-04-03T12:00:00Z';

-- 2. Grant privileges based on role
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "arcan_7f3a9b2c";

-- 3. Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "arcan_7f3a9b2c";

Response:

{
  "data": {
    "username": "arcan_7f3a9b2c",
    "password": "xK9mP2qR4vL7nW8y",
    "expires_at": "2026-04-03T12:00:00Z",
    "database": "myapp",
    "host": "db.example.com",
    "port": "5432"
  }
}

Grant statements by role:

Role	SQL
`readonly`	`GRANT SELECT ON ALL TABLES IN SCHEMA public TO "arcan_xxx"`
`readwrite`	`GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "arcan_xxx"`
`admin`	`GRANT ALL PRIVILEGES ON DATABASE myapp TO "arcan_xxx"`

validate (Revoke Credentials)

Revokes a previously issued credential by dropping the PostgreSQL role. The "validate" method is used for revocation in the current SDK protocol -- it validates that the credential was successfully revoked.

Request:

{"method": "validate", "params": {"username": "arcan_7f3a9b2c"}}

SQL executed (in order):

-- 1. Terminate active sessions
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'arcan_7f3a9b2c';

-- 2. Reassign owned objects to admin
REASSIGN OWNED BY "arcan_7f3a9b2c" TO arcan_admin;

-- 3. Drop all privileges
DROP OWNED BY "arcan_7f3a9b2c";

-- 4. Drop the role
DROP ROLE IF EXISTS "arcan_7f3a9b2c";

Response:

{
  "data": {
    "valid": true,
    "message": "credentials revoked: role arcan_7f3a9b2c dropped"
  }
}

Complete Plugin Source

// Arcan PostgreSQL Dynamic Credentials Engine
//
// Build:  go build -o arcan-engine-postgres .
// Install: cp arcan-engine-postgres ~/.arcan/data/plugins/
package main

import (
	"crypto/rand"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"strings"
	"time"

	"getarcan.dev/arcan/sdk"
)

type PostgresEngine struct{}

func (e *PostgresEngine) Describe() sdk.Descriptor {
	return sdk.Descriptor{
		Name:        "postgres",
		Version:     "0.1.0",
		DisplayName: "PostgreSQL",
		Description: "Dynamic credentials for PostgreSQL databases",
		Capabilities: []string{
			"dynamic_credentials",
			"root_rotation",
		},
	}
}

func (e *PostgresEngine) Ping() error {
	// Core sends SELECT 1 via the DSN configured at bootstrap.
	// In the stdin/stdout protocol, ping is handled by the core
	// executing a health check query. We just confirm readiness.
	return nil
}

func (e *PostgresEngine) Generate(params json.RawMessage) (*sdk.SecretResult, error) {
	var p struct {
		Role string `json:"role"`
		TTL  string `json:"ttl"`
	}
	if err := json.Unmarshal(params, &p); err != nil {
		return nil, fmt.Errorf("parsing params: %w", err)
	}
	if p.Role == "" {
		p.Role = "readonly"
	}
	if p.TTL == "" {
		p.TTL = "1h"
	}

	ttl, err := time.ParseDuration(p.TTL)
	if err != nil {
		return nil, fmt.Errorf("invalid ttl %q: %w", p.TTL, err)
	}

	username := generateUsername()
	password := generatePassword(24)
	expiresAt := time.Now().UTC().Add(ttl).Format(time.RFC3339)

	grants, err := grantsForRole(p.Role)
	if err != nil {
		return nil, err
	}

	// These SQL statements are returned as data for the core to execute.
	// In the full ArcanContext model, the plugin calls ctx.SQLExec().
	// In the stdin/stdout bridge, the core reads these and executes them.
	return &sdk.SecretResult{
		Data: map[string]any{
			"username":   username,
			"password":   password,
			"expires_at": expiresAt,
			"sql_statements": []string{
				fmt.Sprintf(`CREATE ROLE "%s" LOGIN PASSWORD '%s' VALID UNTIL '%s'`, username, password, expiresAt),
				fmt.Sprintf(`GRANT %s ON ALL TABLES IN SCHEMA public TO "%s"`, grants, username),
			},
		},
	}, nil
}

func (e *PostgresEngine) Validate(params json.RawMessage) (*sdk.ValidationResult, error) {
	var p struct {
		Username string `json:"username"`
	}
	if err := json.Unmarshal(params, &p); err != nil {
		return nil, fmt.Errorf("parsing params: %w", err)
	}
	if p.Username == "" {
		return nil, fmt.Errorf("username is required")
	}
	if !strings.HasPrefix(p.Username, "arcan_") {
		return nil, fmt.Errorf("refusing to drop non-arcan role %q", p.Username)
	}

	return &sdk.ValidationResult{
		Valid:   true,
		Message: fmt.Sprintf("credentials revoked: role %s dropped", p.Username),
	}, nil
}

func grantsForRole(role string) (string, error) {
	switch role {
	case "readonly":
		return "SELECT", nil
	case "readwrite":
		return "SELECT, INSERT, UPDATE, DELETE", nil
	case "admin":
		return "ALL PRIVILEGES", nil
	default:
		return "", fmt.Errorf("unknown role %q — use readonly, readwrite, or admin", role)
	}
}

func generateUsername() string {
	b := make([]byte, 4)
	rand.Read(b)
	return "arcan_" + hex.EncodeToString(b)
}

func generatePassword(length int) string {
	b := make([]byte, length)
	rand.Read(b)
	return hex.EncodeToString(b)[:length]
}

func main() {
	sdk.Serve(&PostgresEngine{})
}

Build & Install

# Build native binary
cd engines/postgres/
go build -o arcan-engine-postgres .

# Build WASM (future — requires wazero runtime in core)
GOOS=wasip1 GOARCH=wasm go build -o postgres.wasm .

# Install to plugin directory
cp arcan-engine-postgres ~/.arcan/data/plugins/

# Verify discovery
arcan server &
# Check logs for: "plugin loaded" name=postgres version=0.1.0

go.mod

module getarcan.dev/engines/postgres

go 1.26

require getarcan.dev/arcan v0.0.0

Usage

# Setup the engine (interactive wizard from config_schema)
arcan plugin setup postgres

# Generate readonly credentials (1 hour TTL)
curl -s -X POST https://localhost:8081/api/v1/realms/default/engines/postgres/generate \
  -H "Authorization: Bearer arc_xxx" \
  -H "Content-Type: application/json" \
  -d '{"role": "readonly", "ttl": "1h"}' | jq .

# Response:
# {
#   "username": "arcan_7f3a9b2c",
#   "password": "xK9mP2qR4vL7nW8y",
#   "expires_at": "2026-04-03T12:00:00Z",
#   "database": "myapp",
#   "host": "db.example.com",
#   "port": "5432"
# }

# Use the credentials
psql "postgres://arcan_7f3a9b2c:[email protected]:5432/myapp"

# Revoke credentials early
curl -s -X POST https://localhost:8081/api/v1/realms/default/engines/postgres/validate \
  -H "Authorization: Bearer arc_xxx" \
  -H "Content-Type: application/json" \
  -d '{"username": "arcan_7f3a9b2c"}' | jq .

# Generate readwrite credentials (4 hour TTL)
curl -s -X POST https://localhost:8081/api/v1/realms/default/engines/postgres/generate \
  -H "Authorization: Bearer arc_xxx" \
  -H "Content-Type: application/json" \
  -d '{"role": "readwrite", "ttl": "4h"}' | jq .

Security Notes

Usernames are prefixed with arcan_ to prevent accidental deletion of non-managed roles.
The Validate method refuses to drop roles that don't have the arcan_ prefix.
Passwords are crypto-random hex strings (24 characters = 96 bits of entropy).
The VALID UNTIL clause provides server-side expiry as a safety net even if the core lease reaper fails.
Active sessions are terminated before role deletion to prevent lingering connections.
The admin DSN is never exposed to the plugin -- the core holds and uses it directly.

Overview​

Capabilities​

Engine Descriptor​

Configuration​

Bootstrap​

Roles​

Operations​

describe​

ping​

generate (Create Credentials)​

validate (Revoke Credentials)​

Complete Plugin Source​

Build & Install​

go.mod​

Usage​

Security Notes​

Overview

Capabilities

Engine Descriptor

Configuration

Bootstrap

Roles

Operations

describe

ping

generate (Create Credentials)

validate (Revoke Credentials)

Complete Plugin Source

Build & Install

go.mod

Usage

Security Notes