PostgreSQL, a menudo conocido simplemente como Postgres, es un sistema de administración de base de datos relacional de objetos de uso general y código abierto. PostgreSQL tiene muchas funciones avanzadas, como copias de seguridad en línea, recuperación de un punto en el tiempo, transacciones anidadas, consultas SQL y JSON, control de concurrencia de múltiples versiones (MVCC), replicación asíncrona y más.
En este tutorial, le mostraremos cómo instalar PostgreSQL en Debian 9 y exploraremos los fundamentos de la administración básica de bases de datos.
Requisitos previos #
Antes de continuar con este tutorial, asegúrese de que el usuario con el que inició sesión tenga privilegios de sudo.
Instalando PostgreSQL #
Al momento de escribir este artículo, la última versión de PostgreSQL disponible en los repositorios de Debian es la versión 9.6 de PostgreSQL.
Para instalar PostgreSQL en su servidor Debian complete los siguientes pasos:
-
Comience actualizando el índice del paquete local:
sudo apt update -
Instale el servidor de PostgreSQL y el paquete de contribución de PostgreSQL que proporciona funciones adicionales para la base de datos de PostgreSQL:
sudo apt install postgresql postgresql-contrib -
Cuando se complete la instalación, el servicio de PostgreSQL se iniciará automáticamente. Para verificar la instalación nos conectaremos al servidor de base de datos PostgreSQL usando el
psqlutilidad e imprima la versión del servidor:sudo -u postgres psql -c "SELECT version();"La salida se verá así:
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)
Roles de PostgreSQL y métodos de autenticación #
PostgreSQL maneja los permisos de acceso a la base de datos usando el concepto de roles. Un rol puede representar a un usuario de la base de datos o a un grupo de usuarios de la base de datos.
PostgreSQL admite varios métodos de autenticación. Los métodos más utilizados son:
- Confianza:con este método, el rol puede conectarse sin contraseña, siempre que se cumplan los criterios definidos en
pg_hba.confse cumplen. - Contraseña:un rol puede conectarse proporcionando una contraseña. Las contraseñas se pueden almacenar como
scram-sha-256md5ypassword(texto claro) - Ident:este método solo se admite en conexiones TCP/IP. Funciona obteniendo el nombre de usuario del sistema operativo del cliente, con una asignación de nombre de usuario opcional.
- Peer:igual que Ident pero solo se admite en conexiones locales.
La autenticación del cliente PostgreSQL se define en el archivo de configuración llamado pg_hba.conf . De forma predeterminada, para las conexiones locales, PostgreSQL está configurado para usar el método de autenticación de pares.
El postgres El usuario se crea automáticamente cuando instala PostgreSQL. Este usuario es el superusuario de la instancia de PostgreSQL y es equivalente al usuario root de MySQL.
Para iniciar sesión en el servidor PostgreSQL como usuario de Postgres, primero debe cambiar a UserPostgres y luego puede acceder a un indicador de PostgreSQL usando psql utilidad:
sudo su - postgrespsql
Desde aquí, puede interactuar con su instancia de PostgreSQL. Para salir del tipo de shell de PostgreSQL:
\q
Puedes usar el sudo comando para acceder al indicador de PostgreSQL sin cambiar de usuario:
sudo -u postgres psql
El postgres El usuario generalmente se usa solo desde el host local y se recomienda no establecer la contraseña para este usuario.
Creación de rol y base de datos de PostgreSQL #
Puede crear nuevos roles desde la línea de comando usando el createuser dominio. Solo superusuarios y roles con CREATEROLE privilegio puede crear nuevos roles.
En el siguiente ejemplo, crearemos un nuevo rol llamado john una base de datos llamada johndb y conceder privilegios sobre la base de datos.
-
Crear un nuevo rol de PostgreSQL
El siguiente comando creará un nuevo rol llamado "john":
sudo su - postgres -c "createuser john" -
Crear una nueva base de datos PostgreSQL
Cree una nueva base de datos llamada "johndb" usando
createdbcomando:sudo su - postgres -c "createdb johndb" -
Otorgar privilegios
Para otorgar permisos al
johnusuario en la base de datos que creamos en el paso anterior, conéctese al shell de PostgreSQL:sudo -u postgres psqly ejecuta la siguiente consulta:
GRANT ALL PRIVILEGES ON DATABASE johndb TO john;
Habilitar el acceso remoto al servidor PostgreSQL #
De forma predeterminada, PostgreSQL, el servidor solo escucha en la interfaz local 127.0.0.1 . Para habilitar el acceso remoto a su servidor PostgreSQL, abra el archivo de configuración postgresql.conf y agregue listen_addresses = '*' en el CONNECTIONS AND AUTHENTICATION sección.
sudo vim /etc/postgresql/9.6/main/postgresql.conf#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
guarde el archivo y reinicie el servicio PostgreSQL con:
sudo service postgresql restart
Verifica los cambios con el ss utilidad:
ss -nlt | grep 5432LISTEN 0 128 0.0.0.0:5432 0.0.0.0:*
LISTEN 0 128 [::]:5432 [::]:*
Como puede ver en la salida anterior, el servidor PostgreSQL está escuchando en todas las interfaces (0.0.0.0).
El último paso es configurar el servidor para aceptar conexiones remotas editando el pg_hba.conf archivo.
A continuación se muestran algunos ejemplos que muestran diferentes casos de 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