PostgreSQL Management

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 instructions at the bottom of this page.

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

Set password and create database

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

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

1. Superuser — full ownership

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)

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)

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;"

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.

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.

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

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

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:

New connection → PostgreSQL
Main tab: Host localhost, Port 5432, Database prod_db, User/Password of the role you’re connecting as
SSH tab: enable “Use SSH Tunnel” → Host/IP: your VPS IP, Port 22, User: your VPS SSH user, Auth: private key or password
Test Connection — DBeaver tunnels the connection through SSH automatically

pgAdmin:

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

Useful checks

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:

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:

psql -U <db_user> -h localhost -d <new_db_name> < replica_dump.sql

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

Build the server

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

pnpm db:generate
pnpm db:pull
pnpm build

Use pnpm build:prod if your repo uses a production build script.

​Choose your path

​Set password and create database

​Create the three role tiers

​1. Superuser — full ownership

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

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

​Remote access

​1. Confirm Postgres is listening on localhost only

​2. Check for exposed ports

​3. Set up the SSH tunnel

​Useful checks

​Disaster recovery: dump from a replica

​Build the server

Choose your path

Set password and create database

Create the three role tiers

1. Superuser — full ownership

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

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

Remote access

1. Confirm Postgres is listening on localhost only

2. Check for exposed ports

3. Set up the SSH tunnel

Useful checks

Disaster recovery: dump from a replica

Build the server