pg_dump problem with postgres user - Mailing list 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@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@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@app:~$ su -
Parola:
root@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@app:~# su - postgres
postgres@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@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@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@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