PostgreSQL haldamine

Sissejuhatus

TODO

Skeemid

• Võimaldada mitmetele kasutajatel kasutada sama andmebaasi säilitades kasutajate privaatsuse.
• Korraldada andmebaasi objektid loogilistesse gruppidesse selleks, et andmebaas oleks paremini hallatav
• Erinevates skeemides saab kasutada samu objektide nimesid

Skeemi moodustamiseks sobib öelda, tavaliselt tekitab nii süsteemi administraator oma kasutajatele skeeme

CREATE SCHEMA priiduskeem AUTHORIZATION priit;

kus

• priiduskeem - tekitatava skeemi nimi
• priit - andmebaasi kasutaja (üldisemalt roll)

Skeemide moodustamisel peab nimevalikul arvestama, et pg_ algusega nimede on reserveeritud kasutamiseks pg_catalog skeemis ning kuigi kasutajad saavad neid nimesid ka ise kasutada, kasutab andmebaas vaikimisi süsteemseid objekte kui sellise nimega pöörduda.

Kui andmebaasiga töötades skeemi mitte näidata, siis vaikimisi toimub tegevus

• kasutajanimelises skeemis
• kasutajanimelise skeemi puudumisel skeemis 'public'; see skeem moodustatakse andmebaasi loomisel automaatselt.

test=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

Skeemirada töötab sarnaselt tabelile ka muude skeemis sisalduvate objektidega, nt funktsioonid.

Skeemi suhtes on kasutajatel kahe sorti privileege

• usage - skeemis sisalduvate objektide kasutamine
• create - skeemi objektide tekitamine

Vaikimisi saavad kõik andmebaasi kasutajad töötada public skeemis, st seda kasutada ja sinna tekitada objekte.

Alati sisaldub andmebaasis nö süsteemne skeem pg_catalog ja seda kasutatakse enne skeemirajalt objektide otsimist.

public skeem on iseenesest tavaline skeem nagu iga teinega, nt võib selle vajadusel ka kustutada.

Skeemiraja seadistamiseks sobib öelda

set search_path to skeeminimi1, skeeminimi2

PostgreSQL privileegisüsteem

PostgreSQL privileegisüsteem kontrollib, mida saavad erinevad kasutajad andmebaasis teha. Üldiselt on andmebaasi ligipääsule võimalik piiranguid seada kolmel tasemel

• võrguühenduse tase - nt andmebaasi ees töötavas tulemüüris võrguühendusi src ipi aadressi täpsusega piirates
• pg_hba.conf failis
• andmebaasi sisemiste vahenditega

Andmebaasi sisemiste vahenditega saab ligipääse piirata kõige täpsemalt kusjuures seadistatud ligipääse hoib andmebaas selleks ettenähtud andmebaasi tabelites.

Kasutajad ja grupid - rollid

Üldiselt öeldakse, et PostgreSQLi andmebaasi kasutajad tegutsevad andmebaasi kasutades mingites rollides

• andmebaasi objektide, nt tablitele juures on kirjas, millise rolliga kasutaja saab kõnealuse objektida millist tegevust sooritada, nt tabelit sisu select käsuga lugeda
• andmebaasi sisselogimiseks on kasutajal vaja teada mõnda andmebaasis kirjeldatud LOGIN omadusega rolli ligipääse (sh parooli)
• rollid võivad moodustada hierarhiaid, mida võib praktiliselt ette kujutada grupikuuluvustena; praktiliselt tähendab see nt seda, et andmebaasis kirjeldatakse roll ja sellega seostatakse mingid ligipääsud erinevatele objektidele ning selleks, et konkreetsed sisselogivad kasutajad saaksid kõnealustele objektidega tegevusi sooritada määratakse nad kirjeldatud rolli nö pärijateks

Rollid on kirjeldatud kogu andmeklustri ehk PostgreSQL installatsiooni eksemplari kohta, mitte iga andmebaasi kohta. Tuleb arvestada, et sama nö ühenduse sees saab kasutada ainult ühte andmebaasi, st nt ei saa sooritada päringut, mis tegelab erinevate baaside tabelitega.

Rollide moodustamine

Andmebaasi paigaldamisel tekitatakse andmebaasi selle kasutaja nimeline LOGIN omadusega roll, millena andmebaas paigaldati, traditsiooniliselt on selle rolli nimeks 'postgres'.

Järgnevate rollide moodustamiseks sobib öelda

create role priit

Rolli eemaldamiseks sobib öelda

drop role priit

Rollide kasutamine

Käesoleva rollinime küsimiseks sobib öelda

select user;

Uue rolli omandamiseks sobib öelda

set role uusrollinimi

Vaikimisi rolli tagasilülitamiseks

reset role

Rollidele andmebaasiobjektide kasutamise lubamine

Andmebaasi objektidel on privileegisüsteemi seisukohast kaks olulist omadust

• objekti omanik (ingl. k. owner) - objekti moodustanud roll, kellel on objekti suhtes kõikvõimalikud õigused
• pääsunimekiri (ingl. k. access control list, ACL) - millised omanikud saavad kõnealuse objektida milliseid tegevusi sooritada

Tabel

Olgu andmebaasi administraator tekitanud kaks rolli priit ning mart ning moodustanud kummagi kasutaja jaoks andmebaasi nendenimelise skeemi. Sellises olukorras saavad mõlemad kasutajad oma skeemis toimetada kuid üksteise tabelitele sisule ligi ei pääse.

test=> select * from priit.priidutabel;
ERROR:  permission denied for schema priit
LINE 1: select * from priit.priidutabel;

Selleks, et kasutaja mart pääseks lugema kasutaja priit tabeli priit.priidutabel sisu peab priit kõnealusele tabelile andma kasutajale mart select pääsu

GRANT SELECT ON TABLE priit.priidutabel TO mart;

Tabeli ligipääsude kohta saab küsida öeldes

test=> \z priit.priidutabel 
                               Access privileges
 Schema |    Name     | Type  |  Access privileges  | Column access privileges 
--------+-------------+-------+---------------------+--------------------------
 priit  | priidutabel | table | priit=arwdDxt/priit | 
                             : mart=r/priit          
(1 row)

kust on näha

• priit=arwdDxt/priit - tabel kuulub kasutajale priit ning tal on omanikuna kõik privileegid
• mart=r/priit - tabel kuulub kasutajale priit ning kasutajal mart on sellele r privileeg (select)

Tabelilt ligipääsu eemaldamiseks sobib öelda

REVOKE select ON priit.priidutabel TO mart;

Tabelile saab ligipääse anda ka tulba täpsusega, nt

GRANT SELECT(nimi) ON priit.priidutabel TO mart;

kusjuures siis on võimalik küsida selectiga ainult vastava tulba andmeid.

Kasutaja parooli muutmine

ALTER USER kasutajanimi WITH PASSWORD 'parool';

SQL arhiivifaili moodustamine

pg_dump programmi abil saab teha PostgreSQL andmebaasi (mitte kogu andmeklustrist, st kõigist andmebaasidest ühekorraga) andmetest erinevatel tasemetel koopiaid - andmebaasist, skeemist, tabelist, ja määrata seejuures täpsustusi, nt kas kopeeritakse ainult struktuur või struktuur koos andmetega. pg_dump kasutamise tulemusena moodustatakse arhiivifail.

Programmi kasumisel saab valida kolme formaadi vahel, milles väljund tekitatakse

• tekst (ingl. k. plain) - tekstikujul SQL skript, vaikeformaat
• kostümiseeritud arhiiv (ingl. k. custom) - binaarne ahriiv, vaikimisi pakitud, võimaldab kõige paindlikumalt pärast arhiivifailis olevaid andmeid kasutada
• tar - TODO

pg_dump moodustab tänu MVCC tehnika kasutamisele andmetest kooskõlalise väljundi, nii nagu andmete seis oli pg_dump käivitamisel. Kuig pg_dump kasutamise ajal on nö tavapärane andmebaasi kasutamine võimalik, kasutab ta lukke selliselt, et nt samal ajal ei saab tabelite struktuuri muuta.

SQL skripti moodustamine

SQL skripti kujul esineva arhiivifaili moodustamiseks sobib öelda nt

 $ export PGPASSWORD=parool
 $ pg_dump -U postgres -h localhost baasinimi -f baasinimi-20090802.sql 
   1> baasinimi-20090802-1.log 2> baasinimi-20090802-2.log

Skripti tekitamisel saab määrata nt, kas skripti lisatakse käsud andmebaasi, skeemi või tabeli moodustamise kohta või mitte.

Kostümiseeritud arhiivifaili moodustamine

Nt andbaasi portaal skeemi data tabelist documents kostümiseeritud arhiivi moodustamiseks tuleb öelda

 $ export PGPASSWORD=parool
 $ /pg_dump -Fc -h localhost -U postgres -t data.documents portaal > portaal.data.documents.fcdump

SQL arhiivifaili kasutamine

Andmete taastamiseks sobib esimesel juhul kasutada psql programmi ning kahel viimasel spetsiaalset taasteks mõeldud programmi pg_restore, viimane võimaldab taastamisel suhteliselt lihtsasti suuremat paindlikkust, nt valida

• millised andmed arhiivist taastada (nt tableli täpsusega)
• millised järjekorras andmed arhiivist taastada

SQL skripti kasutamine

Kasutamiseks tuleb psql utiliidiga laadida skript andmebaasi, nt

 $ psql -U postgres -h localhost baasinimi < baasinimi-20090802.sql

Tekstikujul esituse puuduseks on suhteliselt mahtukas tulemus ja aeganõudev protseduuri kestus. Eeliseks võib pidada asjaolu, et andmed on inimesele hõlpsasti loetaval ja muudetaval kujul.

Kasutades psql järel võtit '-1' toimub skripti täitmine ühe transaktsioonina.

Kostümiseeritud arhiivifaili kasutamine

Kostümiseeritud arhiivifaili kasutamiseks sobib öelda nt

 $ pg_restore -d portaal -h localhost -U postgres portaal.data.documents.fcdump

Lisaks taastamisele saab küsida pg_restore abil arhiivi kohta andmeid

 $ pg_restore -l /data/backup/dumps/portaal.data.dokuments.fcdump
 ;
 ; Archive created at Thu Sep 10 14:54:27 2009
 ;     dbname: portaal
 ;     TOC Entries: 11
 ;     Compression: -1
 ;     Dump Version: 1.10-0
 ;     Format: CUSTOM
 ;     Integer: 4 bytes
 ;     Offset: 8 bytes
 ;     Dumped from database version: 8.3.5
 ;     Dumped by pg_dump version: 8.3.5
 ;
 ;
 ; Selected TOC Entries:
 ;
 8462; 1259 40169 TABLE data documents sysadm
 9384; 0 0 ACL data documents sysadm
 8463; 1259 40175 SEQUENCE data documents_doc_id_seq sysadm
 9385; 0 0 SEQUENCE OWNED BY data documents_doc_id_seq sysadm
 9386; 0 0 SEQUENCE SET data documents_doc_id_seq sysadm
 9378; 2604 42053 DEFAULT data doc_id sysadm
 9381; 0 40169 TABLE DATA data documents sysadm
 9379; 1259 48086585 INDEX data documents_doc_id sysadm
 9380; 2620 48087091 TRIGGER data timestamp sysadm

kus

• rea alguses on objekti id väärtus
• kõik semikoolonist paremale jääv on kommentaar

Misc

• show <tab> - esitab nimekirja võimalikest küsimustest, nt

 show max_connections;
 max_connections 
-----------------
 100
(1 row)

• Andmebaasi suuruse küsimine

sql> SELECT pg_size_pretty(pg_database_size('andmebaasinimi')) As fulldbsize;

• Tabeli suuruse küsimine

sql> SELECT pg_size_pretty(pg_total_relation_size('skeeminimi.tabelinimi')) As fulltblsize, \
  pg_size_pretty(pg_relation_size('skeeminimi.tabelinimi')) As justthetblsize;