Thread: How do I activate and change the postgres user's password?
Right now any user on the local machine can log on as postgres to the template1 database. I don't like that, so I wish to turn on password checking. OK so I edit pg_hba.conf and put: local all password host all 127.0.0.1 255.255.255.255 password Then I have problems logging in as ANY user. Couldn't figure out what the default password for the postgres user was. Only after some messing around I found that I could log on as the postgres user with the password \N. Not obvious, at least to me. I only guessed it after looking at the pg_pwd file and noticing a \N there. Is this where the passwords are stored? By the way should they be stored in the clear and in a 666 permissions file? How about hashing them with some salt? Now the next problem is: How do I change the postgres user password? I find the package's handling of passwords rather nonintuitive. 1) There is no obvious way to specify the password for users when you create a user using the supplied shell script createuser. One has to resort to psql and stuff. 2) Neither is there an obvious and easy way to change the user's password. 3) You can specify a password for a user by using pg_passwd and stick it into a separate password file, but then there really is no link between createuser and pg_passwd. I find the bundled scripts and their associated documentation make things very nonintuitive when one switches from a blind trust postgres to an authenticated postgres. Cheerio, Link.
Re: [GENERAL] How do I activate and change the postgres user's password?
From
"Oliver Elphick"
Date:
Lincoln Yeoh wrote: >Right now any user on the local machine can log on as postgres to the >template1 database. I don't like that, so I wish to turn on password >checking. > >OK so I edit pg_hba.conf and put: > >local all password >host all 127.0.0.1 255.255.255.255 password > >Then I have problems logging in as ANY user. Couldn't figure out what the >default password for the postgres user was. Only after some messing around >I found that I could log on as the postgres user with the password \N. Not >obvious, at least to me. > >I only guessed it after looking at the pg_pwd file and noticing a \N there. >Is this where the passwords are stored? By the way should they be stored in >the clear and in a 666 permissions file? How about hashing them with some >salt? The PGDATA directory should have permission rwx------, so that no one can descend into it to look at pg_pwd; therefore the file's own permissions are unimportant. >Now the next problem is: How do I change the postgres user password? ALTER USER will change passwords held in pg_shadow, including that of the postgres user, but will not, I think, change those set by pg_passwd. -- Vote against SPAM: http://www.politik-digital.de/spam/ ======================================== Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "And he shall judge among the nations, and shall rebuke many people; and they shall beat their swords into plowshares, and their spears into pruninghooks; nation shall not lift up sword against nation, neither shall they learn war any more." Isaiah 2:4
Re: [GENERAL] How do I activate and change the postgres user's password?
From
Peter Eisentraut
Date:
On Oct 13, Lincoln Yeoh mentioned: > Then I have problems logging in as ANY user. Couldn't figure out what the > default password for the postgres user was. Only after some messing around > I found that I could log on as the postgres user with the password \N. Not > obvious, at least to me. There is a todo item for the postgres user to have a password by default. I'm not sure though how that would be done. Probably in initdb. (?) > I only guessed it after looking at the pg_pwd file and noticing a \N there. > Is this where the passwords are stored? By the way should they be stored in > the clear and in a 666 permissions file? How about hashing them with some > salt? I had this on my personal things-to-consider-working-on list but I don't see an official todo item. I am personally not sure why this is not done but authentication and security are not most people's specialty around here. (including me) > 1) There is no obvious way to specify the password for users when you > create a user using the supplied shell script createuser. One has to resort > to psql and stuff. Aah. Another misguided user. Some people are of the opinion that using the createuser scripts is a bad idea because it gives you the wrong impression of how things work. (All createuser does is call psql.) Of course, we could somehow put a password prompt in there, I'll put that on the above mentioned list. > 2) Neither is there an obvious and easy way to change the user's password. alter user joe with password "foo"; I'm not sure how obvious it is but it's certainly easy. > 3) You can specify a password for a user by using pg_passwd and stick it > into a separate password file, but then there really is no link between > createuser and pg_passwd. This shows how bad the idea of the scripts was in the first place. > I find the bundled scripts and their associated documentation make things > very nonintuitive when one switches from a blind trust postgres to an > authenticated postgres. So that would put your vote in the "drop altogether" column? Voting is still in progress! -Peter -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgres user's password?
From
Bruce Momjian
Date:
> On Oct 13, Lincoln Yeoh mentioned: > > > Then I have problems logging in as ANY user. Couldn't figure out what the > > default password for the postgres user was. Only after some messing around > > I found that I could log on as the postgres user with the password \N. Not > > obvious, at least to me. > > There is a todo item for the postgres user to have a password by default. > I'm not sure though how that would be done. Probably in initdb. (?) We could enabled it as part of initdb. Prompt them for it there, and assign it. Seems like there should be one on that account espeically. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgresuser's password?
From
Lamar Owen
Date:
Bruce Momjian wrote: > > There is a todo item for the postgres user to have a password by default. > > I'm not sure though how that would be done. Probably in initdb. (?) > > We could enabled it as part of initdb. Prompt them for it there, and > assign it. Seems like there should be one on that account espeically. Also, allow a command line option to set the password for those who need to automate things (like us RedHat people...). This is, I assume, for the postgres user INSIDE the initial database structure, as opposed to the postgres user on the OS. Since, under the RedHat installation, the initdb likely will happen during initial system startup, having a prompt for a password at that point is IMHO not good. Having a default password (in the initdb'd pg_shadow) would be better. If this is about the OS userame 'postgres', ignore that. The RPM installation already creates him, and makes it impossible to directly log in as 'postgres' -- until root changes his password. IMHO, of course. -- Lamar Owen WGCR Internet Radio Pisgah Forest, North Carolina 1 Peter 4:11
Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgres user's password?
From
Oleg Bartunov
Date:
Hi, followin this thread, I think It would be useful to allow user to connect to database he owned (created) without password even if pg_hba.conf is configured with password requirement to this database. Or owner of database could maintain list of users/groups whom he granted trusted connection. After user connects usual grant priviliges could works. Currently it's a pain to work with authentification system - I have to input my password every time I use psql and moreover I had to specify it in perl scripts I developed. Sometimes it's not easy to maintain secure file permissions espec. if several developers share common work. Any user (even not postgres user) could use stealed password to connects to your database. In my proposal, security is rely on local login security. You already passed password control. There are another checks like priviliges. You write your scripts without hardcoded passwords ! Of course this could be just an option in case you need "paranoic" security. Having more granulated privilege types as Mysql does would only make my proposal more secure. You're allowed to connect, but owner of database could restrict you even list of tables, indices et. all. Regards, Oleg PS. I didn't find any plans to improve authen. in TODO On Wed, 13 Oct 1999, Peter Eisentraut wrote: > Date: Wed, 13 Oct 1999 21:56:15 +0200 (CEST) > From: Peter Eisentraut <peter_e@gmx.net> > To: Lincoln Yeoh <lylyeoh@mecomb.com> > Cc: pgsql-general@postgreSQL.org, pgsql-hackers@postgreSQL.org > Subject: [HACKERS] Re: [GENERAL] How do I activate and change the postgres user's password? > > On Oct 13, Lincoln Yeoh mentioned: > > > Then I have problems logging in as ANY user. Couldn't figure out what the > > default password for the postgres user was. Only after some messing around > > I found that I could log on as the postgres user with the password \N. Not > > obvious, at least to me. > > There is a todo item for the postgres user to have a password by default. > I'm not sure though how that would be done. Probably in initdb. (?) > > > I only guessed it after looking at the pg_pwd file and noticing a \N there. > > Is this where the passwords are stored? By the way should they be stored in > > the clear and in a 666 permissions file? How about hashing them with some > > salt? > > I had this on my personal things-to-consider-working-on list but I don't > see an official todo item. I am personally not sure why this is not done > but authentication and security are not most people's specialty around here. > (including me) > > > 1) There is no obvious way to specify the password for users when you > > create a user using the supplied shell script createuser. One has to resort > > to psql and stuff. > > Aah. Another misguided user. Some people are of the opinion that using the > createuser scripts is a bad idea because it gives you the wrong impression > of how things work. (All createuser does is call psql.) Of course, we > could somehow put a password prompt in there, I'll put that on the above > mentioned list. > > > 2) Neither is there an obvious and easy way to change the user's password. > > alter user joe with password "foo"; > > I'm not sure how obvious it is but it's certainly easy. > > > 3) You can specify a password for a user by using pg_passwd and stick it > > into a separate password file, but then there really is no link between > > createuser and pg_passwd. > > This shows how bad the idea of the scripts was in the first place. > > > I find the bundled scripts and their associated documentation make things > > very nonintuitive when one switches from a blind trust postgres to an > > authenticated postgres. > > So that would put your vote in the "drop altogether" column? Voting is > still in progress! > > -Peter > > -- > Peter Eisentraut Sernanders vaeg 10:115 > peter_e@gmx.net 75262 Uppsala > http://yi.org/peter-e/ Sweden > > > ************ > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgresuser's password?
From
Bruce Momjian
Date:
> Bruce Momjian wrote: > > > There is a todo item for the postgres user to have a password by default. > > > I'm not sure though how that would be done. Probably in initdb. (?) > > > > We could enabled it as part of initdb. Prompt them for it there, and > > assign it. Seems like there should be one on that account espeically. > > Also, allow a command line option to set the password for those who need > to automate things (like us RedHat people...). This is, I assume, for > the postgres user INSIDE the initial database structure, as opposed to > the postgres user on the OS. > > Since, under the RedHat installation, the initdb likely will happen > during initial system startup, having a prompt for a password at that > point is IMHO not good. Having a default password (in the initdb'd > pg_shadow) would be better. > > If this is about the OS userame 'postgres', ignore that. The RPM > installation already creates him, and makes it impossible to directly > log in as 'postgres' -- until root changes his password. No, this is about the pgsql-supplied postgres password. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
At 09:56 PM 13-10-1999 +0200, Peter Eisentraut wrote: >There is a todo item for the postgres user to have a password by default. >I'm not sure though how that would be done. Probably in initdb. (?) Initdb sounds ok. Just have no password by default. \N is strange! >> the clear and in a 666 permissions file? How about hashing them with some >> salt? > >I had this on my personal things-to-consider-working-on list but I don't >see an official todo item. I am personally not sure why this is not done >but authentication and security are not most people's specialty around here. >(including me) Well I don't really know C or C++. But you could do the following: p= plain password s= salt (some random stuff). p=p+s (append salt to password). msg= random number from 1 to 4. Do following msg times: p=hash(p); Store in password file as hashed password= p salt = s Multiple salt grinds= msg If msg set to 0 and salt to null you can have plaintext passwords (this can be convenient sometimes). Hash function = SHA1, MD5, etc. You might wish to store hash type, e.g. 1= SHA1, 2=MD5.. >> 2) Neither is there an obvious and easy way to change the user's password. > >alter user joe with password "foo"; > >I'm not sure how obvious it is but it's certainly easy. Hmm, I couldn't find that tho. And I did look at the Admin guide docs. In fact I tried altering user permissions and stuff by trying UPDATEs on the template1.pg_user table and somehow that didn't work. Is there a reason why that doesn't work? It says 0 rows affected, and my where clause works if it's a SELECT. I was the postgres superuser too. >> 3) You can specify a password for a user by using pg_passwd and stick it >> into a separate password file, but then there really is no link between >> createuser and pg_passwd. > >This shows how bad the idea of the scripts was in the first place. Well I know what pg_passwd can be used for. Useful but it seems like it's slapped on- what's a good way to use and admin it? If I set up pg_hba.conf to use an optional password file would the Postgres super user authentication be taken from there too? >> I find the bundled scripts and their associated documentation make things >> very nonintuitive when one switches from a blind trust postgres to an >> authenticated postgres. > >So that would put your vote in the "drop altogether" column? Voting is >still in progress! I'm neutral. I don't mind doing everything from psql. Perhaps the Admin guide should have a section on "How Real Postgres Admins do stuff"- e.g. using psql for admin stuff. I believe the scripts were created when Postgres users didn't really bother about authentication. They could be fine if they have authentication in mind. But as is, it's like: 1) No authentication. Scripts fine- convenient too. Psql fine. Everything fine. 2) Authentication on. Scripts don't work. Psql works if you can figure out the Postgres user password. Ick. Also there's 1) A shadow file 2) A pg_pwd file (why this and shadow?) 3) An option to have a password file. This is just some grumbling, overall Postgres 6.5.x is quite impressive. Great improvement from Postgres95 which I tried and gave up on two years ago- I switched to MySQL. I still get a "cleaner" and clearer impression about MySQL authentication and access controls. The MySQL docs are very clear on that, in general the MySQL documentation is good. Maybe the current postgres scripts do confuse things. Still, the current Postgres docs are better than the Oracle docs, they are actually useful ;). Is it just me, or is installing Oracle based on the Oracle installation manual like doing surgery following an academic textbook? e.g. chapter 1 has 100 ways to do an incision. Chapter 2 has 20 ways on sewing up. Chapter 3 discusses anaesthesia. Chapter 4- tying blood vessels, (by the way please refer to chapter 2 for more sewing hints).. And so on. In the end one has to go to the web and look for a HOWTO :). Cheerio, Link.
Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgresuser's password?
From
Peter Eisentraut
Date:
On Wed, 13 Oct 1999, Lamar Owen wrote: > Bruce Momjian wrote: > > > There is a todo item for the postgres user to have a password by default. > > > I'm not sure though how that would be done. Probably in initdb. (?) > > > > We could enabled it as part of initdb. Prompt them for it there, and > > assign it. Seems like there should be one on that account espeically. > > Also, allow a command line option to set the password for those who need > to automate things (like us RedHat people...). This is, I assume, for > the postgres user INSIDE the initial database structure, as opposed to > the postgres user on the OS. > > Since, under the RedHat installation, the initdb likely will happen > during initial system startup, having a prompt for a password at that > point is IMHO not good. Having a default password (in the initdb'd > pg_shadow) would be better. Um, a default pre-packaged password would yield the whole concept next to useless. I personally think an advice after initdb ("You really ought to assign a password to the superuser now.") will suffice. If people don't do what they're told, too bad. On the other hand, I'll check into that \N default password. This whole security concept needs overhaul anyway, but I don't see it happening anytime soon. -Peter -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Re: [GENERAL] How do I activate and change the postgres user's password?
From
"Aaron J. Seigo"
Date:
hi... > >There is a todo item for the postgres user to have a password by default. > >I'm not sure though how that would be done. Probably in initdb. (?) > > Initdb sounds ok. Just have no password by default. \N is strange! or how about a prompt for a password? when you run initdb it asks for a password? or even a yes/no? i don't like leaving things to command line switches, its too easy for a user to ignore/be ignorant of them and create a situation that isn't secure or isn't what they want w/out knowing it. this only reflects badly on the product at large instead of the clueless admin. :o/ > >> 2) Neither is there an obvious and easy way to change the user's password. > > > >alter user joe with password "foo"; > > > >I'm not sure how obvious it is but it's certainly easy. > > Hmm, I couldn't find that tho. And I did look at the Admin guide docs. i wasn't aware of this either (being relatively new to postgres.. less than a year) but it smacks of ugly, imo. > > >> I find the bundled scripts and their associated documentation make things > >> very nonintuitive when one switches from a blind trust postgres to an > >> authenticated postgres. > > > >So that would put your vote in the "drop altogether" column? Voting is > >still in progress! > > I'm neutral. I don't mind doing everything from psql. > > Perhaps the Admin guide should have a section on "How Real Postgres Admins > do stuff"- e.g. using psql for admin stuff. personally, i think that psql should not be allowed to do any admin stuff.. othewise it become a potential security hazard on a machine used by lots of people. it should (imo) only be a database structure and data retrieval/manipulation tool... admin functions should occur from a seperate stand alone program. this way, you avoid the ugliness of the scripts, which are inherently inflexible and bound to be broken... also, you have one central agency that can be put under the permissions of the postgres user or the DBA group on the box its installed on. also, if its a stand-alone command-line program (C/C++/whatever) we can then put a nice GUI front end on it and have a graphical admin tool which would be AMAZINGLY useful. i'd probably even be willing to help write it (i'm just now dipping into the world QT and finding it extremely exciting =) this would also demand that we all "sit down" and come up with a standard, well though-out process for security to be implemented in the admin tool. perhaps even a different mailing list would be in order for this ... the things i would love the admin tool to cover are (in no particular order): o creating and admining users o passwords o access privileges o creating databases o creating and admining back up policies and procedures o maintaining a postgres installation o disk usage o postmaster options o back-end options o default policies for new databases o logging and usage analysis o source code management (important, imo, for open source projects) o compile-time options (with/without TCL, etc) o application of patches (to alleviate the need to do this "by hand") 0 upgrading from version w.x to version x.y the tool could be made modular, so we can create a skeleton system and add/remove/alter modules as we (the user community) desire. > Is it just me, or is installing Oracle based on the Oracle installation > manual like doing surgery following an academic textbook? e.g. chapter 1 > has 100 ways to do an incision. Chapter 2 has 20 ways on sewing up. Chapter > 3 discusses anaesthesia. Chapter 4- tying blood vessels, (by the way please > refer to chapter 2 for more sewing hints).. And so on. In the end one has ROFL!!!! yes!!!! why is it that commercial software vendors INSIST on making their manuals so arcane that they are as readable as the product is usable w/out a manual? haha... -- Aaron J. Seigo Sys Admin