miércoles, 8 de septiembre de 2010

PostgreSQL 8.4.4 en Ubuntu Server 10.04.1

Vamos a instalar y ejecutar la base de datos PostgreSQL en un Ubuntu Server de 64 Bits 10.04.1. Además, daremos acceso a un usuario en un Kubuntu Lucid que accederá por red al servidor.

Instalación de Paquetes del Servidor

Los paquetes de PostgreSQL están incluidos en los repositorios de Ubuntu, por lo que la instalación es bastante sencilla. Además se incluye la última versión estable, la 8.4.4.

$ sudo aptitude install postgresql

Esto nos instalará tanto el servidor de base de datos como el cliente de la versión 8.4. Además nos creará el usuario postgres que es el que usaremos para gestionar la base de datos.

Una vez instalado, cambiamos la contraseña del usuario postgres a la que queramos.

$ sudo passwd postgres
Introduzca la nueva contraseña de UNIX: 
Vuelva a escribir la nueva contraseña de UNIX: 
passwd: password updated successfully

Una vez cambiada podemos usar el comando su (switch user) para cambiar a este usuario y operar en la base de datos. En el fichero /etc/passwd comprobamos que la shell del usuario es /bin/bash.

$ sudo cat /etc/passwd | grep postgres
postgres:x:104:113:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
fjbejarano@ubults6403:~$ su - postgres
$ su - postgres
Contraseña: 
postgres@ubults6403:~$

El comando usado para gestionar la base de datos es psql. Con este comando podemos entrar a la terminal de postgresql, ejecutar scripts, etc.

Podemos ver las bases de datos del servidor

postgres@ubults6403:~$ psql -l
                                Listado de base de datos
  Nombre   |  Dueño   | Codificación |  Collation  |    Ctype    |      Privilegios      
-----------+----------+--------------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8         | es_ES.UTF-8 | es_ES.UTF-8 | 
 template0 | postgres | UTF8         | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres
                                                                 : postgres=CTc/postgres
 template1 | postgres | UTF8         | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres
                                                                 : postgres=CTc/postgres
(3 filas)

Conectamos a la base de datos template1

$ psql -d template1 -U postgres
psql (8.4.4)
Digite «help» para obtener ayuda.

template1=# \q
postgres@ubults6403:~$

Como vemos ya hemos accedido a la base de datos (-d) template1 con el usuario (-U) postgres. Ahora estamos en el terminal cliente de esa base de datos y podemos operar sobre ella. No debemos confundir el usuario postgres del sistema con el usuario postgres de la base de datos que acabamos de usar aquí para entrar al terminal cliente. Con \q salimos del terminal.

Debemos crear una contraseña para el usuario postgres de la base de datos, ya que, como hemos visto, hemos accedido a la base de datos directamente y esto es muy inseguro.

$ psql -c "ALTER USER postgres WITH ENCRYPTED PASSWORD 'password1'"
ALTER ROLE

Usamos WITH ENCRYPTED PASSWORD para que la contraseña se cifre en la base de datos y podamos usar en la autenticación el cifrado. Esto hace que no se trasmita en texto plano la contraseña por la red.

Salimos del usuario postgres

$ exit
logout
fjbejarano@ubults6403:~$

Configuración de pg_hba.conf (host based autenthication)

Este fichero nos permite configurar la autenticación de usuarios y desde donde se autentican. Ponemos lineas según la autenticación que queramos. Hay que tener en cuenta que si no se encuentra una linea que autentique a un usuario se deniega la conexión, y si se encuentra y falla también se deniega la conexión, no sigue a la siguiente linea.

sudo vim /etc/postgresql/8.4/main/pg_hba.conf

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local   all         postgres                          ident

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident

# IPv4 local connections:
# Permitimos el acceso a todas las bases de datos, todos los usuarios que se conecten
# en la interfaz localhost del servidor con contraseña cifrada.
host    all         all         127.0.0.1/32          md5
# Lo mismo pero el usuario postgres y que se conecte desde cualquier máquina de la red
# 192.168.1.0/24 con contraseña encriptada.
host    all         postgres    192.168.1.0/24        md5
# Lo mismo pero el usuario es fjbejarano y puede conectarse solo desde el host 192.168.1.35
# (el host kubuntu) y solo a la base de datos "mibbdd" sin las comillas.
host    mibbdd      fjbejarano  192.168.1.35/32       md5

# IPv6 local connections:
host    all         all         ::1/128               md5

Si ponemos md5, es importante que cuando creemos un usuario en la base de datos usemos WITH ENCRYPTION PASSWORD en vez de solo WITH PASSWORD ya que con esta última no se cifraría la contraseña y no funcionaria la autenticación.

Configuración del archivo postgresql.conf

Este archivo es el archivo general de configuración del servidor postgresql. Configuraremos un par de cosas para que permita la conexión remota y la encriptación.

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

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

# Directorios y archivos que usará el servidor.

# Este directorio es donde se almacenarán los datos de las bases de datos en el servidor local
data_directory = '/var/lib/postgresql/8.4/main'         # use data in another directory
                                        # (change requires restart)
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf'       # host-based authentication file
                                        # (change requires restart)
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf'   # ident configuration file
                                        # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/8.4-main.pid'          # write an extra PID file
                                        # (change requires restart)


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

# - Connection Settings -

# Permitimos las conexiones a localhost y a 192.168.1.13 que es la IP del servidor
# Podemos poner varias separadas por comas o un * que equivale a todas las interfaces.
listen_addresses = 'localhost,192.168.1.13'     # (change requires restart)
# el puerto por defecto es 5432
port = 5432                             # (change requires restart)

# Número máximo de conexiones que permitimos al servidor. Cada conexión que añadimos
# equivale a unos 400 bytes de memoria compartida más el espacio de lock
# (ver max_locks_per_transaction)
max_connections = 100                   # (change requires restart)

# Conexiones reservadas para el superusuario. Si dejamos las 3, dispondriamos de 97
# conexiones para los usuarios 100 conexiones - 3 de super usuario.
#superuser_reserved_connections = 3     # (change requires restart)

unix_socket_directory = '/var/run/postgresql'           # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min          # 1s-600s
ssl = true                              # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'      # allowed SSL ciphers
                                        # (change requires restart)
#ssl_renegotiation_limit = 512MB        # amount of data between renegotiations

# Permitimos el cifrado de contraseñas.
password_encryption = on
#db_user_namespace = off

...

# - TCP Keepalives -
# see "man 7 tcp" for details

# En el kernel linux, por defecto una conexión inactiva se mantiene 2 horas y después prueba 9
# veces cada 74 sg si obtiene respuesta. Si no cierra la conexión.

# Nosotros permitiremos media hora (1800 sg) de conexión inactiva. Después de media hora
# tcp hará las pruebas para ver si hay respuesta.
tcp_keepalives_idle = 1800               # TCP_KEEPIDLE, in seconds;
                                         # 0 selects the system default
# Cuando pasan los 1800 sg prueba si la conexión está inactiva cada 60 sg.
tcp_keepalives_interval = 60            # TCP_KEEPINTVL, in seconds;
                                        # 0 selects the system default
# Hace 5 pruebas de 60 sg, es decir, probará durante 5 min y si no recibe respuesta 
# cortará la conexión
tcp_keepalives_count = 5                # TCP_KEEPCNT;
                                        # 0 selects the system defaul

...

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

...

# Aqui tenemos la configuración local por defecto. Como vemos usará
# es_ES.UTF8 y español.

# - Locale and Formatting -

datestyle = 'iso, dmy'
#intervalstyle = 'postgres'
#timezone = unknown                     # actually, defaults to TZ environment
                                        # setting
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                                        # abbreviations.  Currently, there are
                                        #   Default
                                        #   Australia
                                        #   India
                                        # You can create your own file in
                                        # share/timezonesets/.
#extra_float_digits = 0                 # min -15, max 2
#client_encoding = sql_ascii            # actually, defaults to database
                                        # encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'es_ES.UTF-8'                     # locale for system error message
                                                # strings
lc_monetary = 'es_ES.UTF-8'                     # locale for monetary formatting
lc_numeric = 'es_ES.UTF-8'                      # locale for number formatting
lc_time = 'es_ES.UTF-8'                         # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.spanish'

Hay muchas más cosas que se pueden configurar.

Creamos el usuario de base de datos fjbejarano con permiso para crear otras bases de datos pero no otros usuarios.

$ createuser -P -d -e -E -h 192.168.1.13 -U postgres fjbejarano
Ingrese la contraseña para el nuevo rol: 
Ingrésela nuevamente: 
¿Será el nuevo rol un superusuario? (s/n) n
¿Debe permitírsele al rol la creación de otros roles? (s/n) n
Contraseña: 
CREATE ROLE fjbejarano ENCRYPTED PASSWORD 'md5a002c7ad4af5f97343cabf0c4e473f01' NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;

Como vemos ha creado el usuario fjbejarano. Para que nos pida crear la contraseña usamos -P, para que sea encriptada -E (importante), para que permita crear bases de datos a fjbejarano -d y para que muestre la sentencia SQL que ha usado para crear el usuario -e. Cuando ejecutamos nos pide que metamos la contraseña de fjbejarano. Después le decimos que no será super usuario no creara otros usuarios (roles). Por último, nos pide la contraseña del usuario de base de datos postgres, es decir, "password1" sin las comillas.

Con esto, ya deberíamos poder configurar Kubuntu y usar el cliente de postgre y pgadmin3 desde el.

Reiniciamos el servidor.

$ sudo service postgresql-8.4 restart
[sudo] password for fjbejarano: 
 * Restarting PostgreSQL 8.4 database                                                                         [ OK ]

Configuración del cliente. GNU\Linux Kubuntu Lucid Lynx

En el cliente, instalamos los siguientes paquetes.

$ sudo aptitude install postgresql-client pgadmin3

Esto instala solo el cliente de linea de comandos de postgresql 8.4.4 y la aplicación gráfica pgadmin3. La aplicación gráfica es opcional.

Vemos si podemos conectar mediante el cliente de linea de comandos.

$ psql -h 192.168.1.13 -U fjbejarano
psql: FATAL:  no hay una l?nea en pg_hba.conf para ... 

Nos dice que hay un error, pero sabemos que es correcto ya que no hemos creado la base de datos mibbdd que necesita fjbejarano para poder conectar. Para ello usamos el usuario postgres desde el cliente para crear la base de datos. La base de datos será creada con las opciones que le pongamos y las opciones que no pongamos las tomará por defecto de la base de datos template1.

$ psql -h 192.168.1.13 -U postgres -c "CREATE DATABASE mibbdd WITH OWNER fjbejarano"
Contraseña para usuario postgres: 
CREATE DATABASE
$ psql -d mibbdd -h 192.168.1.13 -U postgres -l
Contraseña para usuario postgres: 
                                 Listado de base de datos
  Nombre   |   Dueño    | Codificación |  Collation  |    Ctype    |      Privilegios      
-----------+------------+--------------+-------------+-------------+-----------------------
 mibbdd    | fjbejarano | UTF8         | es_ES.UTF-8 | es_ES.UTF-8 | 
 postgres  | postgres   | UTF8         | es_ES.UTF-8 | es_ES.UTF-8 | 
 template0 | postgres   | UTF8         | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres
                                                                   : postgres=CTc/postgres
 template1 | postgres   | UTF8         | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres
                                                                   : postgres=CTc/postgres
(4 filas)

Como vemos hemos creado desde el cliente la base de datos usando el usuario postgres. Volvemos a ver si ahora podemos conectar.

$ psql -d mibbdd -h 192.168.1.13 -U fjbejarano
Contraseña para usuario fjbejarano: 
psql (8.4.4)
conexión SSL (cifrado: DHE-RSA-AES256-SHA, bits: 256)
Digite «help» para obtener ayuda.

mibbdd=> 

Y aquí está.

mibbdd=> select version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.4 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
(1 fila)

Por último también podemos usar la aplicación gráfica. La ejecutamos en una terminal en kubuntu

$ pgadmin3 &

o vamos al menú de KDE y le damos click.

Aparece la aplicación. Le damos a crear un nuevo registro, el botón del enchufe y rellenamos como en la pantalla.


Por último vemos que hemos conectado. Dejo una pantalla panorámica de la herramienta. Como hemos conectado con el usuario de base de datos fjbejarano al servidor, solo podremos cambiar cosas de mibbdd, como crear tablas, etc.

Related Posts with Thumbnails