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
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 |