pg_dump problem with postgres user

Started by Ertan Küçükoğluabout 3 years ago4 messagesgeneral
Jump to latest
#1Ertan Küçükoğlu
ertan.kucukoglu@1nar.com.tr

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

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: Ertan Küçükoğlu (#1)
Re: pg_dump problem with postgres user

On Sun, Feb 12, 2023 at 7:22 AM <ertan.kucukoglu@1nar.com.tr> wrote:

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.

Given that you have "trust" in your pg_hba.conf this point seems immaterial.

BTW, I thought postgres user has permission to read/write everything in all
databases. Am I missing something obvious?

Run \du+ and see what all roles are presently installed in the system and
what their permissions are. I strongly suspect your server has been hacked
and you'll find roles there that don't belong, and the postgres role
stripped of its superuser status.

David J.

#3Ertan Küçükoğlu
ertan.kucukoglu@1nar.com.tr
In reply to: David G. Johnston (#2)
Re: pg_dump problem with postgres user

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

#4Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Ertan Küçükoğlu (#3)
Re: pg_dump problem with postgres user

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