Instalace a správa PostgreSQL

PostgreSQL's official birthday is the 8th July. The PostgreSQL project was born in 1996.

Instalace

Debian GNU/Linux

su -
apt-get install postgresql postgresql-client

Knihovna libpqxx

Pokud chcete psát C++ aplikace komunikující s databázovým systémem PostgreSQL, potřebujete knihovnu libpqxx. C++ klient API pro PostgreSQL nainstalujete

 apt-get install libpqxx-dev

Správa

Jediná (implicitní) možnost, jak se připojit k nově vytvořenému databázovému systému (SŘBD) je přihlásit se jako root a pak změnit login na postgres pomocí příkazů

su
su postgres
psql template1
template1=# \q
exit

Pro založení nového uživatele slouží příkazcreateuser, který se ptá, zda nový uživatel má mít právo vytvářet databáze anebo nové uživatele.

su postgres
createuser  prvni
exit

Uživatel postgres může zakládat nové databáze pomocí příkazu createdb, např. může založit implicitni databazi pro prvniho založeného uživatele

createdb prvni

obecně pak

createdb -h localhost -U uzivatel -W heslo

Uživatel se pak může k databázi připojit např. pomocí klienta psql

psql -h localhost -U webuser -W web

Pokud má k tomu oprávnění, může přihlášený uživatel v klientu psql vytvářet databáze příkazem

psql
cepek=#  CREATE DATABASE test
cepek=#  \q 

Více viz psql a Emacs.

Autentifikační konfigurační soubor pg_hba.conf

Autentifikační konfigurační soubor pro PostgreSQL klienty je pg_hba.conf a nachází se v adresáři /etc/postgresql/, resp. v jeho příslušném podadresáři (hba je zkratka z host-based authentication). Formát souboru je popsán v dokumentaci v kapitole Client Authentication.

Při instalaci postgresu je implicitně povolen přístup pro klienty přistupující přes Unix-domain sockets, tj. pro lokální uživatele metodou ident sameuser.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser

Pokud chceme například přístup pro webovou aplikaci v PHP, musíme přístup explicitně povolit. Vytvoříme si databázi a uživatele.

su postgres
createdb webdb
createuser webuser -P  # vyžádá si heslo

Parametr -P v příkazu createuser určuje, že pro vytvářeného uživatele bued vyžádáno heslo.

Novému uživatel přidělíme přístupová práva k databázi

su postgres
psql
GRANT ALL PRIVILEGES ON DATABASE webdb TO webuser;

Do souboru pg_hba.conf přidáme záznam pro uživatele webuser a databázi webdb (v našem případě přihlášení pouze z lokálního počítače)

host    webdb       webuser 127.0.0.1 255.255.255.255 md5

Z PHP se nyní můžeme připojit pomocí pg_connect

$dbconn = pg_connect("host=127.0.01 dbname=webdb user=webuser  password=tajne.heslo")
   or die('Could not connect: ' . pg_last_error());

Obdobně z programu v jazyce Python nebo třeba C++ (knihovna libpqx)

Pokud se chceme přihlásit jako uživatel webuser pomocí psql, musíme explicitně zadat jméno počítače, uživatele, databáze a heslo.

psql -h localhost -d webdb -U webuser -W

Ident authentizace

Při ident authentizaci se pro přihlašení k databázi využívají uživatelská jména pod kterými jsou uživatelé přihlášeni do systému. Pokud je uživatel přihlášen do systému např. pod uživatelským jménem pytel, při přihlašování k databázi psql databaze SQL monitor psql se s tímto jménem pokusí k databázi přihlásit. Upozornění: Ident authentizace často vede k mylnému dojmu, že uživatelské konta a databázová konta jsou jedno a to samé, což samozřejme není pravda. V následujícím textu se pokusme ident authentizaci nastavit, právě touto authentizací se přihlašují studenti k databázi osdl během cvičení z předmětu OSYL. V tomto návodu si vytvoříme ident authentizaci k databázi osdl pro dva uživatele pytel a cepek. Popsané činnosti by se analogicky prováděly i pro více uživatelů.

Ident authentizace - vytvoření nového db uživatele

Nejprve je nutné založit nové databázové uživatele, pokud již neexistují. Pro založení nového uživatele slouží příkaz createuser, který se ptá, zda nový uživatel má mít právo vytvářet databáze anebo nové uživatele. Pokud příkaz createuser spustíme s parametry -A -D, program již ví že uvedení uživatelé nemají mít práva vytvářet nové uživatele a vytvářet nové databáze - vhodné při dávkovém vytváření db uživatelů.

su postgres
createuser  -A -D pytel
createuser  -A -D cepek
exit

Ident authentizace - sobor /etc/postgresql/pg_ident.conf

Při ident authentizaci je zjištěno uživatelské jméno přihlášeného uživatele který inicioval spojení a PostgreSQL zkontroluje zdali má uživatel povolení se přihlásit. Tato kontrola je prováděna pomocí souboru /etc/postgresql/pg_ident.conf. Tento soubor má následující formát:

 map-name ident-username database-username

PostgreSQL má předdefinovánu mapu sameuser, tato mapa umožňuje přihlásit se kterémukoliv uživateli operačního systému k databázi stejného jména, jestliže existuje. Ostatní mapy se mustí vytvořit manuálně a obsahují seznam uživatelů OS a odpovídajících db uživatelů. Pro uživatele pytel a cepek vytvoříme mapu s názvem osyl:

 osyl pytel pytel
 osyl cepek cepek

Ident authentizace - soubor /etc/postgresql/pg_hba.conf

Autentifikační konfigurační soubor pro PostgreSQL klienty je pg_hba.conf a nachází se v adresáři /etc/postgresql/, resp. v jeho příslušném podadresáři (hba je zkratka z host-based authentication). Formát souboru je popsán v dokumentaci v kapitole Client Authentication. Pokud chceme přidat map-name ze souboru /etc/postgresql/pg_ident.conf, musíme do tohoto souboru přidat následující řádku

 local  database-name all      ident map-name

Pro náš ukázkový případ vypadá řádka následovně:

 local  osdl     all      ident osyl

Tato řádka znamená: Pro připojení z lokálního počítače povol ident připojení k databázi osdl pro všechny uživatele uvedené v mapě osyl.

Nyní se musí restartovat databáze PostgreSQL a poté se již uživatelé pytel a cepek mohou přihlásit k databázi osdl:

  su -
  /etc/init.d/postgresql restart

Ukázka přihlášení:

 pytel@prasatko:~$ psql ocdl
   Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
   Type:  \copyright for distribution terms
          \h for help with SQL commands
          \? for help on internal slash commands
          \g or terminate with semicolon to execute query
          \q to quit
   ocdl=#

Aby uživatelé pytel, cepek mohli v databázi osdl provádět libovolné akce je ideální vytvořit v databázi pro každého uživatele schéma ve kterém bude pracovat:

 CREATE SCHEMA AUTHORIZATION pytel
 CREATE SCHEMA AUTHORIZATION cepek

Výpis databázových uživatelů - změna uživatelských práv

Jedním ze způsobů výpisu databázových uživatelů je využití view pg_user:

su postgres
psql 
postgres=# SELECT * FROM pg_user LIMIT 5;
usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
---------+----------+-------------+----------+-----------+----------+----------+-----------
postgres |       10 | t           | t        | t         | ******** |          |
havlicka |    16387 | f           | f        | f         | ******** |          |
ctrnakar |    16388 | f           | f        | f         | ******** |          |
havloja1 |    16389 | f           | f        | f         | ******** |          |
horejmir |    16390 | f           | f        | f         | ******** |          |
(5 rows)

V případě že chceme změnit parametry pro jednotlivé databázové uživatele, lze použít příkaz ALTER USER. Například přidání, resp. odebrání superuživatele pro databázového uživatele pytel má následující tvar:

 postgres=# ALTER USER pytel SUPERUSER ;
 ALTER ROLE
 postgres=# ALTER USER pytel NOSUPERUSER ;
 ALTER ROLE

Odkazy

• Databázové systémy
• Psql a Emacs