0

I have these logs in my PostgreSQL database log file that appeared after I changed the database password.

2023-09-14 11:57:15.937 UTC [3401773] username@db-name FATAL:  password authentication failed for user "username"
2023-09-14 11:57:15.937 UTC [3401773] username@db-name DETAIL:  Password does not match for user "username" 
Connection matched pg_hba.conf line 96: "host    all             all             127.0.0.1/32            md5"

How can I track where is the source of this request? All of my services are running from docker containers and even when I stopped them the same error keeps showing in PostgreSQL logs.

This is the syslog that I think is related to these requests:

Sep 14 12:43:23 dev supervisord[539]: 2023-09-14 12:43:23,814 INFO exited: laravel-worker_04 (exit status 1; not expected)
Sep 14 12:43:27 dev supervisord[539]: 2023-09-14 12:43:27,025 INFO spawned: 'laravel-worker_04' with pid 3417768
Sep 14 12:43:28 dev supervisord[539]: 2023-09-14 12:43:28,229 INFO success: laravel-worker_04 entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)

And, these logs keep repeating even if I restart the system. I want to know how can I find the file that causes these requests even after restarting. I checked crontab and services and there was no cronjob or service related to this.

Update

I can find the process ID from PostgreSQL connections. And this is the output of ps aux pid:

postgres: 12/main: username database 127.0.0.1(s-port) idle
Improve this question

3 Answers 3

Reset to default
2

Is there any way to find from where a command is executed

in general the simple answer is yes and it will be in /var/log/audit/audit.log as a result of the auditd subsystem in linux. But you will have to tailor the /etc/audit/rules.d/audit.rules file to capture such a thing. I don't believe every command is captured by default... that would blow up the audit.log after an hour.

In your case you would put a watch -w on a folder location to capture such a command being run, or call it out that command specifically as a rule, and then in audit.log search on that command. There will be an exe= field on an audit line showing that command being run, and then either on that line or subesquent lines a cwd= current working directory of where that command was executed from.

Here are some sample /etc/audit/rules.d/audit.rules

-a always,exit -F path=/usr/sbin/semanage -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/sbin/setsebool -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/bin/chcon -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/sbin/setfiles -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/bin/passwd -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/sbin/unix_chkpwd -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/bin/gpasswd -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/bin/chage -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/sbin/userhelper -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/bin/su -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/bin/sudo -F auid>=1000 -F auid!=unset -k privileged-priv_change

-w /var/run/faillock -p wa -k logins
-w /var/log/lastlog -p wa -k logins
-w /etc/sudoers -p wa -k privileged-actions
-w /etc/sudoers.d/ -p wa -k privileged-actions

Complete /etc/audit/rules.d/audit.rules file you can use

Make the below the entire /etc/audit/rules.d/audit.rules text file; back up your existing one to audit.rules.orig. Then do a service auditd restart followed by a service auditd status -l to see if you had any syntax errors the status should respond with active (running) and basic info nothing error like.

## First rule - delete all
-D

## Increase the buffers to survive stress events.
## Make this bigger for busy systems
# -b 8192

# set from 8k to 1mb
-b 1048576


# 2 is shutdown, 1 is runlevel 1
-f 1


-a always,exit -F arch=b32 -S execve -C uid!=euid -F euid=0 -k setuid
-a always,exit -F arch=b64 -S execve -C uid!=euid -F euid=0 -k setuid
-a always,exit -F arch=b32 -S execve -C gid!=egid -F egid=0 -k setgid
-a always,exit -F arch=b64 -S execve -C gid!=egid -F egid=0 -k setgid

-a always,exit -F arch=b32 -S chown -F auid>=1000 -F auid!=unset -k perm_mod
-a always,exit -F arch=b64 -S chown -F auid>=1000 -F auid!=unset -k perm_mod

-a always,exit -F arch=b32 -S chmod -F auid>=1000 -F auid!=unset -k perm_mod
-a always,exit -F arch=b64 -S chmod -F auid>=1000 -F auid!=unset -k perm_mod


-a always,exit -F arch=b32 -S setxattr -F auid>=1000 -F auid!=unset -k perm_mod
-a always,exit -F arch=b64 -S setxattr -F auid>=1000 -F auid!=unset -k perm_mod

-a always,exit -F arch=b32 -S open -F exit=-EPERM -F auid>=1000 -F auid!=unset -k access
-a always,exit -F arch=b32 -S open -F exit=-EACCES -F auid>=1000 -F auid!=unset -k access
-a always,exit -F arch=b64 -S open -F exit=-EPERM -F auid>=1000 -F auid!=unset -k access
-a always,exit -F arch=b64 -S open -F exit=-EACCES -F auid>=1000 -F auid!=unset -k access

-a always,exit -F path=/usr/sbin/semanage -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/sbin/setsebool -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/bin/chcon -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/sbin/setfiles -F auid>=1000 -F auid!=unset -k privileged-priv_change

-w /var/run/faillock -p wa -k logins
-w /var/log/lastlog -p wa -k logins

-a always,exit -F path=/usr/bin/passwd -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/sbin/unix_chkpwd -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/bin/gpasswd -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/bin/chage -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/sbin/userhelper -F auid>=1000 -F auid!=unset -k privileged-passwd
-a always,exit -F path=/usr/bin/su -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/bin/sudo -F auid>=1000 -F auid!=unset -k privileged-priv_change

-w /etc/sudoers -p wa -k privileged-actions
-w /etc/sudoers.d/ -p wa -k privileged-actions

-a always,exit -F path=/usr/bin/newgrp -F auid>=1000 -F auid!=unset -k privileged-priv_change
-a always,exit -F path=/usr/bin/chsh -F auid>=1000 -F auid!=unset -k privileged-priv_change

-a always,exit -F arch=b32 -S mount -F auid>=1000 -F auid!=unset -k privileged-mount
-a always,exit -F arch=b64 -S mount -F auid>=1000 -F auid!=unset -k privileged-mount
-a always,exit -F path=/usr/bin/mount -F auid>=1000 -F auid!=unset -k privileged-mount

-a always,exit -F path=/usr/bin/umount -F auid>=1000 -F auid!=unset -k privileged-mount
-a always,exit -F path=/usr/sbin/postdrop -F auid>=1000 -F auid!=unset -k privileged-postfix
-a always,exit -F path=/usr/sbin/postqueue -F auid>=1000 -F auid!=unset -k privileged-postfix
-a always,exit -F path=/usr/libexec/openssh/ssh-keysign -F auid>=1000 -F auid!=unset -k privileged-ssh
-a always,exit -F path=/usr/bin/crontab -F auid>=1000 -F auid!=unset -k privileged-cron
-a always,exit -F path=/usr/sbin/pam_timestamp_check -F auid>=1000 -F auid!=unset -k privileged-pam
-a always,exit -F arch=b32 -S create_module -k module-change
-a always,exit -F arch=b64 -S create_module -k module-change
-a always,exit -F arch=b32 -S init_module -k module-change
-a always,exit -F arch=b64 -S init_module -k module-change


-a always,exit -F arch=b32 -S delete_module -k module-change
-a always,exit -F arch=b64 -S delete_module -k module-change

-a always,exit -F arch=b32 -S unlink -F auid>=1000 -F auid!=unset -k delete
-a always,exit -F arch=b64 -S unlink -F auid>=1000 -F auid!=unset -k delete

-w /usr/bin/kmod -p x -F auid!=unset -k module-change
-w /etc/passwd -p wa -k identity
-w /etc/group -p wa -k identity
-w /etc/gshadow -p wa -k identity
-w /etc/shadow -p wa -k identity
-w /etc/security/opasswd -p wa -k identity
Improve this answer
4
  • Sorry I'm not a pro. How can I find the folder location that you said? I don't even know the real command that causes these logs. Commented Sep 16, 2023 at 9:22
  • 1
    by using an audit rule having -k whatever, where my example above has logins and privileged-actions you would do something unique like a -k ABCXYZ then search for ABCXYZ within /var/log/audit/audit.log to try and find what you are looking for. I will post a complete /etc/audit/rules.d/audit.rules file which you can add one -w type entry that would be related to your PostGreSQL which would hopefully capture what you're looking for.... your throwing darts at a wall but at least you're facing the generally correct direction. Commented Sep 18, 2023 at 17:59
  • I copied all your rules but I don't have any log in the audit log file related to the postgres user. I do get some logs about user logins but not related to the PostgreSQL logs that I said in the question. Commented Sep 20, 2023 at 7:12
  • I updated the question with the ps aux output. Commented Sep 20, 2023 at 11:20
0

If you add %h to your Postgresql log config you'll get the IP address in those logs. I think this can't be done without a restart of the DBMS. Alternatively you could log new connections from iptables and reconcile the log data. Assuming a policy of accept and no existing rules....

iptables -A INPUT -p tcp -m state --state ESTABLISHED,RELATED -j ACCEPT
iptables -A INPUT -j LOG --log-prefix ' INBOUND TCP ' --log-level 4
Improve this answer
2
  • I added the log entry that shows the connection is coming from 127.0.0.1/32 which means it is an internal process I think. Commented Sep 14, 2023 at 13:08
  • 1
    Then use auditd to track back to the executable + user if you don't have logs from a client showing a failed connection. Commented Sep 14, 2023 at 13:23
0

I finally found that! The second log that I posted was about supervisord service. But I didn't know at that time what is supervisord. When I checked the supervisord processes I found that it was a process named laravel-worker that was running a queued job in the background.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.