6:58 - Thursday, 24 April 2014

Cannot Change Password For User Postgres In Postgresql

I have made the following entry in pg_hba.conf

local all all trust

but still

su postgres

does not accept blank as password. I am not able to run psql nor pg_ctl for same reason as most of the files are owned by postgres.

EDIT1

dhaval@ubuntu:~$ su -c "pg_ctl reload -D template1"Password: su: Authentication failuredhaval@ubuntu:~$ su -c psqlPassword: su: Authentication failure

I am giving the root password above but I guess its expecting “postgres” superuser password. I dont have the same. I need to reset it.

EDIt2

dhaval@ubuntu:~$ sudo -i -u postgres[sudo] password for dhaval:postgres@ubuntu:~$ psqlWelcome to psql 8.3.7, the PostgreSQL interactive terminal.

The above has taken me postgreSQL command prompt. But I am still not sure why the “trust” was not working.

Operating System User passwords (the thing you’re asked for when you try to su) are not the same as Postgres User Passwords (the thing you don’t need when you set auth to trust in pg_hba.conf).

You need to set a valid OS password for the Operating System‘s postgres user and enter that password in order to su to that user (Or su to that user from root, where you won’t be asked for a password).

Typically the postgres OS user password is locked out after DB initialization since the only thing that needs to run as postgres is pg_ctl, which can be launched with su -c from your init scripts (that run as root and therefore don’t need the password).

Most likely you are trying to do ‘su postgres’. Ubuntu does not allow a non-root user to switch to another user. So you may need to assume root and then su postgres.

To become ‘root’, try:

sudo su -

After that, try:

su postgres

That should get you access as the ‘postgres’ user as understood by the OS.

Hope this helps.

best
Vishal Belsare

Share

Advertisement

Comment