Grimpi IT Blog

enero 8, 2011

Desglosando la metada en PostgreSQL 8.2 o superior

Filed under: Metada, PostgreSQL — grimpi @ 2:59 pm

Después de haber probado como extraer la información del schema de todas las bases de datos del mercado, debo decir que por lejos, SQL Server tiene el método mas sencillo y potente de acceso a su metada y PostgreSQL el método mas entrincado y críptico. No me refiero a traer un simple listado de tablas, sino a cosas más complejas, como por ejemplo traer el detalle de las constraints o de los índices.

Traer todas las tablas de una base de datos:

SELECT c.* FROM pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’ and pg_catalog.pg_table_is_visible(c.oid)
and n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’)

Con esto le decimos que nos traiga todos los objetos de la base de tipo tabla que no sean invisibles y que no pertenezcan a los schemas pg_catalog y pg_toast, que son schemas internos de PostgreSQL.
El campo relkind determina qué tipo de objeto queremos buscar. Esta es la tabla de equivalencias:

r Table
S Sequence
i Index
v View
c Composite Type


Traer todas las primary key con sus columnas por tabla de una base de datos:

SELECT cc.relname as tabla, cc.oid as cid, a.attnum, c.conname as Name, a.attname, c.oid as ID FROM pg_constraint c, pg_class cc, pg_attribute a
WHERE contype = ‘p’
and cc.oid = c.conrelid
and a.attrelid = cc.oid
and a.attnum = ANY(c.conkey)
order by cc.oid, c.oid

La table pg_constraint nos trae todas las constraints y el campo contype el tipo de constraint. Esta es la tabla de equivalencias:

p Primary Key
u Unique Key
f Foreign Key
c Check Constraint
t Trigger Constraint
X Exclusive Constraint


Traer el listado de permisos de un objeto:

Dentro de la tabla pg_class, existe un campo vectorial llamado relacl, que contiene una lista de todos los permisos del objeto.

SELECT relacl FROM pg_class

Este campo devuelve un valor similar a este: “{=awdxt/postgres,postgres=awdxt/postgres,crm=arw*d*x*t*/postgres}”. Que significa este resultado?
En esa cadena de string aparentemente inentendible, tenemos toda la información sobre los permisos del objeto.
Recordemos que este campo es un vector, por lo tanto, cada carácter “,” significa un nuevo ítem en el vector. Por lo tanto, este resultado de ejemplo, tiene 3 ítems. Cada posición del vector, corresponde a un usuario/grupo distinto con permisos en el objeto.
Cada ítem del vector, tiene 3 datos: Usuario, Permisos y Owner del objeto.
Ejemplo: “crm=arw*d*x*t*/postgres”
En este caso, el usuario es crm (es el valor que esta antes del “=”), los permisos son “arw*d*x*t*” y el owner es postgres.
Ahora, que significa “arw*d*x*t*” realmente? Simple: cada uno de estos caracteres, es una operación posible (INSERT, DELETE, etc) sobre el objeto.
Esta es la tabla de equivalencias:

“a” INSERT
“r” SELECT
“w” UPDATE
“d” DELETE
“x” REFERENCES
“R” RULE
“t” TRIGGER
“X” EXECUTE
“U” ALTER
“c” CONNECT
“C” CREATE
“*” HABILITA LA OPCION GRAN OPTION A CADA OPERACION

Por lo tanto, este objeto tiene permisos de INSERT, SELECT, UPDATE, DELETE, TRIGGER y REFERENCES, de las cuales para las operaciones UPDATE, DELETE, REFERENCES y TRIGGER, la opción WITH GRAN OPTION esta activada.

=awdxt/postgres GRANT INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO public;

(si no aparece un nombre usuario, significa que el permiso pertenece al rol “public”)

postgres=awdxt/postgres GRANT INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO postgres;

 

crm=arw*d*x*t*/postgres GRANT INSERT, SELECT ON TABLE “Nombre de la tabla” TO “crm”;

GRANT UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO “crm” WITH GRANT OPTION;

 



Traer todos los sequences de la base de datos

SELECT * FROM pg_class c where relkind = ‘S’

El problema de esta query, es que nos trae solo el nombre del sequence, pero no el detalle del mismo (valor máximo y mínimo, valor actual, etc.). Si solo queremos el nombre, perfecto, pero si necesitamos mas, la tabla pg_class no nos sirve.
Para traer entonces el detalle de la información de un sequence simplemente tenemos que hacer un SELECT sobre el mismo como si fuera una vista o una tabla.

SELECT * FROM “nombre_del_sequence”

sequence_name last_value start_value increment_by max_value min_value cache_value is_cycled
nombre_del_sequence 1 1 1 922337203685 1 1 f

Dejo en este link algunos scripts de ejemplo de para extraer la metadata de distintos objetos de PostgreSQL.

1 comentario »

  1. […] https://grimpidev.wordpress.com/2011/01/08/393/ January 11, 2011   //   PostgreSQL   //   No Comments   //   […]

    Pingback por Desglosando la metada en PostgreSQL 8.2 o superior « DbRunas – Noticias y Recursos sobre Bases de Datos — enero 11, 2011 @ 1:57 am


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: