sirve - postgresql version
¿Cómo creas un usuario de solo lectura en PostgreSQL? (9)
Me gustaría crear un usuario en PostgreSQL que solo pueda realizar 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 CREATE, CONNECT, TEMPORARY y TEMP.
Otorgar el 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 USAGE en los espacios de nombres (esquemas) y SELECT en tablas y vistas individualmente, así:
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 a las tablas que ya han sido creadas. Más poderosamente, puede tener automáticamente roles predeterminados asignados a nuevos objetos 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 rol del cual sea miembro el usuario emisor. Sin embargo, no recoja los privilegios predeterminados para todos los roles de los que es miembro cuando crea nuevos objetos ... por lo que todavía hay algunos problemas. Si adopta el enfoque de que una base de datos tiene un rol de propietario, y los cambios de esquema se realizan como ese rol de propietario, entonces debe asignar privilegios predeterminados a ese rol de propietario. En mi humilde opinión, todo esto es un poco confuso y es posible que deba experimentar para crear un flujo de trabajo funcional.
Múltiples tablas / vistas (versiones de PostgreSQL anteriores a 9.0)
Para evitar errores en cambios largos y en varias tablas, se recomienda usar 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 generar los comandos GRANT relevantes para GRANT SELECT en todas las tablas, vistas y secuencias en público, para copiar y pegar el amor. Naturalmente, esto solo se aplicará a las tablas que ya se han creado.
He creado un script conveniente para eso; pg_grant_read_to_db.sh . Este script otorga privilegios de solo lectura a un rol específico en todas las tablas, vistas y secuencias en un esquema de base de datos y los establece como predeterminados.
La forma no sencilla de hacerlo sería otorgar selección en cada tabla de la base de datos:
postgres=# grant select on db_name.table_name to read_only_user;
Podría automatizar eso generando sus declaraciones de concesión a partir de los metadatos de la base de datos.
Por defecto, los nuevos usuarios tendrán permiso para crear tablas. Si planea crear un usuario de solo lectura, probablemente 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 enumerar tablas (es decir, /d no devuelve resultados), es probable que no tenga permisos de USAGE para el esquema. USAGE es un permiso que permite a los usuarios utilizar realmente los permisos que han sido asignados. ¿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
Tenga en cuenta que PostgreSQL 9.0 (hoy en prueba beta) tendrá una forma sencilla de hacerlo :
test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;
GRANT
Tomado de un enlace publicado en respuesta al enlace de despesz'' .
Postgres 9.x parece tener la capacidad de hacer lo que se solicita. Vea el párrafo Otorgar sobre objetos de base de datos de:
http://www.postgresql.org/docs/current/interactive/sql-grant.html
Donde dice: "También hay una opción para otorgar privilegios sobre todos los objetos del mismo tipo dentro de uno o más esquemas. Esta funcionalidad actualmente se admite solo para tablas, secuencias y funciones (pero tenga en cuenta que TODAS LAS TABLAS se considera que incluyen vistas y mesas extranjeras).
Esta página también analiza el uso de ROL y un PRIVILEGIO llamado "TODOS LOS PRIVILEGIOS".
También está presente información sobre cómo se comparan las funcionalidades de GRANT con los estándares SQL.
Referencia tomada de este blog:
Script para crear usuarios 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;
Esta es la mejor manera que he encontrado para agregar usuarios de solo lectura (usando PostgreSQL 9.0 o más reciente):
$ sudo -upostgres psql postgres
postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD ''<USE_A_NICE_STRONG_PASSWORD_PLEASE'';
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
Luego inicie sesión en todas las máquinas relacionadas (master + read-slave (s) / hot-standby (s), etc.) y ejecute:
$ echo "hostssl <PUT_DBNAME_HERE> <PUT_READONLY_USERNAME_HERE> 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/9.2/main/pg_hba.conf
$ sudo service postgresql reload