11 septiembre 2012

GUIA RAPIDA DE POSTGRES PARA SYSADMIN

-=GUIA RAPIDA DE POSTGRES PARA SYSADMIN=-


Este manual solo es un vistaso rapido para saber como funciona las sentencias de sql de postgres, como dar de alta a usuarios, crear algunas tablas, ingresar datos y mas.


-ingresamos a la consola postgres por:
/usr/local/pgsql/bin/psql

-para facilidad le cambiamos el pass a postgres
>ALTER USER postgres with password '123456';

\h ayuda sobre ordenes de sql
\? para ordenes postgres

##CREADIO, ELIMINACION Y MANEJO DE BASE DE DATOS

-listamos todas las bases de datos existentes
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
-creamos una base de datos
prueba=# CREATE DATABASE prueba;
CREATE DATABASE


-listamos todas las bases de datos observamos que ya tenemos creada la base prueba.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 prueba    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

-tambien podemos borrarla
postgres=# DROP DATABASE prueba;

-ahora nos conectaremos a la base prueba para trabajar con ella
postgres=# \c prueba
You are now connected to database "prueba".

## MANEJO DE TABLAS Y DATOS


-listamos todas las tablas o relaciones que tenemos creadas.  no debe aparecer nada
prueba=# \d
No relations found.


-creamos una tabla llamada empleados con los campos nombre, apellido, ciudad, fechanacimiento:
create table empleados(nombre varchar(20), apellido varchar(20), ciudad varchar(10), fechanacimiento date);

-volvemos a listar las tablas y observamos los cambios
prueba=# \d
           List of relations
 Schema |   Name    | Type  |  Owner  
--------+-----------+-------+----------
 public | empleados | table | postgres
(1 row)

-vemos de que se compone la tabla empleados
prueba=# \d empleados
              Table "public.empleados"
     Column      |         Type          | Modifiers
-----------------+-----------------------+-----------
 nombre          | character varying(20) |
 apellido        | character varying(20) |
 ciudad          | character varying(10) |
 fechanacimiento | date                  |

-verificamos que no existe ningun dato en la tabla
prueba=# SELECT * from empleados;
 nombre | apellido | ciudad | fechanacimiento
--------+----------+--------+-----------------
(0 rows)


-ingresamos los siguentes datos
prueba=# insert into empleados values('pedro','peralta','santiago','2000-03-30');
INSERT 0 1

prueba=# insert into empleados values('Mario','Garcia','La Vega','1900-03-30');
INSERT 0 1

-volvemos a listar el contenido de la tabla:
prueba=# SELECT * from empleados;
 nombre | apellido |  ciudad  | fechanacimiento
--------+----------+----------+-----------------
 pedro  | peralta  | santiago | 2000-03-30
 Mario  | Garcia   | La Vega  | 1900-03-30


##USUARIOS
-revisamos que el usuario ariel no esta creado. 
prueba=# select * from pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |               passwd                | valuntil | useconfig
----------+----------+-------------+----------+-----------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t         | md5a3556571e93b0d20722ba62be61e8c2d |          |
(1 rows)

prueba=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | ******** |          |
(1 rows)

pg_user es para usuarios no privilegiados y el pg_shadow es para usuarios privilegiados.  la dif es en el campo passwd

-creamos un usuarios con la contrasena 123456
CREATE USER ariel WITH PASSWORD '123456'; 

-le otorgamos todos los permisos al usuario ariel
GRANT ALL ON empleados TO ariel ;

-ahora confirmamos que nuestro usuario ha sido creado

prueba=# select * from pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |               passwd                | valuntil | useconfig
----------+----------+-------------+----------+-----------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t         | md5a3556571e93b0d20722ba62be61e8c2d |          |
 ariel    |    16389 | t           | t        | t         | md555656b0bc9c7f30d7620a9265c357bdb |          |
(2 rows)


prueba=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | ******** |          |
 ariel    |    16389 | t           | t        | t         | ******** |          |
(2 rows)

-vamos a crear otor usuario de prueba llamado dexter y le otorgaremos permisos de solo crear base de datos:

prueba=# CREATE USER dexter with password '123456';
prueba=# ALTER USER dexter CREATEDB;

-podemos cambiarle el nombre de un usuario.  vamosa  cambiar el nombre de dexter por el de charlie.
prueba=# ALTER USER dexter RENAME TO charlie;

-vamos a borrar nuestro usuario charlie para evitar problemas
prueba=# DROP USER charlie;


##GRUPOS
-cuando tenemos muchos usuarios en postgres es aconsejable utilizar grupos.  observamos cuales son los grupos creados
prueba=# SELECT * from pg_group
 groname | grosysid | grolist
---------+----------+---------
 sales   |    16392 | {}
(1 rows)
-crearemos un grupo llamado ventas
prueba=# CREATE GROUP ventas;

-agregamos al usuario ariel en el grupo de ventas
prueba=# ALTER GROUP ventas ADD USER ariel;

-ahora borramos el usuario de la lista de ventas
prueba=# ALTER GROUP ventas DROP USER ariel;

-cambiamos el nombre del grupo sales por nuevo

prueba=# ALTER GROUP sales RENAME TO nuevo;
ALTER ROLE
prueba=# SELECT * from pg_group;
 groname | grosysid | grolist
---------+----------+---------
 ventas  |    16393 | {}
 nuevo   |    16392 | {}
(2 rows)

-borramos el grupo nuevo
prueba=# DROP GROUP nuevo;
DROP ROLE

prueba=# SELECT * from pg_group;
 groname | grosysid | grolist
---------+----------+---------
 ventas  |    16393 | {}
(1 row)


##PERMISOS
-para conceder los permisos la sentencia es:
GRANT permisos ON base-de-datos.tabla TO usuario;

recordemos que los permisos son:
*CONNECT, *CREATE, *TRUNCATE,  

SELECT    r    Can read data from the object.
*INSERT    a    Can insert data into the object.
*UPDATE    w    Can change data in the object.
*DELETE    d    Can delete data from the object.
RULE    R    Can create a rule on the table
*REFERENCES    x    Can create a foreign key to a table. Need this on both sides of the key.
*TRIGGER    t    Can create a trigger on the table.
*TEMPORARY    T    Can create a temporary table.
*EXECUTE    X    Can run the function.
*USAGE    U    Can use the procedural language.
*ALL        All appropriate privileges. For tables, this equates to arwdRxt

los permisos se le pueden otorgar a grupos y usuarios

para observar los privilegios debemos de estar en la base de datos
postgres=# \c prueba;
You are now connected to database "prueba".
prueba=# \dp
                                 Access privileges
 Schema |   Name    | Type  |     Access privileges     | Column access privileges
--------+-----------+-------+---------------------------+--------------------------
 public | empleados | table | postgres=arwdDxt/postgres+|
        |           |       | ariel=arwdDxt/postgres    |
(1 row)

-le daremos al usuario ariel los permisos siguientes en la tabla empleados.
prueba=# GRANT INSERT, UPDATE, DELETE ON TABLE empleados to ariel;
GRANT

-para eliminiar los permisos de ariel de insertar
prueba=# REVOKE INSERT ON empleados FROM ariel;
REVOKE
prueba=# \dp 
                                 Access privileges
 Schema |   Name    | Type  |     Access privileges     | Column access privileges
--------+-----------+-------+---------------------------+--------------------------
 public | empleados | table | postgres=arwdDxt/postgres+|
        |           |       | ariel=rwdDxt/postgres     |
(1 row)
 vemos que la a ha sido eliminada

podemos cambiar de dueno de una tabla
prueba=# ALTER TABLE empleados OWNER TO ariel;
ALTER TABLE

prueba=# \dp
                              Access privileges
 Schema |   Name    | Type  |  Access privileges  | Column access privileges
--------+-----------+-------+---------------------+--------------------------
 public | empleados | table | ariel=arwdDxt/ariel |
(1 row)



## BACKUP Y RESTAURAR
==BACKUP
pg_dump -c NOMBRE_DE_LA_BD > ARCHIVO.sql




## RESTAURAR
pg_restore -a -v -e -Fc -O –disable-triggers -d prueba prueba.data.dump
pg_restore -i -h localhost -p 5432 -U postgres -d mibase -v "/home/damian/backups/mibase.backup"

    -i le indica que ignore la versión (entre el comando y la base de datos).
    -h localhost es el host de nuestro PostgreSql.
    -p 5432 es la indicación del puerto donde corre el servicio.
    -U postgres especifica que se usará el usuario postgres para la operación.
    -d mibase es para que realize la restauración sobre una base de datos en particular, en este caso mibase.
    -v ejecutará el comando en modo verbose (así podremos ir viendo la salida de cada paso del proceso).
    /home/damian/backups/mibase.backup es el archivo que usaremos como backup y que queremos ingresar.

Este ha sido mi trabajo desde hace semanas y lo comparto con ustedes. Es posible mejorarlo, agregar mas ejemplos, casos mas complicados. Esto sera trabajo para un futuro.

Este documento es liberado por la licencia GPL, asi que compartanlo, modifiquenlo y compartan sus modificaciones para que siga creciendo.  Cualquier preguntas o comentario estoy por aca...

 
referencias:
http://www.postgresql.org/docs/9.1/interactive/index.html
http://www.davidpashley.com/articles/postgresql-user-administration.html#id2564083  permisos y usuarios
http://www.escomposlinux.org/lfs-es/blfs-es-SVN/server/postgresql.html

http://www.davidpashley.com/articles/cert-authority.html  certificados