Permission denied in Postgres

Question

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? Can you help me solve that?

Answer 1

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.

Answer 2

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.)

Answer 3

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.