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/ ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
