Podstawy PostgreSQL dla Administratora

Artykuł ten pokaże Ci podstawy PostgreSQL dla Administratora. Dowiesz się jak poruszać się po konfiguracji serwera bazodanowego, jak ustawić dostęp do bazy danych, a także poznasz narzędzie psql. Post ten jest materiałem typu problem-rozwiązanie.

10 przydatnych zapytań SQL dla Administratora Baz Danych

Konfiguracja adresu nasłuchu – listen address

Domyślnie po instalacji PostgreSQL i utworzeniu bazy danych, baza nie nasłuchuje ruchu z poza maszyny. Aby zmienić to zachowanie, należy poprawić konfiguracje:

[postgres@postgres-lab ~]$ netstat -lptnu | grep post
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1977/postmaster     
tcp6       0      0 ::1:5432                :::*                    LISTEN      1977/postmaster     

W celu zmiany adresu nasłuchu musisz wyedytować postgresql.conf

Aby to zrobić trzeba zlokalizować nasze PGDATA, czyli lokalizacje z plikami bazy. Znajduje się ona po parametrze -D na poniższym widoku:

[postgres@postgres-lab ~]$ ps aux | grep postgres
postgres  1977  0.0  2.5 286388 14864 ?        Ss   Jun28   0:02 /usr/pgsql-12/bin/postmaster -D /postgresql/data
postgres  1979  0.0  0.2 140768  1360 ?        Ss   Jun28   0:00 postgres: logger   
postgres  1981  0.0  0.5 286504  3028 ?        Ss   Jun28   0:00 postgres: checkpointer   
postgres  1982  0.0  0.2 286388  1696 ?        Ss   Jun28   0:03 postgres: background writer   
postgres  1983  0.0  0.9 286388  5676 ?        Ss   Jun28   0:03 postgres: walwriter   
postgres  1984  0.0  0.4 286924  2688 ?        Ss   Jun28   0:02 postgres: autovacuum launcher 

Zlokalizuj plik konfiguracyjny:

[postgres@postgres-lab ~]$ cd /postgresql/data/
[postgres@postgres-lab data]$ ls -lah postgresql.conf
-rw-------. 1 postgres postgres 26K Jun 28 21:44 postgresql.conf

Zmień w postgresql.conf parametr listen_addresses na adres Twojego serwera bazodanowego, lub na '*’ w celu nasłuchu na wszystkich dostępnych adresach.

[postgres@postgres-lab data]$ vi postgresql.conf

##------------------------------------------------------------------------------
## CONNECTIONS AND AUTHENTICATION
##------------------------------------------------------------------------------

## - Connection Settings -

listen_addresses = '*'          ## what IP address(es) to listen on;
                                        ## comma-separated list of addresses;
                                        ## defaults to 'localhost'; use '*' for all

Zrestartuj PostgreSQL w celu wprowadzenia zmian – możesz to zrobić przy pomocy serwisu systemctl lub komendą pg_ctl -D PGDATA restart z użytkownika postgres na systemie operacyjnym:

[root@postgres-lab ~]## systemctl restart postgresql-12.service

Sprawdź aktualny adres nasłuchu PostgreSQL:

[postgres@postgres-lab ~]$ netstat -lptnu | grep post
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      30161/postmaster    
tcp6       0      0 :::5432                 :::*                    LISTEN      30161/postmaster  

Konfiguracja dostępu zdalnego – pg_hba.conf

PostgreSQL pozwala na ograniczanie dostępu do bazy na poziomie sieciowym. Aby zmodyfikować konfiguracje należy zajrzeć do pliku pg_hba.conf.

Możemy podać w nim informacje z jakiego adresu(ADDRESS) do jakiej bazy(DATABASE) na jakiego użytkownika(USER) przy pomocy jakiej metody(METHOD) pozwalamy na połączenie. Dodatkowo podajemy typ połączenia(TYPE).

Plik ten znajduje się w tej samej lokalizacji co postgresql.conf.

[postgres@postgres-lab ~]$ cd /postgresql/data/

[postgres@postgres-lab data]$ vi pg_hba.conf
## TYPE  DATABASE        USER            ADDRESS                 METHOD

## "local" is for Unix domain socket connections only
local   all             all                                     trust
## IPv4 local connections:
host    all             all             127.0.0.1/32            trust
## IPv6 local connections:
host    all             all             ::1/128                 trust

Typy połączeń:

  • local – socket connection – połączenie lokalne z shell serwera bazodanowego
  • host – standardowe połączenie TCP/IP poprzez sieć
  • hostssl – połączenie TCP/IP tylko z SSL
  • hostnossl – przeciwieństwo poprzedniego czyli, tylko bez SSL
  • hostgssenc – TCP/IP tylko z GSSAPI
  • hostnogssenc – TCP/IP tylko bez GSSAPI

W kolumnie DATABASE możemy podać nazwę bazy danych, lub użyć specjalnej wartości sameuser. Wartość ta zakłada że wpis jest dla bazy danych o dokładnie takiej nazwie jak nazwa użytkownika, który się łączy.

Kolumna USER przyjmuje nazwę użytkownika albo roli. Przy nazwie roli musimy przed nią dopisać jeszcze znak „+”. 

Pole ADDRESS to nic innego jak hostname lub zakres IP w formacie CIDR. Opcjonalnie podobnie jak wcześniej pojawiają się tu słowa specjalne:

  • samehost – wszystkie adresy na serwerze bazodanowym
  • samenet – wszystkie adresy IP w podsieci serwera bazodanowego

Pole METHOD pozwala wybrać metodę uwierzytelniania – najpopularniejsze to:

  • trust – pozwala na połączenie nie bez podawania hasła – głownie ustawiane dla sesji shell na serwerze bazodanowym
  • reject – odrzucanie połączeń
  • md5 – pozwala na połączenie po otrzymaniu hasła – haszowane hasła
  • password – pozwala na połączenie po otrzymaniu hasła – NIE UŻYWAĆ! – hasła przesyłane tzw. „plain text”
  • ldap – pobieranie informacji o kontach i uwierzytelnianiu z LDAP

Więcej o metodach uwierzytelniania i bezpiecznej zmianie hasła dowiesz się z artykułu: PostgreSQL szyfrowanie danych: Hasła

W kolumnach DATABASE i USER możemy podać słowo specjalne all, jeżeli nie chcemy nakładać żadnych restrykcji.

Przykładowy wpis pg_hba.conf

Pozwól dowolnemu użytkownikowi podłączyć się do dowolnej bazy danych z dowolnego IP, wymagaj hasła:

## Network access
host    all             all             0.0.0.0/0               md5

Aby zmiany w pg_hba.conf weszły w życie należy wykonać realod konfiguracji np. przy pomocy poniższego polecenia(podstaw swoje PGDATA i ścieżkę do pg_ctl):

[postgres@postgres-lab data]$ /usr/pgsql-12/bin/pg_ctl -D /postgresql/data reload
server signaled
kurs_praktyczna_administracja_postgresql_vertical_ad

Łączenie do PostgreSQL

Lokalne z serwera bazodanowego

Będzie ono działać bez podawania hasła ponieważ mamy trust w pg_hba.conf dla połączeń local:

[postgres@postgres-lab ~]$ psql
psql (12.3)
Type "help" for help.

Maszyna zdalna

Domyślny syntax URI – możesz połączyć się w ten sposób:
psql postgresql://user:passwd@host:5432/dbame
Lub w nieco bardziej przyjazny sposób:
Łączenie do bazy przy pomocy psql z namiarami na bazę podanymi w parametrach(pytanie o hasło wynika z metody md5pg_hba.conf dla połączeń z adresów 0.0.0.0/0):

[postgres@postgres-lab data]$ psql -h 10.128.0.2 -p 5432
Password for user postgres:
psql (12.3)
Type "help" for help.

postgres=##

Można wykorzystać także parametr -U w celu podania nazwy użytkownika bazodanowego innej niż konto OS na którym jesteśmy zalogowani.

Parametry te mogą być też przekazywane prosto z zmiennych ustawionych w powłoce. Gdy ustawimy je wszystkie podłączenie do bazy ograniczać się będzie do wydania polecenia psql bez żadnych parametrów.

  • PGHOST
  • PGPORT
  • PGDATABASE
  • PGUSER
  • PGPASSWORD

Sprawdzenie bazy do której jesteśmy podłączeni

postgres=## select current_database();
 current_database
------------------
 postgres
(1 row)

Sprawdzenie użytkownika którym jesteśmy zalogowani

postgres=## select current_user;
 current_user
--------------
 postgres
(1 row)

Weryfikacja wersji PostgreSQL

postgres=## select version();
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)

Informacje o połączeniu

postgres=## \conninfo
You are connected to database "postgres" as user "postgres" on host "10.128.0.2" at port "5432".

Wykonywanie operacji z poziomu shell

Polecenia z poziomu shell

[postgres@postgres-lab ~]$ psql -c "select current_time"
    current_time    
--------------------
 14:09:19.854598+00
(1 row)

Skrypty SQL z poziomu shell

[postgres@postgres-lab ~]$ psql -c "select current_time"
    current_time    
--------------------
 14:09:19.854598+00
(1 row)

Wykonanie skryptu SQL i polecenia w jednej komendzie

[postgres@postgres-lab ~]$ psql -c "select current_time" -f create_user.sql -c "select current_time"
    current_time    
--------------------
 14:14:26.922453+00
(1 row)

CREATE ROLE
CREATE ROLE
CREATE ROLE
    current_time    
--------------------
 14:14:26.926545+00
(1 row)

Komendy wbudowane psql

Sprawdzenie dostępnych komend wbudowanych

Polecam przećwiczyć samemu, w celu zobaczenia jak wiele możemy zobaczyć przy pomocy psql – nawet bez znajomości SQL:

postgres=## \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

  Query Buffer
    \e [FILE] [LINE]       edit the query buffer (or file) with external editor
    \ef [FUNCNAME [LINE]]  edit function definition with external editor
    \ev [VIEWNAME [LINE]]  edit view definition with external editor
    \p                     show the contents of the query buffer
    \r                     reset (clear) the query buffer
    \s [FILE]              display history or save it to file
    \w FILE                write query buffer to file

Sprawdzenie składni SQL

postgres-## \h SELECT
Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [...]
postgres-## \h UPDATE
Command:     UPDATE
Description: update rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

URL: https://www.postgresql.org/docs/12/sql-update.html
postgres-## \h ALTER SYSTEM
Command:     ALTER SYSTEM
Description: change a server configuration parameter
Syntax:
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL

URL: https://www.postgresql.org/docs/12/sql-altersystem.html

Zmiana hasła w bezpieczny sposób

W celu poznania metody na bezpieczną zmianę hasła w PostgreSQL, ponownie odsyłam do artykuły: PostgreSQL szyfrowanie danych: Hasła

Zapisywanie haseł do bazy w pliku .pgpass

.pgpass używany jest aby nie trzeba było każdorazowo podawać haseł przy łączeniu do baz danych.
Domyślnie plik .pgpass wyszukiwany jest w katalogu domowym.
Powinien on posiadać uprawnienia 0600.

Można zmienić lokalizacje .pgpass ustawiając zmienna PGPASSFILE.
Przykładowy .pgpass może wyglądać następująco – oczywiście może być w nim wiele wpisów:

[postgres@postgres-lab ~]$ vi .pgpass
postgres-lab:5432:*:postgres:postgres12345

Powyższy .pgpass ustawia hasło postgres12345, dla użytkownika postgres do bazy postgres znajdującej się na maszynie postgres-lab, dostępnej na porcie 5432.
Po zapisaniu .pgpass możemy podłączyć się do bazy bez podawania hasła:

[postgres@postgres-lab ~]$ psql -h postgres-lab -p 5432
psql (12.3)
Type "help" for help.

postgres=##

Service file

Plik service file pozwala na zapisanie danych potrzebnych do połączenia w jednym miejscu i odwoływanie się do nich przy pomocy aliasów.
Service file może występować na poziomie:

  • system level – /etc/pg_service.conf
  • user level – ~/.pg_service.conf

Narzędzia klienckie PostgreSQL zawsze szukają .pg_service.conf na poziomie użytkownika, a dopiero potem na poziomie systemu operacyjnego.
Przykładowy wpis w pliku:

[postgres@postgres-lab ~]$ vi .pg_service.conf
[prod_db]
host=postgres-lab 
port=5432 
dbname=postgres

Po zapisaniu pliku możemy łączyć się psql w następujący sposób(hasło pobrane zostanie z  .pgpass):

[postgres@postgres-lab ~]$ psql service=prod_db user=postgres
psql (12.3)
Type "help" for help.

postgres=##

Troubleshooting połączenia

Binarka pg_isready pozwala sprawdzać dostępność bazy danych:

[postgres@postgres-lab ~]$ /usr/pgsql-12/bin/pg_isready -h postgres-lab -p 5432
postgres-lab:5432 - accepting connections

Możesz także ustawić w postgresql.conf parametry aktywujące debug połączeń:

  • log_connections = on
  • log_disconnections = on

Po ich ustawieniu pamiętaj o przeładowaniu konfiguracji PostgreSQL.
Plik logu domyślnie znajduje się w $PGDATA/log – w naszym przykładzie będzie to lokalizacja:

[postgres@postgres-lab ~]$ ls -lah /postgresql/data/log/
total 24K
drwx------.  2 postgres postgres  162 Jul  3 00:00 .
drwx------. 20 postgres postgres 4.0K Jul  3 00:00 ..
-rw-------.  1 postgres postgres  153 Jul  3 10:07 postgresql-Fri.log
-rw-------.  1 postgres postgres    0 Jun 29 00:00 postgresql-Mon.log
-rw-------.  1 postgres postgres  186 Jun 28 21:46 postgresql-Sun.log
-rw-------.  1 postgres postgres 4.7K Jul  2 17:00 postgresql-Thu.log
-rw-------.  1 postgres postgres   72 Jun 30 19:46 postgresql-Tue.log
-rw-------.  1 postgres postgres    0 Jul  1 00:00 postgresql-Wed.log

Jeżeli chcesz kompleksowo poznać administrację bazą danych PostgreSQL lub planujesz ścieżkę zawodową DBA, sprawdź: Kurs Praktyczna Administracja PostgreSQL