> ## Documentation Index
> Fetch the complete documentation index at: https://docs.jethings.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Management

> Configure PostgreSQL 18 users, roles, remote access, and disaster recovery

## Choose your path

There are two ways to proceed at this point:

* **Create a brand new database** on this server. Follow the steps below to set the password, create the database, and configure roles.
* **Import a database from a replica** if you are recovering from a failed or compromised server. Use the [Disaster recovery: dump from a replica](#disaster-recovery-dump-from-a-replica) instructions at the bottom of this page.

<Note>
  If you import from a replica, you can skip the database creation and seed steps and go straight to the restore instructions.
</Note>

## Set password and create database

```bash theme={null}
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'CHANGE_ME';"
sudo -u postgres psql -c "CREATE DATABASE prod_db;"
```

## Create the three role tiers

<Tip>
  Always use `IN SCHEMA public` (or the relevant schema) for grants, and set default privileges so future tables inherit the correct access automatically.
</Tip>

### 1. Superuser — full ownership

```bash theme={null}
sudo -u postgres psql -c "CREATE USER prod_owner WITH PASSWORD 'CHANGE_ME' SUPERUSER;"
sudo -u postgres psql -d prod_db -c "ALTER DATABASE prod_db OWNER TO prod_owner;"
```

### 2. appuser — full CRUD on all tables (read, insert, update, delete)

```bash theme={null}
sudo -u postgres psql -d prod_db -c "CREATE USER appuser WITH PASSWORD 'CHANGE_ME';"
sudo -u postgres psql -d prod_db -c "GRANT CONNECT ON DATABASE prod_db TO appuser;"
sudo -u postgres psql -d prod_db -c "GRANT USAGE ON SCHEMA public TO appuser;"
sudo -u postgres psql -d prod_db -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;"
sudo -u postgres psql -d prod_db -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;"

# Ensure future tables/sequences also get these grants automatically
sudo -u postgres psql -d prod_db -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;"
sudo -u postgres psql -d prod_db -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO appuser;"
```

### 3. appviewer — scoped access (mixed SELECT-only and read/write tables)

```bash theme={null}
sudo -u postgres psql -d prod_db -c "CREATE USER appviewer WITH PASSWORD 'CHANGE_ME';"
sudo -u postgres psql -d prod_db -c "GRANT CONNECT ON DATABASE prod_db TO appviewer;"
sudo -u postgres psql -d prod_db -c "GRANT USAGE ON SCHEMA public TO appviewer;"

# SELECT-only tables
sudo -u postgres psql -d prod_db -c "GRANT SELECT ON table_a, table_b, table_c TO appviewer;"

# Tables with SELECT + INSERT + UPDATE (no DELETE)
sudo -u postgres psql -d prod_db -c "GRANT SELECT, INSERT, UPDATE ON table_d, table_e TO appviewer;"
```

<Note>
  The exact list of which tables `appviewer` can access, and at which permission level (SELECT-only vs. SELECT/INSERT/UPDATE), is maintained in the **J-Spider-Store-Backend** repo docs. Check there before granting or revoking access for this role — do not assume the same table list applies across environments.
</Note>

<Warning>
  Never grant `appviewer` DELETE or DDL (CREATE/ALTER/DROP) privileges. If a new use case needs broader access, update the role definition in J-Spider-Store-Backend docs first, then apply the grant here.
</Warning>

## Remote access

For remote database work (DBeaver, pgAdmin, TablePlus, etc.), **do not expose PostgreSQL directly to the internet**. Use an SSH tunnel through the VPS instead — Postgres stays bound to `localhost` and only the SSH port is reachable.

### 1. Confirm Postgres is listening on localhost only

```bash theme={null}
sudo grep -E "listen_addresses" /etc/postgresql/18/main/postgresql.conf
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
```

By default this line is commented, which still means PostgreSQL listens on `localhost` only. Do not set it to `'*'`, or remove the `#` to set an explicit value if you change it.

### 2. Check for exposed ports

```bash theme={null}
sudo netstat -tulpn
sudo ss -tulpn
```

PostgreSQL should only be listening on `localhost` or `127.0.0.1` for port `5432`. If you see `0.0.0.0:5432` or `:::5432`, it is exposed to the network.

### 3. Set up the SSH tunnel

**DBeaver:**

1. New connection → PostgreSQL
2. **Main tab**: Host `localhost`, Port `5432`, Database `prod_db`, User/Password of the role you're connecting as
3. **SSH tab**: enable "Use SSH Tunnel" → Host/IP: your VPS IP, Port `22`, User: your VPS SSH user, Auth: private key or password
4. Test Connection — DBeaver tunnels the connection through SSH automatically

**pgAdmin:**

1. New Server → **Connection tab**: Host `localhost`, Port `5432`, Maintenance DB `prod_db`, Username/Password
2. **SSH Tunnel tab**: enable tunneling → Tunnel host: VPS IP, Tunnel port `22`, Username, Identity file (or password)
3. Save

## Useful checks

```bash theme={null}
sudo systemctl status postgresql --no-pager
sudo -u postgres psql -c "SELECT version();"
ls /usr/lib/postgresql/   # list installed major versions

# Confirm role privileges
sudo -u postgres psql -d prod_db -c "\du"
sudo -u postgres psql -d prod_db -c "\dp"   # per-table grant list
```

## Disaster recovery: dump from a replica

If the primary server is compromised, down, or lost, you can recover data from a PostgreSQL replica. On the new/replica server, dump the database locally:

```bash theme={null}
pg_dump -U <db_user> -h localhost -d <replica_db_name> > replica_dump.sql
```

Then move `replica_dump.sql` to a safe location and restore it into the new primary database:

```bash theme={null}
psql -U <db_user> -h localhost -d <new_db_name> < replica_dump.sql
```

<Warning>
  Treat `replica_dump.sql` as sensitive data. It contains the full database, so store it encrypted and delete it once the recovery is complete.
</Warning>

### Build the server

Once the database is restored, finish by generating the client, pulling the latest schema, and building the server:

```bash theme={null}
pnpm db:generate
pnpm db:pull
pnpm build
```

<Note>
  Use `pnpm build:prod` if your repo uses a production build script.
</Note>