De nuevo con postgres

Hace tanto que no usaba postgres que ya había olvidado ciertos detalles, los documentare aqui para futuras referencias, estamos usando un postgresql 8.4.5 sobre un ubuntu server 9.10, tambien tuve otras bronquillas interesantes con los accesos locales y remotos.

1. Despues de instalar nuestro postgres con apt-get nos disponemos a accesar con el ya tradicional:

$sudo su postgres

2. Una vez adentro accesamos directamente a pqsl

#psql

3. Digamos que quiero hacer una base de datos llamada pepito y que el usuario sea pepito_user con un password, primero crearemos el usuario, despues la db y al final nos salimos

CREATE USER pepito_user WITH PASSWORD 'n0m4nch35';

postgres=# CREATE DATABASE pepito WITH OWNER pepito_user;

postgres=# \q

4. Ahora accesamos como pepito_user

# psql -d pepito -U pepito_user --password

Aqui lo mas probable es psql te raye la mother, que no te permita accesar, esto es porque no está habilitado el acceso de este tipo para postgres, para hacer esto modificamos nuestro querido archivo pg_hba.conf, en cada maquina puede variar pero en la mia estaba en

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

Lo editamos como root, te encontrarás esta linea la cual vamos a borrar o comentar

local all all ident

y escribiremos la siguiente

local all all md5

Explicacion: el método de autenticación ident se refiere a que el usuario que se validará para el acceso sera el que esta en sesión y como mi usuario es postgres aunque escriba yo --password en psql le valdrá gorro, por lo cual preferimos md5 que nos permite mandar un user y password encriptado por seguridad en md5, esto también es útil para accesar remotamente ya que remotamente no siempre estas logueado como un usuario ident.

Una vez que guardemos los cambios debemos reiniciar el demonio de postgresql

# sudo /etc/init.d/postgresql stop

# sudo /etc/init.d/postgresql start

ó

# sudo /etc/init.d/postgresql restart

5. Ahora que ya estamos dentro ya podemos crear la estructura de nuestra db con CREATE table... cosa que no especificare aqui. Despues de eso nos vamos a otra maquina que tenga instalado un cliente psql e intentamos accesar remotamente:

# psql pepito -h pepito.com.mx -U pepito_user --password

pepito.com.mx puede ser la ip de tu server postgres también... sin embargo lo mas probable es que te rechace la petición... ¿que pasa? que nuestro archivo pg_hba.conf no esta configurado para recibir peticiones externas, por lo cual nos regresamos a nuestro server de postgresql y lo editamos de nuevo, ahora agregaremos la siguiente linea:

host all all 0.0.0.0 0.0.0.0 md5

Y reiniciamos postgres. Ahora ya podrán acceder remotamente y hacer las tablas que quieran a menos que suceda algo como lo del siguiente punto.

6. Mi server tiene 6 direcciones ip para una misma tarjeta por lo cual postgres no mostraba el abierto el puerto mas que en localhost y en la ip principal, para que postgres se escuche en todas las direcciones ip o incluso en las direcciones que tu elijas debemos modificar el archivo

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

Buscamos una linea que diga listen_addresses que esta comentada, ahi vienen las instrucciones para configurarla, yo lo único que hice fue escribir una nueva linea de la siguiente manera:

listen_addresses = '*'

reiniciando el servidor ya se podia ver el puerto 5432 de postgresql en todas las ip de forma remota... como supe esto? pues monitoreando los puertos con ayuda del comando "nmap" y con el comando "netstat -putan" con los cuales puedes escanear los puertos abiertos tanto de tu misma maquina como el de otras maquinas remotas, por ejemplo, al ejecutar nmap dentro de mi server podia ver el puerto 5432 abierto pero si ejecutaba nmap desde otra maquina hacia mi server no lo podia ver, por cierto, recomiendo ejecutar nmap de la siguiente manera, tarda un poco mas pero podemos ver mas puertos abiertos.

#nmap -p1-65535 pepito.com.mx

FIN

Receta de Cocina Postgresql.

Para esta receta utilizaremos un ubuntu feisty montado en una dell inspiron E1505:

1. Dadas estas caracteristicas bajaremos nuestro gestor de bases de datos postgresql mediante el maravilloso apt-get (o si prefieren usar Synaptics allá ustedes):
$sudo apt-get install postgresql-8.2

2. Una vez instalado nos disponemos a entrar con nuestro usuario postgres a nuestra sesion postgres:
$sudo su postgresql
Deberemos ver en nuestro shell como algo similar a esto:
postgres@micompu: /$

3. Ahora, podemos intentar entrar al administrador psql:
postgres@micompu: /$ psql

Esto nos dará acceso a todas las bases de datos como administrador, como ven, esto es poco seguro, además de que no podemos hacer muchas cosas, por eso vamos a colocar la contraseña para administrador y vamos a realizar un pequeño cambio a la configuración:

3a. Para cambiar la contraseña, ya estando dentro del editor psql escribimos las siguientes lineas

=# ALTER USER postgres
=# WITH PASSWORD '12345';

Si todo salio bien nos salimos con el comando \q
=#\q

4a. Luego un exit para volver a nuestro usuario. Entraremos a nuestro archivo de configuracion de conexiónes pg_hba.conf de nuestro postgresql.
$sudo vi /etc/postgresql/8.2/main/hba.conf

y cambiamos la linea
local all all ident sameuser
por
local all all password

y hasta donde tengo entendido esto permitira accesos desde cualquier lugar por medio de un password, de otra manera nadie podrá accesar aunque le asignes un password.

Ahora reiniciaremos nuestro servidor de bases de datos:
$sudo /etc/init.d/postgresql-8.2 restart

Ahora entraremos tal como lo hicimos hasta el paso 3.

4. Una vez en en psql, nos dispongremos a crear un usuario, de hecho hay dos maneras de hacerlo:

4a.la primera mediante el comando createuser pero no en psql sino como usuario general de postgres, asi que tendriamos que salir de psql para hacerlo. Te preguntara algunas cosas como si el usuario creado va a ser

4b. la segunda es ejecutando una sentencia SQL dentro de psql, la cual sería similar a esta:
=#CREATE USER nombre_del_usuario WITH PASSWORD 'supass';

5. Ahora crearemos una base de datos que pertenezca a ese Usuario
=#CREATE DATABASE mi_db WITH OWNER = "nombre_del_usuario";


6. Ahora podemos entrar desde usuario postgres
postgres@micompu: /$ psql -d mi_db -U nombre_de_usuario
y veremos algo similar a esto:
mi_db=>

Ahora podremos meter las sentencias SQL para crear tablas, etc, etc... pero esto no es lo importante, ya que podemos acceder incluso como administrador psql y realizar esto, lo que logramos aqui es utilizar el acceso a un usuario especial al cual podemos asignarle ciertos privilegios por seguridad.

7. Como plus, instalaremos mediante apt-get admin3.
$sudo apt-get install admin3

el cual se agregará a nuestro menú de aplicaciones y herramientas del sistema. Para poder acceder en conexión pondremos los siguientes parámetros(los cuales configuramos en los pasos anteriores):

Address: localhost
MaintenanceDB: postgres
Username: postgres
Port: 5432
SSL: --- lo dejamos en blanco ---
Password: 12345

Algunos otros comandos utiles como psql:
\? - ver ayuda
\q - salir
\dt - ver tus tablas(siempre y cuando hayas entrado como en el paso 6, incluso sin User)

... los que vayan saliendo