changing the PostgreSQL default user

[David Newman]

Greetings.

FreeBSD recently changed its default version of PostgreSQL from 9.5 to 11.

However, attempts to run 'pg_upgrade' on the databases failed for me
because my 9.5 install had a default user of 'pgsql' and version 11 goes
with 'postgres' instead [1].

My hack was to edit /usr/local/etc/rc.d/postgresql, set postgresql_user
to pgsql, (re)run initdb, and then do the pg_upgrade conversion. It
works, but future upgrades might clobber the edit in the rc.d file.

This article suggests one ALTER command will change the default
PostgreSQL user:

https://netnow.jira.com/wiki/spaces/PUBP/pages/119996467/Changing+the+default+user+and+password+for+postgreSQL

Is that, plus chown'ing the data directory, sufficient to effect a name
change?

Thanks!

dn


[1] The pg_upgrade program has a -U switch to specify user, but it still
fails because the pgsql user can't read stuff owned by the postgres user
and vice-versa. Just running chown on either binary or data directory
and its contents doesn't work.