Martijn van Oosterhout wrote:
> I've had some fun in the past where I've had to grant a lot of tables and
> other similar system commands. Unfortunatly, you can't use queries to fill
> in fields for you. Anyway, I've implemented a patch which allows the
> following:
>
> grant select on ":2" to ":1"
> \for select usename, relname from pg_catalog.pg_user, pg_catalog.pg_class where relname not like 'pg_%';
That's definitely a useful thing to do, but I'm not sure I like your
syntax. As someone else mentioned, the ":2" is confusing; it's like a
bind variable, but isn't. And real bind variables don't work, as you are
substituting identifiers, not literals.
You're not completely out in the cold doing something like this without
a patch. Right now, I believe you can do something like (in Oracle
PL/SQL-ish syntax; it's more familiar to me):
declare
grantcursor cursor as
select usename, relname
from pg_catalog.pg_user, pg_catalog.pg_class
where relname not like 'pg_%';
begin
for grantline in grantcursor loop
execute immediate 'grant select on '
|| quoteident(grantline.relname)
|| ' to ' || quoteident(grantline.usename) || '"';
end loop;
end;
(I'm not sure how to do an anonymous plpgsql block. Anyone?)
This is more wordy, but should work.
Scott