Postgresql – Cannot change password for user postgres in postgresql

passwordpostgresql

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 failure

dhaval@ubuntu:~$ su -c psql
Password: 
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:~$ psql
Welcome 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.

Best Answer

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).

Related Solutions

Ubuntu – Reloading PostgreSQL after configuration changes

You can check where your PGDATA is by connecting to pg, and issuing command:

show data_directory;

On ubuntu, it's usually /var/lib/postgresql/8.3/main/.

Also, you can: /etc/init.d/postgresql-8.3 reload

Postgresql – What’s the default superuser username/password for postgres after a new install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Best Answer

Related Solutions

Ubuntu – Reloading PostgreSQL after configuration changes

Postgresql – What’s the default superuser username/password for postgres after a new install

Related Topic