Re: [GENERAL] How do I activate and change the postgres user's password? - Mailing list pgsql-general

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.