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

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