Notas adicionales de la utilización de triggers en PostgreSQL

Complementando a este post Utilizando Triggers en PostgreSQL, estan un par de notas adicionales.

Con el comando \d (describe) puedes mostrar los triggers que esa tabla tiene asociados, la sintaxis del comando es la siguiente:

\d [table]

Si quieres eliminar el trigger asociado a una tabla hay que ejecutar el siguiente comando DROP TRIGGER ON, la sintaxis del comando es la siguiente:

DROP TRIGGER [name] ON [table]

Utilizando triggers en PostgreSQL

Adicionalmente a la utilización de reglas o constraints, existe otra forma de verificar y mantener la integridad en el DBMS para las aplicaciones, esto se logra mediante los triggers (disparadores). Un trigger es básicamente un store procedure o una función del lado del servidor que se dispara cuando una determinada acción INSERT,UPDATE o DELETE se ejecuta cada vez que una fila es modificada.

Aunque los triggers sirven en su mayoría para mantener la integridad de la base de datos también pueden usarse para:

• Auditoría: Los triggers pueden usarse para llenar tablas de auditoría, en donde se registren ciertos tipos de transacciones o accesos hacia tablas.
• Seguridad: Los triggers refuerzan las reglas de seguridad de una aplicación.
• Reglas de negocio: Cuando ciertas reglas de negocio son muy elaboradoras y necesitan ser expresadas a nivel base de datos, es necesario que además de reglas y constraints se utilicen triggers.
• Validación de datos: Los triggers pueden controlar ciertas acciones, por ejemplo: pueden rechazar o modificar ciertos valores que no cumplan determinadas reglas, para prevenir que datos inválidos sean insertados en la base.

A continuación un ejemplo de la utilización de triggers en postgreSQL.

Existe una tabla de facturas como la siguiente:

Creamos unas facturas con el siguiente script.

Bien esta tabla ya cuenta con algunos registros.

Ahora se necesita conocer aquellos registros que por error o intencionalmente cambiaron de fecha o de cantidad. Para cumplir con este requerimiento se crea una tabla en donde se guardará el historial de esos cambios.

Necesitamos una solución para que cada vez que se borre o se actualice un registro en la tabla invoices se registren los cambios en invoices_audit. Bien ya tenemos una razón para crear un trigger.

Antes de utilizar un trigger es indispensable crear una función trigger (trigger function) la cual es similar a un store procedure aunque un poco más restringida y con el acceso a unas variables predefinidas que contienen los valores de la fila que ejecuta el trigger, dependiendo de la operación estas variables son:

• NEW En la operación INSERT representa el registro que se va a crear, en la operación UPDATE representa el valor del registro después de la actualización.
• OLD En la operación UPDATE representa el valor antes de la actualización, en la operación DELETE representa el registro que se borrará.
• TG_NAME El nombre del trigger.
• TG_WHEN El instante en el cual se ejecutará el trigger, los valores son BEFORE o AFTER.
• TG_OP La acción que dispara el trigger: INSERT, UPDATE o DELETE.
• TG_RELNAME El nombre de la tabla que disparó el trigger.
• TG_RELID El OID de la tabla que disparó el trigger

Esta trigger function se crea sin parámetros y con un valor de retorno de tipo trigger, esta función se ejecutará cada vez que una fila es modificada, a continuación el código de la función en donde se muestra el uso de variables predefinidas.

Siendo lo más importante a continuación el código que crea el trigger , esto es lo que asocia la tabla con la ejecución de la función.

La sintaxis general para crear un trigger es:

CREATE TRIGGER [name] [BEFORE | AFTER] [action]
ON [table] FOR EACH ROW
EXECUTE PROCEDURE [function name(arguments)]
  

El código completo es el siguiente:

Cuando ejecutemos el código de la función trigger y de la creación del trigger desde un archivo, PostgreSQL nos mostrará los siguientes mensajes:

  CREATE FUNCTION
CREATE TRIGGER
  

Observamos que la función trigger y el trigger se crearon exitosamente, utilizando PgAdmin III.

Probamos el trigger actualizando un par de registros en la tabla invoices.

Comprobamos que las actualizaciones se realizaron.

Por último mostramos los registros de la tabla invoices_audit, para comprobar que el trigger guardo los valores que cambiaron de los registros actualizados.

Mostrando los privilegios ACL de objetos en PostgreSQL

Complementando los anteriores posts parte I y parte II de los comandos GRANT y REVOKE.

Como parte del estándar de seguridad SQL1 a cada usuario de un DBMS se le asigna un user-id que determina que comandos le están permitidos o prohibidos ejecutar, generalmente estos user-id son asignados por los administradores o superusuarios del DBMS, quienes son los que mantienen esta información en Access Control List (ACL's).

El estándar ANSI/ISO SQL utiliza el termino authorization-id en lugar de user-id (aquí concuerdo con que el termino authorization-id es mejor y más descriptivo).

Los privilegios ACL's de un objeto los puedes mostrar con los comandos: \dp (display permissions) o \z.

A continuación el significado de cada privilegio (explico algunos que no son tan evidentes):

r  = SELECT
a  = INSERT
w  = UPDATE
d  = DELETE
R  = RULES
x  = REFERENCES
t  = TRIGGER (tablas)
X  = EXECUTE (Funciones, procedimientos)
U  = USAGE (Poder enumerar los objetos dentro de un esquema y 
crear funciones con lenguajes de procedimiento)
C  = CREATE (Creación de esquemas, objetos, indices y tablas)
T  = TEMPORARY (Creación de tablas temporales en una base de datos)
*  = GRANT
ALL= arwdRxt

Estos privilegios los despliega el comando \z para tablas, vistas y secuencias.

Para mayor referencia consultar el libro: PostgreSQL, The comprehensive guide to building, programming and administering PostgreSQL databases, Second Edition.; capítulo 23 Security

Utilizando los comandos GRANT y REVOKE en PostgreSQL - Parte II

Continuando con el post acerca de los comandos GRANT y REVOKE , estos comandos adicionalmente cuentan con las opciones de:

• ALL PRIVILEGES Otorga todos los privilegios al usuario sobre un particular objeto.
• PUBLIC En vez de asignar los privilegios a cada usuario (uno por uno) , se utiliza esta palabra para asignar los privilegios a cada uno de los usuarios autentificados en la base de datos, incluso a aquellos que no han sido creados.

Además para el comando GRANT tenemos la opción de:

• WITH GRANT OPTION Cuando se crea un objeto en la base de datos, el creador de ese objeto es el único que puede otorgar privilegios al objeto para que otros usuarios lo utilicen, con esta opción el propietario del objeto permite que otros usuarios puedan asignar privilegios a un objeto de sus propiedad.

Mostraré unos ejemplos:

ALL PRIVILEGES

Primero el uso de GRANT con la opción ALL PRIVILEGES.

Mostramos los privilegios del usuario martin en la tabla authors con el comando \z authors

Ahora con el usuario postgres (propietario del objeto) le asignamos todos los privilegios al usuario martin. con el siguiente comando:

  GRANT ALL PRIVILEGES ON authors TO martin;
  

Vuelvo a mostrar todos los privilegios del usuario martin y compruebo que todos los permisos le han sido otorgados.

Para retirar todos los privilegios ejecuto como el usuario postgres el siguiente comando:

  REVOKE ALL PRIVILEGES ON authors FROM martin;
  

Muestro nuevamente los privilegios de la tabla y compruebo que el usuario martin no tiene ningún privilegio asignado.

PUBLIC

Ahora con el usuario postgres ejecuto el siguiente comando

  GRANT SELECT ON authors TO PUBLIC;
  

El cual otorga el privilegio SELECT a todos los usuarios incluso a aquellos que no se han creado dentro de la DBMS.

  GRANT SELECT ON authors TO PUBLIC;
  

Para comprobar este privilegio ejecuto una consulta SELECT con un usuario llamado docencia, quien no necesito que se le otorgaran privilegios de manera especifica.

WITH GRANT OPTION

Únicamente el creador del objeto puede usar el comando GRANT para otorgar privilegios a otros usuarios, sin embargo con la opción WITH GRANT OPTION del comando GRANT puede delegarle la capacidad de otorgarle privilegios sobre su objeto a otros usuarios.

Para este ejemplo creo una tabla llamada books con el usuario postgres

  create table Books
(
        bookid serial not null primary key,
        isbn varchar(13) not null,
        title varchar(512) not null,
        numpages integer null,
        year smallint not null
);
  

Ahora bien, el usuario postgres requiere que el usuario martin tenga los privilegios de SELECT e INSERT, pero que también martin tenga la capacidad de otorgarlos a otros usuarios, ejecutamos los siguientes comandos:

  GRANT INSERT, SELECT ON Books TO martin WITH GRANT OPTION;

  GRANT UPDATE ON books_bookid_seq TO martin WITH GRANT OPTION;
  

Ahora martin tiene la capacidad de poder otorgarle los privilegios a docencia en la tabla y en la secuencia.

   GRANT INSERT,SELECT ON Books TO docencia;

   GRANT UPDATE ON books_bookid_seq TO docencia;
   

Para que este usuario pueda ahora ejecutar la consulta y la creación de nuevos registros en la tabla.

Utilizando los comandos GRANT y REVOKE en PostgreSQL

Cuando se trata de la seguridad de los datos almacenados es muy importante tener en cuenta las siguientes consideraciones sobretodo en ambientes productivos:

• Los datos de cualquier tabla deben ser accesibles únicamente a los usuarios del DBMS que realmente necesiten esa información, el resto no debe tener acceso.
• A cada uno de los usuarios del DBMS que tienen acceso a una determinada tabla, se les debe de asignar una cierta acción según sus necesidades, por ejemplo a ciertos usuarios se les permitirá ejecutar un UPDATE, mientras que al resto (o a todos) se les permitirá únicamente ejecutar un SELECT.
• En ciertos casos incluso para la ejecución de un SELECT solo se permitirá que un usuario del DBMS pueda consultar una tabla a nivel de ciertas columnas.

Cabe recordar que en producción los usuarios del DBMS son asociados más con aplicaciones o grupos de programas que con usuarios operativos. Existen tres conceptos en el esquema de seguridad SQL: Usuarios, Objetos y privilegios.

Privilegios

Los privilegios suelen clasificarse en privilegios del sistema y de objetos.

Los privilegios del sistema permiten al usuario realizar algún tipo de operación que afecta a todo el sistema.

Los privilegios de objetos se definen como las acciones que le son permitidas a un usuario ejecutar en un determinado objeto de la base de datos (tabla,vista,secuencia,función), esto una vez que el usuario haya sido autentificado dentro del DBMS.

Los privilegios de objetos dependen del tipo de objeto, por ejemplo el estándar SQL1 especifica 4 privilegios para tablas y vistas:

• SELECT - Permite consultar todas las filas.
• INSERT - Permite la creación de nuevos registros.
• DELETE - Permite la eliminación de filas.
• UPDATE - Permite la modificación de filas ya creadas.

Para el resto de los objetos en PostgreSQL pueden o no aplicar los siguientes privilegios:

• RULE - Permite la creación de reglas para una tabla o una vista.
• REFERENCES - Permite la creación de llaves foráneas (foreign key) al crear relaciones.
• TRIGGER - Permite la creación de triggers.
• EXECUTE - Permite la ejecución de funciones o store procedures.
• ALL - Permite todos los privilegios.

De manera predeterminada en PostgreSQL cuando se crea un objeto el creador del objeto es el propietario y se le asignan todos los privilegios sobre ese objeto, el resto de los usuarios no tiene ningún privilegio sobre ese objeto.

El DDL (Data Definition Language) incluye dos comandos para conceder y retirar privilegios: GRANT y REVOKE

Como ejemplo voy a crear la siguiente tabla en una base de datos llamada bibl, cuyo dueño de la base de datos es el usuario postgres.

  CREATE TABLE Authors(
 author_id        serial primary key,
 author_name     varchar(256),
 author_lastname     varchar(256),
 author_birthdate     date
 );
  

Paso siguiente voy a insertar unos registros:

insert into authors(author_name,author_lastname,author_birthdate)
values('Elizabeth','Bishop','02/08/1911');

insert into authors(author_name,author_lastname,author_birthdate)
values('Charles','Dickens','07/02/1812');

insert into authors(author_name,author_lastname,author_birthdate)
values('Jack','London','12/01/1876');

insert into authors(author_name,author_lastname,author_birthdate)
values('Joseph','Conrad','03/12/1857');
  

Hago un SELECT y muestro los registros de la tabla.

Como se ve con el usuario postgres pude ejecutar sin ningún tipo de restricción las siguientes acciones: CREATE, INSERT y SELECT

Ahora ingresaré con un usuario distinto al usuario postgres, ingresaré en la base de datos con el usuario martin y ejecutaré un SELECT sobre la tabla authors.

Al ejecutar el SELECT PostgreSQL nos muestra los siguientes mensajes:

ERROR:  permission denied for relation authors
STATEMENT:  SELECT * FROM authors;
ERROR:  permission denied for relation authors

GRANT

Estos mensajes me indican que el usuario martin no tiene los privilegios necesarios para ejecutar el SELECT en esa tabla y que por lo tanto no podrá leer los registros a menos que el usuario propietario postgres conceda el privilegio de hacerlo ejecutando el comando GRANT. La sintaxis básica del comando es:

GRANT [privilegios] ON [objeto] TO {public | group | username}

Así que con la sesión de postgres ejecuto el siguiente comando:

  GRANT SELECT ON authors TO martin;

Regresando a la sesión del usuario martin, vuelvo a ejecutar el comando SELECT.

  SELECT * FROM authors;

Ya es posible que el usuario martin pueda ejecutar la consulta.

Ahora intentaré crear un nuevo registro

insert into authors(author_name,author_lastname,author_birthdate)
values('Gustave','Flaubert','12/12/1821');

PostgreSQL me envía el siguiente mensaje debido a que este usuario no tiene el privilegio de INSERT:

  ERROR:  permission denied for relation authors
  

Concedo al usuario martin el privilegio de INSERT, con el siguiente comando ejecutado por el usuario postgres

GRANT INSERT ON authors TO martin;

Ejecuto nuevamente el INSERT y PostgreSQL me envía ahora el siguiente mensaje:

ERROR: permission denied for sequence authors_author_id_seq

Concedo entonces al usuario martin el privilegio de poder actualizar la secuencia con el siguiente comando (debe ser ejecutado por postgres):

GRANT UPDATE ON authors_author_id_seq TO martin;

Con los privilegios otorgados a la tabla y a la secuencia ahora ya es posible crear el registro.

Para mostrar los privilegios que se tienen sobre un determinado objeto, utilizamos el comando \z. La sintaxis es:

\z [nombre del objeto]

Para este ejemplo ejecutamos:

\z authors;

REVOKE

De la misma manera que se otorgaron los privilegios al usuario martin se le pueden retirar con el comando REVOKE la sintaxis básica del comando es:

REVOKE [privilegios] ON [objecto] FROM {public | group | username }

Así que con el usuario postgres ejecuto los siguientes comandos para retirarle los privilegios otorgados a martin en la tabla y en la secuencia:

REVOKE INSERT,SELECT ON authors FROM martin;
REVOKE UPDATE on authors_author_id_seq FROM martin;

Si mostramos los privilegios de la tabla y de la secuencia, observamos que ya no se muestran los privilegios para el usuario martin , esto por que los quito el usuario postgres, quien es el propietario de los objetos. Para mostrar los privilegios de ambos objetos (secuencia y tabla) únicamente ejecuto el comando \z sin argumentos.