¿Cómo se crea un usuario de solo lectura en PostgreSQL?

Me gustaría crear un usuario en PostgreSQL que solo pueda hacer SELECT desde una base de datos en particular. En MySQL, el comando sería:

GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy'; 

¿Cuál es el comando equivalente o una serie de comandos en PostgreSQL?

Lo intenté…

 postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy'; postgres=# GRANT SELECT ON DATABASE mydb TO xxx; 

Pero parece que las únicas cosas que puede otorgar en una base de datos son CREAR, CONECTAR, TEMPORAL y TEMP.

Conceder uso / seleccionar a una sola tabla

Si solo otorga CONNECT a una base de datos, el usuario puede conectarse pero no tiene otros privilegios. Debe otorgar USO en espacios de nombres (esquemas) y SELECCIONAR en tablas y vistas individualmente de esta manera:

 GRANT CONNECT ON DATABASE mydb TO xxx; -- This assumes you're actually connected to mydb.. GRANT USAGE ON SCHEMA public TO xxx; GRANT SELECT ON mytable TO xxx; 

Múltiples tablas / vistas (PostgreSQL 9.0+)

En las últimas versiones de PostgreSQL, puede otorgar permisos en todas las tablas / vistas / etc en el esquema usando un solo comando en lugar de tener que escribirlos uno por uno:

 GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx; 

Esto solo afecta tablas que ya han sido creadas. De forma más poderosa, puede tener automáticamente roles predeterminados asignados a objetos nuevos en el futuro:

 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xxx; 

Tenga en cuenta que, de forma predeterminada, esto solo afectará a los objetos (tablas) creados por el usuario que emitió este comando: aunque también se puede establecer en cualquier función a la que pertenezca el usuario emisor. Sin embargo, no recuperas los privilegios predeterminados para todos los roles de los que eres miembro cuando creas objetos nuevos … por lo que todavía hay algunos problemas. Si adopta el enfoque de que una base de datos tiene un rol propietario, y los cambios de esquema se realizan como esa función propietaria, entonces debe asignar privilegios predeterminados a esa función propietaria. En mi humilde opinión, esto es un poco confuso y es posible que deba experimentar para llegar a un flujo de trabajo funcional.

Múltiples tablas / vistas (versiones de PostgreSQL anteriores a 9.0)

Para evitar errores en los cambios extensos de varias tablas, se recomienda utilizar el siguiente proceso ‘automático’ para generar el GRANT SELECT requerido para cada tabla / vista:

 SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S'); 

Esto debería dar salida a los comandos GRANT relevantes para GRANT SELECT en todas las tablas, vistas y secuencias en público, para copiar y pegar amor. Naturalmente, esto solo se aplicará a las tablas que ya se han creado.

Tenga en cuenta que PostgreSQL 9.0 (hoy en pruebas beta) tendrá una forma sencilla de hacerlo :

 test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser; GRANT 

Esta es la mejor forma que he encontrado para agregar usuarios de solo lectura (usando PostgreSQL 9.0 o posterior):

 $ sudo -upostgres psql postgres postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD ' 

A continuación, inicie sesión en todas las máquinas relacionadas (master + read-slave (s) / hot-standby (s), etc.) y ejecute:

 $ echo "hostssl   0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/9.2/main/pg_hba.conf $ sudo service postgresql reload 

Referencia tomada de este blog:

Script para crear un usuario de solo lectura:

 CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'; 

Asignar permiso a este usuario de solo lectura:

 GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User; GRANT USAGE ON SCHEMA public TO Read_Only_User; GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User; 

Creé un guión conveniente para eso; pg_grant_read_to_db.sh . Este script concede privilegios de solo lectura a un rol especificado en todas las tablas, vistas y secuencias en un esquema de base de datos y las establece como predeterminadas.

Por defecto, los nuevos usuarios tendrán permiso para crear tablas. Si planea crear un usuario de solo lectura, probablemente esto no sea lo que desea.

Para crear un verdadero usuario de solo lectura con PostgreSQL 9.0+, ejecute los siguientes pasos:

 # This will prevent default users from creating tables REVOKE CREATE ON SCHEMA public FROM public; # If you want to grant a write user permission to create tables # note that superusers will always be able to create tables anyway GRANT CREATE ON SCHEMA public to writeuser; # Now create the read-only user CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser; 

Si su usuario de solo lectura no tiene permiso para listar tablas (es decir, \d no arroja resultados), es probable que sea porque no tiene permisos de USAGE para el esquema. USAGE es un permiso que permite a los usuarios usar realmente los permisos que se les han asignado. ¿Cuál es el punto de esto? No estoy seguro. Arreglar:

 # You can either grant USAGE to everyone GRANT USAGE ON SCHEMA public TO public; # Or grant it just to your read only user GRANT USAGE ON SCHEMA public TO readonlyuser; 

Si su base de datos está en el esquema público, es fácil (esto supone que ya ha creado el readonlyuser )

 db=> GRANT SELECT ON ALL TABLES IN SCHEMA public to readonlyuser; GRANT db=> GRANT CONNECT ON DATABASE mydatabase to readonlyuser; GRANT db=> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to readonlyuser; GRANT 

Si su base de datos está utilizando customschema , ejecute lo anterior pero agregue un comando más:

 db=> ALTER USER readonlyuser SET search_path=customschema, public; ALTER ROLE 

La forma no directa de hacerlo sería la concesión de seleccionar en cada tabla de la base de datos:

 postgres=# grant select on db_name.table_name to read_only_user; 

Puede automatizar eso al generar sus declaraciones de concesión a partir de los metadatos de la base de datos.

Tomado de un enlace publicado en respuesta al enlace de despesz ‘ .

Postgres 9.x parece tener la capacidad de hacer lo que se solicita. Ver el párrafo Grant on Database Objects de:

http://www.postgresql.org/docs/current/interactive/sql-grant.html

Donde dice: “También hay una opción para otorgar privilegios a todos los objetos del mismo tipo dentro de uno o más esquemas. Esta funcionalidad actualmente solo es compatible con tablas, secuencias y funciones (pero tenga en cuenta que se considera que TODAS LAS TABLAS incluyen vistas y tablas extranjeras) “.

Esta página también discute el uso de ROLE y un PRIVILEGE llamado “TODOS LOS PRIVILEGIOS”.

También está presente la información sobre cómo las funcionalidades GRANT se comparan con los estándares SQL.