Hi Folks,
My first time using postgres and I'm struggling setting the postgres user's password. Am using 9.2 btw,
I'm running the following commands to initialise postgres, create a superuser with a password and then to create my first database:
(as root)
echo pass123 > /etc/pgsql/9.2/postgres.password
chmod 600 /etc/pgsql/9.2/postgres.password
chown postgres:postgres /etc/pgsql/9.2/postgres.password
service postgresql-9.2 initdb --encoding=UTF8 --locale=en_GB.UTF8 --username=postgres --pwfile=/etc/pgsql/9.2/postgres.password
/usr/bin/createdb --username=postgres --owner=postgres --no-password --template=template0 --encoding=UTF8 --locale=en_GB.UTF8 mydb1
And (for now at least) my pg_hba.conf file is as follows:
local all all password
local all all md5
I then try and access the mydb1 database using the following command:
psql mydb1 -U postgres -W
When prompted I type the password contained in /etc/pgsql/9.2/postgres.password but I get the following error:
psql: FATAL: Peer authentication failed for user "postgres"
In my very limited postgres experience it seems like the postgres user's password was not set when running the initdb command?
The
initdb docs state:
- --pwfile=filename Makes initdb read the database superuser's password from a file. The first line of the file is taken as the password.
- I'm understanding the documentation as saying that the password that I place in the first line of the file (/etc/pgsql/9.2/postgres.password) in my case will be set as the password of the user identified by --username (in my case the postgres user). In this example the postgres user should end up with a password of pass123
If I modify my pg_hba.conf file as follows:
local all all peer
and then restart postgres I can get in to the mydb1 Database, selecting from pg_catalog.pg_shadow seems to show that the postgres user doesn't have a password set at all:
mydb1=# select u.* from pg_catalog.pg_shadow u;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+--------+----------+-----------
postgres | 10 | t | t | t | t | | |
(1 row)
This makes me think I've misunderstood the documentation with regard to --pwfile?
Grateful for any advice here.
Cheers,
Edd