Creación y Gestión de Roles en Bases de Datos PostgreSQL
Resumen
¿Por qué es importante gestionar roles en PostgreSQL?
A medida que desarrollamos proyectos con bases de datos en PostgreSQL, la gestión adecuada de los roles se vuelve crítica. Conocer cómo crear, modificar y eliminar roles nos posibilita establecer niveles de seguridad adecuados, evitando potenciales pérdidas de datos por errores humanos. Además, al asignar permisos específicos, podemos garantizar que cada usuario tiene acceso solo a las tablas y funciones necesarias para su tarea, sin riesgos de comprometer la seguridad de la base de datos.
¿Cómo se crean roles y se asignan atributos?
Crear un rol en PostgreSQL es un proceso directo, similar a la creación de una tabla. Se utiliza la consulta CREATE ROLE seguida del nombre del rol y las características deseadas. Como administrador, tienes la opción de definir atributos como:
SUPERUSER: otorga todos los permisos sobre la base de datos.
CREATEDB: permite al usuario crear bases de datos.
CREATEROLE: posibilidad de crear otros roles.
Para crear un rol que pueda realizar consultas pero no eliminar tablas, sigue estos pasos:
Inicia sesión en PostgreSQL utilizando los valores predeterminados.
Crea el rol con la capacidad de realizar lecturas, inserciones y actualizaciones, pero sin permisos para eliminar tablas.
Asigna una contraseña utilizando el comando ALTER ROLE nombre_rol PASSWORD 'contraseña';.
¿Cómo se asignan permisos a roles en PostgreSQL?
Para garantizar que un rol tenga los permisos necesarios, como realizar selecciones, inserciones y actualizaciones, pero no eliminar tablas o bases de datos, sigue el procedimiento indicado:
Accede a la base de datos y localiza las tablas relevantes.
Utiliza la pestaña SQL para verificar y asignar permisos necesarios.
Utiliza la opción "Grant Wizard" en PGAdmin para asignar de forma masiva los permisos a todas las tablas necesarias.
Selecciona los permisos deseados, confirmando que el rol tiene acceso a las operaciones requeridas.
¿Cómo conectar un usuario con roles previamente creados?
Crear roles en PostgreSQL de forma correcta incluye asegurarse de que los usuarios puedan conectarse empleando estos roles. Una vez creado un usuario con permisos asignados, proporciona las credenciales para que se autentiquen utilizando ese rol específico. Aquí te explicamos cómo hacer esto:
Abre tu consola o PGAdmin y conecta usando los valores predeterminados.
Ingresa las credenciales del nuevo usuario creado en los pasos anteriores.
Verifica los permisos asignados realizando operaciones según los atributos otorgados al rol. Esta acción constata que el usuario tenga las capacidades esperadas, como el acceso a consultas y modificaciones.
Gestionar roles eficazmente incrementa la seguridad y funcionalidad de la base de datos, permitiendo trabajar de manera organizada y protegida. Optimizar el uso de roles en PostgreSQL mejora la administración y promueve el desarrollo seguro de cualquier aplicación. ¡Continúa estudiando y explorando las posibilidades de PostgreSQL para llevar tus proyectos al siguiente nivel!
sin duda alguna, beco ha hecho que deteste la interfaz y ame la consola... "la consola es tu amiga"
Totalmente de acuerdo :)
A mi paso con el cliente visual de MySQL, pero me ha gustado mucho el pgAdmin, me parece muy bueno y optimizado y te muestra toda la información de una.
ROLES
Que puede hacer un ROLE
Crear y Eliminar
Asignar atributos
Agrupar con otros roles
Roles predeterminados
--Ver las funciones del comando CREATEROLE(help)\h CREATEROLE;--Creamos un ROLE(consultas -> lectura, insertar, actualizar)CREATEROLE usuario_consulta;--Mostrar todos los usuarios junto a sus atributos
\dg
--Agregamos atributos al usuario o role
ALTERROLE usuario_consulta WITHLOGIN;ALTERROLE usuario_consulta WITHSUPERUSER;ALTERROLE usuario_consulta WITHPASSWORD'1234';--Elimanos el usuario o role
DROPROLE usuario_consulta;--La mejor forma de crear un usuario o role por pgadmin
CREATEROLE usuario_consulta WITHLOGINNOSUPERUSERNOCREATEDBNOCREATEROLEINHERITNOREPLICATIONCONNECTIONLIMIT-1PASSWORD'1234';--Para obtorgar privilegios a nuestro usuario_consulta
GRANTINSERT,SELECT,UPDATEONTABLEpublic.estacionTO usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.pasajeroTO usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.trayectoTO usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.trenTO usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.viajeTO usuario_consulta;
Muchas gracias por el aporte
Gracias buen summary!
Usuarios Linux, a nosotros no nos sale la opcion de configurar el inicio de psql con el usuario todo eso se tiene que hacer a través de este comando
psql -h <host>-d <base de datos>-U<usuario>-p <port>
Dos formas de ver los privilegios de un usuario en una tabla especifica desde consola.
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='pasajero';
===========
\z <table_name>
muchas gracias! me fue de mucha ayuda
Mi resumen de la clase
Desde sqlshell podemos crear roles, los roles son independiente de la base de datos, el comando para crear un rol es CREATE ROLE o CREATE USER, después de la versión 9.3 de postgres ambos comandos hacen lo mismo, en este ejemplo usaremos CREATE ROLE
Podemos ver todas las características que tiene la sentencia CREATE ROLE con \h CREATE ROL
Bien, creemos un rol que tenga la capacidad de hacer login y le asignaremos una contraseña.
Es muy bueno tener 2 roles diferentes, uno podria ser el usuario postgres que me permite crear bases de datos, borrar tablas, etc, y otro que me sirva únicamente para insertar, consultar y crear tablas pero no de eliminar o borrar bases de datos
CREATEROLE usuario_consulta;
Al crearlo así, este no tendrá contraseña y va a poseer todas las características predeterminadas de postgres
Si queremos ver todos los usuarios creados lo podemos hacer con el comando \dg
Ahora queremos que este rol que acabamos de crear tenga acceso a poder acceder a la base de datos, así que vamos a editarlo
ALTERROLE usuario_consulta WITHLOGIN;--Para que pueda logearse
.
Se pueden crear o eliminar otros roles, así como asignar atributos(hacer login, acceder a base de datos, permisos para escribir o eliminar tablas, etc).
.
También es posible agrupar diferentes roles.
.
Existe el rol predeterminado 'Postgres' que es con el que iniciamos al instalar este motor de BBDD. Lo ideal es generar nuestro propio superusuario, de lo contrario, Postgres sería el único superusuario y eso podría dañar nuestra información.
.
Lo ideal es crear un usuario con los niveles de seguridad indicados para nuestra BBDD y tablas y continuar trabajando con el nuevo usuario.
.
Los roles son independientes de las BBDD, por lo que se crean desde cualquier usuario con permisos, en este caso, utilizando el usuario Postgres.
.
CREATE ROLE == CREATE USER
.
Se pueden generar usuarios desde la consola, para ver los comandos disponibles podemos ver en la ayuda con \h.
si quieren usar el modo oscuro para que no se dañen la vista van a File > Preferences > Miscellaneous > Themes
Justo hice eso en esta clase. Casi me quedaba ciega con el cambio de consola a pgAdmin 😂
Gracias
La gestion de usuraios es una labor muy importante para la seguridad de la informacion. Es recomendable tener un inventario detallado de los roles y usuarios creados, asi contar con una superficie de vulnerabilidades por accesos otorgados.
CREATEROLE usuario_consulta;CREATEUSER usuario_consulta;ALTERROLE usuario_consulta WITHLOGIN;ALTERROLE usuario_consulta WITHSUPERUSER;ALTERROLE usuario_consulta WITHPASSWORD'Esta es una contraseña';DROPROLE usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.estacionTO usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.pasajeroTO usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.trayectoTO usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.trenTO usuario_consulta;GRANTINSERT,SELECT,UPDATEONTABLEpublic.viajeTO usuario_consulta;
Los usuarios y sus roles son una parte importante de la seguridad de la base de datos. Con ayuda de los usuarios adicionales y los grants otorgados, se puede determinar el nivel de acceso a ciertos objetos en la base de datos; además, se puede crear procesos de monitoreo (triggers) que permiten crear una bitácora de seguimiento de operaciones de los usuarios. Creando así una relación de: Quien se conecta -desde donde - que consulta.
Es un golpe duro pasar de Mysql a PostgreSQL,se siente la diferencia mucho jajaj
se me ha hecho mas facil PostgreSQL
Para, los que llegaron a containerizar el PostgreSQL.
Deben usar el comando:
docker exec -it <container_name> bash
Luego, para entrar a la base de datos,con el siguiente comando:
psql -U<role>-d <database_name>-W
Gracias viejo, toma una naranja 🍊
Creación de Roles
Los roles sirven para dar priviligios a un usuario en una base de datos. Por defecto el rol que tienen las tablas asignadas es el rol de "posgres", el cual tiene acceso total a todas las acciones. Es peligroso seguir usando este rol porque se podría borrar toda la base de datos.
Crear rol o usuario (es lo mismo): CREATE ROLE nombre_rol / CREATE USER nombre_usuario
Listado de usuarios: \dg
Le damos acceso a conectarse: ALTER ROLE usuario_consulta WITH LOGIN;
Le damos el privilegio de super usuario: ALTER ROLE usuario_consulta WITH SUPERUSER;
Le asignamos una contraseña ALTER ROLE usuario_consulta WITH PASSWORD 'etc123'No usar una contraseña como la de prueba en un entorno real, es solo una prueba
Eliminar rol: DROP ROLE nombre_rol
CREATE ROLE usuario_consulta WITH LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT-1 PASSWORD 'xxxxxx';--contraseña ocultaCOMMENTON ROLE usuario_consulta IS'Este usuario solo podrá consultar datos';
¿Podría usuario_consulta siendo en ese momento SUPERUSER eliminar a postgres?
Yo creo que si podria, ya que un super usuario puede eliminar y crear usuarios
Para que sirve
with grant options
en el grant wizards - sección de privilegios?
Quiere decir que ese rol o ese usuario puede crear otros usuarios y darles permiso de acceso a elementos, con Grant, si no tiene esa opcion, no puede copiar sus permisos a otro usuario.
Super profesional y sencillo
¿Para que quisieras un rol que no pueda hacer login?
De pronto se me ocurre se me ocurre un usuario bloqueado "BLOCKED"("baneado"). No estoy muy familiarizado con el diseño de bases de datos, pero podría ser una posibilidad.
tengo una duda ¿En qué momento en pgAdmin nos logeamos como usuario_consulta, hay una opción para habilitar uno u otro rol?
Al momento de ingresar a pgadmin pide el usuario y contraseña.
Lo pudiste solucionar? a mi me pasa lo mismo automaticamente me dice que ingrese la clave para el usuario postgress
NO ME SALE LA DIAGONAL ESA DEL PROFE ME SALE /DG ! NESECITO SOLUCION
Puedes usar alt + 92
Si tienes teclado latinoamericano (con la ñ al apar de la L) usa ALT GR al apar del teclado + Signo de pregunta, quedaria asi:
Alt Gr + ?
Roles en PostgreSQL
En PostgreSQL, los roles son entidades de seguridad que permiten agrupar usuarios y controlar los permisos de acceso a los objetos de la base de datos.
Cada rol puede tener permisos de inclusión, exclusión, modificación y consulta en objetos específicos de la base de datos, como tablas, vistas, índices y procedimientos almacenados.
Hay dos tipos de roles en PostgreSQL: roles de usuario y roles de grupo
Un rol de usuario es una entidad que se puede usar para iniciar sesión en la base de datos y acceder a los objetos de la base de datos con permisos específicos.
Un rol de grupo es una entidad que agrupa otros roles, incluidos roles de usuario y otros roles de grupo. Los roles de grupo facilitan la administración de permisos, ya que permite asignar permisos a un grupo de usuarios en lugar de a cada usuario individualmente.
Para crear un nuevo rol en PostgreSQL, puede usar el siguiente comando:
Donde nombre_rol es el nombre del nuevo rol y las opciones incluyen opciones adicionales como la contraseña, la fecha de expiración, etc.
Para ver el listados de los roles podemos hacer uso del comando \dg
Para agregar opciones al role ejecutamos el siguiente comando:
ALTERROLE usuario_consulta WITH[OPTIONS]
Para eliminar un rol ejecutamos el siguiente comando:
DROPROLE nombre_rol
Para asignar permisos a un rol, puede usar el siguiente comando:
GRANT permisos ON objeto_de_base_de_datos TO nombre_rol;
Donde permisos es una lista de permisos (por ejemplo, SELECT, INSERT, UPDATE, DELETE), objeto_de_base_de_datos es el nombre del objeto de base de datos al que se están asignando permisos (por ejemplo, una tabla o una vista) y nombre_rol es el nombre del rol al que se están asignando permisos.
En Linux asi se cambia de usuario en la terminal
psql -d nombre_de_baseDeDatos -U tu_usuario_aqui
Y asi logras ver que usuario esta conectado a la base de datos:
