Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL: pg_dump fails with permission denied

Tags:

postgresql

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.

like image 425
mdw Avatar asked Jan 23 '15 09:01

mdw


2 Answers

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.

like image 114
mdw Avatar answered Oct 22 '22 12:10

mdw


The issue for me was that my postgres role was not a SUPERUSER; this was fixed via ALTER ROLE postgres SUPERUSER;.

like image 2
Benjie Avatar answered Oct 22 '22 12:10

Benjie