usuario - Otorgar privilegios para una base de datos particular en PostgreSQL

roles y privilegios en postgresql

Question

Me estoy mudando de MySQL a PostgreSQL y he golpeado una pared con privilegios de usuario. Estoy acostumbrado a asignar a un usuario todos los privilegios a todas las tablas de una base de datos con el siguiente comando:

# MySQL
grant all privileges on mydatabase.* to ''myuser''@''localhost'' identified by ''mypassword'';

Me parece que la solución PostgreSQL 9.x implica la asignación de privilegios a un "esquema", pero el esfuerzo que se me exige para averiguar exactamente qué es lo que debe resolver el SQL es excesivo. Sé que unas pocas horas más de investigación darán una respuesta, pero creo que todos los que pasen de MySQL a PostgreSQL podrían beneficiarse de tener al menos una página en la web que ofrezca una receta simple y completa. Este es el único comando que he tenido que emitir para los usuarios. Prefiero no tener que emitir un comando para cada nueva tabla.

No sé qué escenarios deben manejarse de manera diferente en PostgreSQL, así que enumeraré algunos de los escenarios que normalmente tuve que manejar en el pasado. Supongamos que solo pretendemos modificar los privilegios de una única base de datos que ya se ha creado.

(1a) No todas las tablas se han creado todavía, o (1b) las tablas ya se han creado.

(2a) El usuario aún no se ha creado, o (2b) el usuario ya se ha creado.

(3a) Los privilegios aún no se han asignado al usuario, o (3b) los privilegios se asignaron previamente al usuario.

(4a) El usuario solo necesita insertar, actualizar, seleccionar y eliminar filas, o (4b) el usuario también necesita poder crear y eliminar tablas.

He visto respuestas que otorgan todos los privilegios a todas las bases de datos, pero eso no es lo que quiero aquí. Por favor, estoy buscando una receta simple, aunque tampoco me importaría una explicación.

No quiero otorgar derechos a todos los usuarios ni a todas las bases de datos, ya que parece ser el acceso directo convencional, ya que ese enfoque compromete todas las bases de datos cuando un usuario está comprometido. Alojo varios clientes de base de datos y asigno un inicio de sesión diferente a cada cliente.

Parece que también necesito el privilegio USAGE para obtener los valores crecientes de una columna en serial , pero tengo que otorgarlo en algún tipo de secuencia. Mi problema se hizo más complejo.

Answer 1

¿Quizás podría darme un ejemplo que otorgue a un usuario específico seleccionar / insertar / actualizar / eliminar en todas las tablas, aquellas existentes y aún no creadas, de una base de datos específica?

Lo que usted llama una base de datos en MySQL se parece más a un esquema PostgreSQL que a una base de datos PostgreSQL.

Conectar a la base de datos "test" como superusuario. Aquí eso es

$ psql -U postgres test

Cambie los privilegios predeterminados para el usuario "probador" existente.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES
    TO tester;

Cambiar los privilegios predeterminados no tiene efecto en las tablas existentes. Eso es por diseño. Para las tablas existentes, use la sintaxis estándar de GRANT y REVOKE.

No puede asignar privilegios a un usuario que no existe.

Answer 2

No quiero otorgar derechos a todos los usuarios ni a todas las bases de datos, ya que parece ser el acceso directo convencional, ya que ese enfoque compromete todas las bases de datos cuando un usuario está comprometido. Alojo varios clientes de base de datos y asigno un inicio de sesión diferente a cada cliente.

DE ACUERDO. Cuando asigne tablas al rol correcto, los privilegios otorgados serán específicos del rol y no para todos los usuarios. Entonces puedes decidir a quién dar roles .

1. Crear un role para cada base de datos. Un rol puede tener muchos usuarios.
2. Luego, asigne un client-username al rol correcto.
3. También asigne your-username a cada rol si es necesario.

(1a) No todas las tablas se han creado todavía, o (1b) las tablas ya se han creado.

DE ACUERDO. Puede crear tablas más tarde.
Cuando esté listo, asigne las tablas a la role cliente correcta.

CREATE TABLE tablename();
CREATE ROLE rolename;
ALTER TABLE tablename OWNER TO rolename;

(2a) El usuario aún no se ha creado, o (2b) el usuario ya se ha creado.

DE ACUERDO. Crea nombres de usuario cuando estés listo. Si su cliente necesita más de un nombre de usuario, simplemente cree un segundo client-username .

CREATE USER username1;
CREATE USER username2;

(3a) Los privilegios aún no se han asignado al usuario, o (3b) los privilegios se asignaron previamente al usuario.

DE ACUERDO. Cuando esté listo para otorgar privilegios, cree al usuario y asígnele el rol correcto.
Use el comando GRANT-TO para asignar roles a los usuarios.

GRANT rolename TO username1;
GRANT rolename TO username2;

(4a) El usuario solo necesita insertar, actualizar, seleccionar y eliminar filas, o (4b) el usuario también necesita poder crear y eliminar tablas.

DE ACUERDO. Usted ejecuta estos comandos para agregar permisos a sus usuarios.

GRANT SELECT, UPDATE, INSERT, DELETE ON dbname TO role-or-user-name;
ALTER USER username1 CREATEDB;

Answer 3

Concepto básico en postgres.

Los roles son objetos globales que pueden acceder a todas las bases de datos en un clúster db, dados los privilegios requeridos.

Un clúster contiene muchas bases de datos , que contienen muchos esquemas . Los esquemas (incluso con el mismo nombre) en diferentes bases de datos no están relacionados. La concesión de privilegios para un esquema solo se aplica a este esquema particular en la base de datos actual (la base de datos actual en el momento de la concesión).

Cada base de datos comienza con un esquema public por defecto. Eso es una convención, y muchos ajustes comienzan con ella. Aparte de eso, el esquema public es solo un esquema como cualquier otro.

Desde MySQL, es posible que desee comenzar con un único esquema public , ignorando efectivamente la capa de esquema por completo. Estoy usando docenas de esquemas por base de datos regularmente.
Los esquemas son un poco (pero no completamente) como directorios en el sistema de archivos.

Una vez que haga uso de múltiples esquemas, asegúrese de entender la configuración de search_path :

• ¿Cómo influye el search_path en la resolución del identificador y el "esquema actual"?

Privilegios por defecto

Por documentación en GRANT :

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.

Todos estos valores predeterminados se pueden cambiar con ALTER DEFAULT PRIVILEGES :

• Otorgar todo en un esquema específico en la base de datos a un rol de grupo en PostgreSQL

Rol de grupo

Al igual que @Craig comentó , es mejor GRANT privilegios para un rol de grupo y luego hacer que un usuario específico sea miembro de ese rol ( GRANT el rol de grupo para el rol de usuario). De esta manera, es más sencillo repartir y revocar los paquetes de privilegios necesarios para ciertas tareas.

Un rol de grupo es solo otro rol sin inicio de sesión. Agrega un inicio de sesión para transformarlo en un rol de usuario. Más:

• ¿Por qué PostgreSQL fusionó usuarios y grupos en roles?

Receta

Por ejemplo, tenemos una nueva base de datos mydb , un grupo mygrp y un usuario myusr ...

Mientras está conectado a la base de datos en cuestión como superusuario ( postgres por ejemplo):

REVOKE ALL ON DATABASE mydb FROM public;  -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp;  -- since we revoked from public

GRANT USAGE ON SCHEMA public TO mygrp;

Para asignar a a user all privileges to all tables que escribió (podría ser más restrictivo):

GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; -- don''t forget those

Para establecer privilegios predeterminados para objetos futuros, ejecute para cada rol que crea objetos en este esquema:

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON TABLES TO mygrp;

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON SEQUENCES TO mygrp;

-- more roles?

Ahora, otorga el grupo al usuario:

GRANT mygrp TO myusr;

Respuesta relacionada:

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

Configuración alternativa (no estándar)

Desde MySQL, y dado que desea mantener separados los privilegios de las bases de datos, es posible que le guste esta configuración no estándar db_user_namespace . Por documentación:

Este parámetro habilita los nombres de usuario por base de datos. Está apagado por defecto.

Lea el manual cuidadosamente. Yo no uso este ajuste. No anula lo anterior.

Answer 4

Puedes olvidarte del esquema si solo usas PUBLIC. Entonces haces algo como esto: ( ver doc aquí )

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]