How to know the password for the user 'postgres'

[Shashank Sahni]

Hello people,
I was trying to install dspace on my computer and it
required postgresql as a prerequisite. Since I am using Ubuntu so i just
downloaded and installed it using synaptic package manager. For the
installation of dspace i was supposed to exeucte the following
command..
createuser -U postgres -d -A -P dspace
but whenever i execute it, it asks for password for the new role and then
after giving the password..it says..

createuser: could not connect to database postgres: FATAL: password
authentication failed for user "postgres"

when i posted the question on dspace mailing list..one of the guy replied me
with this solution..
createuser -h localhost -U postgres -d -A -P dspace
but he said that to execute the above command i must know the password for
the user postgres..
on running this command at the end i was prompted to the enter the password
for the user postgres but i don't know the password..I think this user was
created as default at the time postgresql was installed..then there must be
a default password ...
Please help me with this problem..

[Scott Marlowe]

Post by Shashank Sahni
Hello people,
I was trying to install dspace on my computer and it
required postgresql as a prerequisite. Since I am using Ubuntu so i just
downloaded and installed it using synaptic package manager. For the
installation of dspace i was supposed to exeucte the following
command..
createuser -U postgres -d -A -P dspace
but whenever i execute it, it asks for password for the new role and then
after giving the password..it says..
createuser: could not connect to database postgres: FATAL: password
authentication failed for user "postgres"

Odd I would have expected it to say something about identd
authentication failing.

Have you tried:

sudo su - postgres
createuser -d -A -P dpsace

?

[Shashank Sahni]

createuser: could not connect to database postgres: FATAL: password

Post by Shashank Sahni
authentication failed for user "postgres"
Odd I would have expected it to say something about identd
authentication failing.
sudo su - postgres
createuser -d -A -P dpsace
?

Oh... I am sorry...you are right...the error is
"Ident authentication failed for user 'postgres' "
the error specified earlier appeared when i tried running.
createuser -h localhost -U postgres -d -A -P dspace
Thanks for pointing it out..

[Scott Marlowe]

Post by Shashank Sahni

Post by Shashank Sahni
createuser: could not connect to database postgres: FATAL: password
authentication failed for user "postgres"
Odd I would have expected it to say something about identd
authentication failing.
sudo su - postgres
createuser -d -A -P dpsace
?

Oh... I am sorry...you are right...the error is
"Ident authentication failed for user 'postgres' "
the error specified earlier appeared when i tried running.
createuser -h localhost -U postgres -d -A -P dspace
Thanks for pointing it out..

No problem. You might wanna look into the pg_hba.conf file. Like
most pgsql files it's self documenting, so just reading it will tell
you quite a bit. /etc/postgresql/8.3/main/pg_hba.conf I believe is
where it is on ubuntu.

[Shashank Sahni]

On Tue, Oct 28, 2008 at 10:52 AM, Tony Caduto <

Edit the pg_hba.conf file and add a entry for the PC you are doing your
admin from and set it to Trust.
When set to trust you won't need a password, then use the admin tool of
your choice to change the postgres password to whatever
you want. Afterwards remember to set it back to MD5 or whatever it was
prior to you making the change.
Later,
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com

My pg_hba.conf file looks like this..
-----------------------------------------------------------------------------------------------
# Database administrative login by UNIX sockets
local all postgres ident sameuser

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
host dspace dspace 127.0.0.1 255.255.255.255 md5
------------------------------------------------------------------------------------------
Can you tell me which entry should i modify or what entry should i exactly
add ( as you have already mentioned)..
Please don't mind my stupid questions..I am a newbie so I don't have much
idea about it..
I would greatly appreciate your help...
Thankyou..

Shashank Sahni..

[Scott Marlowe]

Post by Shashank Sahni
On Tue, Oct 28, 2008 at 10:52 AM, Tony Caduto

Edit the pg_hba.conf file and add a entry for the PC you are doing your
admin from and set it to Trust.
When set to trust you won't need a password, then use the admin tool of
your choice to change the postgres password to whatever
you want. Afterwards remember to set it back to MD5 or whatever it was
prior to you making the change.
Later,
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com

My pg_hba.conf file looks like this..
-----------------------------------------------------------------------------------------------
# Database administrative login by UNIX sockets
local all postgres ident sameuser
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all ident sameuser
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host dspace dspace 127.0.0.1 255.255.255.255 md5
------------------------------------------------------------------------------------------
Can you tell me which entry should i modify or what entry should i exactly
add ( as you have already mentioned)..
Please don't mind my stupid questions..I am a newbie so I don't have much
idea about it..
I would greatly appreciate your help...

No need for apologies, we all learn by doing. The pg_hba.conf file is
a simple first match filter. PostgreSQL takes the attempted incoming
connection and looks for the first line that matches and processes the
login accordingly. Have you read the fine documentation IN the
pg_hba.conf file itself?

the important bit is that you have local and host type connections to
worry about, and local means unix local sockets (i.e. psql without a
-h or createdb without a -h) and host means that it's using tcp/ip to
connect.

It's quite often simple enough to set a local for the postgres user of
ident so local maintenance tasks can run under the postgres account,
then use -h hostname or -h ip to connect to the server with passwords.

So, leave it for now, sudo su - postgres, create your user, set a
password (see alter user within psql (i.e. from psql type \h alter
user) or the createuser command line command (i.e. createuser -h) for
more info on how to set the password.) I think

[Thomas]

An easy trick I have found to set postgres password: $ sudo passwd
postgres, and now you can type a new password. So now you can switch
user with: $ su postgres, and then connect to the DB with psql.

Beware of pg_hba.conf, it is a bit tricky to understand how the
mechanism of authentification works whether you are on localhost or
remote.

[Sam Mason]

Post by Thomas
An easy trick I have found to set postgres password: $ sudo passwd
postgres, and now you can type a new password. So now you can switch
user with: $ su postgres, and then connect to the DB with psql.

Won't that allow logins to the postgres account then? If there's no
password then logins (either locally, ssh, or whatever is configured)
can't happen and your data is going to be safer. Going in through root
first may require five more characters to be typed (or less if you
create an alias), but wont create any backdoors.


Sam

[Thomas]

Yes this allows to login remotely through ssh for instance. But it
doesn't offer a bigger backdoor than having a weak password on a sudo
account.

[Sam Mason]

Post by Thomas
Yes this allows to login remotely through ssh for instance. But it
doesn't offer a bigger backdoor than having a weak password on a sudo
account.

In my eyes, the you've just increased the attack surface available for
getting the data---you've gone from a single account to two. Having
a weak password on the sudo account is still a way in, in addition
to breaking the postgres password. In practical terms this should
affect things materially; if you've got a strong password on the sudoers
account you're likely to have a strong one on the postgres account and
vice versa.


Sam

[Tom Lane]

Post by Sam Mason

Post by Thomas
An easy trick I have found to set postgres password: $ sudo passwd
postgres, and now you can type a new password. So now you can switch
user with: $ su postgres, and then connect to the DB with psql.

Won't that allow logins to the postgres account then?

True, but that might be safer overall than giving out sudo privileges.
If the sysadmin and the DBA are the same person it hardly matters,
but if you want the DBA to not have root, then giving him a password for
the postgres account is the best way. So it all depends on your
local situation ...

regards, tom lane

[Tim Bruce - Postgres]

Post by Tom Lane

Post by Sam Mason

Post by Thomas
An easy trick I have found to set postgres password: $ sudo passwd
postgres, and now you can type a new password. So now you can switch
user with: $ su postgres, and then connect to the DB with psql.

Won't that allow logins to the postgres account then?

True, but that might be safer overall than giving out sudo privileges.
If the sysadmin and the DBA are the same person it hardly matters,
but if you want the DBA to not have root, then giving him a password for
the postgres account is the best way. So it all depends on your
local situation ...
regards, tom lane

Wouldn't it be better to add the line 'sudo su - postgres' as the entry
(command) for the user(s) in the sudoers file? This would specifically
limit the user(s) to only being able to change to the postgres user's
context.

I think this goes to overall system security, just like the security
methods wrapped around PostgreSQL itself. Weakening system security is no
different than weakening access to the database.

Tim
--
Timothy J. Bruce

Registered Linux User #325725

[Stephane Bortzmeyer]

On Tue, Oct 28, 2008 at 07:13:38AM -0700,

Post by Tim Bruce - Postgres
Wouldn't it be better to add the line 'sudo su - postgres' as the
entry (command) for the user(s) in the sudoers file?

Simpler, set the "runas" parameter:

jsmith ALL=(postgres) ALL

That way, user "jsmith" can run anything as the "postgres" user, such as:

% sudo -u postgres createdb foobar

(I simply do not understand why to use the old su together with
sudo. su is almost useless today: it does not allow fine-grain
control, it doesn't log, etc.)

[Sam Mason]

Post by Tom Lane

Post by Sam Mason

Post by Thomas
An easy trick I have found to set postgres password: $ sudo passwd
postgres, and now you can type a new password. So now you can switch
user with: $ su postgres, and then connect to the DB with psql.

Won't that allow logins to the postgres account then?

True, but that might be safer overall than giving out sudo privileges.

As it was presented as an "easy trick" I wasn't sure if the OP had
realized he'd opened another account up on his system. Thomas's other
reply suggests he's aware of the issue, but I was at least partially
responding for other readers to make them aware that running the command
will have other side effects.

Post by Tom Lane
If the sysadmin and the DBA are the same person it hardly matters,
but if you want the DBA to not have root, then giving him a password for
the postgres account is the best way. So it all depends on your
local situation ...

Indeed it does!


Sam

[Tony Caduto]

Post by Shashank Sahni
when i posted the question on dspace mailing list..one of the guy
replied me with this solution..
createuser -h localhost -U postgres -d -A -P dspace
but he said that to execute the above command i must know the password
for the user postgres..

Edit the pg_hba.conf file and add a entry for the PC you are doing your
admin from and set it to Trust.
When set to trust you won't need a password, then use the admin tool of
your choice to change the postgres password to whatever
you want. Afterwards remember to set it back to MD5 or whatever it was
prior to you making the change.

Later,

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com