2

I feel confident I understand what's involved at the db level:

  • If dropping, I'd need to create users for each sys admin, give them SUPERUSER, and then drop the postgres user.
  • If changing the name, I'd need to alter the postgres account.

My problem, though, is that I'm not sure what the consequences of this action would be. For example, what would it means from the point of view of the Linux postgres user? When I su - postgres, I can run psql and I'm able to connect to the database. I assume that capability goes away if the postgres DB user is gone.

Also, the database is started using the postgres linux user. Is there some issue if I remove the postgres user that would make it so the database wouldn't even be able to start?

From ps:

/usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data PG_GRANDPARENT_PID=1 USER=postgres PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin PWD=/ PGLOCALEDIR=/usr/pgsql-9.3/share/locale LANG=en_US.UTF-8 PGSYSCONFDIR=/etc/sysconfig/pgsql SHLVL=1 HOME=/var/lib/pgsql LOGNAME=postgres PGDATA=/var/lib/pgsql/9.3/data _=/usr/pgsql-9.3/bin/postgres

I'm running PosrgreSQL 9.3 on Cent OS 7.

4

The Linux postgres user is entirely different from the postgres database user. It's created via the rpm package. When you're logged in as the system postgres user, it assumes the database user and database name that you're wanting to connect to is the same as the operating system user that you're attempting to connect from. That's why a simple psql works, and logs you into the database as the postgres user and database.

The postgres database is just a copy of template1. You can delete it or recreate it with few problems. You won't be able to drop the postgres user, however, because it's required by the database system.

user1@[local]:5432:user1:=# DROP ROLE postgres;
ERROR:  cannot drop role postgres because it is required by the database system
Time: 50.570 ms
user1@[local]:5432:user1:=#

However, you can block the login of the user by setting all the connections that you use to reject for the postgres, which will keep users from using that particular user for login, instead of their assigned users.

  • Can the postgres username be changed to something else? It seems like the db start command also specifies the user. – Chris Williams Jan 14 '16 at 21:45
  • The username in the database should be ok to change. The startup script should be referring to the postgres system user rather than the database user. – Kassandry Jan 15 '16 at 0:52
  • Wouldn't I also have to set a password for the newly renamed user? How would the DB relate and trust the Linux account at that point? It's set up to use "peer" in pb_hba.conf. – Chris Williams Jan 18 '16 at 4:59
  • Yes, that's correct. The authentication method would change. You could change to a different auth method in your pg_hba.conf (like md5 and set a password), or you could set an entry in pg_ident.conf which maps the system postgres user to the new database user. – Kassandry Jan 18 '16 at 8:19
  • 1
    Actually, I take back the comment that renaming is ok. Sorry. =( Renaming will break one very important thing. The statistics collector, which is needed for autovacuum, since there are files in pg_global which are created via initdb with the initial superuser name in them. You'd need dump, reload, and do an initdb with the superuser name changed if you want to correctly change the superuser's name without errors. – Kassandry Jan 20 '16 at 23:48

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.