alot of role does not exist in logs

[modernNeo]

I have a dockerized postgres running and I happened to get the logs today and saw something odd.

 tail -1000 login_attempts.txt | grep "does not exist"
2023-09-06 10:45:18.260 UTC [16715] DETAIL:  Role "pgdbadm" does not exist.
2023-09-06 10:45:18.969 UTC [16717] DETAIL:  Role "pgdbadm" does not exist.
2023-09-06 10:45:19.671 UTC [16719] DETAIL:  Role "pgdbadm" does not exist.
2023-09-06 10:45:29.581 UTC [16747] DETAIL:  Role "cloudsqladmin" does not exist.
2023-09-06 10:45:30.291 UTC [16749] DETAIL:  Role "cloudsqladmin" does not exist.
2023-09-06 10:45:31.002 UTC [16751] DETAIL:  Role "cloudsqladmin" does not exist.
2023-09-06 10:45:31.700 UTC [16753] DETAIL:  Role "cloudsqladmin" does not exist.
2023-12-20 08:53:11.592 UTC [19316] DETAIL:  Role "psql" does not exist.
2023-12-20 08:53:23.923 UTC [19319] DETAIL:  Role "psql" does not exist.
2023-12-20 08:53:36.501 UTC [19322] DETAIL:  Role "psql" does not exist.
2023-12-22 03:23:42.637 UTC [22823] DETAIL:  Role "root" does not exist.
2023-12-23 23:43:34.298 UTC [27281] FATAL:  role "root" does not exist
2023-12-23 23:43:46.986 UTC [27283] FATAL:  role "root" does not exist

What is strange is I can't think of why those would be in the logs.

if someone is trying to gain access to the system, I would figure they would be blocked by the sshd login system.

But the above logs indicate that they were somehow able to get past the sshd login system but were unable to gain access to the dockerized database? which is weird cause it's not a secret that every dockeried postgres container has the user postgres so I can't imagine why someone would try the above logins instead?

Is there a documented reason why a dockerized postgres instance might have so many Role does not exist in it's logs?

[LaurentGoderre]

The cloudsqladmin seems to be associated with the Google Cloud managed Postgres service. Is it possible that the data was imported from there or that a Google Postgres client is trying to access it?

[yosifkit]

It does look like a script attempting common PostgreSQL users. Did you expose your postgres container to the internet (-p, ports:, etc)? If so, then anyone that can hit the host IP has direct access to attempt to login. I'd recommend against using -p, -P or ports: when running a database container. Use the network access granted from being on the same docker network and not from a remote server unless absolutely necessary (and it should be protected with things like AWS security groups to prevent unauthorized access to those database ports).

sshd on the host has nothing to do with the postgresql server running in the container.

[modernNeo]

@yosifkit

Did you expose your postgres container to the internet (-p, ports:, etc)? If so, then anyone that can hit the host IP has direct access to attempt to login.

I mean, yea I do

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
27ea045a2dc5        postgres:alpine     "docker-entrypoint.s…"   XXXXXXXXXXX         Up 3 months         0.0.0.0:5432->5432/tcp   csss_site_db

but how can someone attempt to access the database if they can't even log into the system?

[yosifkit]

but how can someone attempt to access the database if they can't even log into the system?

They can just attempt to authenticate through the PostgreSQL API/protocol on the exposed port; there is no need to be a local user first.

[modernNeo]

@yosifkit

They can just attempt to authenticate through the PostgreSQL API/protocol on the exposed port; there is no need to be a local user first.

Well. that's interesting, I had no idea. Can you link me to documentation on how someone does that?

Use the network access granted from being on the same docker network and not from a remote server unless absolutely necessary (and it should be protected with things like AWS security groups to prevent unauthorized access to those database ports).

I'd really love to but the application that is using the database is not dockerized. So I don't know if there is a way to secure my database if it has to be available to non-dockerized applications 😞

[yosifkit]

Well. that's interesting, I had no idea. Can you link me to documentation on how someone does that?

That's just psql -h ip.address.or.hostname -u common-postgresql-user (https://www.postgresql.org/docs/current/app-psql.html) from any server with psql installed (maybe with a -p if the server is running on a non-default port). Or via a PostgreSQL library in any language (c, go, node, php, python, rust, etc).

I'd really love to but the application that is using the database is not dockerized. So I don't know if there is a way to secure my database if it has to be available to non-dockerized applications 😞

Choosing a strong password will be the first important defense. The -p on docker run will usually bypass any locally installed firewall rules (like ufw or firewalld) so they won't offer the protection that you might expect. Using a VPC or AWS security group can add an extra layer of protection to limit network access of the Database host to a specific set of allowed IPs (like the hosts running your non-dockerized applications).

[modernNeo]

That's just psql -h ip.address.or.hostname -u common-postgresql-user (https://www.postgresql.org/docs/current/app-psql.html) from any server with psql installed (maybe with a -p if the server is running on a non-default port). Or via a PostgreSQL library in any language (c, go, node, php, python, rust, etc).

That's an...interesting design choice. allowing you to connect to a DB when you don't have access to the host.

Choosing a strong password will be the first important defense.

It's a randomized password so I am not too concerned there, although if I have to consider this attack vector, maybe I will make it longer. Didn't know you can bypass user authentication when accessing a database on a server when I setup the password.

thanks for the info though @yosifkit !

[modernNeo]

Using a VPC or AWS security group can add an extra layer of protection to limit network access of the Database host to a specific set of allowed IPs (like the hosts running your non-dockerized applications).

Maybe. Considering its a DB for a non-profit, I doubt they can afford those kinds of setups 😅

[yosifkit]

Hopefully you've been able to secure your database. Going to close since there isn't something we can change in the image.