Esquemas de Postgresql

5.9.1. Crear un esquema

5.9.2. El esquema público

5.9.3. La ruta de búsqueda de esquemas

5.9.4. Esquemas y privilegios

5.9.5. El esquema del catálogo del sistema

5.9.6. Patrones de uso

5.9.7. Portabilidad

Un clúster de base de datos de PostgreSQL contiene una o más bases de datos con nombre. Los roles y algunos otros tipos de objetos se comparten en todo el clúster. Una conexión de cliente al servidor solo puede acceder a los datos de una única base de datos, la especificada en la solicitud de conexión.

Nota

Los usuarios de un clúster no tienen necesariamente el privilegio de acceder a todas las bases de datos del clúster. Compartir los nombres de los roles significa que no puede haber diferentes roles nombrados, digamos, joe en dos bases de datos en el mismo clúster; pero el sistema se puede configurar para permitir joe acceso solo a algunas de las bases de datos.

Una base de datos contiene uno o más nombres esquemas, que a su vez contienen tablas. Los esquemas también contienen otros tipos de objetos con nombre, incluidos tipos de datos, funciones y operadores. El mismo nombre de objeto se puede utilizar en diferentes esquemas sin conflicto; por ejemplo, ambos schema1 y myschema puede contener tablas con el nombre mytable. A diferencia de las bases de datos, los esquemas no están rígidamente separados: un usuario puede acceder a objetos en cualquiera de los esquemas de la base de datos a la que está conectado, si tiene privilegios para hacerlo.

Hay varias razones por las que uno podría querer usar esquemas:

• Permitir que muchos usuarios utilicen una base de datos sin interferir entre sí.

• Organizar los objetos de la base de datos en grupos lógicos para hacerlos más manejables.

• Las aplicaciones de terceros se pueden colocar en esquemas separados para que no choquen con los nombres de otros objetos.

Los esquemas son análogos a los directorios en el nivel del sistema operativo, excepto que los esquemas no se pueden anidar.

5.9.1. Crear un esquema

Para crear un esquema, use el comando CREATE SCHEMA. Asigne al esquema un nombre de su elección. Por ejemplo:

CREATESCHEMA myschema;

Para crear o acceder a objetos en un esquema, escriba un nombre calificado que consta del nombre del esquema y el nombre de la tabla separados por un punto:

schema.table

Esto funciona en cualquier lugar donde se espere un nombre de tabla, incluidos los comandos de modificación de la tabla y los comandos de acceso a datos que se describen en los siguientes capítulos. (Por brevedad, hablaremos solo de tablas, pero las mismas ideas se aplican a otros tipos de objetos con nombre, como tipos y funciones).

En realidad, la sintaxis aún más general

database.schema.table

también se puede utilizar, pero en la actualidad esto es solo para pro forma cumplimiento del estándar SQL. Si escribe un nombre de base de datos, debe ser el mismo que el de la base de datos a la que está conectado.

Entonces, para crear una tabla en el nuevo esquema, use:

CREATETABLE myschema.mytable (...);

Para eliminar un esquema si está vacío (todos los objetos que contiene se han eliminado), use:

DROPSCHEMA myschema;

Para eliminar un esquema que incluya todos los objetos contenidos, use:

DROPSCHEMA myschema CASCADE;

Consulte la Sección 5.14 para obtener una descripción del mecanismo general detrás de esto.

A menudo, querrá crear un esquema propiedad de otra persona (ya que esta es una de las formas de restringir las actividades de sus usuarios a espacios de nombres bien definidos). La sintaxis para eso es:

CREATESCHEMA schema_name AUTHORIZATION user_name;

Incluso puede omitir el nombre del esquema, en cuyo caso el nombre del esquema será el mismo que el nombre del usuario. Consulte la Sección 5.9.6 para saber cómo puede resultar útil.

Nombres de esquema que comienzan con pg_ están reservados para propósitos del sistema y no pueden ser creados por los usuarios.

5.9.2. El esquema público

En las secciones anteriores creamos tablas sin especificar ningún nombre de esquema. Por defecto, tales tablas (y otros objetos) se colocan automáticamente en un esquema llamado “público“. Cada nueva base de datos contiene un esquema de este tipo. Por lo tanto, los siguientes son equivalentes:

CREATETABLE products (...);

y:

CREATETABLEpublic.products (...);

5.9.3. La ruta de búsqueda de esquemas

Los nombres calificados son tediosos de escribir y, de todos modos, a menudo es mejor no conectar un nombre de esquema en particular a las aplicaciones. Por lo tanto, las tablas a menudo se denominan nombres no calificados, que consta solo del nombre de la tabla. El sistema determina qué tabla se entiende siguiendo un ruta de búsqueda, que es una lista de esquemas para buscar. Se considera que la primera tabla coincidente en la ruta de búsqueda es la deseada. Si no hay ninguna coincidencia en la ruta de búsqueda, se informa de un error, incluso si existen nombres de tablas coincidentes en otros esquemas de la base de datos.

La capacidad de crear objetos con nombres similares en diferentes esquemas complica la escritura de una consulta que hace referencia precisamente a los mismos objetos cada vez. También abre la posibilidad de que los usuarios cambien el comportamiento de las consultas de otros usuarios, de forma maliciosa o accidental. Debido a la prevalencia de nombres no calificados en las consultas y su uso en los componentes internos de PostgreSQL, se agrega un esquema a search_path confía efectivamente en que todos los usuarios CREATE privilegio en ese esquema. Cuando ejecuta una consulta normal, un usuario malintencionado capaz de crear objetos en un esquema de su ruta de búsqueda puede tomar el control y ejecutar funciones SQL arbitrarias como si usted las hubiera ejecutado.

El primer esquema mencionado en la ruta de búsqueda se denomina esquema actual. Además de ser el primer esquema buscado, también es el esquema en el que se crearán nuevas tablas si el CREATE TABLE El comando no especifica un nombre de esquema.

Para mostrar la ruta de búsqueda actual, use el siguiente comando:

SHOW search_path;

En la configuración predeterminada, esto devuelve:

 search_path
--------------
 "$user", public

El primer elemento especifica que se debe buscar un esquema con el mismo nombre que el usuario actual. Si no existe tal esquema, la entrada se ignora. El segundo elemento se refiere al esquema público que ya hemos visto.

El primer esquema en la ruta de búsqueda que existe es la ubicación predeterminada para crear nuevos objetos. Esa es la razón por la que los objetos se crean de forma predeterminada en el esquema público. Cuando se hace referencia a objetos en cualquier otro contexto sin calificación de esquema (modificación de tabla, modificación de datos o comandos de consulta), se recorre la ruta de búsqueda hasta que se encuentra un objeto coincidente. Por lo tanto, en la configuración predeterminada, cualquier acceso no calificado nuevamente solo puede hacer referencia al esquema público.

Para poner nuestro nuevo esquema en la ruta, usamos:

SET search_path TO myschema,public;

(Omitimos el $user aquí porque no tenemos una necesidad inmediata) .Y luego podemos acceder a la tabla sin calificación de esquema:

DROPTABLE mytable;

Además, desde myschema es el primer elemento de la ruta, por defecto se crearían nuevos objetos en él.

También podríamos haber escrito:

SET search_path TO myschema;

Entonces ya no tendremos acceso al esquema público sin una calificación explícita. No hay nada especial en el esquema público, excepto que existe de forma predeterminada. También se puede dejar caer.

Consulte también la Sección 9.26 para conocer otras formas de manipular la ruta de búsqueda del esquema.

La ruta de búsqueda funciona de la misma manera para los nombres de tipos de datos, los nombres de las funciones y los nombres de los operadores que para los nombres de las tablas. Los nombres de tipos de datos y funciones se pueden calificar exactamente de la misma manera que los nombres de tablas. Si necesita escribir un nombre de operador calificado en una expresión, hay una disposición especial: debe escribir

OPERATOR(schema.operator)

Esto es necesario para evitar la ambigüedad sintáctica. Un ejemplo es:

SELECT3 OPERATOR(pg_catalog.+)4;

En la práctica, uno suele confiar en la ruta de búsqueda de los operadores, para no tener que escribir algo tan feo como eso.

5.9.4. Esquemas y privilegios

De forma predeterminada, los usuarios no pueden acceder a ningún objeto en esquemas que no sean de su propiedad. Para permitir eso, el propietario del esquema debe otorgar la USAGE privilegio sobre el esquema. Para permitir que los usuarios utilicen los objetos del esquema, es posible que deban otorgarse privilegios adicionales, según corresponda para el objeto.

A un usuario también se le puede permitir crear objetos en el esquema de otra persona. Para permitir eso, el CREATE es necesario conceder privilegios sobre el esquema. Tenga en cuenta que, de forma predeterminada, todos tienen CREATE y USAGE privilegios en el esquema public. Esto permite a todos los usuarios que pueden conectarse a una base de datos determinada crear objetos en su public esquema. Algunos patrones de uso exigen revocar ese privilegio:

REVOKECREATEONSCHEMApublicFROMPUBLIC;

(El primero “público“ es el esquema, el segundo “público“ medio “cada usuario“. En el primer sentido es un identificador, en el segundo sentido es un key palabra, de ahí las diferentes mayúsculas; recuerde las pautas de la Sección 4.1.1.)

5.9.5. El esquema del catálogo del sistema

Además de public y esquemas creados por el usuario, cada base de datos contiene un pg_catalog esquema, que contiene las tablas del sistema y todos los tipos de datos, funciones y operadores integrados. pg_catalog siempre es efectivamente parte de la ruta de búsqueda. Si no se nombra explícitamente en la ruta, se busca implícitamente antes de buscando los esquemas del camino. Esto asegura que siempre se podrán encontrar los nombres integrados. Sin embargo, puede colocar explícitamente pg_catalog al final de la ruta de búsqueda si prefiere que los nombres definidos por el usuario anulen los nombres integrados.

Dado que los nombres de las tablas del sistema comienzan con pg_, es mejor evitar dichos nombres para asegurarse de que no sufrirá un conflicto si alguna versión futura define una tabla del sistema con el mismo nombre que su tabla. (Con la ruta de búsqueda predeterminada, una referencia no calificada al nombre de su tabla se resolvería como la tabla del sistema). Las tablas del sistema continuarán siguiendo la convención de tener nombres que comiencen con pg_, para que no entren en conflicto con nombres de tablas de usuarios no calificados siempre que los usuarios eviten la pg_ prefix.

5.9.6. Patrones de uso

Los esquemas se pueden utilizar para organizar sus datos de muchas formas. A patrón de uso de esquema seguro evita que los usuarios que no son de confianza cambien el comportamiento de las consultas de otros usuarios. Cuando una base de datos no utiliza un patrón de uso de esquema seguro, los usuarios que deseen consultar esa base de datos de forma segura tomarían medidas de protección al comienzo de cada sesión. Específicamente, comenzarían cada sesión estableciendo search_path al vacio string o eliminar esquemas de escritura que no sean de superusuario de search_path. Hay algunos patrones de uso fácilmente compatibles con la configuración predeterminada:

• Restrinja a los usuarios normales a esquemas privados de usuarios. Para implementar esto, emita REVOKE CREATE ON SCHEMA public FROM PUBLICy cree un esquema para cada usuario con el mismo nombre que ese usuario. Recuerde que la ruta de búsqueda predeterminada comienza con $user, que se resuelve en el nombre de usuario. Por lo tanto, si cada usuario tiene un esquema independiente, accede a sus propios esquemas de forma predeterminada. Después de adoptar este patrón en una base de datos donde los usuarios no confiables ya habían iniciado sesión, considere auditar el esquema público para los objetos nombrados como objetos en el esquema. pg_catalog. Este patrón es un patrón de uso de esquema seguro a menos que un usuario que no sea de confianza sea el propietario de la base de datos o tenga el CREATEROLE privilegio, en cuyo caso no existe un patrón de uso de esquema seguro.

• Elimine el esquema público de la ruta de búsqueda predeterminada modificando postgresql.conf o emitiendo ALTER ROLE ALL SET search_path = "$user". Todos conservan la capacidad de crear objetos en el esquema público, pero solo los nombres calificados elegirán esos objetos. Si bien las referencias de tabla calificadas están bien, las llamadas a funciones en el esquema público serán inseguras o poco confiables. Si crea funciones o extensiones en el esquema público, utilice el primer patrón en su lugar. De lo contrario, como en el primer patrón, esto es seguro a menos que un usuario que no sea de confianza sea el propietario de la base de datos o tenga la CREATEROLE privilegio.

• Mantenga el valor predeterminado. Todos los usuarios acceden implícitamente al esquema público. Esto simula la situación en la que los esquemas no están disponibles en absoluto, lo que proporciona una transición fluida desde el mundo sin esquemas. Sin embargo, este nunca es un patrón seguro. Es aceptable solo cuando la base de datos tiene un solo usuario o unos pocos usuarios que confían mutuamente.

Para cualquier patrón, para instalar aplicaciones compartidas (tablas para ser utilizadas por todos, funciones adicionales proporcionadas por terceros, etc.), colóquelas en esquemas separados. Recuerde otorgar los privilegios adecuados para permitir que los demás usuarios accedan a ellos. Luego, los usuarios pueden hacer referencia a estos objetos adicionales calificando los nombres con un nombre de esquema, o pueden colocar los esquemas adicionales en su ruta de búsqueda, según lo deseen.

5.9.7. Portabilidad

En el estándar SQL, la noción de objetos en el mismo esquema que son propiedad de diferentes usuarios no existe. Además, algunas implementaciones no le permiten crear esquemas que tengan un nombre diferente al de su propietario. De hecho, los conceptos de esquema y usuario son casi equivalentes en un sistema de base de datos que implementa solo el soporte de esquema básico especificado en el estándar. Por lo tanto, muchos usuarios consideran que los nombres calificados realmente consisten en user_name.table_name. Así es como PostgreSQL se comportará efectivamente si crea un esquema por usuario para cada usuario.

Además, no existe el concepto de public esquema en el estándar SQL. Para una máxima conformidad con el estándar, no debe utilizar el public esquema.

Por supuesto, es posible que algunos sistemas de bases de datos SQL no implementen esquemas en absoluto, o brinden soporte de espacio de nombres al permitir (posiblemente limitado) acceso cruzado de bases de datos. Si necesita trabajar con esos sistemas, entonces se lograría la máxima portabilidad al no usar esquemas en absoluto.