# PostgreSQL Remote Access Guide

Updated: 2026-03-13

## Server endpoint

- Host: `user2804.openweb.direct`
- Port: `5432`
- Default admin database: `postgres`
- Admin role: `neondb_owner`
- Authentication: `SCRAM-SHA-256`
- Network access: `0.0.0.0/0` and `::/0`
- TLS: enabled

PostgreSQL does not provide a built-in REST API. Remote servers use the PostgreSQL wire protocol over TCP and interact with the database through SQL drivers.

## Connection requirements

Use these settings from remote machines:

- Host: `user2804.openweb.direct`
- Port: `5432`
- Database: the target database name
- Username: the target PostgreSQL role
- Password: that role's password
- SSL mode: `verify-full` recommended

Example DSN:

```text
postgresql://USERNAME:PASSWORD@user2804.openweb.direct:5432/DATABASE_NAME?sslmode=verify-full
```

JDBC format:

```text
jdbc:postgresql://user2804.openweb.direct:5432/DATABASE_NAME?sslmode=verify-full
```

## Admin access

Use the `neondb_owner` role for administration only. Do not give that role to application servers.

Example admin login with `psql`:

```bash
psql "host=user2804.openweb.direct port=5432 dbname=postgres user=neondb_owner sslmode=verify-full"
```

## Recommended client provisioning model

Create one PostgreSQL role and one database per client or application.

Example:

```sql
CREATE ROLE client_app_01
WITH LOGIN PASSWORD 'replace-this-password';

CREATE DATABASE client_app_01
OWNER client_app_01;
```

Client application connection string:

```text
postgresql://client_app_01:replace-this-password@user2804.openweb.direct:5432/client_app_01?sslmode=verify-full
```

Server-side provisioning command:

```bash
/usr/local/bin/provision-postgresql-client.sh client_app_01 client_app_01_admin 'replace-this-password'
```

This creates:

- one database owned by the client admin role
- one login role for that client
- no superuser, cluster-admin, or replication privileges for the client role
- a locked-down database where `PUBLIC` does not keep default database-wide access

## Common SQL operations

Health check:

```sql
SELECT now();
SELECT current_database();
SELECT current_user;
```

Create a table:

```sql
CREATE TABLE IF NOT EXISTS customers (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

Insert:

```sql
INSERT INTO customers (email) VALUES ('user@example.com');
```

Read:

```sql
SELECT id, email, created_at
FROM customers
ORDER BY id DESC
LIMIT 100;
```

Update:

```sql
UPDATE customers
SET email = 'new@example.com'
WHERE id = 1;
```

Delete:

```sql
DELETE FROM customers
WHERE id = 1;
```

## Driver examples

Python with `psycopg`:

```python
import psycopg

conn = psycopg.connect(
    "postgresql://client_app_01:replace-this-password@user2804.openweb.direct:5432/client_app_01?sslmode=verify-full"
)

with conn, conn.cursor() as cur:
    cur.execute("SELECT now()")
    print(cur.fetchone())
```

Node.js with `pg`:

```javascript
import pg from "pg";

const client = new pg.Client({
  host: "user2804.openweb.direct",
  port: 5432,
  database: "client_app_01",
  user: "client_app_01",
  password: "replace-this-password",
  ssl: { rejectUnauthorized: true }
});

await client.connect();
const result = await client.query("SELECT now()");
console.log(result.rows[0]);
await client.end();
```

Java with JDBC:

```java
String url = "jdbc:postgresql://user2804.openweb.direct:5432/client_app_01?sslmode=verify-full";
Connection conn = DriverManager.getConnection(url, "client_app_01", "replace-this-password");
```

## Operational notes

- The server is reachable publicly on `5432`.
- A trusted Let's Encrypt certificate is installed for `user2804.openweb.direct`.
- Standard clients can use `sslmode=verify-full` and validate the certificate normally.
- A nightly logical backup runs via systemd at `02:15` server time and keeps 14 days of dumps in `/var/backups/postgresql`.
- Rotate the `neondb_owner` password after initial setup.
- Use separate roles and databases for each client instead of sharing the admin role.
- Restrict access by firewall or allowed IP ranges if your client list becomes stable.
- Backups are configured, but monitoring and alerting are not yet configured.