Thread: RE: [HACKERS] Re: [GENERAL] How do I activate and change the postgres user's password?

Hello,

I agree with this point of view : the granularity of the authentication is not small enough to allow a good setup of
accesssecurity to the PG databases. 
I plan to setup a database backed web servers :
* the databases are stored on one Linux box,
* the Apache servers are on another,
* all machines are exposed to all attacks from the Internet (and there are a lot)
* some databases must be feed via ODBC connections from workstations.
I can setup :
* the firewall on Linux to allow rough and low-level security restrictions,
* the pg_hba.conf can be setup to allow connections from the Apache box only
* there is still a problem for the access to the database themselves : site 1 should access database 1, and not
database2, but there should have the least password in the calling scripts 
* etc...

I already posted a message concerning security, but nobody seems to be concerned about this. I read the advices at
www.cert.org,and since then, I became paranoiac... 
I don't know exactly how it would be better to do, but a KISS solution would be good (I don't want to setup a Kerberos
authenticationsfor instance, because it could work badly with simple workstations updating data via ODBC). 

Nicolas Huillard

-----Message d'origine-----
De:    Oleg Bartunov [SMTP:oleg@sai.msu.su]
Date:    jeudi 14 octobre 1999 00:11
À:    Peter Eisentraut
Cc:    Lincoln Yeoh; pgsql-general@postgreSQL.org; pgsql-hackers@postgreSQL.org
Objet:    Re: [HACKERS] Re: [GENERAL] How do I activate and change the postgres user's password?

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


************

hi..

> * there is still a problem for the access to the database themselves : site
> 1 should access database 1, and not database 2, but there should have the
> least password in the calling scripts

a quick thought: if you are really paranoid, set up different installations of
postgres, even if on the same box... don't run them on the default port, set up
seperate pg_hba files and it should keep everything QUITE seperate.

> I already posted a message concerning security, but nobody seems to be
> concerned about this. I read the advices at www.cert.org, and since then, I
> became paranoiac...

as a side note, CERT sucks. they know security, if only because they know about
much of the cracking activity on the net, via reports. however, they are
close-mouthed about it all. they don't offer solutions, don't require vendors
to produce solutions and don't tell the public about the problems until the
vendor says "ok, tell 'em now", which is usually FAR too late. why do you think
they lose most of their star players (such as the guy who wrote SATAN?)? A:
frustration.

there are MUCH better security sites/sources than CERT. e.g. security portal.

--
Aaron J. Seigo
Sys Admin