Last change 25/04/2014

I just started playing around with postgre... and stumbled around on common errors

Grant a user access rights

I created a new Database, but then wanted another user to have access as well. Creating a new user was pretty straight foreward. But then I kept getting errors like

ERROR: permission denied for sequence

To get around this I needed to dig quite deeply... It wasn't really explained in the manual, but then I found it. It consists of two parts:

Grant access to the tables of a database to a user
Grant access to all sequences

Unfortunately the documentation at http://www.postgresql.org/docs/current/interactive/sql-grant.html was not helpful.

I finally found the solution in http://suwala.eu/blog/2011/04/16/grant-all-privileges-all-tables-and-sequences-post/ and http://serverfault.com/questions/60508/grant-select-to-all-tables-in-postgresql

 
# grant access to user "NEWUSER"
postgres# psql DBNAME 
DBNAME=>  grant all on all tables in schema DBNAME  to NEWUSER;

# grant access to sequence (this will create and execute a script in /tmp)

a
o /tmp/sqlscript
SELECT 'GRANT ALL ON '  ||  relname || ' TO NEWUSER ;' FROM pg_class where relkind='S';
o
i /tmp/sqlscript