Re: "Limpiar" asignación de permisos a objetos en esquema.

Hola Federico, muy bueno tu aporte.

Nosotros tenemos una infraestructura similar de bases-esquemas y
grupos-usuarios, con la salvedad que una aplicación tiene una base y
un esquema (en pocos caso tenemos mas de un esquema).
Cada proyecto "nom_proyecto" tiene una aplicación "nom_proyecto_app" y
una base de datos "nom_proyecto" que tendrá un esquema
"sch_nom_proyecto" y 3 grupos "nom_proyecto_owner" dueño de todos los
objetos de la base (no tiene usuarios afiliados), "nom_proyecto_rw"
permisos para el usuario de la aplicación (tiene afiliado al usurario
"nom_proyectoN"), "nom_proyecto_ro" que se lo asignamos a los usuarios
de aplicaciones que solo realizan consultas.
Por último revocamos todos los permisos del usuario public y dropeamos
el esquema public. Todo esto lo hacemos a mano (ahí es cuando tu
script me alegra el día).

Tenemos también el mismo problema con el área de desarrollo y su
cantidad de permisos. Este problema se presenta al de pasar de entorno
y a la hora de documentar el proyecto, justamente como armamos la
documentación no nos evitamos de hacer el dump (adjunto el script que
usamos para eso).

El mié., 31 jul. 2019 a las 10:16, Federico Pascual
(<federico(dot)pascual(at)gmail(dot)com>) escribió:
>
> Juan José,
> Hola. Si cómo no.
> Comento brevemente como está organizado para que se entienda.
> * Cada db tiene un conjunto de esquemas.
> * Cada esquema tiene asociado 3 grupos:
> - uno con permisoso de administración sobre ESE esquema. ALL PRIVILEGES
> - uno con permisos de app sobre ESE esquema. SELECT, INSERT, UPDATE, DELETE
> - uno con permisos de select sobre ESE esquema. SELECT
> * Los permisos a los usuarios solo son dados mediante la asignación a esos grupos.
>
> De estar forma un usuario nominal (una persona) está "afiliada" a distintos grupos según los esquemas a los que necesita acceso y el entorno de trabajo (integración, testing, staging, etc.).
>
> Cuando un esquema se crea, automáticamente se crean los grupos mencionados.
>
> La asignación masiva de privilegios de la que hablaba que "acomoda" los permisos a lo mencionado podemos hacerla mediante la ejecución del script adjunto (que recibe como parámetros el nombre de la db y el esquema sobre el que se va a trabajar). Lo hacemos por si los usuarios que crean las estructuras olvidan realizar la configuración correspondiente sobre los objetos.
>
> En esta infraestructura hay dbs por dependencias que a su ves tienen esquemas por sistema o lo que fuere que necesiten. En los entornos no-productivos los usuarios (desarrolladores) pueden realizar modificaciones sobre las estructuras, pero en producción toda intervencion que no sea desde los servidores de app se canaliza por el área de base de datos. Por eso la forma de trabajo.
>
> Saludos, espero se entienda y sea de utilidad.
>
> Federico.
>
>
> El mar., 30 jul. 2019 a las 16:19, Jose Mercedes Venegas Acevedo (<jvenegasperu(at)gmail(dot)com>) escribió:
>>
>> Hola Federico
>> Buen dia
>>
>> Que interesante este tema podrias tu tambien por favor compartir el script de esto ultimo que mencionas " La reasingación del owner correcto y los permisos correspondientes se hace a travez de otro script" aprovechando que ya lo tienes resuelto seria tambien barbaro.
>>
>> Atentamente
>>
>> José
>>
>>
>>
>> El mar., 30 jul. 2019 a las 11:55, Federico Pascual (<federico(dot)pascual(at)gmail(dot)com>) escribió:
>>>
>>> Juan José,
>>> Hola. Bárbaro che, muchicimas gracias.
>>> Es exactamente esto lo que quiero, solo voy a exeptuar los usuarios del sistema (pg_signal_backend, etc.).
>>> Pensé por un momento que podía existir alguna alternativa del tipo "... from all roles" como decis, pero tu solución se ajusta a lo que queremos hacer. La reasingación del owner correcto y los permisos correspondientes se hace a travez de otro script que ya está resuelto.
>>>
>>> Saludos y muchas gracias nuevamente.
>>> Federico.
>>>
>>> El mar., 30 jul. 2019 a las 13:12, Juan José Santamaría Flecha (<juanjo(dot)santamaria(at)gmail(dot)com>) escribió:
>>>>
>>>> On Tue, Jul 30, 2019 at 3:24 PM Federico Pascual
>>>> <federico(dot)pascual(at)gmail(dot)com> wrote:
>>>> >
>>>> > Yo quisiera algo como:
>>>> >
>>>> > revoke all privileges on all tables on schema <schema name> from all fucking world;
>>>> >
>>>> > Esta es la referencia más cercana que encontré a lo que quiero:
>>>> > http://www.postgresonline.com/journal/index.php?/archives/221-Bulk-Revoke-of-Permissions-for-Specific-GroupUser-role.html
>>>> >
>>>> > Quisiera evitar tener que exportar la db con la cláusula que evita la asignación de permisos para tener que reimportarla.
>>>> >
>>>>
>>>> De la lógica que quieres, la única parte que no puedes hacer en una
>>>> única instrucción es el 'from all roles'. Tienes que iterar por cada
>>>> uno de los roles a los que les vas a hacer el 'revoke'. En cualquier
>>>> caso, te recomendaría no quitar los privilegios a los dueños del
>>>> esquema.
>>>>
>>>> Con SQL encadenado puedes generar las instrucciones. Y con PL/pgSQL
>>>> puedes automatizarlo:
>>>>
>>>> DO $$
>>>> DECLARE rol record;
>>>> BEGIN
>>>> FOR rol IN
>>>> SELECT r.rolname, nsp.nspname
>>>> FROM pg_roles r
>>>> CROSS JOIN pg_namespace nsp
>>>> WHERE nsp.nspowner <> r.oid AND nsp.nspname = '<schema_name>'
>>>> LOOP
>>>> RAISE NOTICE 'REVOKE ALL ON ALL TABLES IN SCHEMA % FROM %',
>>>> rol.nspname, rol.rolname;
>>>> RAISE NOTICE 'REVOKE ALL ON ALL SEQUENCES IN SCHEMA % FROM %',
>>>> rol.nspname, rol.rolname;
>>>> RAISE NOTICE 'REVOKE ALL ON ALL FUNCTIONS IN SCHEMA % FROM %',
>>>> rol.nspname, rol.rolname;
>>>> --EXECUTE 'REVOKE ALL ON ALL TABLES IN SCHEMA ' ||
>>>> quote_ident(rol.nspname) || ' FROM ' || quote_ident(rol.rolname);
>>>> --EXECUTE 'REVOKE ALL ON ALL SEQUENCES IN SCHEMA ' ||
>>>> quote_ident(rol.nspname) || ' FROM ' || quote_ident(rol.rolname);
>>>> --EXECUTE 'REVOKE ALL ON ALL FUNCTIONS IN SCHEMA ' ||
>>>> quote_ident(rol.nspname) || ' FROM ' || quote_ident(rol.rolname);
>>>> END LOOP;
>>>> END$$;
>>>>
>>>> Asegúrate que esta es la funcionalidad que buscas antes de quitar los
>>>> comentarios.
>>>>
>>>> Un saludo,
>>>>
>>>> Juan José Santamaría Flecha
>>
>>
>>
>> --
>> José Mercedes Venegas Acevedo
>> cel Mov RPC 964185205
>>
>>

--
Carlos D. Hernández