pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation abouts

Question

I'm trying to dump my pg db but got these errors please suggest

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation abouts
pg_dump: [archiver (db)] query was: LOCK TABLE public.abouts IN ACCESS SHARE MODE

Answer 1

The user which you're performing your pg_dump as doesn't have permissions on the public schema.

Add permissions if allowed:

GRANT USAGE ON SCHEMA public TO <user>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user>;

Answer 2

This can be a common error, when using a ROLE (user) that could not open the objects to dump them.

Like said before, you can grant to the specific schema that you want to dump, or even use a ROLE with SUPERUSER attribute.

Note that when you are dealing with some cloud database providers, like AWS/RDS you will not receive a user with the SUPERUSER attribute, so you will need to manage to make sure that the one used to dump will have all access needed.

https://www.postgresql.org/docs/current/static/sql-grant.html will show how give GRANT to many objects on your database, but also remember that when restoring you will need to create the database first. Only if you are using pg_dumpall that is not necessary, but you also need to dump the ROLES.