one of the more common problems new users have with postgresql (especially those that came from mysql background), is the lack of easy way to grant/revoke/do-something with many objects (tables/sequences/views) at once.
there are number of pages that deal with the problem, let's just name some from #postgresql infobots:
now, both of these pages have their benefits, but i'd like to show something simpler, yet (perhaps) more powerful.
instead of giving you the fish (figuratively speaking) i will give you the net and the skills so you'll be able to do the magic yourself.
first let me state one thing: i don't think it's good that we (postgresql) don't have the ability to do it with simple grant/revoke command.
it would be really cool to have it. but i'll not code it ( for various of reasons, including lack of c-coding skills 🙂 ). and i dont know anyone willing to.
why nobody wants to code it? because it is not really that much of a problem. and there are more important things to do.
now, how to do the job?
we'll hack the problem with simple (not necessarily nice) solution.
first – let's assume we want to grant select/insert/update/delete rights on all tables in current database to user depesz.
so, we want equivalent of:
grant select, insert, update, delete on * to depesz;
first of all – you have to understand that this is not exactly what you want to do. you want to grant on all user tables. system tables should (generally) be left alone.
so, let's get the list of tables that we want to act on:
select tablename from pg_tables where schemaname = 'public';
now, if we would need to do it one-by-one, we would have to write:
grant select, insert, update, delete on table_a to depesz; grant select, insert, update, delete on table_b to depesz; ... grant select, insert, update, delete on table_n to depesz;
hmm … so, let's modify our tablename-fetching query to generate appropriate sqls:
select 'grant select, insert, update, delete on ' || tablename || ' to depesz;' from pg_tables where schemaname = 'public';
now, we just need a way to make postgres run the queries. there are 2 basic approaches, shell-based, or (simple) function based.
first, let's check the shell based:
psql -qAt -c "select 'grant select, insert, update, delete on ' || tablename || ' to depesz;' from pg_tables where schemaname = 'public'" | psql
so, what does it do?
it runs our query and outputs it's result to stdout (usually screen). -qAt means that only generated grants will be returned, no “frames", no comments, just the real, important code. then, output of first psql is “piped" to second psql, which effectively runs the sqls.
i think it's simple. it's definitely not elegant, but it is in harmony with general unix way of doing things by connecting many commands by piping data (think: sed, awk, cut, cat, grep, perl, …).
but, can it be done without using shell tricks?
yes. but we will need one (very simple) plpgsql function:
create function execute(text) returns void as $BODY$BEGIN execute $1; END;$BODY$ language plpgsql;
this functions gets text parameter, and executes it as if it was sql query. something like eval() in another languages.
so, now i can:
select execute('grant select, insert, update, delete on ' || tablename || ' to depesz;') from pg_tables where schemaname = 'public';
that's all. everything done.
now – this approach (based on simplistic function or shell “double-psql-piping") has one very big benefit – it's extremely flexible.
if you want to do something on only some of the tables – add where to the query.
if you want something different than grant/revoke – fine, do it.
select execute('create table backup.' || tablename || ' as select * from ' || tablename || ';') from pg_tables where schemaname = 'public';
will make copy of all tables in public schema to backup schema.
ever need to add field to all tables named tmp* ? simple:
select execute('alter table ' || tablename || ' add column id int8;') from pg_tables where schemaname = 'public' and tablename like 'tmp%';
possibilities are endless. you'll just have to fight that instinct saying “oh my god, it's so ugly". 🙂