grant XXX on * ?

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.

simple example:

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". 🙂

17 thoughts on “grant XXX on * ?”

  1. Nice suggestions. I use similar tricks myself.

    I guess the only thing I would say is that I prefer using the information_schema views instead of pg_tables because it makes the trick more portable. I can use the same trick in other databases that support information_schema like in SQL Server and Oracle to add fields, update fields etc as you said.

    So instead of
    SELECT tablename from pg_tables WHERE schemaname = ‘public’;

    I would do

    SELECT table_name from information_schema.tables WHERE table_schema = ‘public’;

  2. Regina Obe:
    “I can use the same trick in other databases that support information_schema like in SQL Server and Oracle…”

    Oracle 10gR2 does not have the INFORMATION_SCHEMA.

    Does 11g have it?

  3. FYI: I found this article unreadable and the links on the right hand side to be almost invisible until I switched off your stylesheet. Please consider using 1em/100% for your font size and a colour scheme other than grey on black.

  4. Rodrigo,

    I think you are right about Oracle. I stand corrected. Sorry for the misinformation.

    I don’t know what made me think that just because SQL Server 2005, MySQL 5,IBM DB2, and PostgreSQL support information_schema that Oracle would even consider supporting such a thing.

  5. Slight correction – I’m not sure about DB2. Db2 may not support information_schema. But I’ve used information_schema in MySQL 5, SQL Server 2005, and even SQL Server 2000 and those all support the information_schema.

    For SQL Server – its the recommended way to go since the underlying system tables may change.

    For PostgreSQL I think its much easier to for things like select ing columns and data types since the joins you’d have to do against the pg_catalog to get the same basic info you get from querying information_schema.columns table is a lot more.

  6. Nice post on the scripting technique. One thing to consider, though, is that, with Postgres, you really should use pg_class and pg_namespace so that you can include views and sequences in your grants. For example, if all you use is pg_tables or information_schema.tables then you’ll get bitten when you try to insert on a table that uses a sequence for id generation. I use a one liner shell script (one each for grant and revoke).

    grant.sh:
    #!/bin/sh
    # $1 -> privilege(s)
    # $2 -> rolename
    # $3 -> dbname

    psql -t -c “SELECT ‘GRANT $1 ON public.’ || t.relname || ‘ TO $2;’ from pg_class t, pg_namespace s WHERE t.relkind IN (‘r’, ‘v’, ‘S’) AND t.relnamespace=s.oid AND s.nspname=’public’;” $3 | psql $3

  7. Pingback: BostonGIS Blog

  8. from irc:

    19:44 < jjore-w> depesz: IF that's your blog, don't you want TO be USING quote_ident() ON the identifiers you're accepting?
19:48 < depesz> jjore-w: what identifiers?
19:50 < jjore-w> depesz: select execute('alter_table '||quote_ident(tablename)|| ...?
19:50 < jjore-w> Those identifiers?
19:50 < depesz> jjore-w: technically - sure. on the other hand - how often do you have table names with ' OR SPACE IN its name?
19:51 < jjore-w> depesz: Uh... it happens.
19:52 < jjore-w> I figure it's better not to be sorry.
19:52 < PJMODOS> depesz: what about mixed case ?
19:52 < PJMODOS> not that I am using it
19:52 < depesz> jjore-w: never happened to me.
19:53 < depesz> PJMODOS: point taken.

    so. if you can have “strange” characters in your table names – always use quote_ident.

    like in:

    SELECT EXECUTE('create table backup.' || quote_ident(tablename) || ' as select * from ' || quote_ident(tablename) || ';') FROM pg_tables WHERE schemaname = 'public';

  9. An aside:
    Don’t GRANT/REVOKE to actual USERs (ROLEs with login). Instead GRANT/REVOKE to GROUPs (ROLEs without login) and make users members of groups with the needed privileges.

    If you do this even when you don’t think it’s necessary, you will save yourself untold headaches down the road.

    Also, superusers (ROLEs with the SUPERUSER attribute) don’t need any explicit GRANTs to access database objects, as pg never checks permissions for SUPERUSERs.

  10. In oracle.
    SELECT ‘GRANT SELECT,INSERT,UPDATE ON ‘|| table_name ||’ FOR user_test;’ FROM user_tables;
    or

    SELECT ‘GRANT SELECT,INSERT,UPDATE ON ‘|| table_name ||’ FOR user_test;’ FROM dba_tables WHERE owner=’user_test_123333′;

  12. Thanks for this great tutorial.

    Im migrating from mysql to postgresql; the postgres string concatenation you used (SELECT func(‘…’ || tablename || ‘…’)) to execute a function looks to me very likely a sql inection 😉

  13. @Daniel:
    sure. If the tablename can contain something bad, you should use quote_ident function.
    But do you really have table names that are valid, and malicious, sql statements?

  16. Pingback: Solution: Modify OWNER on all tables simultaneously in PostgreSQL #fix #programming #development | Good Answer
  17. Pingback: Fix: Modify OWNER on all tables simultaneously in PostgreSQL #fix #development #dev | IT Info

Comments are closed.