PostgreSQL-i paigaldamine: andmebaas, kasutaja, ligipääs

curl -sSL https://cp.lv/scripts/ustanovit-postgresql.sh | sudo bash

wget -qO- https://cp.lv/scripts/ustanovit-postgresql.sh | sudo bash

#!/usr/bin/env bash
# Install PostgreSQL, create a database and a user
set -e
export DEBIAN_FRONTEND=noninteractive

DB_NAME=app_db
DB_USER=app_user
DB_PASS='StrongPassword123!'

# 1. Install PostgreSQL and the contrib extensions
sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

# 2. Create the role (user) and the database
sudo -u postgres psql << EOF
CREATE ROLE ${DB_USER} WITH LOGIN PASSWORD '${DB_PASS}';
CREATE DATABASE ${DB_NAME} OWNER ${DB_USER};
GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} TO ${DB_USER};
EOF

# 3. Grants on the public schema (required from PostgreSQL 15 on)
sudo -u postgres psql -d ${DB_NAME} -c "GRANT ALL ON SCHEMA public TO ${DB_USER};"

echo "Done: database ${DB_NAME} and user ${DB_USER} created."

PostgreSQL on võimas relatsiooniline andmebaasisüsteem, mis toetab JSON-i, täistekstiotsingut ja ranget andmeterviklust. Allpool on paigaldus Ubuntu 22.04/24.04 peale, andmebaasi ja kasutaja loomine ning ligipääsu seadistamine.

Samm 1. Paigaldamine

PostgreSQL on standardhoidlas olemas:

sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl enable --now postgresql

Kontrolli versiooni ja olekut:

psql --version
sudo systemctl status postgresql

Pakett postgresql-contrib lisab kasulikud laiendused (näiteks pg_stat_statements, uuid-ossp).

Samm 2. Roll ja andmebaas

PostgreSQL loob süsteemikasutaja postgres. Logi tema alt konsooli:

sudo -u postgres psql

Loo rakenduse jaoks kasutaja (roll) ja andmebaas:

CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPassword123!';
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;
\q

PostgreSQL 15+ puhul on skeemi public õigused vaikimisi piiratud. Kui rakendus ei saa tabeleid luua, anna õigused skeemile:

sudo -u postgres psql -d app_db

GRANT ALL ON SCHEMA public TO app_user;
\q

Samm 3. Sisselogimise kontroll

Ühendu uue kasutajaga lokaalselt:

psql -h 127.0.0.1 -U app_user -d app_db -W

Sisesta parool. Kui ilmus viip app_db=>, siis ligipääs töötab.

Samm 4. Kaugligipääsu seadistamine

Vaikimisi kuulab PostgreSQL ainult localhosti. Väliste ühenduste vastuvõtmiseks (näiteks teises serveris asuvalt taustarakenduselt) muuda kahte faili. Leia nende teed:

sudo -u postgres psql -c 'SHOW config_file;'
sudo -u postgres psql -c 'SHOW hba_file;'

Failis postgresql.conf määra kuulatavad aadressid. Ava fail redaktoris (nano, vim või mcedit):

sudo nano "$(sudo -u postgres psql -tAc 'SHOW config_file;')"

Seadistuse rida:

listen_addresses = '10.0.0.5, localhost'

Või lisa see ühe käsuga — kopeeri ja kleebi konsooli (heredoc lisab rea faili lõppu):

sudo tee -a "$(sudo -u postgres psql -tAc 'SHOW config_file;')" > /dev/null << 'EOF'
listen_addresses = '10.0.0.5, localhost'
EOF

Failis pg_hba.conf lisa reegel usaldusväärse alamvõrgu jaoks koos parooli krüpteerimisega. Ava fail redaktoris:

sudo nano "$(sudo -u postgres psql -tAc 'SHOW hba_file;')"

Reegli sisu:

# TYPE  DATABASE  USER      ADDRESS         METHOD
host    app_db    app_user  10.0.0.0/24     scram-sha-256

Või lisa reegel ühe käsuga (jutumärgid 'EOF' — tekst kirjutatakse nii, nagu on):

sudo tee -a "$(sudo -u postgres psql -tAc 'SHOW hba_file;')" > /dev/null << 'EOF'
host    app_db    app_user  10.0.0.0/24     scram-sha-256
EOF

Ära kunagi ava andmebaasi aadressile 0.0.0.0/0 — ainult konkreetsetele IP-dele või privaatvõrgule. Rakenda muudatused:

sudo systemctl restart postgresql

Ava port ainult vajalikule allikale:

sudo ufw allow from 10.0.0.10 to any port 5432

Kasulikud psql-i käsud

Korduma kippuvad küsimused

Mille poolest erineb roll kasutajast PostgreSQL-is? See on üks ja sama: kasutaja on roll atribuudiga LOGIN. CREATE USER on sünonüüm käsule CREATE ROLE ... WITH LOGIN.

Miks kirjutab rakendus «permission denied for schema public»? Alates PostgreSQL 15-st on skeemi public õigused piiratud. Anna need selgesõnaliselt: GRANT ALL ON SCHEMA public TO app_user.

Kuidas määrata kasutajale postgres parool? Logi sisse käsuga sudo -u postgres psql ja käivita ALTER USER postgres PASSWORD 'uus_parool';.

Kuidas kontrollida, kas andmebaas kuulab välist porti? sudo ss -tlnp | grep 5432 näitab kuulatavaid aadresse. Kui seal on ainult 127.0.0.1, siis muuda listen_addresses.

PostgreSQL on tundlik ketta kiiruse ja keši jaoks mõeldud mälumahu suhtes. Andmebaaside jaoks vali NVMe-paketid: vaata VPS-i rent ja eriotstarbeline andmebaasi VPS.