1

For security reasons, we've been asked to rename our postgres user in our database. We've made the change in our staging environment and bounced PostgreSQL. We're now seeing an error in the log on server start up. We thought it was related to auto-vacuuming but we've since turned that off and we are still seeing the error:

< 2016-01-19 13:42:55.910 EST    >LOG:  database system was shut down at 2016-01-19 13:42:43 EST
< 2016-01-19 13:42:55.917 EST    >LOG:  database system is ready to accept connections
< 2016-01-19 13:42:56.869 EST [unknown] [unknown] [unknown] >LOG:  connection received: host=[local]
< 2016-01-19 13:42:56.871 EST postgres postgres [unknown] >LOG:  connection authorized: user=postgres database=postgres
< 2016-01-19 13:42:56.871 EST postgres postgres [unknown] >FATAL:  role "postgres" does not exist

I'm still able to login using my user account and our app is also able to login.

I'd like to be able to be sure nothing else is broken (and eliminate this error message) before pushing this change to our production environment.

FWIW, I see the same error in the log after su - postgres and then running psql. It looks like there may be some process that's assuming that peer logins to the database from localhost are allowed. How would I identify this process and fix it?

This is on Cent OS 7 and PostgreSQL 9.3. PostgreSQL is being started via a service using the default postgresql-9.3.service file.

Here's the output from systemctl status postgresql-9.3:

postgresql-9.3.service - PostgreSQL 9.3 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.3.service; enabled)
   Active: active (running) since Tue 2016-01-19 13:42:56 EST; 5min ago
  Process: 23847 ExecStop=/usr/pgsql-9.3/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
  Process: 10944 ExecReload=/usr/pgsql-9.3/bin/pg_ctl reload -D ${PGDATA} -s (code=exited, status=0/SUCCESS)
  Process: 23861 ExecStart=/usr/pgsql-9.3/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 23856 ExecStartPre=/usr/pgsql-9.3/bin/postgresql93-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 23866 (postgres)
   CGroup: /system.slice/postgresql-9.3.service
           ├─23866 /usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data
           ├─23867 postgres: logger process   
           ├─23869 postgres: checkpointer process   
           ├─23870 postgres: writer process   
           ├─23871 postgres: wal writer process   
           ├─23872 postgres: archiver process   
           └─23873 postgres: stats collector process   

Jan 19 13:42:55 snapp-db1.mobdev.local systemd[1]: Starting PostgreSQL 9.3 database server...
Jan 19 13:42:55 snapp-db1.mobdev.local pg_ctl[23861]: < 2016-01-19 13:42:55.907 EST    >LOG:  redirecting log output to logging c...rocess
Jan 19 13:42:55 snapp-db1.mobdev.local pg_ctl[23861]: < 2016-01-19 13:42:55.907 EST    >HINT:  Future log output will appear in d...gres".
Jan 19 13:42:56 snapp-db1.mobdev.local systemd[1]: Started PostgreSQL 9.3 database server.
Hint: Some lines were ellipsized, use -l to show in full.
  • 5
    Security through obscurity? How silly – Philᵀᴹ Jan 19 '16 at 22:00
  • you need to run psql -U <new_user_name> to login as the alternate user. – dizzystar Jan 20 '16 at 0:38
  • It's not someone running psql from the command line that is causing the error in the log. It is showing up every time the server starts. It's as if the process of starting the server through systemctl is also running some kind of script that tries to login to the db and perform some action but is still attempting to run as postgres. – Chris Williams Jan 20 '16 at 2:00
1

So after much more research and help from users here, it sounds like renaming your postgres DB user isn't a good idea. You'll need to backup, re-initdb, restore, and some other stuff.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.