-=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
No hay comentarios:
Publicar un comentario