En PostgreSQL, los roles son objetos globales que puede acceder a todas las bases de datos de cluster (contando con los privilegios adecuados). Los roles están completamente separados de los usuarios a nivel sistema operativo, aunque es conveniente mantener una correspondencia entre los mismos. A fin de inicializar un sistema de bases de datos, cada instalación fresca siempre contiene un rol predefinido. Este rol es siempre un superusuario, y tiene el mismo nombre (a menos que sea cambiado cuando se corre initdb) que el del usuario (a nivel sistema operativo) que inicializó el cluster de bases de datos. Como habrán notado en artículos anteriores, habitualmente se utiliza el nombre de usuario "postgres" para dicho rol. Los roles determinan el conjunto de privilegios disponibles a un cliente conectado.

Cada conexión a un servidor de base de datos se realiza utilizando el nombre de un rol en particular. Este rol determina los privilegios de acceso iniciales para los comandos ejecutados en esa conexión. El nombre del rol para usar en una conexión en particular es indicado por el cliente que la inicia. Por ejemplo, el cliente psql dispone de la opción de línea de comandos -U para indicar el rol a utilizar. Otras aplicaciones asumen el nombre del usuario a actual (a nivel sistema operativo) como rol a utilizar. Esta es la razón por la cual es conveniente mantener una correspondencia entre nombres de usuario a nivel sistema operativo y roles.

Existe un pseudo-rol (más bien palabra clave) PUBLIC que puede pensarse como un grupo que almacena a todos los roles. Cuando se otorga un permiso sobre el rol PUBLIC, se otorga a todos los roles. PostgreSQL otorga privilegios por defecto a PUBLIC sobre algunos tipos de objetos. No se otorgan privilegios a PUBLIC por defecto sobre tablas, columnas, schemas o tablespaces (espacios de almacenamiento para tablas en el sistema de archivos). Los privilegios por defecto otorgados a PUBLIC son: CONNECT y CREATE TEMP TABLE para bases de datos; EXECUTE para funciones; y USAGE para lenguajes.

Una forma conveniente de trabajo consiste en otorgar permisos a un rol de grupo, y hacer a los usuarios miembros de dicho grupo. De esta forma se simplifica la gestión de permisos, pues es más simple lidiar con la granularidad de altas (GRANT) y bajas (REVOKE) de privilegios. Un rol de grupo es un rol común y corriente sin posibilidad de login (si se le otorga login se convierte en un rol de usuario).

Un cluster PostgreSQL contener múltiples bases de datos, que a su vez pueden contener varios schemas (colección de tablas). Los schemas son individuales a cada base de datos, y no tienen relación con otros schemas de otras bases en el mismo cluster. Por defecto, cada base de datos inicia con un schema "public". Este schema no tiene nada en particular, sólo que existe por defecto. Cuando se otorga un permiso sobre un schema, sólo aplica a éste schema en particular sobre la base de datos actual (al momento de ejecutar el GRANT). Una tabla puede ser referenciada por su nombre calificado (schema.tabla) o simplemente utilizando el nombre de la tabla (nombre sin calificar). Cuando se utilizan nombres sin calificar, el sistema necesita determinar a qué tabla se refiere examinando los schemas en una lista (search path).

Habiendo asimilado todos estos conceptos, veamos como obtener e interpretar esta información desde línea de comandos, utilizando el cliente psql.

Conectarse a un servidor PostgreSQL con psql

Algunas opciones típicas para conectarse a un servidor de bases de datos Postgres con psql son el rol (usuario), host y puerto. Por ejemplo:

$ psql -U postgres -h localhost -p 5432

Conectarse a una base de datos

El cliente psql posee una amplia variedad de subcomandos (es posible listarlos utilizando el subcomando \?). Para conectarse a una base de datos se utiliza el subcomando \c, por ejemplo:

postgres=# \c sghe4_db7
You are now connected to database "sghe4_db7" as user "postgres".
sghe4_db7=#

¿Qué rol posee un usuario?

Es posible listar usuarios/roles utilizando el subcomando \du (o \dg):

sghe4_db7=# \du+
                                         List of roles
 Role name  |                   Attributes                   |       Member of       | Description 
------------+------------------------------------------------+-----------------------+-------------
 abot       |                                                | {role_ro}             | 
 lenny      |                                                | {role_devel}          | 
 homer      |                                                | {role_ro}             | 
 carl       |                                                | {role_devel}          | 
 postgres   | Superuser, Create role, Create DB, Replication | {}                    | 
 role_prod  | Cannot login                                   | {}                    | 
 role_devel | Cannot login                                   | {role_prod,role_bckp} | 
 role_ro    | Cannot login                                   | {}                    | 
 role_bckp  | Cannot login                                   | {}                    | 
 sghe_admin | Superuser, Create DB                           | {}                    | 
 willy      |                                                | {role_devel}          | 

Para diferenciar usuarios de roles, basta notar que los roles no tienen login ("Cannot login"). Por ejemplo "lenny" es un usuario y "role_ro" un rol.

Notar además que esta tabla indica a qué rol pertenece cada usuario. Por ejemplo "lenny" pertenece al rol "role_devel".

¿A qué schema pertenece una tabla?

La tabla "tables" de la base de datos "information_schema" posee información acerca de todas las tablas de todos los esquemas en un servidor de bases de datos. La siguiente consulta SQL permite saber a qué schema pertenece una tabla en particular (a modo de ejemplo, la tabla "kevsigqpbh_fh234512"):

sghe4_db7=# select table_catalog,table_schema,table_name from information_schema.tables where table_name='kevsigqpbh_fh234512';
 table_catalog | table_schema |     table_name      
---------------+--------------+---------------------
 sghe4_db7     | public       | kevsigqpbh_fh234512
(1 row)

Se observa que la tabla "kevsigqpbh_fh234512" pertenece al schema "public".

Listar la ACL de una base de datos

La ACL (lista de control de acceso) de una base de datos define los usuarios que pueden acceder a la misma, y con qué rol.

sghe4_db7=# select datname,datacl from pg_database where datname='sghe4_db7';
  datname   |                            datacl                             
------------+---------------------------------------------------------------
 sghe4_db7  | {=Tc/postgres,postgres=CTc/postgres,role_ro=Tc/postgres}
(1 row)

Para interpretar una lista de control de acceso es necesario leer detenidamente la página del manual del comando GRANT.

Esta ACL en particular se interpreta de la siguiente forma:

  • =Tc/postgres: por defecto (cuando el cliente no especifica un usuario al momento de conectarse) se accede como el rol "postgres" y sólo se posee el permiso de conexión a la base de datos, es decir, cualquiera se puede conectar a la base de datos.
  • postgres=CTc/postgres: el usuario "postgres" posee permisos de conexión y creación de tablas.
  • role_ro=Tc/postgres: el rol "rol_ro" sólo tiene permiso de conexión a la base de datos.

También es posible obtener la lista de control de acceso de cada base de datos recurriendo al subcomando \l:

sghe4_db7=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 sghe4_db5  | postgres | UTF8     | es_AR.UTF-8 | es_AR.UTF-8 | 
 sghe4_db6  | postgres | UTF8     | es_AR.UTF-8 | es_AR.UTF-8 | 
 sghe4_db7  | postgres | UTF8     | es_AR.UTF-8 | es_AR.UTF-8 | 
 postgres   | postgres | UTF8     | es_AR.UTF-8 | es_AR.UTF-8 | 
 template0  | postgres | UTF8     | es_AR.UTF-8 | es_AR.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | es_AR.UTF-8 | es_AR.UTF-8 | postgres=CTc/postgres+
            |          |          |             |             | =c/postgres
(6 rows)

Listar los privilegios sobre los schemas de una base de datos

El subcomando \dn del cliente psql se utiliza para listar rápidamente todos los schemas de una base de datos. Cuando se agrega el modificador + adicionalmente lista los privilegios (ACL) sobre cada uno:

sghe4_db7=# \dn+
                                     List of schemas
        Name        |   Owner    |      Access privileges       |      Description       
--------------------+------------+------------------------------+------------------------
 audit              | postgres   | postgres=UC/postgres        +| 
                    |            | sghe_admin=UC/postgres      +| 
                    |            | role_ro=U/postgres          +| 
                    |            | role_prod=U/postgres        +| 
                    |            | role_devel=UC/postgres       | 
 content            | postgres   | postgres=UC/postgres        +| 
                    |            | sghe_admin=UC/postgres      +| 
                    |            | role_ro=U/postgres          +| 
                    |            | role_prod=U/postgres        +| 
                    |            | role_devel=UC/postgres       | 
 cache              | postgres   | postgres=UC/postgres        +| 
                    |            | sghe_admin=UC/postgres      +| 
                    |            | role_ro=U/postgres          +| 
                    |            | role_prod=U/postgres        +| 
                    |            | role_devel=UC/postgres       | 
 public             | postgres   | postgres=UC/postgres        +| standard public schema
                    |            | sghe_admin=UC/postgres      +| 
                    |            | role_ro=U/postgres          +| 
                    |            | role_prod=U/postgres        +| 
                    |            | role_devel=UC/postgres       | 
 tmp_tables         | sghe_admin | sghe_admin=UC/sghe_admin    +| 
                    |            | role_devel=UC/sghe_admin    +| 
                    |            | role_bckp=U/sghe_admin       | 
(5 rows)

Listar permisos sobre cada tabla/vista

Ya hemos visto cómo listar los permisos sobre cada base de datos y cada schema, sólo resta conocer los permisos sobre cada tabla. Para ello, se puede recurrir al subcomando \dp

sghe4_db7=# \dp+ imp*
                                      Access privileges
 Schema |       Name        | Type  |      Access privileges       | Column access privileges 
--------+-------------------+-------+------------------------------+--------------------------
 public | imp_class         | table | role_prod=arwd/postgres     +| 
        |                   |       | sghe_admin=arwdDxt/postgres +| 
        |                   |       | role_devel=r/postgres       +| 
        |                   |       | role_devel=arwdDxt/postgres  | 
 public | imp_messages      | table | role_prod=arwd/postgres     +| 
        |                   |       | sghe_admin=arwdDxt/postgres +| 
        |                   |       | role_ro=r/postgres          +| 
        |                   |       | role_devel=arwdDxt/postgres  | 
 public | imp_mensaje_types | table | role_prod=arwd/postgres     +| 
        |                   |       | sghe_admin=arwdDxt/postgres +| 
        |                   |       | role_ro=r/postgres          +| 
        |                   |       | role_devel=arwdDxt/postgres  | 
(3 rows)

Listar permisos efectivos sobre tablas

La tabla "table_privileges" de la base de datos "information_schema" almacena toda la información de privilegios sobre todas las tablas.

Es posible listar todos los permisos sobre una tabla específica utilizando la siguiente consulta SQL. Por ejemplo, para la tabla "kevsigqpbh_fh234512":

sghe4_db7=# select * from information_schema.table_privileges where table_name='kevsigqpbh_fh234512';
 grantor  |  grantee   | table_catalog | table_schema |     table_name      | privilege_type | is_grantable | with_h
ierarchy 
----------+------------+---------------+--------------+---------------------+----------------+--------------+-------
---------
 postgres | role_prod  | sghe4_db7    | public       | kevsigqpbh_fh234512 | INSERT         | NO           | NO
 postgres | role_prod  | sghe4_db7    | public       | kevsigqpbh_fh234512 | SELECT         | NO           | NO
 postgres | role_prod  | sghe4_db7    | public       | kevsigqpbh_fh234512 | UPDATE         | NO           | NO
 postgres | role_prod  | sghe4_db7    | public       | kevsigqpbh_fh234512 | DELETE         | NO           | NO
 postgres | sghe_admin | sghe4_db7    | public       | kevsigqpbh_fh234512 | INSERT         | YES          | NO
 postgres | sghe_admin | sghe4_db7    | public       | kevsigqpbh_fh234512 | SELECT         | YES          | NO
 postgres | sghe_admin | sghe4_db7    | public       | kevsigqpbh_fh234512 | UPDATE         | YES          | NO
 postgres | sghe_admin | sghe4_db7    | public       | kevsigqpbh_fh234512 | DELETE         | YES          | NO
 postgres | sghe_admin | sghe4_db7    | public       | kevsigqpbh_fh234512 | TRUNCATE       | YES          | NO
 postgres | sghe_admin | sghe4_db7    | public       | kevsigqpbh_fh234512 | REFERENCES     | YES          | NO
 postgres | sghe_admin | sghe4_db7    | public       | kevsigqpbh_fh234512 | TRIGGER        | YES          | NO
 postgres | role_ro    | sghe4_db7    | public       | kevsigqpbh_fh234512 | SELECT         | NO           | NO
 postgres | role_devel | sghe4_db7    | public       | kevsigqpbh_fh234512 | INSERT         | NO           | NO
 postgres | role_devel | sghe4_db7    | public       | kevsigqpbh_fh234512 | SELECT         | NO           | NO
 postgres | role_devel | sghe4_db7    | public       | kevsigqpbh_fh234512 | UPDATE         | NO           | NO
 postgres | role_devel | sghe4_db7    | public       | kevsigqpbh_fh234512 | DELETE         | NO           | NO
 postgres | role_devel | sghe4_db7    | public       | kevsigqpbh_fh234512 | TRUNCATE       | NO           | NO
 postgres | role_devel | sghe4_db7    | public       | kevsigqpbh_fh234512 | REFERENCES     | NO           | NO
 postgres | role_devel | sghe4_db7    | public       | kevsigqpbh_fh234512 | TRIGGER        | NO           | NO
(19 rows)

Esta es una tabla muy completa que indica quién otorgó qué permiso a qué usuario sobre cada tabla, perteneciente a qué esquema de qué base de datos. También existe la vista "role_table_grants", la cual presenta la misma información exeptuando todos los permisos otorgados al usuario actual a través de grants a PUBLIC.

Referencias


Tal vez pueda interesarte


Compartí este artículo