Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pRDFa-0007S3-Jw for pgsql-general@arkaria.postgresql.org; Sun, 12 Feb 2023 14:22:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pRDFY-0005ce-Uj for pgsql-general@arkaria.postgresql.org; Sun, 12 Feb 2023 14:22:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pRDFY-0005WR-Jb for pgsql-general@lists.postgresql.org; Sun, 12 Feb 2023 14:22:40 +0000 Received: from mta13.doruk.net.tr ([81.21.172.170]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pRDFU-0006U6-5N for pgsql-general@lists.postgresql.org; Sun, 12 Feb 2023 14:22:38 +0000 Received: from cmail19.webkontrol.doruk.net.tr ([212.58.6.99]:53531) by mta13.doruk.net.tr with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1pRDFP-0007Lk-A1 for pgsql-general@lists.postgresql.org; Sun, 12 Feb 2023 17:22:31 +0300 Received: from DESKTOPQCEPGF2 ([176.88.190.218]) by cmail19.webkontrol.doruk.net.tr (12.2.1 build 1 ) with ASMTP id 202302121722221172 for ; Sun, 12 Feb 2023 17:22:22 +0300 From: To: Subject: pg_dump problem with postgres user Date: Sun, 12 Feb 2023 17:22:30 +0300 Message-ID: <000001d93eed$71315510$5393ff30$@1nar.com.tr> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Outlook 16.0 Content-Language: en-us Thread-Index: Adk+7WLC2fnv/3TkRnK6l3cqUnYD5w== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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=FEar=FDs=FDz 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=FEar=FDs=FDz 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=FEar=FDs=FDz 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=FEar=FDs=FDz 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=3D> \l+ =20 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=3D> \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access method | = Size | Description --------+-----------+-------+---------+-------------+---------------+----= --- +------------- public | usbserial | table | counter | permanent | heap | 16 = kB | (1 row) counter=3D> \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: 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