Como lo prometido es deuda, continuamos con nuestra serie de Mini Cursos de PostgreSQL. Esta nueva entrega trata la administración y gestión de usuarios de Bases de Datos, lo que nos permitirá establecer un orden de todos los usuarios que se conectan a nuestra Base de Datos. Veamos cómo crear usuarios, la asignación de privilegios sobre objetos y configuración de las restricciones de acceso.
Mini Curso de PostgreSQL – Tercera Parte
Creación de Usuarios
Comenzaremos con la creación de usuarios en PostgreSQL. Los usuarios que se creen pueden ser designados para el uso de personal DBA como también para la conexión de aplicaciones (web, de escritorio, entre otras). No olvides echarle un vistazo a la segunda parte del mini curso de PostgreSQL antes de seguir con esta.
Para crear un usuario debemos de ejecutar en la consola de PostgreSQL el comando «CREATE USER» como se muestra a continuación, para este ejemplo crearemos 3 usuarios: appweb01 (usuario de una aplicación web) , dba01 (usuario de un personal DBA) y data01 (usuario de una aplicación de minería de datos)
postgres=# CREATE USER appweb01 WITH PASSWORD 'alguna clave' ;
postgres=# CREATE USER dba01 WITH PASSWORD 'alguna clave' ;
postgres=# CREATE USER data01 WITH PASSWORD 'alguna clave' ;
Donde alguna clave es el password que hemos designado para el usuario. Es importante destacar que dicha contraseña debe de ir entre comillas simples como se muestra en el ejemplo.
Ahora para verificar la creación de usuarios, utilizaremos el comando \du, que mostrará el listado de los usuarios creados en la base de datos. La salida del comando será similar a la siguiente:
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
appweb01 | | {}
data01 | | {}
dba01 | | {}
Para listar los privilegios otorgados sobre objetos, ejecutamos el comando \dp, el cual mostrará una salida como esta:
Schema | Name | Type | Access privileges | Column access privileges
----------+--------------+-------+---------------------------+--------------------------
public | nombre_tabla | table | data01=r/postgres |
| | | |
Donde data01=r significa que tiene permiso de lectura sobre la tabla. Mientras más permisos se adicionen al usuario sobre el objeto, este cambiará, por ejemplo: con permisos full de SELECT, INSERT, UPDATE y DELETE el usuario quedaría así: data01=arwd
Este es el significado de cada letra
- a: Permisio de INSERT o append
- r: Permisio de SELECT o read
- w: Permisio de UPDATE o write
- d: Permiso de DELETE o delete
Asignación de Privilegios de Superuser
Al asignar privilegios de Superuser, el usuario que los reciba tendrá la potestad de ejecutar comandos DDL (Data Definition Language), es decir podrá crear, modificar y eliminar objetos en la base de datos, por lo que se sugiere no asignar este nivel tan alto de permisos a una aplicación por ejemplo. Por lo general este privilegio lo posee el personal DBA.
Para asignar privilegios de Superuser a un usuario debemos de ejecutar el siguiente comando en la consola de PostgreSQL:
postgres=# ALTER USER dba01 SUPERUSER ;
Luego para verificar si el atributo fue otorgado correctamente, ejecutamos el comando \du
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
appweb01 | | {}
data01 | | {}
dba01 | Superuser | {}
Creación de un Usuario de Solo Lectura (Read Only User)
Es un caso muy común que se requiera un usuario solo lectura, por lo general es utilizado por aplicaciones de minería de datos, es decir que solo necesitan extraer data por medio de consultas.
postgres=# ALTER USER data01 SET default_transaction_read_only = on ;
Asignamos permisos de conexión al usuario:
GRANT CONNECT ON DATABASE nombre_BD TO data01 ;
Ahora asignamos permisos de acceso al esquema en donde están las tablas que el usuario necesita consultar:
GRANT USAGE ON SCHEMA nombre_esquema TO data01 ;
Podemos asignar permisos de SELECT sobre una tabla en especifico o sobre todas las tablas de un esquema.
Permiso de SELECT sobre una tabla:
GRANT SELECT ON nombre_tabla TO data01 ;
Permiso de SELECT sobre todas las tablas de un esquema:
GRANT SELECT ON ALL TABLES IN SCHEMA nombre_esquema TO data01;
Asignacion de Privilegios de INSERT, UPDATE y DELETE
Otro caso común es el de asignación de privilegios mixtos sobre diferentes objetos. Esta es una tarea que permitirá tener un máximo control de qué exactamente puede o no ejecutar un usuario.
- Asignación de permisos de SELECT en una tabla especifica:
GRANT SELECT ON nombre_tabla TO nombre_usuario ;
- Asignación de permisos de SELECT en todas las tablas de un esquema:
GRANT SELECT ON ALL TABLES IN SCHEMA nombre_esquema TO nombre_usuario ;
- Asignación de permisos de INSERT en una tabla especifica:
GRANT INSERT ON nombre_tabla TO nombre_usuario ;
- Asignación de permisos de INSERT en todas las tablas de un esquema:
GRANT INSERT ON ALL TABLES IN SCHEMA nombre_esquema TO nombre_usuario ;
- Asignación de permisos de DELETE en una tabla especifica:
GRANT DELETE ON nombre_tabla TO nombre_usuario ;
- Asignación de permisos de DELTE en todas las tablas de un esquema:
GRANT DELETE ON ALL TABLES IN SCHEMA nombre_esquema TO nombre_usuario ;
- Asignación de permisos de UPDATE en una tabla especifica:
GRANT UPDATE ON nombre_tabla TO nombre_usuario ;
- Asignación de permisos de UPDATE en todas las tablas de un esquema:
GRANT UPDATE ON ALL TABLES IN SCHEMA nombre_esquema TO nombre_usuario ;
- Asignación de permisos de UPDATE en un capo específico de una tabla:
GRANT UPDATE (nombre_campo) ON nombre_tabla TO nombre_usuario ;
- Asignación de permisos de SELECT y DELETE sobre una tabla:
GRANT SELECT, DELETE ON nombre_tabla TO nombre_usuario ;
- Asignación de permisos de SELECT y DELETE en todas las tablas de un esquema:
GRANT SELECT, DELETE ON ALL TABLES IN SCHEMA nombre_esquema TO nombre_usuario ;
Ahora conocemos más a fondo cómo manejar usuarios y privilegios en PostgreSQL. Con cada entrega de este Mini Curso nuestros conocimientos sobre esta poderosa Base de Datos aumentan, aunque por supuesto aún queda mucho por aprender, así que si quieres conocer más sobre PostgreSQL sugerimos que estés atento al resto de nuestras publicaciones (puedes seguirnos en Twitter o suscribirte a nuestro boletín desde el pie de la página). ¡En breve se viene la parte 4!