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.