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
