Re: grant access

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 19 07:03 am, Manuel Trujillo wrote:
> Hi.
>
> I'm using a postgresql-7.1.3 compiled into a Debian Potato. I need to grant
> access "select" to one user in all tables. How can I make this into "one
> command", without make "grant 1 to x", "grant 2 to x", etc, etc????

I don't think there's one easy way to do this, so I wrote the following function to simplify things:

DROP FUNCTION multi_grant(text,text);
CREATE FUNCTION multi_grant(text, text) RETURNS boolean AS '
DECLARE privilidge ALIAS FOR $1;
user_name ALIAS FOR $2;
row RECORD;
BEGIN FOR row IN SELECT * FROM pg_class WHERE relkind = ''r'' AND relname !~ ''^pg_''
LOOP RAISE NOTICE ''GRANT % ON % TO %'', privilidge, row.relname, user_name;
EXECUTE ''GRANT '' || privilidge || '' ON '' || row.relname || '' TO '' || user_name;
END LOOP;
RETURN ''t'';
END;' LANGUAGE 'plpgsql';
SELECT multi_grant('select', 'public');

I suppose that I should quote_ident(row.relname) and quote_literal(user_name), but I'm lazy.

- --
Andrew G. Hammond mailto:drew(at)xyzzy(dot)dhs(dot)org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjv6yigACgkQCT73CrRXhLFXcACggqnf6CTsBscS3tgx9OtTMfE3
ObYAnAjVGnOP0sLDeCkBluoGGZmv/BJC
=/xzb
-----END PGP SIGNATURE-----