Thread: How to know the password for the user 'postgres'
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..
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..
On Mon, Oct 27, 2008 at 10:38 PM, Shashank Sahni <shredder12@gmail.com> wrote: > 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 ?
createuser: could not connect to database postgres: FATAL: passwordOdd I would have expected it to say something about identd
authentication failed for user "postgres"
authentication failing.
Have you tried:
sudo su - postgres
createuser -d -A -P dpsace
?
"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..
On Mon, Oct 27, 2008 at 10:55 PM, Shashank Sahni <shredder12@gmail.com> wrote: > > >> 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 >> >> ? > > 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.
On Tue, Oct 28, 2008 at 10:52 AM, Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:
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..
On Mon, Oct 27, 2008 at 11:34 PM, Shashank Sahni <shredder12@gmail.com> wrote: > > On Tue, Oct 28, 2008 at 10:52 AM, Tony Caduto > <tony_caduto@amsoftwaredesign.com> wrote: >> >> 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... 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
Shashank Sahni wrote: > > 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
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.
On Tue, Oct 28, 2008 at 10:42:47AM +0100, Thomas wrote: > 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
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 <sam@samason.me.uk> writes:
> On Tue, Oct 28, 2008 at 10:42:47AM +0100, Thomas wrote:
>> 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
On Tue, Oct 28, 2008 at 01:43:08PM +0100, Thomas wrote: > 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
On Tue, October 28, 2008 05:57, Tom Lane wrote: > Sam Mason <sam@samason.me.uk> writes: >> On Tue, Oct 28, 2008 at 10:42:47AM +0100, Thomas wrote: >>> 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
On Tue, Oct 28, 2008 at 08:57:30AM -0400, Tom Lane wrote: > Sam Mason <sam@samason.me.uk> writes: > > On Tue, Oct 28, 2008 at 10:42:47AM +0100, Thomas wrote: > >> 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. > 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
On Tue, Oct 28, 2008 at 07:13:38AM -0700, Tim Bruce - Postgres <postgres@tbruce.com> wrote a message of 41 lines which said: > 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.)