Re: How to grant a user read-only access to a database?

if you don't want to search the archives, it could just be easier to look at the catalog tables
yourself. If you have no experience with them, many times if you do pg_foo when you are interested
in 'foo' you will get something, i.e pg_user also exists.

#\d pg_tables
View "pg_catalog.pg_tables"
Column | Type | Modifiers
-------------+---------+-----------
schemaname | "name" |
tablename | "name" |
tableowner | "name" |
tablespace | "name" |
hasindexes | boolean |
hasrules | boolean |
hastriggers | boolean |
View definition:
SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner,
t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0
AS hastriggers
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = 'r'::"char";

and then the sql just comes naturally:

select 'grant select on '|| schemaname || '.' || tablename || ' to baz' from pg_tables where
schemaname = 'bar' ;

Note that it is important to select the schemaname because there could be two different tables in
two different schemas with the same tablename. Also you should keep in mind that this will only work
for tables, if you start adding views you have to add more to the generation of sql.
-Said

Said Ramirez
Raymond O'Donnell wrote:
> On 02/03/2010 14:56, Thom Brown wrote:
>>> But I still need to define access to each table separately?
>>>
>>> Thanks,
>>> Antonio.
>>>
>> As far as I'm aware. It's only in the upcoming version 9.0 that you
>> can do things like:
>>
>> GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
>>
>> Other folk on here may have some alternative suggestions though.
>
> I think people have in the past posted queries that extract the table
> names from the system catalogues and then grant privileges on them....
> it might be worthwhile having a trawl through the archives.
>
> Ray.
>