Permission denied in Postgres

I added the user myuserto 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 CREATECONNECT, 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;

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.