ASIX-M10-UF2. Accés remot a la base de dades: MySQL i PostgreSQL

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

Introducció

Fins ara hem treballat bàsicament connectant-nos al MySQL i PostgreSQL de forma local. Però normalment els usuaris de la base de dades no tenen accés directe al servidor de base de dades.

Referències

Desenvolupament

MySQL

Per defecte, a mysql les bases de dades estan capades de manera que només podem accedir des de la màquina local.

$ mysql -h localhost -u root -p langtrainer

Si la IP de la màquina local és la 192.168.1.34, ja no podem accedir:

$ mysql -h 192.168.1.34 -u root -p langtrainer
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.34' (111)

Per poder accedir, haurem de tocar el fitxer de configuració my.cnf, com ja havíem fet en una altra ocasió.

$ sudo joe /etc/mysql/my.cnf
!includedir /etc/mysql/mysql.conf.d/

és en el fitxer /etc/mysql/mysql.conf.d/mysqld.cnf on està tota la informació que ens interessa:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address           = 127.0.0.1

S'ha de comentar la línia, de manera que quedarà:

#bind-address           = 127.0.0.1

i reiniciar el servidor

$ sudo /etc/init.d/mysql reload

Però encara no podem accedir:

$ mysql -h 192.168.1.34 -u root -p langtrainer
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'joan-portatil.local' (using password: YES)

He de donar permisos (a nivell de seguretat, com féiem en la pràctica anterior) per tal de què l'usuari root es pugui connectar a la base de dades langtrainer des d'un IP concreta:

mysql> GRANT ALL ON langtrainer.* TO root@192.168.1.34 identified by "*****";
flush privileges

Fixem-nos que aquesta acció provoca una entrada en la taula mysql.user:

mysql> SELECT User, Host FROM mysql.user;
| root             | 192.168.1.34  |

I què passa si ens volem connectar a una base de dades remota fora de la xarxa local? Doncs que la nostra petició de connexió haurà de passar prèviament pel firewall i els ports hauran d'estar oberts.

$ mysql -h wiki.joanillo.org -u root -p
ERROR 2003 (HY000): Can't connect to MySQL server on 'wiki.joanillo.org' (110)
$ mysql -h 83.63.221.54 -u root -p
ERROR 2003 (HY000): Can't connect to MySQL server on '83.63.221.54' (110)

S'hauria d'obrir el port 3306, que està tancat.

$ nmap -A -T4 -p3306 wiki.joanillo.org

Starting Nmap 5.21 ( http://nmap.org ) at 2014-12-12 00:06 CET
Nmap scan report for wiki.joanillo.org (83.63.221.54)
Host is up (0.041s latency).
rDNS record for 83.63.221.54: 54.Red-83-63-221.staticIP.rima-tde.net
PORT     STATE    SERVICE VERSION
3306/tcp filtered mysql

PostgreSQL

El comportament de PostgreSQL en el nostre sistema es pot controlar amb tres fitxers de configuració que es troben al directori de dades des d’on inicialitzem el nostre clúster PostgreSQL (en el nostre cas, /etc/postgresql/9.1/main/, amb la instal.lació a partir del paquet Ubuntu). Aquests tres fitxers són els següents:

Vegem a continuació els canvis més importants que podem fer en algun d'aquests fitxers.

postresql.conf

D'entrada postgresql accepta una connexió local:

$ psql -h localhost -p 5432 -U postgres -d project
password:

postresql.conf és el fitxer principal de configuració del PostgreSQL. Per defecte tenim aquesta directiva:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
...

que implica que el servidor només accepta connexions locals. Si volem accedir des de la xarxa, haurem de ficar:

listen_addresses = '*'

i reiniciar el servidor (recordar treure el comentari).

$ sudo /etc/init.d/postgresql restart

però encara no ens podem connectar des de la xarxa local: (192.168.1.34 és la IP de la màquina del servidor).

$ psql -h 192.168.1.34 -p 5432 -U postgres -d project
psql: could not connect to server: S’ha refusat la connexió

pg_hba.conf

És possible restringir l'accés a usuaris o a adreces IP modificant les opcions de l’arxiu pg_hba.conf, que es troba a /etc/postgresql/9.1/main/pg_hba.conf.

Aquest fitxer s'utilitza per definir com, on i des d'on un lloc d'un usuari pot emprar el nostre clúster PostgreSQL.

Totes les línies que comencin pel caràcter # s’interpreten com a comentaris. La resta ha de seguir el format següent:

[Tipus de connexió][database][usuari][IP][Netmask][Tipus d’autentificació][opcions]

Depenent del tipus de connexió i del mètode d’autentificació, [IP],[Netmask] i [opcions] poden ser opcionals. Ara explicarem una mica com definir regles d’accés.

El tipus de connexió pot tenir els valors següents:

El tipus de mètode pot tenir els valors següents:

Després de la instal.lació, les línies que ens interessen tenen el següent format:

# Database administrative login by Unix domain socket
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Amb la primera línia queda clar que l'usuari postgres té permisos administratius per connectar-se a totes les bases de dades de forma local. Per ex,

$ psql -h localhost -p 5432 -U postgres -d postgres
$ psql -h localhost -p 5432 -U postgres -d projecte

Si vull acceptar connexions des de la xarxa local 192.168.*.* només a la base de dades project (i no pas a la base de dades postgres), farem:

host    project             all             192.168.1.1 255.255.0.0           md5

Recorda reiniciar el servidor després de qualsevol canvi en el fitxer de configuració. I ara ens podem connectar a la base de dades project, però no pas a postgres:

$ psql -h 192.168.1.34 -p 5432 -U postgres -d project
Password for user postgres: 

$ psql -h 192.168.1.34 -p 5432 -U postgres -d postgres
psql: FATAL:  no pg_hba.conf entry for host "192.168.1.34", user "postgres", database "postgres", SSL on

Per tal de què no ens demani el password, farem servir el mètode d'autenticació trust:

host    project             all             192.168.1.1 255.255.0.0           trust

I ara crearem un usuari exclusiu per connectar-nos a la base de dades project.

$ psql -h localhost -p 5432 -U postgres -d postgres
postgres=> CREATE USER project;
ROLE CREATED
postgres=> ALTER USER project WITH ENCRYPTED PASSWORD 'project';
ALTER ROLE

postgres=> GRANT ALL PRIVILEGES ON DATABASE project TO project;

Ara farem que només l'usuari project pugui connectar-se a la base de dades project, des de la xarxa 192.168.*.*:

host    project             project             192.168.1.1 255.255.0.0           trust
$ psql -h 192.168.1.34 -p 5432 -U postgres -d project
psql: FATAL:  no pg_hba.conf entry for host "192.168.1.34", user "postgres", database "project", SSL on
FATAL:  no pg_hba.conf entry for host "192.168.1.34", user "postgres", database "project", SSL off

$ psql -h 192.168.1.34 -p 5432 -U project -d project
project=>

Altres exemples que ens ajudaran a entendre millor com hem de configurar diferents accessos al clúster PostgreSQL.

host test001 test 10.0.0.100 255.255.255.255 md5
host test001 test 10.0.0.100/32 md5
host test001 test 10.0.0.0 255.255.255.0 md5

Aquesta mateixa entrada es podria escriure també amb la màscara de xarxa en notació CIDR:

host test001 test 10.0.0.0/24 md5
hostssl all test 10.0.0.100 255.255.255.255 md5
hostssl all test 10.1.1.100 255.255.255.255 md5
host all test 10.0.0.0/24 reject
host all all 0.0.0.0/0 md5

Així podríem continuar jugant amb totes les possibilitats que ens ofereix aquest fitxer de configuració. Donem per suposat que a les bases de dades i els usuaris emprats en aquest fitxer han d’existir en el nostre clúster perquè tot funcioni, i que a més alguns dels paràmetres només es podran usar si hem compilat amb les opcions pertinents en el procés d’instal·lació (per exemple, hostssl, pam, krb5).

Per posar en producció els canvis que fem en aquest fitxer cal donar l’ordre a PostgreSQL perquè torni a llegir-lo. Cal un senzill reload:

/usr/local/bin/pg_ctl -D /var/pgsql/data reload

des de la línia d’ordres o amb la funció pg_reload_conf() com a usuari Postgres des de psql, el client PostgreSQL.

Per a una documentació detallada sobre el fitxer pg_hba.conf, cal donar un cop d’ull a la secció Chapter 20. Client Authentication de la documentació oficial de PostgreSQL.

Entrega

(TBD)


creat per Joan Quintana Compte, Novembre 2014

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines