Sisteme de baze de date: PostgreSQL si MySQL

Sisteme de baze de date: PostgreSQL si MySQL

MySQL si PostgreSQL sunt sisteme relationale de gestiune a bazelor de date, compatibile SQL (Structured Query Language, cel mai raspāndit limbaj standardizat de interogare a bazelor de date), cu arhitectura client-server. Spre deosebire de alte sisteme similare disponibile īn Linux, cu 11211i89l m ar fi Oracle, DB2 sau Informix, acestea doua sunt open source.

2.1. Sistemul PostgreSQL

O sesiune Postgres este alcatuita din urmatoarele trei componente:

un proces supervizor de tip daemon (numit postmaster);

aplicatia care utilizeaza serviciile SQL (cum ar fi programul psql);

unul sau mai multe servere (procesele postgres).

Un proces postmaster gestioneaza o colectie de baze de date pe o masina, colectie denumita cluster. Aplicatiile utilizeaza biblioteca postgresql. Aceasta trimite cereri prin retea daemonului postmaster. Astfel, pentru fiecare asemenea conexiune este pornit cāte un server postgres.

Atāt daemonul postmaster, cāt si serverele postgres ruleaza sub un utilizator special, de regula postgres. īn nici un caz acest utilizator special nu trebuie sa fie root. De asemenea, toate fisierele din cadrul bazelor de date trebuie sa apartina acestui utilizator special.

La prima pornire a daemonului postmaster va fi verificata existenta clusterului de baze de date. īn general, acesta este localizat īn /var/iib/pgsqi/data. Daca acesta nu exista, el va fi creat automat de catre scripturile de pornire, apelānd programul initdb.

Fisierul- de configurare se afla īn directorul de date si are numele postgresql. conf. Atunci cānd daemonul primeste semnalul SIGHUP, fisierul de configurare este recitit.

Optiunile importante sunt urmatoarele:

Optiuni generale

FSYNC daca aceasta optiune este activata, Postgres va utiliza apelul

de sistem fsync() pentru a se asigura ca datele sunt scrise pe

Setari avansate ale sistemului

disc. Aceasta va creste siguranta datelor, īncetinind īnsa sistemul datorita utilizarii mai intense a discului. Implicit: false.

MAX_CONNECTIONS stabileste numarul maxim de conexiuni acceptat de

server. Implicit: 32.
PORT stabileste portul TCP pe care serverul va astepta cereri.

Implicit: 5432. SHARED_BUFFERS stabileste numarul de zone-tampon de memorie comuna

utilizate de server. O zona contine 8192 de octeti. Implicit:

KB). SORT_MEM         specifica cantitatea de memorie utilizata pentru sortarHe

interne, īnainte de a recurge la fisiere temporare. Este

exprimata īn kilobytes si are valoarea implicita 512 KB. TCPIP_SOCKET daca aceasta optiune este activata, serverul va accepta

conexiuni TCP/IP (adica si de pe alte masini). Implicit: Off.

Optiuni privind informatiile de depanare si jurnalizarea

DEBUG_LEVEL stabileste nivelul informatiilor de depanare generate. Implicit este O, adica serverul nu va genera informatii de depanare.

HOSTNAME_LOOKUP determina numele masinii de unde se fac conexiuni, nu doar adresa IP, atunci cānd conexiunile sunt jurnalizate. De mentionat ca activarea acestei optiuni va creste traficul prin retea. Implicit: Off.

LOG_CONNECTIONS jumalizeaza fiecare conexiune reusita la server. Implicit este Off.

LOG_TIMESTAMP pentru fiecare linie din jurnal precizeaza si momentul īn care s-a petrecut evenimentul. Implicit este Off.

SYSLOG precizeaza modalitatea de jurnalizare. Daca are valoarea l,

mesajele vor fi trimise atāt sistemului syslog, cāt si catre iesirea standard. Pentru valoarea 2, mesajele vor fi trimise numai sistemului syslog. Implicit este O, ceea ce īnseamna ca mesajele vor fi trimise doar la iesirea standard.

SYSLOG_FACILITY stabileste clasa de jurnalizare utilizata de syslog (LOCALO, ..., LOCALT). Implicit este LOCALO. Pentru detalii privind clasele de jurnalizare, vezi cap. 10.

SYSLOG_IDENT atunci cānd este utilizat sistemul syslog, stabileste numele programului folosit pentru identificarea mesajelor PostgreSQL. Implicit estepostgres.

Iata un fragment dintr-un fisier postgresqi. conf:

l*

l # Acest fisier este alcatuit din linii de forma:

Administrarea si configurarea sistemelor Linux

optiune = valoare
#

Caracterul "=" este optional.

t Caracterul "f" desemneaza īnceputul unui comentariu.

Nu conteaza daca sunt folosite litere mari sau mici.
l

Parametri legati de conexiune
tcpip_socket = true
max_connections = 1024

port = 5432 hostname_lookup = false

Parametri legati de performanta
sort_mem = 2048

shared_buffers = 2048 fsync = false

Parametri legati de jurnalizare
syslog = 2

syslog_facility = 'LOCAL7'

Oprirea serverului

SIGTERM dupa primirea semnalului SIGTERM, daemonul va refuza

cererile de conectare, permitānd totusi serverelor sa-si continue executia. Postmaster-u\ se va opri doar atunci cānd toate serverele s-au oprit īn urma cererii clientilor.

SIGINT dupa receptionarea acestui semnal, daemonul va refuza

cererile de conectare si va trimite tuturor serverelor pornite semnalul SIGTERM, iar acestea vor renunta la tranzactiile curente si īsi vor īncheia executia. Daemonul se va opri dupa ce toate serverele vor fi oprite.

SIGOUIT daemonul va trimite serverelor semnalul SIGQUIT si apoi īsi

va īncheia imediat executia, fara a salva eventualele modificari asupra sistemului de baze de date. Nu este recomandata utilizarea acestui semnal.

Autentificarea clientilor

Autentificarea este procedura prin care serverul de baze de date stabileste identitatea clientului, determinānd daca se permite conectarea aplicatiei-client.

Numele de utilizatori PostgreSQL sunt separate logic de numele de utilizatori Linux de pe masina pe care ruleaza serverul.

Autentificarea clientilor se configureaza īn fisierul pg_hba.conf (HBA īnseamna Host-Based Authentication), localizat īn directorul de date postgres, Acest fisier este alcatuit din mai multe īnregistrari, cāte una pe linie, formate, la

Setari avansate ale sistemului

rāndul lor, din mai multe cāmpuri. Cāmpurile sunt separate prin spatii sau tab-uri. Liniile īncepānd cu "#" sunt considerate comentarii, īnregistrarile pot lua trei forme:

local baza_de_date metoda_de_autentificare

host baza_de_date adresa_IP masca_retea metoda de_autentificare

hostssl baza_de_date adresa_IP mascā_retea metoda__de_autentificare

unde:

local conexiunea se face local, de pe aceeasi masina;

host conexiunea se face de pe o alta masina;

hostssl conexiunea se face de pe o alta masina, prin intermediul

SSL;

baza_de_date        specifica numele bazei de date la care se refera īnregistrarea.

Valoarea all permite accesul la toate bazele de date;

samenser semnifica baza de date cu acelasi nume cu al

utilizatorului;

adresa_IP, masca_retea specifica adresa IP si masca de retea a masinii de pe

care se face conexiunea;

metoda_de_autentificare stabileste metoda pe care trebuie sa o foloseasca utilizatorul pentru a se autentifica atunci cānd se conecteaza la baza de date. Sunt posibile urmatoarele metode:

trust - este permis accesul oricarui utilizator de pe

masina-client;

reject - conexiunea nu este permisa;

password - clientul trebuie sa trimita o parola pentru a se

putea conecta. Dupa cuvāntul password se poate

specifica un nume de fisier care contine o lista de

utilizatori si eventual parole, carora le este permis

accesul. Parola este trimisa īn clar prin retea, fara a fi

criptata;

crypt - este similara metodei password, īnsa parolele vor

fi criptate īnainte de a fi trimise.

Prima īnregistrare pentru care se potrivesc adresa IP, masca de retea si numele bazei de date va fi utilizata pentru autentificare. Daca nu se potriveste nici o īnregistrare, conexiunea este refuzata.

Iata un exemplu de fisier pg_hba. conf:

accepta orice conexiune locala

local       all trust

accepta utilizatorii de pe masina 193.226.26.10 sa acceseze orice

baza de date, fara a specifica o parola

host       all 193.226.26.10 255.255.255.0 crypt

Administrarea si configurarea sistemelor Linux

accepta utilizatorii de pe orice masina sa acceseze orice baza de

date, īn conditia corectitudinii parolei

host       all crypt

Administrarea utilizatorilor PostgreSQL

Initial, dupa prima pornire a daemonului PostgreSQL, doar utilizatorul postgres este definit. Pentru a crea un nou utilizator, operatiunea trebuie facuta de sub acest utilizator, spre exemplu:

| su -c postgres "createuser root"

Adaugarea de utilizatori se face fie folosind comanda SQL create user, fie din shell-u\ de comenzi, prin comanda createuser:

CREATE USER utilizator [ WITH

[ SYSID uid ] [ PASSWORD 'parola' ) ]

[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] [ IN GROUP lista_grupuri ] [ VALID UNTIL ' timp' ]

unde:

utilizator este numele utilizatorului care va fi creat;

uid este identificatorul utilizatorului, īn mod uzual, acest parametru

nu este necesar;

parola specifica parola de acces;

CREATEDB, NOCREATEDB acorda / nu acorda utilizatorului dreptul de a crea

noi baze de date. Implicit este NOCREATEDB; CREATEUSER, NOCREATEUSER acorda / nu acorda utilizatorului dreptul de a

crea noi utilizatori. Implicit este NOCREATEUSER;
grup lista de grupuri īn care va fi introdus utilizatorul;

timp specifica data pāna la care utilizatorul va fi valid.

| createuser t optiuni ] utilizator

unde optiuni pot fi:

-d, -createdb acorda utilizatorului dreptul de a crea baze de date; -D, -no-create-db nu acorda utilizatorului dreptul de a crea baze de date; -a, -adduser permite utilizatorului sa adauge utilizatori; A, -no-adduser nu permite utilizatorului sa adauge utilizatori; -P, -pwprompt solicita parola pentru noul utilizator creat; -i, --sysid id     specifica identificatorul utilizatorului. Nu este necesar īn mod normal.

Setari avansate ale sistemului

Prezentam īn continuare un exemplu de executie a comenzii createuser:

l createuser dragos

īs the new user allowed to create databases? (y/n) y

Shall the new user be allowed to create more users? (y/n) y

CREATE USER

stergerea de utilizatori se face fie prin intermediul comenzii SQL drop user, fie din shell-ul de comenzi, prin comanda dropuser:

| DROP USER utilizator

sau

| dropuser utilizator

De asemenea, PostgreSQL permite folosirea de grupuri de utilizatori, similar grupurilor UNIX. Adaugarea de grupuri se face prin comanda SQL create group, iar stergerea,

CU DROP GROUP.

Gestiunea privilegiilor

PostgreSQL ofera si posibilitatea de acorda fiecarui utilizator privilegii pentru fiecare tip de interogare īn parte. Aceasta se poate face folosind comanda SQL grant, avānd sintaxa:

GRANT lista_jDrivi2egii ON lista_ojbiecte TO

unde:

lista_privilegii este o lista de privilegii, separate prin virgula, putānd lua

valorile:

SELECT (accesarea cāmpurilor);

INSERT (inserarea de īnregistrari);

UPDATE (modificarea datelor);

DELETE (stergerea de īnregistrari);

RULE (definirea de reguli);

ALL (toate privilegiile); listajobiecte     este o lista de obiecte la care se refera privilegiile, separate prin

virgula:

table

view

sequence',

Administrarea si configurarea sistemelor Linux

PUBLIC se refera la toti utilizatorii;

GROUP grup acorda privilegiile grupului specificat;

utilizator specifica numele utilizatorului caruia i se acorda privilegiile.

De exemplu, comanda urmatoare SQL va acorda tuturor utilizatorilor dreptul de introducere de īnregistrari noi īn tabela facturi:

| GRANT INSERT ON facturi TO PUBLIC;

Revocarea privilegiilor se face cu revoke, avānd sintaxa:

REVOKE lista^privilegii ON lista_obiecte FROM

semnificatia parametrilor fiind aceeasi ca la grant.

Iata si un exemplu care retrage dreptul de stergere de īnregistrari din tabela facturi utilizatorului sabin:

REVOKE DELETE ON facturi TO sabin;

2.2. Sistemul MySQL

O sesiune MySQL este alcatuita din urmatoarele doua componente:

serverul mysqld;

aplicatia care utilizeaza serviciile SQL (cum ar fi programul mysql).

De remarcat ca, spre deosebire de PostgreSQL, MySQL nu foloseste cāte un proces separat pentru fiecare conexiune, ci un singur server, ce utilizeaza fire de executie (threads).

La prima pornire a serverului mysqld va fi verificata existenta clusterului de baze de date. īn general, acesta este localizat īn /var/iib/mysqi/data. Daca acesta nu exista, el va fi creat automat de catre scripturile de pornire, apelānd programul mysql_install_db.

Fisierul de configurare se poate afla fie īn directorul /etc, fie īn directorul de date, fie īn directorul home, si are numele my.cnf. Fisierul de configurare contine mai multe linii, avānd unul dintre urmatoarele formate:

[ grup ]

optiune

optiune =. valoare

set-variable = variabila = valoare

Setari avansate ale sistemului

unde grup reprezinta numele unui program sau grup pentru care se stabilesc optiunile. Grupul client se refera la toti clientii mysqld; mysqld se refera la serverul mysql.

Furnizam mai jos un fisier global my. cnf:

[client] port=3306

[mysqld]

port=3306

set-variable = key_buffer_size = 16M

set-variable = max_allowed_packet = IM

[mysqldump] quick

Autentificarea clientilor

Autentificarea este procedura prin care serverul de baze de date stabileste identitatea clientului, determinānd daca se permite conectarea aplicatiei-client.

Numele de utilizatori MySQL sunt separate logic de numele de utilizatori Linux de pe masina pe care ruleaza serverul.

Autentificarea clientilor se face prin intermediul unor ACL-m\ (Access Control List), si anume utilizānd tabelele host, user si db, respectiv tabies_priv si columns_priv. Acestea se gasesc īn baza de date mysql. MySQL ofera si posibilitatea de acorda fiecarui utilizator privilegii pentru fiecare tip de interogare īn parte.

Tabela user determina daca sunt acceptate sau respinse conexiuni. Pentru conexiunile acceptate, orice drepturi acordate īn aceasta tabela indica privilegii globale. Aceste privilegii se refera la toate bazele de date.

Tabelele db si host sunt utilizate īmpreuna, astfel:

tabela db determina ce utilizatori conectati de pe anumite masini pot accesa
fiecare baza de date si ce operatiuni pot efectua;

tabela host este folosita ca o extensie a tabelei db, daca se doreste ca o
īnregistrare īn tabela db sa se refere la mai multe masini.

De mentionat ca drepturile administrative (reload, shutdown etc.) sunt specificate doar īn tabela user. Cāmpurile din aceste tabele sunt prezentate īn cele ce urmeaza:

Tabelul 2.1. Tabelele interne utilizate pentru autentificarea clientilor

Administrarea si configurarea sistemelor Linux

I

Tabelele tables_priv si coiumns_priv sunt similare cu db, īnsa se refera la nivel de tabel (respectiv coloana) īn loc de baza de date.

Cāmpurile din tabela user se completeaza astfel:

Host este numele masinii sau adresa sa de IP. Poate fi localhost pentru a indica masina locala. Pot fi folosite si caracterele wildcard "%" (orice sir) sau "_" (orice caracter). Valoarea vida semnifica faptul ca privilegiul trebuie confruntat cu īnregistrarea din tabela host pentru care se potriveste numele masinii specificat; User este numele utilizatorului. Daca este vid, este acceptat orice nume;

Password este parola de acces. Daca este vida, utilizatorul nu trebuie sa
furnizeze nici o parola.

Tabelele db si host se completeaza astfel:

Cāmpul Host se completeaza ca la tabela user. Valoarea "%" īn tabela db
īnseamna "orice masina". Valoarea vida īn tabela db īnseamna "consulta
tabela db". Valoarea "%" sau vida īn tabela host īnseamna "orice masina";

Db este numele bazei de date la care se permite accesul. Valoarea "%" sau
vida īnseamna "orice baza de date";

User este numele utilizatorului. Daca este vid, este acceptat orice nume.

Tabelele db si host sunt citite la pornirea serverului MySQL. Pentru a reciti aceste tabele dupa ce s-au efectuat modificari, trebuie apelata comanda SQL flush

PRIVILEGES.

Cāmpurile din tabelele tables_priv si columns_priv se specifica dupa cum urmeaza:

Setari avansate ale sistemului

Cāmpul Host se completeaza ca la tabela user. Valoarea "%" sau vida
īnseamna "orice masina";

Cāmpurile Db, Tablejiame si Columnjiame nu pot contine caractere
wildcard si nu pot fi vide. Ele specifica numele bazei de date, numele tabelei
si numele coloanei la care se refera regula de acces.

Modificarea acestor table se face cu ajutorul comenzilor SQL GRANT si REVOKE, a caror sintaxa este:

GRANT privilegiu [ lista_coloane ] ON TO utilizator [ IDENTIFIED BY 'parola' ] [ WITH GRANT OPTION ]

REVOKE prilegiu [ lista_coloane ]

ON FROM utilizator

unde privilegiu poate fi:

ALL PRIVILEGES  toate privilegiile

ALTER modificarea cāmpurilor unei tabele

CREATE creare de noi tabele

DELETE stergere īnregistrari dintr-o tabela

DROP stergere de tabele

GRANT OPTION apelul comenzii GRANT

INSERT introducere de noi īnregistrari īntr-o tabela

RELOAD repornirea serverului MySQL

SELECT citirea de īnregistrari dintr-o tabela

SHUTDOWN oprirea serverului MySQL

UPDATE modificarea valorilor cāmpurilor

USAGE nici un privilegiu

De exemplu, comanda:

|GRANT ALL PRIVILEGES on biosfarm%.* to dragos@'10.O.O.%';

permite accesul utilizatorului dragos, de pe orice masina din reteaua 10.0.0.0, la bazele de date care īncep cu biosfarm.

Administrarea utilizatorilor MySQL

Utilizatorii MySQL nu au legatura cu utilizatorii sistemului UNIX, ca de altfel nici parolele acestora. Numele utilizatorilor pot avea maxim 16 caiac! o lungime. ^; ;a,'.;i de date cu utilizatorii se afla īn tabela mysql.user, la care acestia nu trebuie sa aiba acces.

Administrarea si configurarea sistemelor Linux

Spre deosebire de PostgresSQL, la prima pornire a serverului MySQL este creat doar utilizatorul root, fara parola, avānd toate privilegiile. Se recomanda stabilirea imediata a unei parole pentru acest utilizator.

Crearea de noi utilizatori MySQL se face tot prin intermediul comenzii GRANT. Daca utilizatorul exista, sunt modificate privilegiile acestuia, īn caz contrar, el este creat. De exemplu, comanda:

GRANT ALL PRIVILEGES ON *.* TO dragos@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;

creeaza utilizatorul dragos, care se poate conecta de pe masina locala, avānd toate drepturile, inclusiv dreptul de apelare GRANT. Parola initiala este 123456.

Modificarea parolei unui utilizator se poate face cu ajutorul comenzii:

| SET PASSWORD FOR utilizator = PASSWORDt'parola' )

Spre exemplu:

| SET PASSWORD FOR dragos@localhost = PASSWORD('ppaarroollaa'}