Re: grant select script

Marc,
Try pg_stat_user_tables - it will eliminate the tables in pg_catalog, information_schema, and the toast tables.
Bob Lunney

--- On Wed, 3/30/11, Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu> wrote:

From: Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu>
Subject: [ADMIN] grant select script
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Date: Wednesday, March 30, 2011, 1:54 PM

I am working with a script to automate grant select to all tables in a database to a user.
  1 #!/bin/bash
  2 for table in 'echo "SELECT relname FROM pg_stat_all_tables;" | psql cswe2 | grep -v "pg_" | grep "^ "';
  3 do
  4 echo "GRANT SELECT ON TABLE $table to tom;"
  5 echo "GRANT SELECT ON TABLE $table to tom;" | psql cswe2
  6 done
 
The script works—meaning it grants the select to the user, but it generates errors on tables that do not exist like the following.
The data base cswe2 does not contain the table sql_languages, unless it is hidden. Is there a way to tell the script to ignore them?
GRANT SELECT ON TABLE sql_languages to tom;
ERROR:  relation "sql_languages" does not exist
 
Thanks
 
Marc

 
 
Marc Fromm

Information Technology Specialist II

Financial Aid Department

Western Washington University

Phone: 360-650-3351

Fax:   360-788-0251