PostgreSQL

Web-palvelimen taustapalveluiden tuottamiseen tarvitaan tietokantapalvelin. PostgreSQL ja sen PostGIS-laajennus ovat useimpien paikkatietoja tarjoavien sovellusten taustalla. PostgreSQL-palvelimen voi asentaa Webmin-hallintasovelluksen avulla. Ainoastaan pääkäyttäjän salasana pitää määritellä erikseen joko komentoriviltä tai Usermin-työkalulla.

Palvelimen asennus käynnistetään avaamalla Un-used Modules ja valitsemalla sieltä PostgreSQl Server. Asennuksen jälkeen lisätään vielä mahdolliset puuttuvat Perl-moduuli, jos asennus ilmoittaa niiden puuttuvan. Ilmoituskentässä on painike, joka suorittaa niiden asennuksen.

Tämän jälkeen annetaan pääkäyttötunnukselle salasanksi esim. Q2werty ja tallennetaan käyttötiedot. Ulkopuoliset yhteydet sallitaan Allowed Hosts-painikkeella lisäämällä verkko 172.29.112.0/24 sallitujen koneiden listalle. Kaikkikäyttäjät pääsevät näistä osoitteista kaikkiin tietokantoihin ja salasanat salakirjoitetaan MD5-algoritmilla (Message Digest version 5). Tämä luo rivin host all all 172.29.112.0/24 md5 tiedostoon pg_hba_conf. PostgreSQL v. 15 ei kuitenkaan kuuntele oletuksena muita verkkokortteja kuin 127.0.0.1-korttia. Jos halutaan, että kone kuuntele kaikkia verkkokortteja lisätään rivi listen_address = *tiedostoonpostgresql.conf. Debian-asennuksessa tiedosto löytyy /etc/postgresql/15/main-hakemistosta.

Hallintatyökalujen asentaminen

Windows-työasemaan asennetaan PG Admin 4 -ohjelma osoitteesta https://jdbc.postgresql.org/download/. Jos koneessa on ennestään hallintaohjelma se kannattaa poistaa kokonaisuudessaan ennen uuden version asentamista. Tietokantojen suunitellua varten asennetaan SQL Power Architect -sovellus Best of Bi. Koska itse palvelin os asenettu Debian-alustalle tarvitaan Windows-ajurit. Java Database Connector (JDBC) ajurit mahdollistava SQL Power Architect -sovellusken ja DBeaver -monitietokantasovellus käyttämiseen. Ajurit ladataan Postgreen sivulta.

PG Admin

PG Admin on ilmainen hallintatyökalu PostgreSQL-tietokantapalvelimen ylläpitoon. Sovelluksessa on runsaasi virheitä ja siitä syystä sitä kannataa päivittää aina, kun ohjelma ilmoittaa uudesta päivityksestä.

Hallintaohjelma piilotaa hakemistopuun oletuksena. Sitä tarvitaan kuitenkin kyselyissä tietokantaobjektien tuomisessa kyselyikkunaan. Sen saa näkyviin muuttamalla asetuksia File - Preferences - Miscellaneous - User Interface - Layout - Classic.

SQL Power Architect

Sovellukseen on alussa lisättävä JDBC-ajurit. Ajurit lisätään jar -tiedostoina. Lisäystä tapahtuu valikkoriviltä Connections - Connection Manager - JDBC Driver. Ajureiden lisäyksen jäleen luodaan yhteys tietokantapalvelimeen Connection Manager -työkalulla:

yhteyden luomisen jälkeen on aina syytä testa yhteyden toiminta Test Connection -painikkeela. JDBC-ajurit löytyvät Postgresql.orgin:sivulta. Jos tietokantapalvelin asennetan Windows-alustalle ajurit asennetaan palvelimen asennusohjelmaan ** Stack Builder** avulla. Jos asennusohjelmaa ei käytetä ajurit on asennetava käsin. Ajurien laaslinkit ilmevevät seuraavassa taulukossa.

Ajuri	Latausosoite
Java Database Connector (JDBC)	https://jdbc.postgresql.org/
.net (npgsql)	https://github.com/npgsql/npgsql/releases
Open Database Connector (ODBC)	https://www.postgresql.org/ftp/odbc/releases/

DBeaver

Yhteys tietokantaa luodaan päävalikosta "töpseli" -painikeen avulla. Tietokanna tyypiksi valitaan PostgreSQL ja annetaan tietokanna yhteystiedot. Valitsemalla Show all databases -ruudun yhteys näyttää palvelimen kaikki tietokannat. Yhteyden toiminta varmistetaan Test Connection -painikella.

Käyttöoikeude

Tietokantoihin liityviä käyttöoikeuksia voidaan määritellä sekä komentopohjaisesti, että PG Admin ja DBeaver -sovelluksien graafisesta käyttöliitymästä. PostgreSQl:n käyttäjähallinta on erittäin pelkistetty. Käyttäjästä ja käyttäjäryhmistä käytetään termiä *rooli (role). Jos roolin avulla voi kirjautua, kysessä on käytäjätunnus (user, login). Jos roolilla ei ole kirjautumisominaisuutta se on ryhmä.

Kuvan yläosassa olevat oikeudet mahdollistavat pääkäyttöoikesien antamisen (Super User). Create Role oikeuden omava käyttäjä voi tehdä uusia käyttäjätunnuksia ja rymiä. Create Database -oikeuden omaava käyttäjä voi luoda itse omia tietokantoja palvelimelle. Tietoknna luoja on ns. OWNER, jola on tietokantaan täydet oikeudet. Replication-oikeuden omaava käyttäjä voi tehdä tietokannasta kopioita toiselle tietokantapalvelimelle. Yleensä ämä oikeus antaa muös oikeuden tehdä varmuskopiota tietokannasta. Bypass RLS -oikeus mahdollista rivikohtaisten (Row Level Security) käyttöoikeusastusten ohittamisen.

Sekä käyttäjäll, että ryhmille voidaan märitellä käyttöoikeiksia eri tietokantaobjekteihin. Oikeudet ilmevät seuraavasta taulukosta:

Nimitys	Oikeus
SELECT	Lukea tiedot
INSER	Lisätä tietoa
UPDATE	Muokata tietoja
DELETE	Poistaa tietoja
TRUNCATE	Poistaa tietokantaobjekteja
CREATE	Luoda tietokantaobjekteja
GRANT	Myöntää oikeuksia
REFERENCES	Määritell relaatioviitauksia
TRIGGER	Käyttää liipasimia

:bulb: Termi TRUNCATEvoi virheilmoituksissa myös tarkoittaa sitä, että syötetty tieto ei mahdu sille varattuun tilaan taulussa. Truncate --> katkaista.

Käyttöoikeudet kannataa ensisijaisesti määritellä käyttjäryhmille (rooleille, joilla ei ole Cam Login -oikeudet) ja liittä sitten yksittäiset käyttäjät näihin ryhmiin. DBeaver ei kuitenkaan tarjoa mahdollisuutta käyttäjien ryhmään, lisääminen vaan se on tehtävä PGAdmin-sovelluksessa.

Jos käyttäjä on luotu DBeaver-ohjelmassa, käyttäjätunnus avataan PGAdmi-sovelluksessa ja sitä päästään muokkaamaan valitsemalla ominaisuudet (properties) hiiren 2-painikkella. Ryhmäjäsenyydet ovat muokattavissa Membership-välilehdessä

Harjoitus

Laiterekisteriä käyttävät sekä organisaation tietotekniset palveut, johto ka taloushallinto. Luo ryhmä näitä osastoja varten. Tarvittavat käyttöoikeudet ilmenevät seuraavasta taulukosta:

Rooli	Kohde	Oikeudet
Tietotekniset palvelut	Kaikki taulut	RVM
Johto	Kaikki taulut	R
Taloushallinto	Laite-taulu Laitetyyppu-taulu	R
Liisa Lipevä	Kaikki taulut	RD

Oikeuslyhenteet:

• R (Read) lukuoikeus
• W (Write) tallennusoikeus
• M (Modify) muokkausoikeus
• D (Delete) poisto-oikeus

Joissakin yhteyksissä käytetään myös lyhennettä CRUD jossa C on Create (luonti), R Read (luku), U Upload (päivitys/muokkaus) D Delete (poisto).

Luo seuraavat käyttäjät:

Käyttäjä	Ryhmät joihin kuuluu
Jakke Jäynä	Tietotekniset palvelut
Calle Keckelberg	Tietotekniset palvelut, Johto
Harri Herkkäusko	Johto
Assi Kalma	Tauloushallinto Johto
Tuitti Kiukkunen	Taloishallinto
Liisa Lipevä	Osastosihteeri, Tietohallinto