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.
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.confZmień 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 allZrestartuj 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.serviceSprawdź 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 trustTypy połączeń:
local– socket connection – połączenie lokalne z shell serwera bazodanowegohost– standardowe połączenie TCP/IP poprzez siećhostssl– połączenie TCP/IP tylko z SSLhostnossl– przeciwieństwo poprzedniego czyli, tylko bez SSLhostgssenc– TCP/IP tylko z GSSAPIhostnogssenc– 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 bazodanowymsamenet– 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 bazodanowymreject– odrzucanie połączeńmd5– pozwala na połączenie po otrzymaniu hasła – haszowane hasłapassword– 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 md5Aby 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Łą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 md5 w pg_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.
PGHOSTPGPORTPGDATABASEPGUSERPGPASSWORD
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 fileSprawdzenie 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.htmlpostgres-## \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.htmlZmiana 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:postgres12345Powyż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=postgresPo 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 connectionsMożesz także ustawić w postgresql.conf parametry aktywujące debug połączeń:
log_connections = onlog_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.logJeżeli chcesz kompleksowo poznać administrację bazą danych PostgreSQL lub planujesz ścieżkę zawodową DBA, sprawdź: Kurs Praktyczna Administracja PostgreSQL