PostgreSQL

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

Palvelimien asennus käynnistetään avaamalla Un-usede Modules ja valitsemalla sieltä PostgreSQL Server. Asennuksen jälkeen lisätään vielä mahdolliset puuttuvat Pearl-moduulit, jos asennus ilmoittaa niiden puuttuvan. Ilmoituskentässä on painike, joka suorittaa niiden asennuksen.

Tämän jälkeen annetaan pääkäyttötunnukselle postgres salasanaksi esim. Q2wertyja tallennetaan käyttäjätiedot. Ulkopuoliset yhteydet sallitaan lisäämällä Allow Host-painikkeella lisäämällä verkko 192.168.222.0/24 sallittujen koneiden listaan.

Kaikki käyttäjät pääsevät näistä osoitteista kaikkiin tietokantoihin ja salasanat salakirjoitetaan MD5-algoritmillä ( Message Digest version 5). Tämä luo rivin host all all 192.168.222.0/24 md5tiedostoon pg_hba.conf. PostgreSQL v. 15 ei kuitenkaan kuuntele oletuksena muita verkkokortteja kuin 127.0.0.1-korttia. Jos halutaan, että kone kuuntelee kaikkia verkkokortteja lisätään rivi listen_addresses = '*' tiedostoon postgresql.conf. Debian-asennuksessa tiedosto löytyy /etc/postgresql/15/main-hakemistosta.

Kun .conf-tiedostoja on muokattu, palvelin on käynnistettävä uudestaan Webminin stop ja start painikkeiden avulla tai annettava komentoriviltä komento systemctl restart postgresql.

Hallintatyökalujen asentaminen

Windows-työasemaan asennetetaan PG Admin 4 -ohjelma osoitteesta Pg Admin org:sivuilta. Jos koneessa on ennestään hallintaohjelma se kannattaa poistaa kokonaisuudessaan enne uuden version asentamista. Tietokantojen suunnittelua varten asennetaan SQL Power Architect - sovellus Best of Bi:n sivuilta. Koska itse palvelin on asennettu *Debian-alustalle tarvitaan Windows-ajurit. Java Database Connector (JDBC) ajurit mahdollistavat SQL Power Architet- sovelluksen ja DBeaver-monitietokantasovelluksen käyttämisen. Ajurit ladataan Postresql.org:n sivuilta.

PG Admin

PG Admin on ilmainen hallintatyökalu PostreSQL-tietokantapalvelienten ylläpitoon. Sovelluksessa on runsaasti virheitä ja siitä syystä sitä kannattaa päivittää aina, kun ohjelma ilmoittaa uudesta päivityksestä. Tietokantayhteyden määrittely näyttää seuraavalta:

Hallintaohjelma piilottaa hakemistopuun oletuksena. Sitä tarvitaan kuitenkin kyselyissä tietokantaobjektien tuomisessa kyselyikkunaan. Sen saa näkyviin muuttamassa 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 Managaer - JDBC Drivers. Ajureiden lisäyksen jälkeen luodaan yhteys tietokantapalvelimeen Connection Manager työkalulla:

Yhteyden luomisen jälkeen on aina syytä testata yhteyden toiminta Test Connection - painikkeella. JDBC ajurit löytyvät Postresql.org:n sivuilta. Jos tietokantapalvelin asennetaan Windows-alustalle ajurit asennetaan palvelimen asennusohjelman ** Stack Builder** avulla. Jos asennusohjelmaa ei käytetä ajurit on asennetteva käsin. Ajurien latauslinkit ilmenevät seuraavasta taulukosta:

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

DBeaver

Yhteys tietokantaan luodaan painikevalikon "töpseli"-painikkeen avulla. Tietokannan tyypiksi valitaan PostgreSQL ja annetaan tietokannan yhteystiedot:

Valitsemalla Show all databases -ruudun yhteys näyttää palvelimen kaikki tietokannat. Yhteyden varmistetaan Test Connection - painikkeella.

Käyttöoikeudet

Tietokantoighin liittyviä käyttöoikeuksia voidaan määritellä sekä komentopohjaisesti eetä PG Admin ja DBeaver -sovelluksien graafisista käyttöliittymistä. PostgreSQL: käyttäjähallinta on erittäin pelkistetty. KÄyttäjästä ja käyttäjäryhmästä käytetetään termia rooli ( role). Jos roolin avulla voi kirjautua, kyseessä on käyttäjätunnus ( user, login). Jos roolilla ei ole kirjautumisominaisuutta se on ryhmä

Kuvan yläosassa olevat oikeudet mahdollistavat pääkäyttöoikeuksien antamisen ( Super User). Greate Role -oikeuden omaava käyttäjä voi tehdä uusia käyttäjätunnuksia ja ryhmiä. Create Database -oikeuden omaava käyttäjä voi luoda itse omia tietokantoja palvelimelle. Tietokannan luoja on ns. OWNER, jolla on tietokantaan täydet oikeudet. Replication. oikeuden osmaava käyttäjä voi tehdä tietokannasta kopioita toiselle tietokantapelvelimelle. Yleensä tämä oikeus antaa myös oikeuden tehdä varmuuskopiointi tietokannasta. Bypass RLS -oikeus mahdollistaa rivikohtaisten ( Row Level Security) käyttöoikeusasetusten ohiuttamisen.

Sekä käyttäjällä että ryhmälle voidaan määritellä käyttöoikeuksia eri tietokantaobjekteihin. Oikeudet ilmenevät seuravasta taulukosta:

Nimitys	Oikeus
SELECT	Lukea tiedot
USAGE	Proseduurien ja funktioiden suoritus
INSERT	Lisätä tietoja
UPDATE	Muokata tietoja
DELETE	Poistaa tietoja
TRUNCATE	Poistaa kaikki tiedot
CREATE	Luoda tietokantaobjekteja
GRANT	Myöntää oikeuksia
REFEGRANCES	Määritellä relaatitioviittauksia
TRIGGER	Käyttää liipaisimia

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

Käyttöoikeudet kannattaa ensisijaisesti määritellä käyttäjäryhmille ( rooleille, joilla ei ole Can Login -oikeutta ja liittää siihen yksittäiset käyttäjät näihin ryhmiin. DBeaver ei kuitenkaan tarjoa mahdollisuutta käyttäjien ryhmään lisäämiseen, vaan se on tehtävä PGAdmin-sovelluksessa.

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

Harjoitus

Laiterekisteriä käyttävät sekö organisaation tietotekniset palvelut, johto ja taloushallinto- Luo ryhmät näitä ostoja varten. Tarvittavat käyttöoikeudet ilmenevät seuraavasta taulukosta:

| Rooli | Kohde | Oikeudet | |---|---| Tietotekniset palvelut | Kaikki taulut | RWM Johto | Kaiki taulut | R Taloushallinto | Laite-taulu, Laitetyyppi-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 Create (luonti), R Read (luku), U Update (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	Taloushallinto Johto
Tuittu Kiukkunen	Taloushallinto
Liisa Lipevä	Osastosihteerit, Tietotekniset palvelut