PostgreSQL – How to Handle User with No Password

postgresqlUbuntu

I installed postgresql but it's asking me for passwords that I never had and never entered. There is a postgres ubuntu user and a database user named moodle. Neither of these were created with a password. Now that my system is asking me for those passwords, what should I do? Why is this process not easy?

$ psql -Umoodle -hlocalhost moodle < /var/www/vcmoodle/moodle/kth-install/moodle_fresh_postgres.sql 
Password for user moodle: 
psql: fe_sendauth: no password supplied
postgres@dac-VPCSA2Z9E:~$ sudo psql -Umoodle -hlocalhost moodle < /var/www/vcmoodle/moodle/kth-install/moodle_fresh_postgres.sql 
[sudo] password for postgres: 
postgres@dac-VPCSA2Z9E:~$ sudo -u moodle psql moodle
sudo: unknown user: moodle
sudo: unable to initialize policy plugin
postgres@dac-VPCSA2Z9E:~$ sudo -u postgres psql moodle
postgres is not in the sudoers file.  This incident will be reported.
postgres@dac-VPCSA2Z9E:~$ sudo -u dac psql moodle
[sudo] password for postgres: 
Sorry, try again.
[sudo] password for postgres: 
sudo: 1 incorrect password attempt
postgres@dac-VPCSA2Z9E:~$ sudo -u postgres psql
postgres is not in the sudoers file.  This incident will be reported.
postgres@dac-VPCSA2Z9E:~$

Best Answer

In pg_hba.conf, you should set:

# TYPE DATABASE USER ADDRESS METHOD

local     all           all                                   trust

Authentication method should be TRUST, that will allow you to enter postgres without a password and set it for postgres user. Below you see how to change password using SQL:

ALTER USER postgres WITH PASSWORD 'pass';

However, you should use psql's \password instead, so the password change (and the password itself) is not logged under any circumstance.

From the PostgreSQL 9.4.12 Documentation for psql:

\password [ username ]

Changes the password of the specified user (by default, the current user). This command prompts for the new password, encrypts it, and sends it to the server as an ALTER ROLE command. This makes sure that the new password does not appear in cleartext in the command history, the server log, or elsewhere.

Of course you should change option TRUST to for example md5 later for security reasons.

Related Question