PostgreSQL: el usuario de la base de datos solo debe tener permiso para llamar a funciones

stored-procedures transactions

Question

Actualmente estoy usando PostgreSQL para mi aplicación. Ya que estoy tratando de poner cada SQL que contiene una transacción (es decir, insertar, actualizar, eliminar) en una función, me topé con este problema:

¿Es posible que a un usuario de la base de datos solo se le permita llamar a funciones y Declaraciones Selectas mientras que no puede llamar a Declaraciones SQL que contienen una transacción? Por "funciones de llamada" me refiero a cualquier función. Independientemente de si contiene una transacción o no.

Ya intenté crear un usuario que solo puede llamar a funciones y Declaraciones Selectas. Pero siempre termino con un error, al llamar a funciones que contienen transacciones. Por lo que entiendo, un dbuser necesita permisos de escritura si llama a una función que usa una instrucción de inserción, actualización o eliminación.

¿Me estoy perdiendo de algo? ¿Realmente no es posible este escenario? Desde el punto de vista de seguridad, esto sería realmente bueno porque, en primer lugar, previene la inyección de SQL.

Answer 1

No hay "privilegio en SELECT ". Todo lo que necesitas es el privilegio de EXECUTE funciones. La función relevante se puede ejecutar con SECURITY DEFINER para heredar todos los privilegios del propietario. Para restringir la posible escalada de privilegios a un mínimo a priori, haga que un rol de daemon tenga funciones relevantes con solo los privilegios necesarios, ¡no un superusuario!

Receta

Como superusuario ...

Crear una función de no-superusuario myuser .

CREATE ROLE myuser PASSWORD ...;

Cree una función de grupo mygroup y convierta a myuser en miembro.

CREATE ROLE mygroup;
GRANT mygroup TO myuser;

Es posible que desee agregar más usuarios al igual que myuser más tarde.

No otorgue ningún privilegio a mi myuser .
Solo concédelos a mygroup :

• GRANT CONNECT ON DATABASE mydb TO mygroup;
• GRANT USAGE ON SCHEMA public TO mygroup;
• GRANT EXECUTE ON FUNCTION foo() TO mygroup;

Eliminar todos los privilegios para el public que mi myuser no debería tener.

REVOKE ALL ON ALL TABLES IN SCHEMA myschema FROM public;

Puede haber más. Cito el manual:

PostgreSQL otorga privilegios predeterminados en algunos tipos de objetos a PUBLIC . No se otorgan privilegios a PUBLIC de forma predeterminada en tablas, columnas, esquemas o espacios de tablas. Para otros tipos, los privilegios predeterminados otorgados a PUBLIC son los siguientes: CONNECT y CREATE TEMP TABLE para las bases de datos; Privilegio EXECUTE para funciones; y privilegio USAGE para idiomas. El propietario del objeto puede, por supuesto, REVOKE los privilegios predeterminados y otorgados expresamente. (Para obtener la máxima seguridad, emita REVOKE en la misma transacción que crea el objeto; entonces no hay ninguna ventana en la que otro usuario pueda usar el objeto). Además, estas configuraciones de privilegio predeterminadas iniciales se pueden cambiar usando el comando ALTER DEFAULT PRIVILEGES .

Crear un rol de demonio para poseer funciones relevantes.

CREATE ROLE mydaemon;

Otorgue solo los privilegios necesarios para ejecutar estas funciones a mydaemon , (incluido EXECUTE ON FUNCTION para permitir que se llame a otra función). Nuevamente, puede usar roles de grupo para agrupar privilegios y otorgarlos a mydaemon

GRANT bundle1 TO mydaemon;

Además, puede usar DEFAULT PRIVILEGES para otorgar automáticamente ciertos privilegios para objetos futuros a un paquete o al demonio directamente:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES    TO bundle1;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE  ON SEQUENCES TO bundle1;

Esto se aplica solo a la función para la que se ejecuta. Según la documentación:

Si se omite FOR ROLE , se asume el rol actual.

Para cubrir también los objetos preexistentes en el esquema (ver el comentario de rob ):

GRANT SELECT ON ALL TABLES    IN SCHEMA public TO bundle1;
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO bundle1;

Hacer mydaemon propias funciones relevantes. Podría verse así:

CREATE OR REPLACE FUNCTION foo()
  ...
SECURITY DEFINER SET search_path = myschema, pg_temp;

ALTER FUNCTION foo() OWNER TO mydaemon;
REVOKE EXECUTE ON FUNCTION foo() FROM public;
GRANT  EXECUTE ON FUNCTION foo() TO mydaemon;
GRANT  EXECUTE ON FUNCTION foo() TO mygroup;
-- possibly others ..

###Nota
Debido a este error en la versión actual 1.16.1 de pgAdmin el comando necesario

REVOKE EXECUTE ON FUNCTION foo() FROM public;

Falta en el script DDL de ingeniería inversa. Recuerda agregarlo, al recrearlo.
Este error se corrigió en la versión actual pgAdmin 1.18.1.