Permission denied in Postgres

You've granted CREATE, CONNECT, and TEMPORARY privileges on the database to myuser but you haven't granted SELECT and INSERT table privileges yet. You'll need something like:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

In addition you need privileges on sequences if you have any serial columns or other column defaults drawing from sequences. Generally, the USAGE privilege is be enough for INSERT operations to work, but since you asked to "give all rights":

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

Details on GRANT in the manual.


Use the command supplied by @Gord for already existing objects.

You will probably want to grant DEFAULT PRIVILEGES, too. So your user myuser can access future objects automatically, too.

Can be done per schema:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myuser;

If you omit the schema, it applies to the whole database:

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO myuser;

Only applies to objects created by the role specified (defaulting to the role that executes this command):

ALTER DEFAULT PRIVILEGES FOR ROLE administrator GRANT IN SCHEMA public ... ;

Available since PostgreSQL 9.0.
Don't forget to GRANT privileges on SEQUENCES in addition if you have any. (For instance as source for default values in a serial column.)


While the other answers to this question are correct, allow me to propose another option. When a database is restored, you may be able to control the user the database is restored as. I am unsure about the specifics of how to do this with pgAdmin3, but the pg_dump and pg_restore utilities include specific options to allow this to be done.

If you use pg_dump --no-owner then the dump file will not include any ownership restoration. When you restore a dump created using --no-owner, the user you used to do the restore will own all of the objects. If you have a custom-format dump file and are using pg_restore, you can use the --no-owner option with pg_restore to have it skip over any ownership restoration when restoring the database.

Note that this will require the user doing the restore to have the necessary rights on the database to create the objects which exist in the dump file. Generally speaking, granting "CREATE" at the database level to the user doing the restore will be sufficient.