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

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 manera 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 '<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 ejecutar:

$ 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

Referencia tomada de este blog:

Script para Crear 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;

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

Para crear un usuario verdadero 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 devuelve resultados), probablemente sea porque no tiene permisos USAGE para el esquema. USAGE es un permiso que permite a los usuarios utilizar realmente los permisos han sido asignados. ¿Qué sentido tiene esto? No estoy seguro. Para 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;

He creado un script conveniente para eso; pg_grant_read_to_db.sh . Este script otorga privilegios de solo lectura a un rol especificado en todas las tablas, vistas y secuencias en un esquema de base de datos y los establece como predeterminados.

Si su base de datos está en el esquema público, es fácil (esto asume 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á usando customschema, ejecute lo anterior pero agregue un comando más:

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

La forma no sencilla de hacerlo sería otorgar select 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 subvención a partir de los metadatos de la base de datos.

Tomado de un enlace publicado en respuesta a despesz' enlace.

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 se admite actualmente solo para tablas, secuencias y funciones (pero tenga en cuenta que TODAS las TABLAS se considera que incluyen vistas y tablas extranjeras)."

Esta página también discute el uso de roles 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.