Creación de Roles

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

• Crear y Eliminar
• Asignar atributos
• Agrupar con otros roles
• Roles predeterminados

-- 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

Creación de 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

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 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:

• Crear y eliminar
• Asignar atributos
• Agrupar con otros roles
• Roles predeterminados
  Dependiendo de los permisos con los que estos cuente, pueden ejecutar diferentes acciones sobre la base de datos como actualizaciones, inserciones, consultas, etc.

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.

1. Creamos el grupo de role

CREATE ROLE lectura; 

1. Le asignamos los privilegios

GRANT SELECT ON ALL TABLES
IN SCHEMA "public"
TO lectura;

1. Creamos usuario de solo consulta

CREATE ROLE usuario_consulta2 LOGIN PASSWORD 'visita';

1. Lo asignamos al grupo lectura

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

• Crear y Eliminar
• Asignar atributos
• Agrupar con otros roles
• Roles predeterminados

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 💚