Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 "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?

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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.

share|improve this answer

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

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

Can be done per schema:

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

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

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO administrator;

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

ALTER DEFAULT PRIVILEGES FOR staff 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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.