PostgreSQL 17 Setup

Add the PGDG repository

sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

sudo apt update

Install PostgreSQL 17

sudo apt install -y postgresql-17 postgresql-client-17

sudo systemctl enable postgresql
sudo systemctl start postgresql

Verify the version:

  psql --version
  # psql (PostgreSQL) 17.7 (Ubuntu 17.7-3.pgdg24.04+1)

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/17/main/postgresql.conf
# listen_addresses = 'localhost'   <- keep this, do not change to '*'

2. 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

​Add the PGDG repository

​Install PostgreSQL 17

​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. Set up the SSH tunnel

​Useful checks

Add the PGDG repository

Install PostgreSQL 17

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. Set up the SSH tunnel

Useful checks