webinstall.dev/postgres Star on GitHub

PostgreSQL: The World's Most Advanced Open Source Relational Database.

https://www.postgresql.org/| Installer Source| Releases (json) (tab)

To update or switch versions, run webi postgres@stable (or @v10, @beta, etc).

Files

These are the files / directories that are created and/or modified with this install:

~/.config/envman/PATH.env
~/.local/opt/postgres/
~/.local/share/postgres/var/postgresql.conf

# for psql (postgres client)
~/.pgpass
~/.psqlrc
~/.config/psql/

Cheat Sheet

Postgres is the all-in-one database for beginners and experts alike. It handles SQL, 'NoSQL', JSON, HSTORE, Full-Text Search, Messages Queues and more. Best bang for buck.

To enable Postgres as a Linux Service with serviceman:
(see macOS below)

serviceman add --name 'postgres' --workdir ~/.local/share/postgres/var -- \
    postgres -D ~/.local/share/postgres/var -p 5432

sudo systemctl restart systemd-journald

To login:
(see creating remote app users below)

# as Postgres admin
psql "postgres://postgres:postgres@localhost:5432/postgres"

# as remote user
psql "postgres://db-xxxx@pg-1.example.com:5432/db-xxxx?sslmode=require&sslnegotiation=direct"

Table of Contents

  • Server vs Client & PG Essentials
  • Initialize a database with a password
  • Start the Postgres Server
    • development
    • systemd (most Linuxes)
    • OpenRC (container Linuxes)
    • macOS
  • Enable Secure Remote Access
  • Create Secure Remote App (User/DB)
  • Change an User's (App's) password

Where to Find the Postgres Client Cheat Sheet

This is exclusively a server-side cheat sheet.

For the client-side cheat sheet, see The PSQL (Client) Cheat Sheet.

For a collection of other helpful scripts, see PG Essentials:

  • pg-register-service
  • pg-addgroup
  • pg-adduser
  • pg-passwd

How to Initialize the Postgres Database (with password)

  1. Create a database directory, paired to the Postgres version
    mkdir -p ~/.local/share/postgres-17/var/
  2. Use a password file and initdb to create a new DB directory
    echo 'postgres' > /tmp/pwfile && \
initdb -D ~/.local/share/postgres/var/ \
    --username 'postgres' --pwfile /tmp/pwfile \
    --auth-local=password --auth-host=password \ &&
rm /tmp/pwfile
  3. Test that the server starts
    postgres -D ~/.local/share/postgres/var -p 5432
    (kill with ctrl+c)

How to run the Postgres server (daemon or foreground)

  • foreground (development)
  • systemd (Debian, Ubuntu, Redhat, etc)
  • OpenRC (Alpine, Arch, Gentoo, etc)
  • macOS (launchd)

To run in the foreground (for development):

postgres -D ~/.local/share/postgres/var -p 5432

Debian / Systemd

curl https://webi.sh/serviceman | sh

serviceman add --name 'postgres' --workdir ~/.local/share/postgres/var -- \
    postgres -D ~/.local/share/postgres/var -p 5432

sudo systemctl restart systemd-journald
sudo journalctl -xefu postgres

Alpine / OpenRC

/etc/init.d/postgres:

#!/sbin/openrc-run

name="postgres"
description="postgres daemon"
command="/home/app/.local/opt/postgres/bin/postgres"
command_args="-D /home/app/.local/share/postgres/var -p 5432"
command_user="app:app"

supervisor="supervise-daemon"
output_log="/var/log/postgres"
error_log="/var/log/postgres"

depend() {
    need net
}

start_pre() {
    checkpath --directory --owner root /var/log/
    checkpath --file --owner ${command_user} ${output_log} ${error_log}
}

start() {
    ebegin "Starting ${name}"
    supervise-daemon ${name} --start \
        --stdout ${output_log} \
        --stderr ${error_log} \
        --pidfile /run/${RC_SVCNAME}.pid \
        --respawn-delay 5 \
        --respawn-max 10 \
        -- \
        ${command} \
        ${command_args} \
    eend $?
}

stop() {
    ebegin "Stopping ${name}"
    supervise-daemon ${name} --stop \
        --pidfile /run/${RC_SVCNAME}.pid
    eend $?
}

sudo rc-update add postgres
sudo rc-service postgres restart

sudo tail -f /var/log/postgres

macOS

serviceman add --name 'postgres' --workdir ~/.local/share/postgres/var -- \
    postgres -D ~/.local/share/postgres/var -p 5432

tail -f ~/.local/share/postgres/var/log/postgres.log

How to Enable Secure Remote Postgres Access

  1. Create the my_remote_users group:

    echo 'CREATE ROLE "my_remote_users" NOLOGIN;' |
    psql "postgres://postgres:postgres@localhost:5432/postgres" -f -

  2. Update the hba permissions to enable App users (sameuser):
    ~/.local/share/postgres/var/pg_hba.conf:

    # Allow 'my_remote_users' to connect remotely directly over the internet
hostssl sameuser         +my_remote_users        0.0.0.0/0             scram-sha-256
hostssl sameuser         +my_remote_users        ::0/0                 scram-sha-256

# Allow 'my_remote_users' group to connect through a local TLS-terminating proxy
hostnossl sameuser       +my_remote_users        127.0.0.1/8           scram-sha-256
hostnossl sameuser       +my_remote_users        ::1/128               scram-sha-256
host      sameuser       +my_remote_users        10.0.0.0/8            scram-sha-256
host      sameuser       +my_remote_users        172.16.0.0/12         scram-sha-256
host      sameuser       +my_remote_users        192.168.0.0/16        scram-sha-256
host      sameuser       +my_remote_users        fc00::/7              scram-sha-256

  3. Create a 10-year Self-signed cert for your server name or IP

    my_host=pg-1.example.com

openssl req -new -x509 -days 3650 -nodes -text \
    -out ./server.crt -keyout ./server.key -subj "/CN=$my_host"

chmod 0600 ./server.key ./server.crt
mv ./server.key ./server.crt ~/.local/share/postgres/var/

  4. Update the main postgres config to allow secure connections:
    ~/.local/share/postgres/var/postgresql.conf:

    ssl = on
password_encryption = scram-sha-256
listen_addresses = '*'

  5. Restart Postgres for the change to take effect

    # all unixes
killall postgres

# systemd (Debian, Ubunut, RedHat, Suse, etc)
sudo systemctl restart postgres

# openrc (Alpine, Gentoo, Arch)
rc-service postgres restart

# macos
launchctl unload ~/Library/LaunchAgents/postgres.plist
launchctl load ~/Library/LaunchAgents/postgres.plist

How to Create Secure Remote Users

The sameuser database directive REQUIRES the user and db name to be the same.

my_app="foobar_xxxx"

# e.g. generate password / token with 'xxd -l16 -ps /dev/urandom'
my_token="db-token-for-the-app"

echo "
   CREATE DATABASE \"$my_app\";
   CREATE ROLE \"$my_app\" LOGIN INHERIT
       IN ROLE \"my_remote_users\" ENCRYPTED PASSWORD '$my_token';
   GRANT ALL PRIVILEGES ON DATABASE \"$my_app\" to \"$my_app\";
" | psql "postgres://postgres:postgres@localhost:5432/postgres" -f -

Notes:

  • quote style matters:
    • double quotes " for identifiers (users, tables, groups, roles)
    • single quotes ' for values (password)

How to Change a DB's Password

my_app='foobar_xxxx'
my_token="$(xxd -l16 -ps /dev/urandom)"

echo "ALTER USER \"$my_app\" PASSWORD '$my_token';" |
    psql 'postgres://postgres:postgres@localhost:5432/postgres' -f -

Contribute

Report an Issue Submit Installer Star on GitHub