usuario - Conceder todo en un esquema específico en el DB a un rol de grupo en PostgreSQL

roles y privilegios en postgresql

Question

Usando PostgreSQL 9.0, tengo un rol de grupo llamado "staff" y me gustaría otorgar todos (o ciertos) privilegios a este rol en tablas en un esquema particular. Ninguno de los siguientes trabajos

GRANT ALL ON SCHEMA foo TO staff;
GRANT ALL ON DATABASE mydb TO staff;

Los miembros del "personal" aún no pueden SELECCIONAR o ACTUALIZAR en las tablas individuales en el esquema "foo" o (en el caso del segundo comando) a ninguna tabla en la base de datos a menos que otorgue todo en esa tabla específica.

¿Qué puedo hacer para facilitar mi vida y la de mis usuarios?

Actualización: lo resolvió con la ayuda de una pregunta similar en serverfault.com .

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO staff;

Answer 1

Encontró la abreviatura para establecer privilegios para todas las tablas existentes en el esquema dado. El manual aclara :

(pero tenga en cuenta que se considera que ALL TABLES incluyen vistas y tablas extrañas ).

Negrita énfasis mío. serial columnas en serial se implementan con nextval() en una secuencia como columna predeterminada y, citando el manual :

Para las secuencias, este privilegio permite el uso de las funciones currval y nextval .

Entonces, si hay columnas en serial , también querrás otorgar USAGE (o ALL PRIVILEGES ) en las secuencias

GRANT USAGE ON ALL SEQUENCES IN SCHEMA foo TO mygrp;

Nota: las columnas de identidad en Postgres 10 o posterior usan secuencias implícitas que no requieren privilegios adicionales. (Considere actualizar columnas en serial ).

¿Qué hay de los objetos nuevos ?

También le interesarán DEFAULT PRIVILEGES para usuarios o esquemas :

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT ALL PRIVILEGES ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT USAGE          ON SEQUENCES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

Esto establece privilegios para objetos creados en el futuro automáticamente, pero no para objetos preexistentes.

Los privilegios predeterminados solo se aplican a los objetos creados por el usuario objetivo ( FOR ROLE my_creating_role ). Si se omite esa cláusula, se establece de forma predeterminada en el usuario actual que ejecuta ALTER DEFAULT PRIVILEGES . Para ser explícito:

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;

Tenga en cuenta también que todas las versiones de pgAdmin III tienen un error sutil y muestran privilegios predeterminados en el panel SQL, incluso si no se aplican a la función actual. Asegúrese de ajustar manualmente la cláusula FOR ROLE al copiar el script SQL.

Answer 2

Mi respuesta es similar a esta en ServerFault.com .

Para ser conservador

Si quieres ser más conservador que otorgar "todos los privilegios", es posible que quieras probar algo como estos.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO some_user_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO some_user_;

El uso de public allí se refiere al nombre del esquema predeterminado creado para cada nueva base de datos / catálogo. Reemplace con su propio nombre si creó un esquema.

Acceso al esquema

Para acceder a un esquema, para cualquier acción, el usuario debe tener derechos de "uso". Antes de que un usuario pueda seleccionar, insertar, actualizar o eliminar, primero debe otorgarse un "uso" a un usuario a un esquema.

No notará este requisito cuando use Postgres por primera vez. Por defecto, cada base de datos tiene un primer esquema llamado public . Y a cada usuario de manera predeterminada se le han otorgado automáticamente derechos de "uso" para ese esquema en particular. Al agregar un esquema adicional, debe otorgar explícitamente los derechos de uso.

GRANT USAGE ON SCHEMA some_schema_ TO some_user_ ;

Extracto del documento de Postgres :

Para los esquemas, permite el acceso a los objetos contenidos en el esquema especificado (suponiendo que también se cumplen los requisitos de privilegio propios de los objetos). Esencialmente, esto le permite al concesionario "buscar" objetos dentro del esquema. Sin este permiso, aún es posible ver los nombres de los objetos, por ejemplo, consultando las tablas del sistema. Además, después de revocar este permiso, los backends existentes pueden tener declaraciones que hayan realizado previamente esta búsqueda, por lo que esta no es una forma completamente segura de evitar el acceso a los objetos.

Para obtener más información, consulte la pregunta ¿Qué hace exactamente el USO DE LA SUBVENCIÓN EN SCHEMA? . Preste especial atención a la respuesta del experto de Postgres, Craig Ringer .

Objetos existentes versus futuro

Estos comandos solo afectan a los objetos existentes. Las tablas y demás que cree en el futuro obtienen privilegios predeterminados hasta que vuelva a ejecutar esas líneas arriba. Vea la otra respuesta de Erwin Brandstetter para cambiar los valores predeterminados que afectan los objetos futuros.