PostgreSQL diegimas: bazė, vartotojas, prieiga

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 yra galinga reliacinė duomenų bazių valdymo sistema su JSON palaikymu, pilnateksčia paieška ir griežtu duomenų vientisumu. Žemiau — diegimas Ubuntu 22.04/24.04, duomenų bazės ir vartotojo sukūrimas bei prieigos konfigūracija.

1 žingsnis. Diegimas

PostgreSQL yra standartinėje saugykloje:

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

Patikrinkite versiją ir būseną:

psql --version
sudo systemctl status postgresql

Paketas postgresql-contrib prideda naudingų plėtinių (pavyzdžiui, pg_stat_statements, uuid-ossp).

2 žingsnis. Rolė ir duomenų bazė

PostgreSQL sukuria sisteminį vartotoją postgres. Prisijunkite jo vardu prie konsolės:

sudo -u postgres psql

Sukurkite vartotoją (rolę) ir duomenų bazę programai:

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+ versijose teisės į schemą public pagal numatytuosius nustatymus apribotos. Jei programa negali kurti lentelių, suteikite teises į schemą:

sudo -u postgres psql -d app_db

GRANT ALL ON SCHEMA public TO app_user;
\q

3 žingsnis. Prisijungimo patikra

Prisijunkite nauju vartotoju lokaliai:

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

Įveskite slaptažodį. Jei pasirodė eilutė app_db=> — prieiga veikia.

4 žingsnis. Nuotolinės prieigos konfigūracija

Pagal numatytuosius nustatymus PostgreSQL klauso tik localhost. Kad priimtų išorinius prisijungimus (pavyzdžiui, iš kitame serveryje veikiančios užpakalinės dalies), pataisykite du failus. Suraskite jų kelius:

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

Faile postgresql.conf nurodykite klausymo adresus. Atidarykite failą redaktoriuje (nano, vim arba mcedit):

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

Nustatymo eilutė:

listen_addresses = '10.0.0.5, localhost'

Arba pridėkite ją viena komanda — nukopijuokite ir įklijuokite į konsolę (heredoc prirašys eilutę failo gale):

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

Faile pg_hba.conf pridėkite taisyklę patikimam potinkliui su slaptažodžio šifravimu. Atidarykite failą redaktoriuje:

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

Taisyklės turinys:

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

Arba pridėkite taisyklę viena komanda (kabutės 'EOF' — tekstas bus įrašytas toks, koks yra):

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

Niekada neatverkite duomenų bazės adresu 0.0.0.0/0 — tik konkretiems IP arba privačiam tinklui. Pritaikykite pakeitimus:

sudo systemctl restart postgresql

Atidarykite prievadą tik reikiamam šaltiniui:

sudo ufw allow from 10.0.0.10 to any port 5432

Naudingos psql komandos

Dažni klausimai

Kuo rolė skiriasi nuo vartotojo PostgreSQL sistemoje? Tai tas pats: vartotojas — tai rolė su atributu LOGIN. CREATE USER yra CREATE ROLE ... WITH LOGIN sinonimas.

Kodėl programa rašo „permission denied for schema public“? Nuo PostgreSQL 15 teisės į schemą public apribotos. Suteikite jas aiškiai: GRANT ALL ON SCHEMA public TO app_user.

Kaip nustatyti slaptažodį vartotojui postgres? Prisijunkite sudo -u postgres psql ir įvykdykite ALTER USER postgres PASSWORD 'naujas_slaptazodis';.

Kaip patikrinti, ar bazė klauso išorinio prievado? sudo ss -tlnp | grep 5432 parodys klausymo adresus. Jei tik 127.0.0.1 — taisykite listen_addresses.

PostgreSQL jautri disko greičiui ir atminties kiekiui talpyklai. Duomenų bazėms rinkitės planus su NVMe: žiūrėkite VPS serverio nuoma ir specializuotą VPS duomenų bazėms.