Thread: pg_dump problem with postgres user

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

Hello,

My service provider spam filter just blocked your message and so I send a
separate one.

Your suspicion seems like correct. Postgres user is no more a superuser.
Seems like they added postgresql_user and made it superuser.

I thought I only allow access with certificate to the database.
OS access is limited to single IP. Probably this is the weak point.
I should read more about security in general.

postgres=> \du+
                                        List of roles
    Role name    |                   Attributes                    | Member
of | Description
-----------------+-------------------------------------------------+--------
---+-------------
postgres        | Create role, Create DB, Replication, Bypass RLS | {}
|
 postgresql_user | Superuser                                       | {}
|

postgres=>

Is it possible to put everything back to normal?
Can I use single user mode to change passwords and permissions?

Thanks & Regards,
Ertan

On 2/12/23 13:10, ertan.kucukoglu@1nar.com.tr wrote:
> Hello,
> 
> My service provider spam filter just blocked your message and so I send a
> separate one.
> 
> Your suspicion seems like correct. Postgres user is no more a superuser.
> Seems like they added postgresql_user and made it superuser.

Who is they?


> Is it possible to put everything back to normal?

If it was some outside entity that you don't know that changed the 
permissions then you can't trust the instance any more.

> Can I use single user mode to change passwords and permissions?
> 
> Thanks & Regards,
> Ertan
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com