PostgreSQL: pg_dump fails with permission denied

4
votes

Today I have tried to dump my PgSQL database, something I do from time to time without any trouble, but it failed:

borelupo@l5nets02:~$ pg_dump -f spam-20150123.sql -F p -O -C -h x.x.x.x -U borelupo spam
Password:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  permission denied for relation badports
pg_dump: The command was: LOCK TABLE public.badports IN ACCESS SHARE MODE

Now, the user borelupo is owner of the table, how can he get permission denied at all?

spam=> \d
         List of relations
Schema |     Name      | Type  |  Owner
-------+---------------+-------+----------
public | badports      | table | borelupo

What gives? PostgreSQL is 9.1.14.

Edit: When I connect as admin user (postgres), the dumps works OK. But I'd surely like not needing to login into db server, switch users etc.

3
postgresql
I have seen that, but it doesn't help: they talk about having SELECT privilege, but here I'm even the owner so I have all the privileges (or so I believe). - mdw
just change -U borelupo to -U postgres - Vivek S.
Using -U postgres needs being on local machine with the db and being logged in as 'postgres'. Anyway, I think I have found why I'm getting this: apparently, being owner does not automatically mean having all privileges! - mdw
Please, checkout this question with my response, looks like it is the same problem: [stackoverflow.com/questions/39329228/… . - Vinnix

3 Answers

3
votes

I think I've found the problem: Being owner of said table doesn't mean having all privileges. So I really lacked the privileges for given table. My bad.

0
votes

pg_dump is an admin function. So, need to have proper privileges to the user to be allowed to use the function.

Try GRANT select and update access to it. Or, you may login as a superuser to perform the action.

0
votes

The issue for me was that my postgres role was not a superuser; this was fixed via alter role postgres superuser;.