changing the PostgreSQL default user

[Louis Kowolowski]

roughly, you will want to do:
take backup (pg_dump)
add the new user to the database
fix the pg_hba.conf to have the new user
restart pg
alter default user to new user
shutdown pg
remove pgsql entries from pg_hba.conf
add postgres user to freebsd. use same UID so you don’t need to chown anything
upgrade postgres
start pg
you should now have pg running as new (unix) user, and you should be able to connect as the new user
delete pgsql user (nolonger needed or used)

> On Oct 21, 2019, at 1:22 AM, David Newman <dnewman at networktest.com> wrote:
> 
> 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.
> 
> 
> 
> _______________________________________________
> freebsd-questions at freebsd.org mailing list
> https://lists.freebsd.org/mailman/listinfo/freebsd-questions
> To unsubscribe, send any mail to "freebsd-questions-unsubscribe at freebsd.org"

--
Louis Kowolowski                                louisk at cryptomonkeys.org <mailto:louisk at cryptomonkeys.org>
Cryptomonkeys:                                   http://www.cryptomonkeys.com/ <http://www.cryptomonkeys.com/>

Making life more interesting for people since 1977