Thread: 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

> 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

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

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

> 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.

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

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