Come installare PostgreSQL su Debian 9

PostgreSQL, spesso noto semplicemente come Postgres, è un sistema di gestione di database relazionale a oggetti per uso generale open source. PostgreSQL ha molte funzionalità avanzate come backup online, ripristino point-in-time, transazioni nidificate, query SQL e JSON, controllo della concorrenza multi-versione (MVCC), replica asincrona e altro ancora.

In questo tutorial, ti mostreremo come installare PostgreSQL su Debian 9 ed esploreremo i fondamenti dell'amministrazione di base del database.

Prerequisiti #

Prima di procedere con questo tutorial, assicurati che l'utente a cui hai effettuato l'accesso disponga dei privilegi sudo.

Installazione di PostgreSQL #

Al momento della stesura di questo articolo, l'ultima versione di PostgreSQL disponibile dai repository Debian è PostgreSQL versione 9.6.

Per installare PostgreSQL sul tuo server Debian, completa i seguenti passaggi:

1. Inizia aggiornando l'indice del pacchetto locale:

   sudo apt update

2. Installa il server PostgreSQL e il pacchetto contrib PostgreSQL che fornisce funzionalità aggiuntive per il database PostgreSQL:

   sudo apt install postgresql postgresql-contrib

3. Al termine dell'installazione, il servizio PostgreSQL si avvierà automaticamente. Per verificare l'installazione ci collegheremo al server del database PostgreSQL usando il psql utility e stampa la versione del server:

   sudo -u postgres psql -c "SELECT version();"

   L'output sarà simile a questo:

                                                    version                                                  
   -----------------------------------------------------------------------------------------------------------
   PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
   (1 row)

Psql è un programma terminale interattivo che consente di interagire con il server PostgreSQL.

Ruoli PostgreSQL e metodi di autenticazione #

PostgreSQL gestisce le autorizzazioni di accesso al database utilizzando il concetto di ruoli. Un ruolo può rappresentare un utente del database o un gruppo di utenti del database.

PostgreSQL supporta numerosi metodi di autenticazione. I metodi più comunemente usati sono:

• Fiducia - Con questo metodo, il ruolo può connettersi senza password, purché i criteri definiti nel pg_hba.conf sono soddisfatte.
• Password:un ruolo può connettersi fornendo una password. Le password possono essere memorizzate come scram-sha-256 md5 e password (testo in chiaro)
• Ident - Questo metodo è supportato solo su connessioni TCP/IP. Funziona ottenendo il nome utente del sistema operativo del client, con una mappatura del nome utente opzionale.
• Peer - Uguale a Ident ma è supportato solo su connessioni locali.

L'autenticazione del client PostgreSQL è definita nel file di configurazione denominato pg_hba.conf . Per impostazione predefinita, per le connessioni locali, PostgreSQL è impostato per utilizzare il metodo di autenticazione peer.

Il postgres user viene creato automaticamente quando installi PostgreSQL. Questo utente è il superutente per l'istanza PostgreSQL ed è equivalente all'utente root di MySQL.

Per accedere al server PostgreSQL come utente postgres devi prima passare a userpostgres e poi puoi accedere a un prompt PostgreSQL usando il psql utilità:

sudo su - postgrespsql

Da qui, puoi interagire con la tua istanza PostgreSQL. Per uscire dal tipo di shell PostgreSQL:

\q

Puoi usare il sudo comando per accedere al prompt di PostgreSQL senza cambiare utente:

sudo -u postgres psql

Il postgres utente viene in genere utilizzato solo dall'host locale e si consiglia di non impostare la password per questo utente.

Creazione del ruolo PostgreSQL e del database #

Puoi creare nuovi ruoli dalla riga di comando usando createuser comando. Solo superutenti e ruoli con CREATEROLE privilegio può creare nuovi ruoli.

Nell'esempio seguente creeremo un nuovo ruolo denominato john un database chiamato johndb e concedere privilegi sul database.

1. Crea un nuovo ruolo PostgreSQL

   Il comando seguente creerà un nuovo ruolo chiamato "john":

   sudo su - postgres -c "createuser john"

2. Crea un nuovo database PostgreSQL

   Crea un nuovo database chiamato "johndb" usando il createdb comando:

   sudo su - postgres -c "createdb johndb"

3. Concedi privilegi

   Per concedere le autorizzazioni al john utente sul database che abbiamo creato nel passaggio precedente, connettiti alla shell PostgreSQL:

   sudo -u postgres psql

   ed esegui la seguente query:

   GRANT ALL PRIVILEGES ON DATABASE johndb TO john;

Abilita l'accesso remoto al server PostgreSQL #

Per impostazione predefinita, il server PostgreSQL è in ascolto solo sull'interfaccia locale 127.0.0.1 . Per abilitare l'accesso remoto al tuo server PostgreSQL, apri il file di configurazione postgresql.conf e aggiungi listen_addresses = '*' nel CONNECTIONS AND AUTHENTICATION sezione.

sudo vim /etc/postgresql/9.6/main/postgresql.conf

/etc/postgresql/9.6/main/postgresql.conf

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

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

salva il file e riavvia il servizio PostgreSQL con:

sudo service postgresql restart

Verifica le modifiche con ss utilità:

ss -nlt | grep 5432

LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

Come puoi vedere dall'output sopra, il server PostgreSQL è in ascolto su tutte le interfacce (0.0.0.0).

L'ultimo passaggio consiste nel configurare il server per accettare connessioni remote modificando il pg_hba.conf file.

Di seguito sono riportati alcuni esempi che mostrano diversi casi d'uso:

/etc/postgresql/9.6/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane will be able to access all databases from all locations using a md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane will be able to access only the janedb from all locations using a md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane will be able to access all databases from a trusted location (192.168.1.134) without a password
host    all             jane            192.168.1.134            trust