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(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden