I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be...
GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';
What is the equivalent command or series of commands in PostgreSQL?
I tried...
postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;
But it appears that the only things you can grant on a DB are CREATE, CONNECT, TEMPORARY, and TEMP.
The not straightforward way of doing it would be granting select on each table of the database:
postgres=# grant select on db_name.table_name to read_only_user;
You could automate that by generating your grant statements from the database metadata.
You might find this blogpost helpful: http://www.depesz.com/index.php/2007/10/19/grantall/
If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually. So something like:
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;
Granting 'SELECT' to each table/view individually can be a bore. Something like this can help:
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')
This should output the relevant GRANT commands to GRANT SELECT on all tables and views in public, for copy-n-paste love.
Edit - PostgreSQL 9
This post is quite old, and perhaps it's worth mentioning now that PostgreSQL 9.0 introduces functionality to make this easier.
You still need to grant USAGE permissions on schemas, but you can grant permissions on all tables, views etc. in the schema using a single command rather than having to generate them:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;
This only affects tables that have already been created, so it's the equivalent of the generated commands above. More powerfully, you can automatically have default roles assigned to new objects in future:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO xxx;
Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.
Do note that PostgreSQL 9.0 (today in beta testing) will have a simple way to do that:
test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;
GRANT