pg_dump problem with postgres user

From: <ertan(dot)kucukoglu(at)1nar(dot)com(dot)tr>
To: <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: pg_dump problem with postgres user
Date: 2023-02-12 14:22:30
Message-ID: 000001d93eed$71315510$5393ff30$@1nar.com.tr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I am using postgresql 14.6. PostgreSQL 15.1 is also installed in my system.
I just recognize that my scheduled backups are failing.
I am using postgres user for backup user and .pgpass file for no password
prompt.
Result is same even I force a password prompt.

Google results are I get are mostly for directory permission problems people
are having.

What I tried is as following.

ek(at)app:~$ pg_dump -U postgres -h localhost -p 5432 -f test.bak counter
pg_dump: hata: sorgu başarısız oldu: ERROR: permission denied for table
usbserial
pg_dump: detail: Query was: LOCK TABLE public.usbserial IN ACCESS SHARE MODE
ek(at)app:~$ pg_dump -U postgres -h localhost -p 5432 -W -f test.bak counter
Parola:
pg_dump: hata: sorgu başarısız oldu: ERROR: permission denied for table
usbserial
pg_dump: detail: Query was: LOCK TABLE public.usbserial IN ACCESS SHARE MODE
ek(at)app:~$ su -
Parola:
root(at)app:~# pg_dump -U postgres -h localhost -p 5432 -f test.bak counter
pg_dump: hata: sorgu başarısız oldu: ERROR: permission denied for table
usbserial
pg_dump: detail: Query was: LOCK TABLE public.usbserial IN ACCESS SHARE MODE
root(at)app:~# su - postgres
postgres(at)app:~$ pg_dump -U postgres -h localhost -p 5432 -f test.bak counter
pg_dump: hata: sorgu başarısız oldu: ERROR: permission denied for table
usbserial
pg_dump: detail: Query was: LOCK TABLE public.usbserial IN ACCESS SHARE MODE
postgres(at)app:~$ psql counter
psql (15.1 (Debian 15.1-1.pgdg100+1), server 14.6 (Debian 14.6-1.pgdg100+1))
Type "help" for help.

counter=> \l+

List of databases
Name | Owner | Encoding | Collate | Ctype | ICU
Locale | Locale Provider | Access privileges | Size | Tablespace |
Description
---------------+------------+----------+-------------+-------------+--------
----+-----------------+-----------------------+---------+------------+------
--------------------------------------
counter | counter | UTF8 | tr_TR.UTF-8 | tr_TR.UTF-8 |
| libc | | 8593 kB | pg_default |
(other databases are cropped from list)

counter=> \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size
| Description
--------+-----------+-------+---------+-------------+---------------+-------
+-------------
public | usbserial | table | counter | permanent | heap | 16 kB
|
(1 row)

counter=> \q
postgres(at)app:~$

Relevant lines from pg_hba.conf file is as following
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust

# IPv6 local connections:
host all all ::1/128 md5

I have following line in my .pgpass file
ek(at)app:~$ cat .pgpass
localhost:5432:*:postgres:<my own password>

This was all working. I cannot remember what I changed, when I changed.
Old backups are automatically deleted after certain days and that prevents
me pointing to exact date of change.

BTW, I thought postgres user has permission to read/write everything in all
databases. Am I missing something obvious?

Any help is appreciated.

Thanks & Regards,
Ertan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2023-02-12 15:37:20 Re: pg_dump problem with postgres user
Previous Message Georg H. 2023-02-12 09:24:04 Re: psql "\d" no longer working