PostgreSQL uzstādīšana: datubāze, lietotājs, piekļuve

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
# PostgreSQL uzstādīšana, datubāzes un lietotāja izveide
set -e
export DEBIAN_FRONTEND=noninteractive

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

# 1. PostgreSQL un paplašinājumu uzstādīšana
sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

# 2. Lomas (lietotāja) un datubāzes izveide
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. Tiesības uz shēmu public (obligāti no PostgreSQL 15)
sudo -u postgres psql -d ${DB_NAME} -c "GRANT ALL ON SCHEMA public TO ${DB_USER};"

echo "Gatavs: datubāze ${DB_NAME} un lietotājs ${DB_USER} izveidoti."

PostgreSQL ir jaudīga relāciju datubāzu pārvaldības sistēma ar JSON atbalstu, pilnteksta meklēšanu un stingru datu integritāti. Zemāk — uzstādīšana uz Ubuntu 22.04/24.04, datubāzes un lietotāja izveide, piekļuves konfigurācija.

1. solis. Uzstādīšana

PostgreSQL ir pieejama standarta repozitorijā:

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

Pārbaudiet versiju un statusu:

psql --version
sudo systemctl status postgresql

Pakotne postgresql-contrib pievieno noderīgus paplašinājumus (piemēram, pg_stat_statements, uuid-ossp).

2. solis. Loma un datubāze

PostgreSQL izveido sistēmas lietotāju postgres. Pieteicieties ar to konsolē:

sudo -u postgres psql

Izveidojiet lietotāju (lomu) un datubāzi lietotnei:

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 un jaunākās versijās tiesības uz shēmu public pēc noklusējuma ir ierobežotas. Ja lietotne nevar izveidot tabulas, piešķiriet tiesības uz shēmu:

sudo -u postgres psql -d app_db

GRANT ALL ON SCHEMA public TO app_user;
\q

3. solis. Pieteikšanās pārbaude

Pieslēdzieties ar jauno lietotāju lokāli:

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

Ievadiet paroli. Ja parādījās uzvedne app_db=>, piekļuve strādā.

4. solis. Attālinātās piekļuves konfigurācija

Pēc noklusējuma PostgreSQL klausās tikai localhost. Lai pieņemtu ārējus savienojumus (piemēram, no aizmugursistēmas citā serverī), jārediģē divi faili. Atrodiet to ceļus:

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

Failā postgresql.conf norādiet klausīšanās adreses. Atveriet failu redaktorā (nano, vim vai mcedit):

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

Iestatījuma rinda:

listen_addresses = '10.0.0.5, localhost'

Vai arī pierakstiet to ar vienu komandu — nokopējiet un ielīmējiet konsolē (heredoc pievienos rindu faila beigās):

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

Failā pg_hba.conf pievienojiet noteikumu uzticamajam apakštīklam ar paroles šifrēšanu. Atveriet failu redaktorā:

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

Noteikuma saturs:

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

Vai pievienojiet noteikumu ar vienu komandu (pēdiņas 'EOF' — teksts tiks ierakstīts tāds, kāds ir):

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

Nekad neatveriet datubāzi uz 0.0.0.0/0 — tikai konkrētām IP adresēm vai privātajam tīklam. Pielietojiet izmaiņas:

sudo systemctl restart postgresql

Atveriet portu tikai vajadzīgajam avotam:

sudo ufw allow from 10.0.0.10 to any port 5432

Noderīgas psql komandas

Biežākie jautājumi

Ar ko loma atšķiras no lietotāja PostgreSQL? Tas ir viens un tas pats: lietotājs ir loma ar atribūtu LOGIN. CREATE USER ir sinonīms komandai CREATE ROLE ... WITH LOGIN.

Kāpēc lietotne raksta «permission denied for schema public»? Sākot ar PostgreSQL 15, tiesības uz shēmu public ir ierobežotas. Piešķiriet tās skaidri: GRANT ALL ON SCHEMA public TO app_user.

Kā iestatīt paroli lietotājam postgres? Pieteicieties ar sudo -u postgres psql un izpildiet ALTER USER postgres PASSWORD 'jauna_parole';.

Kā pārbaudīt, vai datubāze klausās ārējo portu? sudo ss -tlnp | grep 5432 parādīs klausīšanās adreses. Ja tur ir tikai 127.0.0.1, labojiet listen_addresses.

PostgreSQL ir jutīga pret diska ātrumu un atmiņas apjomu kešatmiņai. Datubāzēm izvēlieties tarifus uz NVMe: skatiet VPS serveru noma un specializēto VPS datu bāzēm.