grant all privileges to all tables in a database

I'm a refugee from MySQL due to license restrictions.
With MySQL, i was used to do "GRANT ALL PRIVILEGES ON dbname.* TO
username" to allow a certain user to do anything within a given
database. This is useful when using applications that run on a SQL
backend, e.g. a blog or a logging server or something like that - one
just creates a dedicated database and lets the application rule supreme.

On PostgreSQL, i lost about half a day trying to figure it out. I'm
posting this message to help others in my situation. I googled for an
answer, but everything that i've found was unhelpful. Hopefully this
mailing list is indexed by Google.

So, you have a database named dbname and a user named username. You want
to give the user all privileges on that particular database.
On MySQL, it's enough to do this:

GRANT ALL PRIVILEGES ON dbname.* TO username [IDENTIFIED BY 'password'];

On PostgreSQL, you have to give it privileges not only to the database,
but to all components within (tables, sequences and whatnot). The
following three commands will grant those privileges, first to the
database, then to the tables, then to the sequences.

echo "GRANT ALL ON DATABASE dbname TO username;" | psql -d dbname

psql -At -d dbname -c "SELECT 'GRANT ALL ON '||tablename||' TO
username;' FROM pg_tables WHERE schemaname='public';" | psql -d dbname

psql -At -d dbname -c "SELECT 'GRANT ALL ON '||c.relname||' TO
username;' FROM pg_class c JOIN pg_namespace n ON (n.oid=c.relnamespace)
WHERE c.relkind='S' AND n.nspname='public';" | psql -d dbname

It seems to work fine on pgsql version 8.
Of course, after creating new tables and stuff, you may have to re-run
the last two commands. That is not necessary on MySQL.

Thanks to AndrewSN who helped me on IRC.

--
Florin Andrei

http://florin.myip.org/