Обсуждение: Setting postgres user's password when running initdb
Hi Folks,
My first time using postgres and I'm struggling setting the postgres user's password. Am using 9.2 btw,(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
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
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
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"
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?
- --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
Edd Grant <edd@eddgrant.com> writes: > My first time using postgres and I'm struggling setting the postgres user's > password. Am using 9.2 btw, > 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" This indicates that the database is not using password authentication; it's using peer authentication, which basically checks that your actual OS user name matches the database user name you want to log in as. You could edit pg_hba.conf to fix this, or if you want to start over there's a --auth switch for initdb. Either way you want to select the "md5" auth method not the "peer" method. Another thing that would be worth checking is whether "service postgresql initdb" is passing through any of those arguments at all. I'm not real sure that "service" makes that possible, nor that everyone's versions of the init script do it even if it's possible. The most robust way of dealing with this sort of thing is to not force the issue, but just use peer auth initially --- that is, su to the postgres account and then psql should let you in. After that you can create more users, set database passwords, and adjust the auth method to your taste. It's worth reading most of this chapter: http://www.postgresql.org/docs/9.2/static/client-authentication.html Password auth is not the be-all and end-all; frequently, people end up preferring peer auth anyway for local connections, so the default you're getting here is not insane. regards, tom lane
Hi Tom,
Thanks for your response, I'd not noticed the 'Peer' in the message - stupid to have missed it.Edd
On 14 August 2013 19:06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Edd Grant <edd@eddgrant.com> writes:
> My first time using postgres and I'm struggling setting the postgres user's
> password. Am using 9.2 btw,> When prompted I type the password contained inThis indicates that the database is not using password authentication;
> /etc/pgsql/9.2/postgres.password but I get the following error:
> psql: FATAL: Peer authentication failed for user "postgres"
it's using peer authentication, which basically checks that your actual
OS user name matches the database user name you want to log in as.
You could edit pg_hba.conf to fix this, or if you want to start over
there's a --auth switch for initdb. Either way you want to select
the "md5" auth method not the "peer" method.
Another thing that would be worth checking is whether "service postgresql
initdb" is passing through any of those arguments at all. I'm not real
sure that "service" makes that possible, nor that everyone's versions
of the init script do it even if it's possible.
The most robust way of dealing with this sort of thing is to not force the
issue, but just use peer auth initially --- that is, su to the postgres
account and then psql should let you in. After that you can create more
users, set database passwords, and adjust the auth method to your taste.
It's worth reading most of this chapter:
http://www.postgresql.org/docs/9.2/static/client-authentication.html
Password auth is not the be-all and end-all; frequently, people end up
preferring peer auth anyway for local connections, so the default you're
getting here is not insane.
regards, tom lane
--
Web: http://www.eddgrant.com
Email: edd@eddgrant.com
Mobile: +44 (0) 7861 394 543