Salta el contingut
 

Configuració de connexions

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Configuració de les connexions a PostgreSQL

Perquè un client es puga connectar a PostgreSQL, cal configurar dues coses: per on escolta el servidor i qui té permís per connectar-s'hi. A més, cal saber indicar els paràmetres de connexió des del client.

Configuració del servidor

La configuració de les connexions del servidor es reparteix entre dos fitxers, que per defecte es troben al directori de dades (PGDATA).

Fitxer postgresql.conf

El fitxer postgresql.conf és el fitxer principal de configuració del servidor. Conté molts paràmetres, però els que determinen per on escolta el servidor són dos:

  • listen_addresses: indica en quines adreces IP dels adaptadors de xarxa del servidor s'escolten connexions. Per defecte val localhost, de manera que el servidor només escolta en la interfície de loopback i només s'hi pot connectar des de la mateixa màquina. Per accedir-hi des d'altres equips, cal canviar aquest valor.

    Valors de listen_addresses
    Valor Significat
    localhost Només la interfície de loopback (valor per defecte)
    192.168.1.10 La IP d'un adaptador concret del servidor
    localhost,192.168.1.10 Una llista d'adreces separades per comes
    * Totes les interfícies de xarxa

  • port: és el port TCP on el servidor espera les connexions. Per defecte és el 5432, i normalment només cal canviar-lo si hi ha diversos servidors PostgreSQL a la mateixa màquina.

listen_addresses i port són paràmetres estàtics (context postmaster): cal reiniciar el servidor perquè s'apliquen els canvis.

Amb aquesta configuració, per exemple, el servidor acceptaria connexions des de qualsevol interfície de xarxa pel port per defecte:

listen_addresses = '*'
port = 5432

Fitxer pg_hba.conf

El fitxer pg_hba.conf (host-based authentication) decideix qui es pot connectar, des d'on i amb quin mètode d'autenticació. Cada línia del fitxer és una regla com aquesta:

# TYPE  DATABASE  USER  ADDRESS       METHOD
host    all       all   0.0.0.0/0     scram-sha-256

Cada columna de la regla té un significat concret:

  • TYPE: el tipus de connexió al qual s'aplica la regla. Distingeix entre connexions locals (des de la mateixa màquina) i connexions per xarxa, amb o sense xifratge.

    Valors de TYPE
    Valor Significat
    local Connexió des de la mateixa màquina a través de sòcols Unix
    host Connexió per xarxa TCP/IP, amb o sense xifratge
    hostssl Connexió per xarxa TCP/IP xifrada amb TLS obligatòriament
    hostnossl Connexió per xarxa TCP/IP sense xifrar

  • DATABASE: la base de dades a la qual es vol accedir.

    Valors de DATABASE
    Valor Significat
    botiga Una base de dades concreta
    botiga,rrhh Una llista de bases de dades separades per comes
    all Totes les bases de dades
    sameuser La base de dades amb el mateix nom que l'usuari que es connecta
    replication Connexions de replicació (no correspon a cap base de dades)

  • USER: l'usuari (rol) de PostgreSQL que es connecta.

    Valors de USER
    Valor Significat
    admin Un usuari concret
    admin,joan Una llista d'usuaris separats per comes
    all Tots els usuaris
    +professors Tots els membres del grup (rol) professors

  • ADDRESS: el rang d'adreces IP des del qual es connecta el client, en notació CIDR. Aquesta columna no s'utilitza en les connexions local, ja que no passen per la xarxa.

    Valors de ADDRESS
    Valor Significat
    192.168.1.50/32 Un únic equip
    192.168.1.0/24 Tota una xarxa (de 192.168.1.0 a 192.168.1.255)
    127.0.0.1/32 Només la pròpia màquina per IPv4 (loopback)
    0.0.0.0/0 Qualsevol adreça IPv4
    ::/0 Qualsevol adreça IPv6
    samenet Qualsevol adreça de les xarxes a les quals està connectat el servidor

  • METHOD: el mètode d'autenticació, és a dir, com ha de demostrar l'usuari qui és.

    Valors de METHOD
    Valor Significat
    scram-sha-256 Demana contrasenya i la verifica de manera segura. És el mètode recomanat
    md5 Demana contrasenya amb un algorisme antic i menys segur
    peer Comprova que l'usuari del sistema operatiu coincideix amb el de PostgreSQL (només local)
    trust Permet la connexió sense cap comprovació. Només per a entorns de proves
    reject Rebutja la connexió

Els canvis a pg_hba.conf no requereixen reiniciar el servidor: n'hi ha prou amb recarregar la configuració.

SELECT pg_reload_conf();

Només afecta les connexions noves. Si el fitxer conté errors, no s'aplica i es manté la configuració anterior. Els errors es poden consultar a la vista pg_hba_file_rules:

SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules;

Documentació oficial de PostgreSQL

Connexió des dels clients

Client de línia d'ordres: psql

El client de línia d'ordres psql permet connectar-se a un servidor indicant els paràmetres de connexió com a opcions:

psql [-h <host>] [-p <port>] [-U <usuari>] [-d <base_de_dades>]
  • [-h <host>]: (opcional) nom o IP del servidor. Si no s'indica, es connecta per sòcol Unix local.
  • [-p <port>]: (opcional) port TCP del servidor. Per defecte, 5432.
  • [-U <usuari>]: (opcional) rol amb què es connecta. Per defecte, l'usuari del sistema operatiu.
  • [-d <base_de_dades>]: (opcional) base de dades a la qual es connecta. Per defecte, la que té el mateix nom que l'usuari.

Aquests valors també es poden definir amb les variables d'entorn PGHOST, PGPORT, PGUSER i PGDATABASE, de manera que no cal repetir-los a cada connexió.

Documentació oficial: psql – PostgreSQL

Exemple: Connexió amb psql

Es connecta a la base de dades postgres del servidor local amb l'usuari postgres:

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

També es pot utilitzar una cadena de connexió (connection string) en format URI:

psql "postgresql://postgres@localhost:5432/postgres"

Clients gràfics

Per treballar de manera gràfica, es pot utilitzar l'extensió PostgreSQL per a Visual Studio Code, o ferramentes dedicades com pgAdmin o DBeaver.

Els paràmetres bàsics de connexió a configurar en un client gràfic són:

Paràmetre Valor
Nom de l'amfitrió (host) localhost
Port 5432
Base de dades de manteniment postgres
Nom d'usuari postgres
Contrasenya La configurada al contenidor (POSTGRES_PASSWORD)

Comprovació de la connexió

Una vegada creada la connexió, es pot comprovar executant una consulta com la següent, que retorna informació de la sessió actual:

SELECT current_user,
       session_user,
       current_database() AS db_name,
       inet_server_addr() AS host,
       inet_server_port() AS port,
       version();

 current_user | session_user | db_name  |    host    | port |      version
--------------+--------------+----------+------------+------+-------------------
 postgres     | postgres     | postgres | 172.18.0.2 | 5432 | PostgreSQL 16.4 ...
📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez