pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedgrant select script
3+ messages / 3 participants
[nested] [flat]
* grant select script
@ 2011-03-30 17:54 Marc Fromm <Marc.Fromm@wwu.edu>
2011-03-30 18:06 ` Re: grant select script Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-03-30 18:06 ` Re: grant select script Bob Lunney <bob_lunney@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: Marc Fromm @ 2011-03-30 17:54 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: grant select script
2011-03-30 17:54 grant select script Marc Fromm <Marc.Fromm@wwu.edu>
@ 2011-03-30 18:06 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2011-03-30 18:06 UTC (permalink / raw)
To: pgsql-admin; Marc Fromm <Marc.Fromm@wwu.edu>
Marc Fromm <Marc.Fromm@wwu.edu> wrote:
> I am working with a script to automate grant select to all tables
> in a database to a user.
You don't say what version you're using, but if it's available, you
might want to use the GRANT SELECT ON ALL TABLES IN SCHEMA syntax.
> 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
It looks like you're not dealing with schema properly. To learn how
to write queries for that, it can help to run psql with the -E
switch and use the \d commands.
-Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: grant select script
2011-03-30 17:54 grant select script Marc Fromm <Marc.Fromm@wwu.edu>
@ 2011-03-30 18:06 ` Bob Lunney <bob_lunney@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Bob Lunney @ 2011-03-30 18:06 UTC (permalink / raw)
To: pgsql-admin; Marc Fromm <Marc.Fromm@wwu.edu>
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.Fromm@wwu.edu> wrote:
From: Marc Fromm <Marc.Fromm@wwu.edu>
Subject: [ADMIN] grant select script
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-03-30 18:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-30 17:54 grant select script Marc Fromm <Marc.Fromm@wwu.edu>
2011-03-30 18:06 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-03-30 18:06 ` Bob Lunney <bob_lunney@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox