sin duda alguna, beco ha hecho que deteste la interfaz y ame la consola… “la consola es tu amiga”
Configurar Postgres
Qué aprenderás sobre PostgreSQL
¿Qué es Postgresql?
Instalación y configuración de la Base de Datos
Interacción con Postgres desde la Consola
PgAdmin: Interacción con Postgres desde la Interfaz Gráfica
Archivos de Configuración
Comandos más utilizados en PostgreSQL
Presentación del Proyecto
Tipos de datos
Diseñando nuestra base de datos: estructura de las tablas
Jerarquía de Bases de Datos
Gestión de la información en bases de datos
Creación de Tablas
Particiones
Creación de Roles
Llaves foráneas
Inserción y consulta de datos
Inserción masiva de datos
Generar consultas avanzadas
Cruzar tablas: SQL JOIN
Funciones Especiales Principales
Funciones Especiales Avanzadas
Vistas
PL/SQL
Triggers
Integrar bases de datos con servicios externos
Simulando una conexión a Bases de Datos remotas
Transacciones
Otras Extensiones para Postgres
Implementar mejores prácticas
Backups y Restauración
Mantenimiento
Introducción a Réplicas
Implementación de Réplicas en Postgres
Otras buenas prácticas
Cierre del curso
No tienes acceso a esta clase
¡Continúa aprendiendo! Únete y comienza a potenciar tu carrera
Aportes 90
Preguntas 13
sin duda alguna, beco ha hecho que deteste la interfaz y ame la consola… “la consola es tu amiga”
ROLES
Que puede hacer un ROLE
-- Ver las funciones del comando CREATE ROLE (help)
\h CREATE ROLE;
-- Creamos un ROLE (consultas -> lectura, insertar, actualizar)
CREATE ROLE usuario_consulta;
-- Mostrar todos los usuarios junto a sus atributos
\dg
-- Agregamos atributos al usuario o role
ALTER ROLE usuario_consulta WITH LOGIN;
ALTER ROLE usuario_consulta WITH SUPERUSER;
ALTER ROLE usuario_consulta WITH PASSWORD'1234';
-- Elimanos el usuario o role
DROP ROLE usuario_consulta;
-- La mejor forma de crear un usuario o role por pgadmin
CREATE ROLE usuario_consulta WITH
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1
PASSWORD'1234';
--Para obtorgar privilegios a nuestro usuario_consulta
GRANT INSERT, SELECT, UPDATE ON TABLE public.estacion TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.pasajero TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.trayecto TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.tren TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.viaje TO usuario_consulta;
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>
En ubuntu y sus derivados para ingresar a un usuario por consola, se debe ingresar:
psql --host=localhost --dbname=postgres --username=usuario_consulta```
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
CREATE ROLE 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
ALTER ROLE usuario_consulta WITH LOGIN; --Para que pueda logearse
Y luego le asignamos una contraseña
ALTER ROLE usuario_consulta WITH PASSWORD 'nuestraContraseña';
Ahora ya podemos acceder a la consola con este rol
Con el comando DROP ROLE nombre_rol, podemos eliminar roles
.
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
.
\h CREATE ROLE
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
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.
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.
CREATE ROLE usuario_consulta;
CREATE USER usuario_consulta;
ALTER ROLE usuario_consulta WITH LOGIN;
ALTER ROLE usuario_consulta WITH SUPERUSER;
ALTER ROLE usuario_consulta WITH PASSWORD 'Esta es una contraseña';
DROP ROLE usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.estacion TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.pasajero TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.trayecto TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.tren TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.viaje TO usuario_consulta;
¿Podría usuario_consulta siendo en ese momento SUPERUSER eliminar a postgres?
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
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.
CREATE ROLE nombre_rol
/ CREATE USER nombre_usuario
\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
DROP ROLE nombre_rol
CREATE ROLE usuario_consulta WITH
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1
PASSWORD 'xxxxxx'; --contraseña oculta
COMMENT ON ROLE usuario_consulta IS 'Este usuario solo podrá consultar datos';
Modificando persmisos en tablas para un rol:
GRANT INSERT, SELECT, UPDATE ON TABLE public."Estaciones" TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public."Pasajeros" TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public."Trayectos" TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public."Trenes" TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public."Viajes" TO usuario_consulta;
Super profesional y sencillo
Vaya que he escuchado sobre los roles en la terminal pero no lo habia entendido para que quería hasta que este video me enseñó una gran lección. Tanto en la terminal como en la interfaz gráfica son muy amigables a la hora de crearlo.
Coincido que la consola es más cómoda para este paso.
Desde que empece a estudiar siempre le había tenido miedo a la consola y siempre prefería usar las interfaces gráficas pero este curso me ha hecho amar la consola, ya que veo que todo es mas sencillo y rápido desde ahí.
El video tiene un error.
En primer lugar, un rol con el atributo INHERIT tendrá automáticamente los privilegios de los roles de EL GRUPO del que es miembro, incluidos los privilegios heredados por ese rol.
No como menciona él que va a heredar los permisos del ROLE que lo está creando 🧐 que en este caso es postgres
Preguntas que me contesté yo mismo:
En la venta de grant wizard - sección de privilegios:
la 2 columna de “WITH GRANT options” permite al usuario brindarle ese permiso a otro usuario. si no se selecciona, será un permiso único de el.
Cuando ingreses con el nuevo rol e intentes insertar un registro probablemente te salga este error:
ERROR: permission denied for sequence…
Solución: Schemas > Public > Sequences > Grant Wizard (clic derecho) > asignar permiso de USAGE
\dg o \du para listar usuarios roles
listo, el curso es muy practico y fluido, me parece muy buena practica dejar, permisos restringidos dependiendo de las necesidades de los usuarios
--//////////////////
--13. Roles
--/////////////////
--Como usar el comando CREATE ROL
\h CREATE ROL ;
--Crear un role
CREATE ROLE usuario_consulta;
-- Consultar los usuarios dentro de la bases de datos;
/dg
--Modificar Role
ALTER ROLE usuario_consulta WITH LOGIN;
ALTER ROLE usuario_consulta WITH SUPERUSER;
ALTER ROLE usuario_consulta WITH PASSWORD '1234';
--Eliminar usuario
DROP ROLE usuario_consulta;
CREATE ROLE usuario_consulta WITH
LOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION
ENCRYPTED PASSWORD 'md57d24e489563fca3e9842fd8407d58dbf';
--obtorgar privilegios
GRANT INSERT, SELECT, UPDATE ON TABLE public.estacion TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.pasajero TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.trayecto TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.tren TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.viaje TO usuario_consulta;
Excelente explicación.
Dar los privilegios ‘update’,‘select’ y ‘insert’ a un user sobre todas las tablas con schema ‘public’ dentro de una base de datos
postgres=# grant update, select, insert on all tables in schema public to user_name;
Excelente clase. Es bueno saber que existe diferentes niveles de seguridad que podemos aplicar a la BD
Los roles permiten:
Usuarios Linux, copiar y pegar esto en su consola
psql --host=localhost --dbname=postgres --username=usuario_consulta
Luego pedirá el password y lograrán acceder
la verdad la consola no que me guste mucho. Prefiero la interfaz🙈🙈🙈
Información resumida de esta clase
#EstudiantesDePlatzi
Existen roles dentro de PostgreSQL, estos lores pueden crear, modificar y eliminar datos, así que es bueno crear un usuario con ciertos niveles de seguridad. Los roles son independientes de la base de datos
Una buena práctica es usar un creador de contraseñas aleatorio para que sea más difícil el robo de datos
El grupo de permisos y el usuario se diferencia por el icono
Depende de nuestro proyecto debemos asignarle ciertos permisos a los roles
Este rol con ciertos permisos son los que le compartimos al equipo y así tenemos una capa más de seguridad
Aquí dejo información importante tanto resumen como adicional, especialmente de los grupos de usuarios que son muy importantes (leer hasta el final)
Los roles son los usuarios que tenemos creados en la base de datos. Estos roles tienen distintos atributos y privilegios. Hasta ahora hemos usado el usuario postgres que es el de más altos privilegios, vamos a crear uno nuevo.
Para crear un nuevo usuario:
CREATE ROLE usuario_consulta LOGIN PASSWORD '123456'
Hay que tener en cuenta que si se hace CREATE USER se crea con LOGIN por defecto.
En linux para conectarnos con otro usuario debemos usar este comando:
psql -h <servidor> -d <nombre_db> -U <nombre_usuario> -p <puerto>
por ejemplo:
psql -h localhost -d transportes -U usuario_consulta -p 5432
Los roles o usuarios tienen distintos atributos ya vimos algunos como son login y password. Aquí están los demás: atributos
Privilegios:
Los usuarios a pesar de que ya tienen acceso a logearse y pueden acceder a bases de datos no pueden realizar ninguna acción sobre estas si es que no se les da los privilegios. Si nos ocnectamos como el usuario_consulta en la base de datos transportes e intentamos hacer alguna consulta dará error de permisos
PAra dar privilegios a un usuario por consola:
GRANT SELECT
ON ALL TABLES
IN SCHEMA "public"
TO usuario_consulta;
Este dará al usuario el privilegio de SELECT en todas las tablas.
Para revocar permisos por consola:
REVOKE SELECT
ON ALL TABLES
IN SCHEMA "public"
FROM usuario_consulta;
Grupos de roles:
Digamos que tengamos que crear 2 usuarios, uno con permisos de solo ver y otro que pueda ver y editar, en este caso tendríamos que crear los roles y asignarles los privilegios a cada uno. Podemos optimizar esto creando grupos de roles donde le indicaremos los los privilegios que van a tener y cuando creamos un usuario nuevo podemos hacer que herede estos mismos permisos.
CREATE ROLE lectura;
GRANT SELECT ON ALL TABLES
IN SCHEMA "public"
TO lectura;
CREATE ROLE usuario_consulta2 LOGIN PASSWORD 'visita';
GRANT lectura TO usuario_consulta2;
De esta manera ya el usuario tendra los privilegios de SELECT que tiene lectura.
excelente curso.
¡por favor cambie el color!
Para trabajar es aconsejable usar distintos roles (usuarios) con distintos niveles de privilegios.
Tenemos al rol por default postres, que generalmente tiene todos los privilegios.
Podemos crear nuevos roles que hereden (grantee) del usuario postgres (grantor) sus privilegios, pero limitando algunos. Como DELETE y DROP. Este nuevo rol se lo podemos dar a un equipo de trabajo que no queremos que elimine data de la DB.
De esta manera agregamos una capa de seguridad a nuestro trabajo
Me gusto mucho esta clase, no sabia que se podía dar permisos tan específicos a los usuarios contra las tablas
En caso de que deseen configurar los permisos desde sus consolas podrán usar el siguiente comando:
GRANT <Nombre del permiso> ON <Nombre de la tabla> TO <Nombre Usuario>
Ejemplo:
GRANT SELECT ON passenger TO tester
Si desean asignarles todos los permisos deberan escribir lo siguiente:
GRANT ALL ON <Nombre de la tabla> TO <Nombre Usuario>
Ejemplo:
GRANT ALL ON train TO tester
Listo usuario creado y listo para el trabajo.
Otra forma de dar acceso a todas las tablas de un esquema:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;
To get information about current connection from the psql command prompt:
\conninfo
This displays more information, though.
To change user:
\c - a_new_user
‘-’ substitutes for the current database.
To change database and user:
\c a_new_database a_new_user
The SQL command to get this information:
SELECT current_user;
En Ubuntu, para acceder a la base de datos “transporte” desde otro usuario que no sea el admin postgres:
psql -U usuario_consulta -W -h localhost transporte.
No se si les paso pero sucede el siguiente problema y es que cuando escribía la sentencia
postgres-# ALTER ROLE usuario_consulta WITH LOGIN;
ERROR: error de sintaxis en o cerca de «ALTER»
LÍNEA 2: ALTER ROLE usuario_consulta WITH LOGIN;
^
y el error estaba en que entre # y el ALTER debe haber un espacio
postgres=# ALTER ROLE usuario_consulta WITH LOGIN;
ALTER ROLE
postgres=#
Me gusta como fluye el curso. Las explicaciones son claras y la estructura del proyecto es entendible.
.
Quitar todos los privilegios a un user de todas las tablas con schema ‘public’ dentro de una base de datos:
postgres=# revoke all privileges on all tables in schema public from transport_manager;
Es muy intersante tener los diversos usuarios para la administración de la base de datos, asi se le agrega una capa de seguridad mayor al proyecto.
PostgreSQL gestiona los permisos de acceso a la base de datos utilizando el concepto de roles (roles) . Un rol puede considerarse como un usuario de la base de datos o un grupo de usuarios de la base de datos
Los roles tienen atributos y privilegios;
Los roles de la base de datos están conceptualmente completamente separados de los usuarios del sistema operativo. En la práctica, puede ser conveniente mantener una correspondencia, pero esto no es obligatorio
Cada conexión al servidor de la base de datos se realiza en nombre de algún rol en particular, y este rol determina los privilegios de acceso inicial para los comandos emitidos en esa conexión
Los roles tienen atributos y privilegios; los atributos se pueden ver como ¿Cuál usuario se está conectando a la bases de datos? y los atributos se pueden visualizar como ¿Cuáles acciones pueden ejecutar el rol que se está conectando?
Algunos de los privilegios de los roles son: SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER
Algunos de los atributos: SUPERUSER|CREATE ROLE|DROP ROLE|PASSWORD|DATABASE CREATION
Los roles pueden ser agrupados en un conjuntos de privilegios o permisos.
Para arrancar el sistema de base de datos, un sistema recién inicializado siempre contiene un rol predefinido. Este rol es siempre un "superusuario" y, de manera predeterminada (a menos que se modifique al ejecutar initdb )
Crear superusuario: ALTER ROLE ... WITH SUPERUSER;
Es una buena práctica crear un rol que tenga los privilegios CREATEDB y CREATEROLE, pero que no sea un superusuario,y luego usar este rol para toda la administración de rutina de bases de datos y roles. Este enfoque evita los peligros de operar como superusuario cuando no se deba
Se puede crear los roles independiente a las bases de datos.
\h CREATE ROLE, para ver como usar el comando.
CREATE USER es un alias de CREATE ROL. Algo más para agregar para verificar sí un usuario a sido creado podemos utilizar la instrucción: \dg
ALTER ROLE name_rol WITH atributo_name, para la modificación de un rol
Para crear un rol, realizamos clic derecho en la opción Login/Group Roles... Luego de ello
Para crear un rol, realizamos clic derecho en la opción Login/Group Roles-> Create-> Login Role
En la Pestaña General tenemos el campo name dónde el nombre del rol y podemos colocar un comentario.
Pestaña Definition se coloca la contraseña y también se puede agregar una fecha de expiración de la cuenta o también un limite de conexión.
En la pestaña privilegios van los atributos que llevará el rol, aquí tenemos que tener en cuenta que al dejar la opción de Ingerit right from parent esta opción permite consultar y modificar las tablas de la bd
En la pestaña Membership sirve para cuando hayamos creado un grupo de permisos y queremos hacer que este rol sea de ese grupo de permisos
En la pestaña Parameters podemos personalizar variables y atributos. Ejemplo podemos limitar la cantidad de memoria que va a usar la base de datos cuando él se conecte Si queremos que sea un usuario que de pronto no tenga el acceso al 100% de la CPU o al 100% la ram
En la pestaña Security se pueden asignar etiquetas a los roles para distinguir más fácilmente que roles tienen cierto grado de permisos y se haga más fácil el identificar por etiquetas.
CREATE ROLE login_user; : create new user/role
\dg : list users/roles
Crear contraseña para un ROLE: ALTER ROLE ... WITH PASSWORD 'nombre contraseña'
se pierde visibilidad de Pgadmin con el resaltado en rojo.Es difícil leer, lo cual no es buen aspecto pedagógico
Ya tengo lo que necesita un admin en Minecraft 😛
Para borrar un Role: DROP ROLE + nombre_role
Así es como uno debe manejar un proyecto profesional!
Para crear un usuario en pgAdmin: Login/Group Role + click derecho + Create / Login/Group Role...
Con la pestaña Privileges accedemos a todos los Roles que puede tener un usuario.
Si creo un usuario con todos los permisos, ¿Podria borrar el usuario postgres?
Funciones de los Roles
Creación de Roles por consola
Excelente explicación y de las dos forma
Para saber los tipos de Roles: \h CREATE ROLE
Para ver todos los Roles creados: \dg
Para modificar un Role: ALTER ROLE + nombre_role + WITH tipo_role
Dar permiso a un usuario de manera masiva a todas las tablas con Tables + click derecho + Grant Wizard...
Los roles pueden eliminar otros roles
Pueden crear y eliminar atributos
El rol por defecto es el de admin por lo que es peligroso
se deben crear roles más adecuados
rol y usuario es lo mismo
//Crea un usuario
*CREATE ROLE usuario_consulta;
//Se puede loguear
*ALTER ROLE usuario_consulta WITH LOGIN;
//Es un super usuario
ALTER ROLE usuario_consulta WITH SUPERADMIN
//Asingar una contraseña
ALTER ROLE usuario_consulta WITH PASSWORD ‘12345’
//Borrar USUARIO
DROP ROLE usuario_consulta
Se pueden agregar permisos específicos de inserción, actualización, selección y borrado a un usuario. con PGADMIN
Una buena introducción a la administración de seguridad en la DB, muy interesante!
CREATE USER es un alias de CREATE ROLE despues de la version 9.2 de postgresql
COMAN PARA CREAR UN ROL
CREATE ROLE
DROP ROLE
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO query_user;
GRANT INSERT, SELECT, UPDATE ON TABLE public.estacion TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.pasajero TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.trayecto TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.tren TO usuario_consulta;
GRANT INSERT, SELECT, UPDATE ON TABLE public.viaje TO usuario_consulta;
hubiera quitado las etiquetas rojas por las normales se vería mucho mejor para identificar las carpetas de pgAdmin.
Excelente.
Los roles en las bases de datos son algo muy esencial ya asi el administrador puede estar tranquilo de que los demas usuarios que solo van a leer estas bases de datos como por ejemplo los contadores no nos den problemas 💚
Los aportes, preguntas y respuestas son vitales para aprender en comunidad. Regístrate o inicia sesión para participar.