scripting & psql issues

[Dino Vliet]

Hi folks,
probably this is a question you've heard so many times
but I wasn't able to find a solution to it.

I'm using a shell script to create a textfile for me.
It looks like
#!/usr/local/bin/bash
psql -c "select foo from bar;" -d database1 -t
psql -c "\q" -d database1
exit 0

The second psql -c "\q" command is used to logout from
the first database. But I'm not sure whether this is
correct. How do I mix these sql and psql meta-commands
otherwise if I want to be able to use myscript >
output.txt to get the output from this select
statement in the file output.txt. I'm planning to
write a cron job for it too.

Another issues is passwords. Normally when I log onto
database1, I have to issue a password because only the
superuser is trusted in the pg_hba.conf file. However
for scripts that I want to be able to run in the
background like this one, I had to add an entry to the
pg_hba.conf file to trust this user also. But as from
security point of view, I'm wondering if this is the
best way to achieve it.

Thanks in advance

__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Paul Thomas]

On 18/08/2004 12:57 Dino Vliet wrote:

Hi folks,
probably this is a question you've heard so many times
but I wasn't able to find a solution to it.

I'm using a shell script to create a textfile for me.
It looks like
#!/usr/local/bin/bash
psql -c "select foo from bar;" -d database1 -t
psql -c "\q" -d database1
exit 0

The second psql -c "\q" command is used to logout from
the first database. But I'm not sure whether this is
correct.
Not needed. The 1st command will exit by itself (man psql).
How do I mix these sql and psql meta-commands
otherwise if I want to be able to use myscript >
output.txt to get the output from this select
statement in the file output.txt. I'm planning to
write a cron job for it too.
You could use $HOME/.psqlrc.

Another issues is passwords. Normally when I log onto
database1, I have to issue a password because only the
superuser is trusted in the pg_hba.conf file. However
for scripts that I want to be able to run in the
background like this one, I had to add an entry to the
pg_hba.conf file to trust this user also. But as from
security point of view, I'm wondering if this is the
best way to achieve it.

You could use $HOME/.pgpass.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

Dino Vliet wrote:

Hi folks,
probably this is a question you've heard so many times
but I wasn't able to find a solution to it.

I'm using a shell script to create a textfile for me.
It looks like
#!/usr/local/bin/bash
psql -c "select foo from bar;" -d database1 -t
psql -c "\q" -d database1
exit 0
Put your commands in a separate file and run them with psql -f myfile.
That's simplest.
The second psql -c "\q" command is used to logout from
the first database. But I'm not sure whether this is
correct. How do I mix these sql and psql meta-commands
otherwise if I want to be able to use myscript >
output.txt to get the output from this select
statement in the file output.txt. I'm planning to
write a cron job for it too.
You can set an output file from psql with \o output.txt.
Another issues is passwords. Normally when I log onto
database1, I have to issue a password because only the
superuser is trusted in the pg_hba.conf file. However
for scripts that I want to be able to run in the
background like this one, I had to add an entry to the
pg_hba.conf file to trust this user also. But as from
security point of view, I'm wondering if this is the
best way to achieve it.

You'll want to investigate the .pgpass file. See the chapter:
Client Interfaces / libpq / files

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Christopher Browne]

The world rejoiced as di********@yahoo.com (Dino Vliet) wrote:

Hi folks,
probably this is a question you've heard so many times
but I wasn't able to find a solution to it.

I'm using a shell script to create a textfile for me.
It looks like
#!/usr/local/bin/bash
psql -c "select foo from bar;" -d database1 -t
psql -c "\q" -d database1
exit 0

The second psql -c "\q" command is used to logout from
the first database. But I'm not sure whether this is
correct. How do I mix these sql and psql meta-commands
otherwise if I want to be able to use myscript >
output.txt to get the output from this select
statement in the file output.txt. I'm planning to
write a cron job for it too.
You oughtn't need to put in the explicit quit, "\q"; the "^D" that
occurs at the end of the input stream would close things out nicely.
Another issues is passwords. Normally when I log onto
database1, I have to issue a password because only the
superuser is trusted in the pg_hba.conf file. However
for scripts that I want to be able to run in the
background like this one, I had to add an entry to the
pg_hba.conf file to trust this user also. But as from
security point of view, I'm wondering if this is the
best way to achieve it.

Check the docs for information on the "care and feeding" of .pgpass.
You can put authentication information into $HOME/.pgpass and anything
running using libpq will automatically look there.

The passwords sit there in plain text form; it might be nice to use
some encoded form (similar to the way Apache handles authentication).

But .pgpass at least has the existing merit that you don't have to
have passwords in your scripts.
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/spreadsheets.html
/Utopia-Bold 40 selectfont/n{moveto}def/p{gsave true charpath clip 72
400 n 300 -4 1{dup 160 300 3 -1 roll 0 360 arc 300 div 1 1 sethsbcolor
fill}for grestore 0 -60 rmoveto}def 72 500 n(This signature has been)p
(brought to you by the)p(letter Q and the number 42.)p(Chris Browne)
p(ch***@cbbrowne.com)p showpage

[Alvaro Herrera]

On Wed, Aug 18, 2004 at 04:20:55PM -0400, Christopher Browne wrote:

Check the docs for information on the "care and feeding" of .pgpass.
You can put authentication information into $HOME/.pgpass and anything
running using libpq will automatically look there.

The passwords sit there in plain text form; it might be nice to use
some encoded form (similar to the way Apache handles authentication).

Not sure what you mean here. Apache handles the server-side
authentication by storing a hashed version of the password (I take it
you are talking about the .htpasswd files). However .pgpass is for
client-side password storage. Do you know of a better way to store the
password than the plain text version?

I know CVS stores a mangled version, but it's trivial to go from the
stored password to the cleartext password, so a cracker can still get
the password easily, and it would be a PITA for the users to have to
process the password before storing if they are not going to get any
extra security. Other ideas?

The problem here is that the password can't be stored one-way-hash
digested, because the cleartext version is needed to be sent to the
server.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ellos andaban todos desnudos como su madre los parió, y también las mujeres,
aunque no vi más que una, harto moza, y todos los que yo vi eran todos
mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Daniel Martini]

Hello list,

Citing Alvaro Herrera <al******@dcc.uchile.cl>:

The problem here is that the password can't be stored one-way-hash
digested, because the cleartext version is needed to be sent to the
server.

Actually why this is so has been a question for me for some time now, too.
Did just nobody have the time / idea to implement support for sending
hashed passwords to the server, or are there serious difficulties involved
with this, and I don't see them?

Regards,
Daniel

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Oliver Elphick]

On Thu, 2004-08-19 at 08:30, Daniel Martini wrote:

Hello list,

Citing Alvaro Herrera <al******@dcc.uchile.cl>:

The problem here is that the password can't be stored one-way-hash
digested, because the cleartext version is needed to be sent to the
server.

Actually why this is so has been a question for me for some time now, too.
Did just nobody have the time / idea to implement support for sending
hashed passwords to the server, or are there serious difficulties involved
with this, and I don't see them?

As far as I am aware, crypt and md5 passwords are not sent in cleartext
form. password passwords (?!) are sent clear, but their use is
deprecated.

I think the password can't be stored hash-digested because it has to be
encrypted with a salt established at runtime. If you could just send
the same hash-digested password over and over, it would be no more
secure than a plaintext one.

Oliver Elphick

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Oliver Elphick <ol**@lfix.co.uk> writes:

I think the password can't be stored hash-digested because it has to be
encrypted with a salt established at runtime. If you could just send
the same hash-digested password over and over, it would be no more
secure than a plaintext one.

[ looks at code... ] The actual algorithm is

t = md5hash(cleartext_password || username);
p = md5hash(t || salt);
transmit p;

where || means string concatenation. On the server side, t is the value
actually stored in pg_shadow, so it just has to do the second step to
obtain the value to compare to the password message.

In theory we could make libpq accept the password in the form of t
rather than cleartext_password, but I pretty much fail to see the point.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tino Wildenhain]

Hi,

Am Do, den 19.08.2004 schrieb Tom Lane um 16:44:

Oliver Elphick <ol**@lfix.co.uk> writes:

I think the password can't be stored hash-digested because it has to be
encrypted with a salt established at runtime. If you could just send
the same hash-digested password over and over, it would be no more
secure than a plaintext one.

[ looks at code... ] The actual algorithm is

t = md5hash(cleartext_password || username);
p = md5hash(t || salt);
transmit p;

where || means string concatenation. On the server side, t is the value
actually stored in pg_shadow, so it just has to do the second step to
obtain the value to compare to the password message.

In theory we could make libpq accept the password in the form of t
rather than cleartext_password, but I pretty much fail to see the point.

Actually it is a bit lame anyway ;) Since the database readable
string is really the key it does not matter how it is generated
in the first place. So in the current situation, there is no
advantage over clear text passwords then.

(Ok, it saves us from "over the shoulder looking") but not more.

Otoh, if one needs security, there is a pretty (open)ssl - layer
and it even supports client certificates...

Regards
Tino Wildenhain

PS: the hash would suit better when used in a challenge authorization,
meaning the server sends a random key, let the client
hash(random_key || md5( cleartext_password || username ) )
and compare it on server with
hash(random_key || stored_password)


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Tino Wildenhain <ti**@wildenhain.de> writes:

PS: the hash would suit better when used in a challenge authorization,
meaning the server sends a random key, let the client
hash(random_key || md5( cleartext_password || username ) )
and compare it on server with
hash(random_key || stored_password)

Hm? That is exactly what we're doing.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Daniel Martini]

Hi,

On Thu, Aug 19, 2004 at 10:44:57AM -0400, Tom Lane wrote:

[ looks at code... ] The actual algorithm is

t = md5hash(cleartext_password || username);
p = md5hash(t || salt);
transmit p;

In theory we could make libpq accept the password in the form of t
rather than cleartext_password, but I pretty much fail to see the point.

Alright, I admit hashing the password in the scenario of a direct connection
to the postgresql server via libpq doesn't make much sense. So here is the
complete (a bit lengthy) story, how this question came into my mind.

Consider the following scenario:

A webserver serving a interface to a postgresql database as cgi. The cgi
is coded using libpq. Access to the database server is restricted to the
webserver (e.g. via pg_hba.conf). Users connect with their browser to the
webserver. The server presents a login page, where users have to type their
postgresql username and password.

How does authentication happen?
User enters password, it gets sent (ssl-protected) to the webserver. The
cgi calls PQconnectdb or PQconnectStart with the plaintext password and
other parameters, checks for success, sends the appropriate message to the
browser and exits. As the http protocol is stateless, the webserver will
normally forget about the user's username and password. So for the user
to be able to do further queries without having to enter username and
password again and again, we have to establish a session. To do this, we
generate a unique session id with a certain expiration time and either
send it as a cookie or embed it in a html form we send back. To be able
to use this session id to connect to the postgresql server via
PQconnectdb/PQconnectStart in the future, we have to maintain a mapping
on the webserver of session ids to username/password combinations. This
mapping has to be readable by the user the cgi runs as. An attacker, who
could gain access to this mapping would have the database passwords of all
the users, which currently have a session open. Encrypting the mapping
would only reduce the risk of a successfull attack marginally, as the
encryption key has to be readable by the cgi as well and could probably
be gotten hold of in the same way the attacker got hold of the mapping.

Now how would this work, if it would be possible to send hashed passwords
from libpq:
user sends username/password, this gets hashed by the cgi, then the hashed
value is sent by libpq. Session id is generated and
stored together with the hashed password in the mapping. Now attacker gets
hold of the mapping. Assuming he does only have access as the user the cgi
is running as, he would not have gained anything (except having compromised
the current sessions, which is less bad than having all passwords in
cleartext), as he only has the hashed passwords (a brute force attack on
the hashed values would be possible, but that is at least additional effort
for the attacker). If he had root, he could install a backdoor allowing
him to use the hashed passwords, but a compromise like this is much easier
detected than a compromise based on spied passwords.

So the key question to me is:
How can I ensure in such an application, that the password is visible in
cleartext only at one place (while the user enters it at his browser)?
Any ideas?
Does access via mod_php solve the problem? If yes, how is the session id
user/password mapping maintained?
Probably kerberos in conjunction with apache and mod_auth_kerberos would
do the job, but I have not yet looked into this.

Regards,
Daniel

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Daniel Martini <dm******@uni-hohenheim.de> writes:

Now how would this work, if it would be possible to send hashed passwords
from libpq:
user sends username/password, this gets hashed by the cgi, then the hashed
value is sent by libpq. Session id is generated and
stored together with the hashed password in the mapping. Now attacker gets
hold of the mapping. Assuming he does only have access as the user the cgi
is running as, he would not have gained anything (except having compromised
the current sessions, which is less bad than having all passwords in
cleartext), as he only has the hashed passwords (a brute force attack on
the hashed values would be possible, but that is at least additional effort
for the attacker). If he had root, he could install a backdoor allowing
him to use the hashed passwords, but a compromise like this is much easier
detected than a compromise based on spied passwords.

What backdoor? AFAICS you are proposing that we add a *front* door for
use of hashed passwords. Maybe the attacker won't know what the
original cleartext was, but that adds zero security as far as exploits
against the database go. If the webserver can log in with it, so can he.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Daniel Martini]

Hi,

Citing Tom Lane <tg*@sss.pgh.pa.us>:

Daniel Martini <dm******@uni-hohenheim.de> writes:

Now how would this work, if it would be possible to send hashed passwords
from libpq:
user sends username/password, this gets hashed by the cgi, then the hashed
value is sent by libpq. Session id is generated and
stored together with the hashed password in the mapping. Now attacker gets
hold of the mapping. Assuming he does only have access as the user the cgi
is running as, he would not have gained anything (except having

compromised

the current sessions, which is less bad than having all passwords in
cleartext), as he only has the hashed passwords (a brute force attack on
the hashed values would be possible, but that is at least additional

effort

for the attacker). If he had root, he could install a backdoor allowing
him to use the hashed passwords, but a compromise like this is much easier
detected than a compromise based on spied passwords.

What backdoor? AFAICS you are proposing that we add a *front* door for
use of hashed passwords. Maybe the attacker won't know what the
original cleartext was, but that adds zero security as far as exploits
against the database go. If the webserver can log in with it, so can he.

No he can't:
Only if he is able to install a program on the webserver to
actually login with a hashed password. If he wants to log in over the
cgi, this won't work, because the hashed value he gained by reading the
mapping will get hashed again and this will produce a wrong value.
Direct logins to the database from his machine won't work either, because
the database only allows connections from the webserver.

Regards,
Daniel

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Bruno Wolff III]

On Fri, Aug 20, 2004 at 09:43:08 +0200,
Daniel Martini <dm******@uni-hohenheim.de> wrote:

No he can't:
Only if he is able to install a program on the webserver to
actually login with a hashed password. If he wants to log in over the
cgi, this won't work, because the hashed value he gained by reading the
mapping will get hashed again and this will produce a wrong value.
Direct logins to the database from his machine won't work either, because
the database only allows connections from the webserver.

If all user logins are done using the webserver then you can have people
authenticate to the cgi application with whatever scheme makes you happy
and then have the cgi application authenticate to postgres using some other
authentication. (Trust would probably be OK if packet spoofing isn't a
significant risk on your local network.)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Daniel Martini <dm******@uni-hohenheim.de> writes:

Citing Tom Lane <tg*@sss.pgh.pa.us>:

... use of hashed passwords. Maybe the attacker won't know what the
original cleartext was, but that adds zero security as far as exploits
against the database go. If the webserver can log in with it, so can he.

No he can't:
Only if he is able to install a program on the webserver to
actually login with a hashed password. If he wants to log in over the
cgi, this won't work, because the hashed value he gained by reading the
mapping will get hashed again and this will produce a wrong value.
True, but if he can break into the webserver's memory, most likely he can
do that too.
Direct logins to the database from his machine won't work either, because
the database only allows connections from the webserver.

[ shrug ] That protection is the same whether you use hashed passwords
or not.

In any case, there are plenty of ways to defend against this scenario
that don't require weird new features in the database API. You could
for instance keep each user/password pair encrypted in the webserver's
memory, using a separate key for each session that is not stored in that
memory (perhaps it is in the cookie you use to identify the session).
Or for that matter, never keep the user/password pair in webserver
memory at all, but instead in the cookie (again encrypted, but this time
the webserver holds the key).

The fact that the password is hashed in a particular way is an
implementation detail that's subject to change, so I don't wish to
expose it as part of the API without darn good reason. I don't see
a darn good reason here...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Daniel Martini]

Hi Tom,

Thanks a lot, that was the kind of clarification I needed.

On Fri, Aug 20, 2004 at 10:32:59AM -0400, Tom Lane wrote:

Daniel Martini <dm******@uni-hohenheim.de> writes:

No he can't:
Only if he is able to install a program on the webserver to
actually login with a hashed password. If he wants to log in over the
cgi, this won't work, because the hashed value he gained by reading the
mapping will get hashed again and this will produce a wrong value.
True, but if he can break into the webserver's memory, most likely he can
do that too.

With a plain cgi, I probably can't store the data in memory, as the
cgi-process dies as soon as the request is handled (or am I wrong here?).
It would have to be filebased, and a file might be read without being root
(as the cgi has to read it as well and will not be running as root).

Anyways, the solution you proposed below is much better than hashed
passwords in libpq: You could
for instance keep each user/password pair encrypted in the webserver's
memory, using a separate key for each session that is not stored in that
memory (perhaps it is in the cookie you use to identify the session).
Or for that matter, never keep the user/password pair in webserver
memory at all, but instead in the cookie (again encrypted, but this time
the webserver holds the key).
That looks to me like the most secure way to defend against the database
server being taken fast as well, if the webserver is compromised (as
passwords are nowhere stored in cleartext and can be encrypted quite
strongly and flexible (e.g. different keys for every session etc.).
The fact that the password is hashed in a particular way is an
implementation detail that's subject to change, so I don't wish to
expose it as part of the API without darn good reason. I don't see
a darn good reason here...

Agreed. Keep libpq simple. I learned that there are better workarounds
against my original problem than adding this to the libpq API.

Regards,
Daniel

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org