Permission denied in Postgres

Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres.

Then I tried to give all rights to access and modify mydatabase to myuser. I logged into psql as user postgres:

psql -d template1 -U postgres  

and then I ran this query:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser

Now I can use myuser to log, but if I try a simple query I get this error:

ERROR:  permission denied for relation table_name

Am I missing something?

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 INSERToperations to work, but since you asked to "give all rights":

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