# Direct PostgreSQL Connection

Connect directly to your Volcano PostgreSQL database from functions, Node.js servers, or any PostgreSQL client.

Connect directly to your Volcano PostgreSQL database from functions, Node.js servers, or any PostgreSQL client.

## Keep connection pools within capacity

Native PostgreSQL connections share a limit of **20 open sessions per project on each server**, across all databases, branches, full-access connections, and user-access connections. Idle driver-pool connections count until their sockets close. The owning platform account also shares **20 sessions across all its projects on each server**. Application users do not get separate account allowances. These safety limits apply to every plan and in local mode. In the default local setup, all projects share one platform account, so the local server shares one allowance of 20 native sessions and 20 active or waiting REST requests across those projects.

Each server also admits at most **32 authenticated sessions in total**. These are per-server limits, not project-wide or account-wide fleet allowances. Capacity can be reached before your project or account reaches 20 sessions. Database REST traffic shares this capacity. Its idle connections use a two-minute timeout. Capacity reclamation can restart this timeout for retained idle connections, extending their project/account occupancy beyond two minutes after the last query. A native driver's refusal does not trigger REST reclamation; only later REST requests reclaim their own idle sockets when acquisition needs capacity. Applications sharing a source address do not face an additional IP-based connection limit.

When capacity is full, a new authenticated connection receives PostgreSQL SQLSTATE `53300` (`too_many_connections`), for example:

```text
FATAL: PostgreSQL connection capacity reached (project)
HINT: Close idle connections, reduce pool sizes, and retry with backoff.
```

The refused connection closes; existing healthy sessions remain available. Share small, reusable pools, bound the number of per-user pools, and close idle pools when they are no longer needed. Include all application instances and databases when sizing pools, and reserve capacity for server-side database operations, migrations, and interactive clients. Returning a connection to a driver's pool keeps its session open; closing the pool releases its idle sessions. Retry new connections with exponential backoff and jitter instead of a tight reconnect loop. An authenticated session outside a transaction closes after **five minutes waiting for its next client message**, with PostgreSQL `57P05` (`idle_session_timeout`). Active queries continue normally; explicit transactions retain their existing idle-in-transaction timeout. Configure driver pools to retire idle connections before five minutes and reconnect when the server closes a stale session. Retry connection acquisition with backoff; do not automatically replay writes after a connection error.

## Overview

**Two ways to connect:**

### 1. REST API (Browser)

Use Query Builder query builder
No PostgreSQL driver needed
Works from browser
See [Query Builder API Guide](https://docs.volcano.dev/docs/platform/databases/query-builder-api.md)

### 2. Direct Connection (Function/Server)

Native PostgreSQL protocol
Full SQL power
Any PostgreSQL client/ORM
**This guide**

---

## Authentication & User Impersonation

### The Key: `application_name` Parameter

Volcano uses two connection modes via the `application_name` parameter:

**Full Admin Access** (for DDL, migrations):

```text
postgres://volcano_client_11111111-1111-1111-1111-111111111111:vpg_password@host:port/db?application_name=volcano_full_access
```

**User Impersonation** (RLS enforced):

```text
postgres://volcano_client_11111111-1111-1111-1111-111111111111:vpg_password@host:port/db?application_name=volcano_user_access:USER_ID
                                                              ↑
                                                  This makes auth.uid() return USER_ID
```

**Anonymous Access** (RLS enforced, no user context):

```text
postgres://volcano_client_11111111-1111-1111-1111-111111111111:vpg_password@host:port/db?application_name=volcano_user_access
```

### Format

```text
volcano_full_access              # Full admin access (DDL/migrations)
volcano_user_access:{user_id}    # User impersonation (RLS enforced)
volcano_user_access              # Anonymous access (anon role, RLS enforced)
```

The proxy identifies the database by the **username** in the connection string
(`volcano_client_{id}`), which is globally unique — database names are only
unique within a project, so the username is what reaches the exact database even
when two projects reuse a name. `application_name` carries only the access mode,
so it can be overridden by clients for their own purposes without changing which
database is reached. Use the `connection_string` Volcano returns as-is; for user
impersonation, keep its username and set `application_name` to
`volcano_user_access:{user_id}`.

**Example:**

```text
username=volcano_client_11111111-1111-1111-1111-111111111111  application_name=volcano_user_access:abc-123-uuid    # User impersonation
username=volcano_client_11111111-1111-1111-1111-111111111111  application_name=volcano_full_access                 # Admin access
```

The proxy looks up the user's email and role from the database automatically. This is more secure because:

- **No spoofing**: Email and role cannot be forged by the client
- **User validation**: The proxy verifies the user exists in the database's project
- **Override-safe**: routing lives in the username, not the client-settable `application_name`

When pgproxy receives a user impersonation request, it:

1. Validates the user exists in the database's project
2. Looks up their email and role from the database
3. Sets the auth context:

```sql
SET LOCAL request.jwt_sub = 'abc-123-uuid';
SET LOCAL request.jwt_email = 'user@example.com';  -- Looked up from DB
SET LOCAL request.jwt_role = 'authenticated';       -- 'authenticated' or 'anonymous', looked up from DB
```

Then:

```sql
SELECT auth.uid();    -- Returns: 'abc-123-uuid'
SELECT auth.email();  -- Returns: 'user@example.com'
SELECT auth.role();   -- Returns: 'authenticated' (or 'anonymous' for an anonymous user)
```

---

## Quick Start

### Get Your Connection String

```bash
# Via API
curl https://api.volcano.dev/projects/PROJECT_ID/databases/DATABASE_NAME \
  -H "Authorization: Bearer $PLATFORM_TOKEN"
```

**Response includes:**

```json
{
  "connection_string": "postgres://volcano_client_11111111-1111-1111-1111-111111111111:vpg_abc123@database.volcano.dev:5432/my_app?sslmode=require&application_name=volcano_full_access"
}
```

This is your **base connection string** with full admin access (for DDL/migrations). To impersonate a user, change the `application_name` from `volcano_full_access` to `volcano_user_access` and add the user ID.
The password is Volcano-managed and starts with `vpg_`. The internal owner credentials are not exposed and will not authenticate through pgproxy.

---

## Functions (Recommended)

### From event.__volcano_auth

**When users invoke your function with their access token**, Volcano automatically creates `event.__volcano_auth`:

```javascript
const { Client } = require('pg');

exports.handler = async (event) => {
  // 1. Get auth context (created by Volcano from user's JWT)
  const auth = event.__volcano_auth;

  if (!auth) {
    return { statusCode: 401, body: 'Unauthorized' };
  }

  console.log('User invoking function:', auth.user_id, auth.email);

  // 2. Select the access mode by REPLACING application_name. DATABASE_URL already
  //    has application_name=volcano_full_access, so set (don't append) it —
  //    a duplicate param would leave the startup mode up to the driver.
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', `volcano_user_access:${auth.user_id}`);
  const connStr = url.toString();

  // 3. Connect to database
  const client = new Client({ connectionString: connStr });
  await client.connect();

  try {
    // 5. Query - auth.uid() returns auth.user_id
    const { rows } = await client.query('SELECT * FROM posts');
    // RLS automatically filters to this user's posts!

    return {
      statusCode: 200,
      body: JSON.stringify({
        posts: rows,
        queried_as: auth.user_id,
      }),
    };
  } finally {
    await client.end();
  }
};
```

**How it works:**

1. User calls function with their access token
2. Volcano validates JWT and creates `event.__volcano_auth`
3. The function builds a connection string with the user's identity
4. pgproxy receives and sets session variables
5. `auth.uid()` returns the user's ID
6. RLS filters data to that user

### With Connection Pooling (Production)

pgproxy fixes the access mode and RLS identity from `application_name` at connection **startup** — `SET application_name` after connect has no effect. So a pool can only ever serve one identity: key pools by user. Save the [bounded pool helper](https://github.com/Kong/volcano-docs/blob/main/content/platform/examples/database-pool-example/user-db-pool.js) as `user-db-pool.js` alongside your function. It reuses up to four identity pools, retires idle pools when another user needs capacity, and preserves checked-out or waiting clients. When every slot is busy or a connection fails, return a temporary-unavailable response as below. For a larger changing set of user identities, the [database REST API](https://docs.volcano.dev/docs/platform/databases/rest-api.md) manages scoped identity pools for you.

```javascript
const { connectForUser } = require('./user-db-pool');

exports.handler = async (event) => {
  const auth = event.__volcano_auth;

  if (!auth) {
    return { statusCode: 401, body: 'Unauthorized' };
  }

  // Get a connection from THIS user's pool — every connection in it started
  // up as volcano_user_access:{userId}, so RLS is already in effect.
  let client;
  try {
    client = await connectForUser(auth.user_id);
  } catch (error) {
    // Log diagnostic codes, never connection strings or error messages.
    console.error('Database connection failed', { name: error?.name, code: error?.code ?? 'UNKNOWN' });
    return {
      statusCode: 503,
      headers: { 'Retry-After': '1' },
      body: JSON.stringify({ error: 'Database temporarily unavailable' }),
    };
  }

  try {
    const { rows } = await client.query('SELECT * FROM posts');

    return {
      statusCode: 200,
      body: JSON.stringify({ posts: rows }),
    };
  } finally {
    client.release(); // Return to pool
  }
};
```

**Why pooling?**

- New connection: 50-100ms overhead
- Pooled connection: 1-5ms overhead
- **10-50x faster!**

---

## Protocol safety limits

All plans use these PostgreSQL protocol safety limits per connection:

| Resource                                               | Ceiling                   |
| ------------------------------------------------------ | ------------------------- |
| Frontend message body, including Query, Parse and Bind | 16 MiB (16,777,216 bytes) |
| Each CopyData message body                             | 1 MiB (1,048,576 bytes)   |
| Retained prepared statements                           | 1,024                     |
| Retained portals (bound statements)                    | 1,024                     |
| Combined accounted statement and portal memory         | 32 MiB (33,554,432 bytes) |

These are per-message and per-session ceilings, not a global memory bound.
Keep application connection pools bounded.

Message body sizes exclude the one-byte message type and four-byte length
field. An oversized declared body gets a PostgreSQL FATAL error with SQLSTATE
`54000` naming the permitted body size in bytes, then the connection closes
before allocating a receive buffer for that body. Delivery is best effort: a
client that stops reading may see only the disconnect. Split large queries or
parameter batches into separate operations. A single
parameter cannot be split across Bind messages; change the operation or use
a suitable streaming COPY input for values exceeding the frame limit. For COPY,
stream data in chunks of at most 1 MiB; the total COPY may exceed 1 MiB, and a
row may span chunks. See [Importing data](https://docs.volcano.dev/docs/platform/databases/importing-data.md#limits).

The frame ceiling applies after driver encoding and includes protocol overhead.
For example, text/hex encoding of `bytea` roughly doubles the value size: an
8 MiB value already exceeds 16 MiB once encoded. Use smaller batches or a
supported binary/streaming API rather than relying on the value's in-memory size.

Session memory includes names, SQL, parameter values, type and format arrays,
retained column descriptions, and accounting overhead. A portal is charged for
the statement data it retains even after that statement is closed or replaced.
The shared SQL and column descriptions are conservatively charged once per
retaining object, even when their underlying storage is shared. Large SQL
bound to many portals can therefore reach the byte ceiling before either
count ceiling.

Replacing an object reclaims its previous charge; protocol Close releases that
object's charge, and disconnecting releases the session. Portals also expire
at COMMIT/ROLLBACK and at Sync outside an explicit transaction. Sync inside
BEGIN preserves portals, including while the transaction awaits rollback after
an error. Named prepared statements survive transaction completion. A simple
Query discards the unnamed statement and replaces the unnamed portal when it
executes a nonempty query; an empty Query inside BEGIN preserves that portal.

SQL `DEALLOCATE [PREPARE] name` also releases a prepared statement created by
protocol Parse. `DEALLOCATE ALL` releases all named prepared statements, and
`DISCARD ALL` releases them and the portals while performing PostgreSQL's
session reset. Both forms of ALL preserve the protocol's unnamed statement
when executed through the extended protocol; a simple Query independently
discards it. Bound portals survive statement deallocation until their own
Close or transaction end, and their retained data still counts toward the
byte ceiling. Failed cleanup does not release named statements.

`DISCARD ALL` cannot run inside an explicit transaction, or after an earlier
Execute in a pipeline before Sync. Send Sync before issuing it as a standalone
command. Use your driver's cache-clear/deallocation API when available so its
own prepared-name cache stays consistent with the server.

Using an expired portal fails with SQLSTATE `34000`; Bind a surviving named
statement again to create a new portal. A statement removed by Close or simple
Query must be parsed again; otherwise its lookup fails with SQLSTATE `26000`.

A Parse, Bind or Describe that would exceed a session ceiling fails with
SQLSTATE `54000` and `pgproxy session prepared statement or portal limit exceeded; close unused statements and portals`.
The previous retained objects remain available. As with other extended-query
errors, the proxy discards pending messages until Sync. Send Sync, then Close
unused statements or portals before retrying. Inside a transaction, roll back
the failed transaction before resuming queries. Drivers that cache prepared
statements should evict entries with protocol Close.

## Node.js Server (Custom Backend)

If you have your own Node.js server and want to impersonate Volcano auth users,
verify each request's access token with Volcano before you trust its user ID.
Send the token to `GET /auth/user`. Volcano checks its signature, expiry,
session revocation, and ban status, and returns the user. Volcano doesn't share
the key that signs access tokens, so you can't verify them yourself. In a Volcano
function, use `event.__volcano_auth` instead: Volcano has already verified the
token.

### Verify the Access Token

```javascript
const API_URL = process.env.VOLCANO_API_URL ?? 'https://api.volcano.dev';

// Returns the Volcano user the request's access token belongs to, or null.
async function verifiedUser(req) {
  const authorization = req.headers.authorization;
  if (!authorization?.startsWith('Bearer ')) {
    return null;
  }
  const response = await fetch(`${API_URL}/auth/user`, { headers: { authorization } });
  if (!response.ok) {
    return null; // 401: invalid, expired, or revoked token. 403: banned user.
  }
  const { user } = await response.json();
  // A token from another Volcano project verifies there, not here.
  return user.project_id === process.env.VOLCANO_PROJECT_ID ? user : null;
}
```

The SDK's [server client](https://docs.volcano.dev/docs/sdk/js/nextjs.md) makes the same `GET /auth/user` call.

### Option 1: Pool Connections Per User

```javascript
const { connectForUser } = require('./user-db-pool');
const express = require('express');

const app = express();

app.get('/api/posts', async (req, res) => {
  // 1. Verify the access token with Volcano (verifiedUser from above)
  const user = await verifiedUser(req);
  if (!user) {
    return res.status(401).json({ error: 'Not signed in' });
  }

  // 2. Get a connection from this user's pool (started under RLS)
  let client;
  try {
    client = await connectForUser(user.id);
  } catch (error) {
    // Log diagnostic codes, never connection strings or error messages.
    console.error('Database connection failed', { name: error?.name, code: error?.code ?? 'UNKNOWN' });
    return res.status(503).set('Retry-After', '1').json({ error: 'Database temporarily unavailable' });
  }

  try {
    // 3. Query - auth.uid() = user.id
    const { rows } = await client.query('SELECT * FROM posts');

    res.json({ posts: rows });
  } finally {
    client.release();
  }
});

app.listen(3000);
```

### Option 2: Build Connection String Per Request

```javascript
const { Client } = require('pg');
const express = require('express');

const app = express();

app.get('/api/posts', async (req, res) => {
  const user = await verifiedUser(req); // verifiedUser from above
  if (!user) {
    return res.status(401).json({ error: 'Not signed in' });
  }

  // Build connection string with user's auth context. Replace application_name
  // (DATABASE_URL already carries volcano_full_access) so the connection starts
  // up under RLS instead of leaving duplicate params to the driver.
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', `volcano_user_access:${user.id}`);
  const connStr = url.toString();

  const client = new Client({ connectionString: connStr });
  await client.connect();

  try {
    const { rows } = await client.query('SELECT * FROM posts');
    res.json({ posts: rows });
  } finally {
    await client.end();
  }
});
```

---

## Session Settings

Volcano shares server connections between clients. A statement you send outside a transaction can run on a different server connection from your last one, and Volcano resets a server connection before it runs another statement. Session state therefore lasts only for the statement that created it, and never reaches another client. That includes a session-level `SET` or `set_config(..., false)`, `SET ROLE`, a temporary table, a session advisory lock, `LISTEN`, a `WITH HOLD` cursor, the values `currval()` and `lastval()` return, and a statement you `PREPARE` in SQL. The exception is a statement that a function you created earlier prepares in its body: `DEALLOCATE` it before the function returns.

Volcano reads only the `user` and `application_name` startup parameters. It ignores the others, including `options=-c search_path=...`.

### Scope settings to a transaction

To apply a setting to several statements, run them in a transaction and use `SET LOCAL`. A transaction keeps one server connection from `BEGIN` to `COMMIT` or `ROLLBACK`:

```sql
BEGIN;
SET LOCAL search_path TO reporting, public;
SET LOCAL statement_timeout = '30s';
SELECT count(*) FROM daily_totals;
COMMIT;
```

### Settings your driver sends when it connects

Some ORMs and drivers run session-level statements once, right after connecting, and expect them to last for the connection. Through Volcano they last for that one statement. Examples include Django's `SET TIME ZONE` when `USE_TZ` is on, Rails' `SET intervalstyle = iso_8601` and `variables:` in `database.yml`, and a pool's connect hook that runs `SET search_path`. Set the value in each transaction with `SET LOCAL`, or qualify names in your SQL, such as `reporting.daily_totals` and `now() AT TIME ZONE 'UTC'`.

When you can't change the statements, such as the queries an ORM writes for you, make the value the database's default. Volcano resets a setting to its default, so a database default applies to every statement. Run this with the full-access connection string:

```sql
DO $$
BEGIN
  EXECUTE format('ALTER DATABASE %I SET intervalstyle = %L', current_database(), 'iso_8601');
END
$$;
```

Server connections that were already open keep the old default until Volcano replaces them, within 30 minutes.

### Read a generated ID

`lastval()` or `currval()` in a statement after an `INSERT` fails with `... is not yet defined in this session`. Return the ID from the `INSERT` instead:

```sql
INSERT INTO listings (title) VALUES ('Loft') RETURNING id;
```

To read it in a later statement, run both in one transaction.

### Advisory locks

A session advisory lock is released when the statement that took it finishes, so a later `pg_advisory_unlock` returns `false`. Take the lock with `pg_advisory_xact_lock` inside the transaction that needs it. Postgres releases it at `COMMIT` or `ROLLBACK`:

```sql
BEGIN;
SELECT pg_advisory_xact_lock(42);
UPDATE counters SET value = value + 1 WHERE id = 1;
COMMIT;
```

Rails migrations hold a session advisory lock across statements. They run, then fail with `ActiveRecord::ConcurrentMigrationError: Failed to release advisory lock`. Set `advisory_locks: false` for the database in `database.yml`, and run migrations from one place at a time.

### Restore a pg_dump

A `pg_dump` script starts with session-level settings, such as `SELECT pg_catalog.set_config('search_path', '', false)`, that the rest of the script depends on. Restore the script in one transaction so its settings reach every statement:

```bash
psql "$VOLCANO_DATABASE_URL" --single-transaction --set ON_ERROR_STOP=1 --file dump.sql
```

[Importing data](https://docs.volcano.dev/docs/platform/databases/importing-data.md) covers the `pg_dump` options to use, the `SET transaction_timeout` line that a database on PostgreSQL 16 rejects, and loading rows with `COPY`.

---

## Any PostgreSQL Client

The `application_name` parameter works with **any** PostgreSQL client!

### Node.js (pg)

```javascript
const { Client } = require('pg');

const client = new Client({
  host: 'database.volcano.dev',
  port: 5432,
  database: 'mydb',
  user: 'volcano_client_11111111-1111-1111-1111-111111111111',
  password: 'password',
  ssl: { rejectUnauthorized: false },
  application_name: 'volcano_user_access:user-id',
});

await client.connect();
const { rows } = await client.query('SELECT auth.uid()');
console.log('Auth UID:', rows[0].auth_uid); // Returns: user-id
```

### Python (psycopg2)

```python
import psycopg2
from urllib.parse import quote

auth_context = f"volcano_user_access:{user_id}"

conn = psycopg2.connect(
    host="database.volcano.dev",
    port=5432,
    database="mydb",
    user="volcano_client_11111111-1111-1111-1111-111111111111",
    password="password",
    application_name=auth_context,
    sslmode="require"
)

cursor = conn.cursor()
cursor.execute("SELECT auth.uid()")
print(cursor.fetchone()[0])  # Returns: user_id
```

### Go (pgx)

```go
import (
    "context"
    "fmt"
    "net/url"
    "os"

    "github.com/jackc/pgx/v5"
)

func main() {
    // DATABASE_URL already carries application_name=volcano_full_access, so parse
    // it and REPLACE application_name (Set) — appending a second one would leave
    // the startup mode up to the driver's duplicate-param handling.
    u, _ := url.Parse(os.Getenv("DATABASE_URL"))
    q := u.Query()
    q.Set("application_name", fmt.Sprintf("volcano_user_access:%s", userID))
    u.RawQuery = q.Encode()

    conn, _ := pgx.Connect(context.Background(), u.String())
    defer conn.Close(context.Background())

    var authUID string
    conn.QueryRow(context.Background(), "SELECT auth.uid()::TEXT").Scan(&authUID)
    fmt.Println("Auth UID:", authUID)  // Returns: userID
}
```

### psql (Command Line)

```bash
psql "postgres://volcano_client_11111111-1111-1111-1111-111111111111:pass@database.volcano.dev:5432/mydb?sslmode=require&application_name=volcano_user_access:USER_ID"

# Then query:
SELECT auth.uid();
-- Returns: USER_ID
```

---

## Without User Impersonation (Service Role)

### For Admin Operations

If you need to access ALL data (bypassing RLS), use the **service role connection string**:

```javascript
const { Client } = require('pg');

// Use service_role connection string (has BYPASSRLS)
const client = new Client({
  connectionString: process.env.SERVICE_ROLE_CONNECTION_STRING,
});

await client.connect();

// See ALL data (no RLS filtering)
const { rows } = await client.query('SELECT * FROM posts');
// Returns: All users' posts (not filtered)

await client.end();
```

**When to use:**

- Admin dashboards
- Background jobs
- Data migrations
- Analytics aggregation

> **Warning:** Service role bypasses RLS - use carefully!

---

## Using ORMs with Auth Context

### Sequelize

```javascript
const { Sequelize } = require('sequelize');

exports.handler = async (event) => {
  const auth = event.__volcano_auth;

  // Replace application_name (DATABASE_URL already has volcano_full_access)
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', `volcano_user_access:${auth.user_id}`);

  const sequelize = new Sequelize(url.toString());

  // Define model
  const Post = sequelize.define('Post', {
    id: { type: DataTypes.UUID, primaryKey: true },
    title: DataTypes.STRING,
    content: DataTypes.TEXT,
    user_id: DataTypes.UUID,
  });

  // Query - RLS enforced!
  const posts = await Post.findAll();
  // Returns: Only this user's posts

  await sequelize.close();
};
```

### Prisma

```javascript
const { PrismaClient } = require('@prisma/client');

exports.handler = async (event) => {
  const auth = event.__volcano_auth;

  // Replace application_name (DATABASE_URL already has volcano_full_access)
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', `volcano_user_access:${auth.user_id}`);
  const databaseUrl = url.toString();

  const prisma = new PrismaClient({
    datasources: {
      db: { url: databaseUrl },
    },
  });

  // Query - RLS enforced!
  const posts = await prisma.post.findMany();
  // Returns: Only this user's posts

  await prisma.$disconnect();
};
```

### TypeORM

```javascript
const { DataSource } = require('typeorm');

exports.handler = async (event) => {
  const auth = event.__volcano_auth;

  // Build connection with auth (simplified format)
  const appName = `volcano_user_access:${auth.user_id}`;
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', appName);

  const dataSource = new DataSource({
    type: 'postgres',
    url: url.toString(),
    entities: [Post],
    synchronize: false,
  });

  await dataSource.initialize();

  const postRepo = dataSource.getRepository(Post);
  const posts = await postRepo.find();
  // Returns: Only this user's posts (RLS enforced)

  await dataSource.destroy();
};
```

---

## Advanced: Multiple Users in One Connection

You **can't** impersonate multiple users with one connection. Each connection impersonates ONE user.

### This Won't Work

```javascript
// BAD - application_name is set at connection time!
const client = new Client({ connectionString: baseConnStr });
await client.connect();

// Try to change user (won't work!)
await client.query("SET application_name = 'volcano_user_access:user1'");
await client.query('SELECT * FROM posts'); // Still uses original app_name

// Try to change again (won't work!)
await client.query("SET application_name = 'volcano_user_access:user2'");
await client.query('SELECT * FROM posts'); // Still uses original app_name
```

**Why:** pgproxy reads `application_name` during connection **startup**, not from SET commands.

### Correct Approach

**Create a new connection per user:**

```javascript
async function queryAsUser(userId) {
  // Replace application_name (DATABASE_URL already has volcano_full_access)
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', `volcano_user_access:${userId}`);

  const client = new Client({ connectionString: url.toString() });
  await client.connect();

  try {
    const { rows } = await client.query('SELECT * FROM posts');
    return rows; // Filtered to userId's posts
  } finally {
    await client.end();
  }
}

// Query as different users
const user1Posts = await queryAsUser('user1-id');
const user2Posts = await queryAsUser('user2-id');

// Different results!
```

**Or pool connections per user.** Since the auth identity is fixed at connection startup, a pool can only ever serve one user — so key pools by user:

```javascript
const { connectForUser } = require('./user-db-pool');

async function queryAsUser(userId) {
  const client = await connectForUser(userId);

  try {
    const { rows } = await client.query('SELECT * FROM posts');
    return rows; // Every connection in this pool started as userId
  } finally {
    client.release();
  }
}
```

---

## Connection String Formats

### Via pgproxy (Recommended)

```text
postgres://volcano_client_11111111-1111-1111-1111-111111111111:vpg_password@database.volcano.dev:5432/dbname?sslmode=require&application_name=volcano_user_access:USER_ID
```

**Advantages:**

- Automatic auth context injection
- Connection pooling
- Works with any client

### Direct to the underlying instance

The underlying instance's credentials are internal to Volcano and are not returned by the API. Use the pgproxy connection string returned by Volcano instead.

---

## Impersonating Different User Types

### Authenticated User (Email/Password)

With the simplified format, email and role are looked up from the database:

```javascript
const appName = `volcano_user_access:${userId}`;
```

**Database sees:**

- `auth.uid()` = userId
- `auth.email()` = looked up from auth_users table
- `auth.role()` = "authenticated"

### Anonymous Access (No Auth Context)

For anonymous access, use the `volcano_user_access` format without a user ID:

```javascript
const appName = `volcano_user_access`;
```

**Database sees:**

- `auth.uid()` = NULL
- `auth.email()` = NULL
- `auth.role()` = "anon" (uses anon PostgreSQL role)

### Service Role (No RLS)

```javascript
// Use service_role connection string
// Don't set application_name
const client = new Client({
  connectionString: process.env.SERVICE_ROLE_CONNECTION_STRING,
});
```

**Database sees:**

- `auth.uid()` = NULL
- `auth.role()` = "service_role"
- **RLS bypassed** (BYPASSRLS privilege)

---

## Security Considerations

### Safe Patterns

**Using event.\_\_volcano_auth (function):**

```javascript
const auth = event.__volcano_auth; // ← Created by Volcano, trusted
const appName = `volcano_user_access:${auth.user_id}`;
```

**Verifying the access token with Volcano (Node.js):**

```javascript
const user = await verifiedUser(req); // GET /auth/user, see above
if (!user) return res.status(401).end();
const appName = `volcano_user_access:${user.id}`;
```

### Unsafe Patterns

**Never trust user input directly:**

```javascript
// DANGEROUS - user can set any user_id!
const userId = req.body.user_id; // From user input
const appName = `volcano_user_access:${userId}`; // Don't do this!
```

**Never read the user ID out of an unverified token:**

```javascript
// DANGEROUS - decoding is not verifying; anyone can mint this payload
const { sub } = JSON.parse(atob(token.split('.')[1]));
const appName = `volcano_user_access:${sub}`; // Don't do this!
```

**Always verify with Volcano:**

```javascript
// SAFE - user.id comes from Volcano's GET /auth/user
const user = await verifiedUser(req);
if (!user) return res.status(401).end();
const appName = `volcano_user_access:${user.id}`;
```

---

## Error Codes

A statement that PostgreSQL refuses comes back with its own SQLSTATE, so you can
branch on the failure instead of matching on message text:

```javascript
const { Client } = require('pg');

try {
  await client.query('INSERT INTO posts (id, title) VALUES ($1, $2)', [id, title]);
} catch (err) {
  if (err.code === '23505') {
    return { status: 409, error: 'That post already exists' };
  }
  if (err.code === '40001') {
    return retry(); // serialization failure, safe to try again
  }
  throw err;
}
```

The codes you are most likely to handle:

| SQLSTATE | Meaning                                                                        | Typical response                        |
| -------- | ------------------------------------------------------------------------------ | --------------------------------------- |
| `23505`  | Unique constraint violation                                                    | 409, or upsert instead                  |
| `23503`  | Foreign key violation                                                          | 400, referenced row is missing          |
| `23502`  | Not-null violation                                                             | 400, required field omitted             |
| `42501`  | Insufficient privilege, including a write a row-level security policy rejected | 403                                     |
| `42P01`  | Undefined table                                                                | 400, check the name and your migrations |
| `40001`  | Serialization failure                                                          | Retry the transaction                   |

Volcano also emits PostgreSQL-compatible SQLSTATEs:

| SQLSTATE | Meaning                                                                                                                                                                                                                                                            |
| -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `53400`  | The account is over its billing-cycle database request allowance                                                                                                                                                                                                   |
| `25006`  | The database is over its storage allowance; writes are blocked                                                                                                                                                                                                     |
| `57P03`  | The platform is briefly unable to accept connections — retry                                                                                                                                                                                                       |
| `53300`  | New authenticated connection refused because the server, project or owning account has reached its session limit; reduce pool sizes, close idle connections and retry acquisition with backoff. See [connection capacity](#keep-connection-pools-within-capacity). |
| `57P05`  | Session closed after five minutes waiting for client input outside a transaction; reconnect and retire idle driver connections sooner. Do not automatically replay writes.                                                                                         |
| `0A000`  | The query holds more than one statement; see [One Statement per Query](https://docs.volcano.dev/docs/platform/databases/connection-strings.md#one-statement-per-query). PostgreSQL raises this code too, for features it does not support; the message says which                                                   |

Some failures, such as an unsupported statement like `COMMIT AND CHAIN`, arrive without a SQLSTATE. Inspect the driver's error code when handling a connection failure.

### Pipelined Queries

When a driver pipelines extended queries, an error skips all remaining messages until the next `Sync`. Queries after that boundary can run again. Inside an explicit transaction, send `ROLLBACK` after the error's `Sync` before issuing more queries; a rollback queued before that boundary is skipped too. Drivers normally manage these protocol boundaries for you.

Outside an explicit transaction, Volcano commits each statement independently: writes that succeeded before the error stay committed. Use an explicit `BEGIN`/`COMMIT` transaction when several writes must succeed or fail together.

---

## Troubleshooting

### Problem: auth.uid() returns NULL

**Cause:** application_name not set or wrong format

**Solution:**

```javascript
// Verify format (simplified - 3 parts)
const appName = `volcano_user_access:${userId}`;
console.log('Application name:', appName);

// Must be: volcano_user_access:user_id
```

### Problem: Can't access another user's data

**Cause:** RLS is working! (This is correct)

**Solution:** This is intentional. Each connection can only access one user's data. To query as a different user, create a new connection with their auth context.

### Problem: Connection pool shows wrong user_id

**Cause:** The pool was created from a connection string with a different (or full-access) `application_name`. The identity is fixed at connection **startup** — `SET application_name` after connect has no effect on pgproxy.

**Solution:** Create the pool from a connection string whose `application_name` is already `volcano_user_access:{userId}`, and keep one pool per user:

```javascript
// Wrong - SET after connect does nothing for pgproxy
const client = await pool.connect();
await client.query('SET application_name = $1', [appName]);

// Correct - bake the identity into the pool's connection string
const url = new URL(process.env.DATABASE_URL);
url.searchParams.set('application_name', `volcano_user_access:${userId}`);
const userPool = new Pool({ connectionString: url.toString() });
```

---

## Examples

### Query as Specific User

```javascript
const { Client } = require('pg');

async function getPostsAsUser(userId, projectId) {
  // Replace application_name (DATABASE_URL already has volcano_full_access)
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', `volcano_user_access:${userId}`);

  const client = new Client({ connectionString: url.toString() });
  await client.connect();

  try {
    const { rows } = await client.query('SELECT * FROM posts');
    return rows; // Filtered to this user's posts
  } finally {
    await client.end();
  }
}

const projectId = 'project-uuid';

// Query as User A
const user1Posts = await getPostsAsUser('abc-123-uuid', projectId);

// Query as User B
const user2Posts = await getPostsAsUser('def-456-uuid', projectId);

// Different results!
console.log('User 1 has', user1Posts.length, 'posts');
console.log('User 2 has', user2Posts.length, 'posts');
```

### Verify Identity

```javascript
const { Client } = require('pg');

async function verifyAuthContext(userId, projectId) {
  // Replace application_name (DATABASE_URL already has volcano_full_access)
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('application_name', `volcano_user_access:${userId}`);

  const client = new Client({ connectionString: url.toString() });
  await client.connect();

  try {
    const { rows } = await client.query(`
      SELECT 
        auth.uid()::TEXT as user_id,
        auth.email() as email,
        auth.role() as role
    `);

    console.log('Database sees:');
    console.log('  auth.uid():', rows[0].user_id);
    console.log('  auth.email():', rows[0].email); // Looked up from DB
    console.log('  auth.role():', rows[0].role); // 'authenticated', or 'anonymous' for an anonymous user

    // Verify they match
    console.log('Expected user_id:', userId);
    console.log('Match:', rows[0].user_id === userId);

    return rows[0];
  } finally {
    await client.end();
  }
}

// Verify auth context is working
await verifyAuthContext('test-user-uuid', 'project-uuid');
```

---

## Complete Function Example

```javascript
const { connectForUser } = require('./user-db-pool');

/**
 * Handler that queries as the authenticated user
 */
exports.handler = async (event) => {
  // 1. Extract auth context (from user's JWT token)
  const auth = event.__volcano_auth;

  if (!auth) {
    return {
      statusCode: 401,
      body: JSON.stringify({
        error: 'No authentication context - user must sign in first',
      }),
    };
  }

  // Log who is making the request
  console.log('Request from user:', {
    user_id: auth.user_id,
    email: auth.email,
    role: auth.role,
    project_id: auth.project_id,
  });

  // 2. Get a connection from THIS user's pool — every connection in it
  //    started up as volcano_user_access:{userId}, so RLS is already active.
  let client;
  try {
    client = await connectForUser(auth.user_id);
  } catch (error) {
    // Log diagnostic codes, never connection strings or error messages.
    console.error('Database connection failed', { name: error?.name, code: error?.code ?? 'UNKNOWN' });
    return {
      statusCode: 503,
      headers: { 'Retry-After': '1' },
      body: JSON.stringify({ error: 'Database temporarily unavailable' }),
    };
  }

  try {
    // 3. Parse request
    const { action, ...params } = event;

    // 4. Execute query based on action
    switch (action) {
      case 'get_posts': {
        const { rows } = await client.query(
          `
          SELECT id, title, content, created_at 
          FROM posts 
          WHERE status = $1 
          ORDER BY created_at DESC 
          LIMIT 50
        `,
          ['published'],
        );

        return {
          statusCode: 200,
          body: JSON.stringify({
            posts: rows,
            count: rows.length,
            user_id: auth.user_id,
          }),
        };
      }

      case 'create_post': {
        const { title, content } = params;

        const { rows } = await client.query(
          `
          INSERT INTO posts (title, content, status) 
          VALUES ($1, $2, 'draft') 
          RETURNING id, title, user_id, created_at
        `,
          [title, content],
        );

        // user_id automatically set by trigger using auth.uid()

        return {
          statusCode: 201,
          body: JSON.stringify({ post: rows[0] }),
        };
      }

      case 'update_post': {
        const { id, title, status } = params;

        const { rows } = await client.query(
          `
          UPDATE posts 
          SET title = $1, status = $2 
          WHERE id = $3 
          RETURNING id, title, status
        `,
          [title, status, id],
        );

        // RLS ensures only this user's posts can be updated

        if (rows.length === 0) {
          return {
            statusCode: 404,
            body: JSON.stringify({ error: 'Post not found or access denied' }),
          };
        }

        return {
          statusCode: 200,
          body: JSON.stringify({ post: rows[0] }),
        };
      }

      case 'delete_post': {
        const { id } = params;

        const { rows } = await client.query(
          `
          DELETE FROM posts 
          WHERE id = $1 
          RETURNING id, title
        `,
          [id],
        );

        // RLS ensures only this user's posts can be deleted

        if (rows.length === 0) {
          return {
            statusCode: 404,
            body: JSON.stringify({ error: 'Post not found or access denied' }),
          };
        }

        return {
          statusCode: 200,
          body: JSON.stringify({ deleted: rows[0] }),
        };
      }

      default:
        return {
          statusCode: 400,
          body: JSON.stringify({ error: 'Unknown action' }),
        };
    }
  } catch (error) {
    console.error('Database error:', error);
    return {
      statusCode: 500,
      body: JSON.stringify({ error: error.message }),
    };
  } finally {
    // 5. Return connection to pool
    client.release();
  }
};
```

---

## Summary

### How to Impersonate Auth Users

**Key:** Replace the `application_name` parameter in the connection string (it already carries `volcano_full_access`)

**Format (Simplified):**

```text
volcano_user_access:{user_id}
```

The proxy looks up email and role from the database, making this more secure.

**Methods:**

1. **Volcano function (Recommended):**

   ```javascript
   const auth = event.__volcano_auth;
   const appName = `volcano_user_access:${auth.user_id}`;
   ```

2. **Node.js Server:**

   ```javascript
   const user = await verifiedUser(req); // GET /auth/user
   if (!user) return res.status(401).end();
   const appName = `volcano_user_access:${user.id}`;
   ```

3. **Any Client:**
   ```javascript
   const url = new URL(DATABASE_URL);
   url.searchParams.set('application_name', `volcano_user_access:${userId}`);
   const connStr = url.toString();
   ```

**Result:**

- `auth.uid()` returns the user_id you specified
- `auth.email()` returns the user's email (looked up from database)
- `auth.role()` returns "authenticated", or "anonymous" for an anonymous user
- RLS policies use these values for filtering

---

## See Also

- [Authentication Flow](https://docs.volcano.dev/docs/platform/databases/authentication-flow.md) - Complete auth guide
- [Query Builder API](https://docs.volcano.dev/docs/platform/databases/query-builder-api.md) - Browser query builder
- [Row-Level Security](https://docs.volcano.dev/docs/platform/databases/row-level-security.md) - Using auth.uid() in policies
- [Auth Helpers](https://docs.volcano.dev/docs/platform/databases/auth-helpers.md) - Database helper functions
- [Importing Data](https://docs.volcano.dev/docs/platform/databases/importing-data.md) - Restore a dump and bulk-load rows with COPY
- [Examples](https://docs.volcano.dev/docs/platform/examples/database-pool-example/README.md) - Production patterns
