Grant usage/select to a single table

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 like so:

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;

Multiple tables/views (PostgreSQL 9.0+)

In the latest versions of PostgreSQL, you can grant permissions on all tables/views/etc in the schema using a single command rather than having to either type them one by one:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

This only affects tables that have already been created. 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.

Multiple tables/views (PostgreSQL versions before 9.0)

To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required GRANT SELECT to each table/view:

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. Naturally, this will only be applied to tables that have already been created.

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

You might find this blogpost helpful: http://www.depesz.com/index.php/2007/10/19/grantall/

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.

http://archives.postgresql.org/pgsql-admin/2008-01/msg00200.php

"grant select" seems deprecated in Postgres 9.x. in deference to "grant connect"

However, it is still used in the 9.0 manual.

See http://wiki.glitchdata.com/index.php?title=PostgreSQL:_Grant_Privileges_to_a_user

Taken from a link posted in response to despesz' link.

Postgres 9.x appears to have the capability to do what is requested. See the Grant On Database Objects paragraph of:

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

Where it says: "There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, and functions (but note that ALL TABLES is considered to include views and foreign tables)."

This page also discusses use of ROLEs and a PRIVILEGE called "ALL PRIVILEGES".

Also present is information about how GRANT functionalities compare to SQL standards.