Thread: scripting & psql issues

scripting & psql issues

From
Dino Vliet
Date:
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

Re: scripting & psql issues

From
Paul Thomas
Date:
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   |
+------------------------------+---------------------------------------------+

Re: scripting & psql issues

From
Richard Huxton
Date:
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

Re: scripting & psql issues

From
"Bob Parkinson"
Date:
I've started to use the "here document" idea a lot when writing scripts to do tasks.

#!/usr/local/bin/bash

psql -d myDB <<EOSQL

select foo ....
update bar;
delete from ...

EOSQL






Bob



This message has been scanned but we cannot guarantee that it and any
attachments are free from viruses or other damaging content: you are
advised to perform your own checks.  Email communications with the
University of Nottingham may be monitored as permitted by UK legislation.


Re: scripting & psql issues

From
Christopher Browne
Date:
The world rejoiced as dino_vliet@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(chris@cbbrowne.com)p showpage

Re: scripting & psql issues

From
Alvaro Herrera
Date:
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)


libpq: passwords WAS: scripting & psql issues

From
Daniel Martini
Date:
Hello list,

Citing Alvaro Herrera <alvherre@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

Re: libpq: passwords WAS: scripting & psql issues

From
Oliver Elphick
Date:
On Thu, 2004-08-19 at 08:30, Daniel Martini wrote:
> Hello list,
>
> Citing Alvaro Herrera <alvherre@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



Re: scripting & psql issues

From
Oliver Elphick
Date:
On Wed, 2004-08-18 at 16:26, Bob Parkinson wrote:
> I've started to use the "here document" idea a lot when writing scripts to do tasks.
>
> #!/usr/local/bin/bash
>
> psql -d myDB <<EOSQL
>
> select foo ....
> update bar;
> delete from ...
>
> EOSQL

If the here document is long and complicated, you should make that
command line

  psql -d myDB -f - <<EOSQL

so as to get the line number of any error (counting from the start of
the here document).



Re: libpq: passwords WAS: scripting & psql issues

From
Tom Lane
Date:
Oliver Elphick <olly@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

Re: libpq: passwords WAS: scripting & psql issues

From
Tino Wildenhain
Date:
Hi,

Am Do, den 19.08.2004 schrieb Tom Lane um 16:44:
> Oliver Elphick <olly@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)




Re: libpq: passwords WAS: scripting & psql issues

From
Tom Lane
Date:
Tino Wildenhain <tino@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

Re: libpq: passwords WAS: scripting & psql issues

From
Daniel Martini
Date:
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

Re: libpq: passwords WAS: scripting & psql issues

From
Tom Lane
Date:
Daniel Martini <dmartini@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

Re: libpq: passwords WAS: scripting & psql issues

From
Daniel Martini
Date:
Hi,

Citing Tom Lane <tgl@sss.pgh.pa.us>:
> Daniel Martini <dmartini@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

Re: libpq: passwords WAS: scripting & psql issues

From
Bruno Wolff III
Date:
On Fri, Aug 20, 2004 at 09:43:08 +0200,
  Daniel Martini <dmartini@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.)

Re: libpq: passwords WAS: scripting & psql issues

From
Tom Lane
Date:
Daniel Martini <dmartini@uni-hohenheim.de> writes:
> Citing Tom Lane <tgl@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

Re: libpq: passwords WAS: scripting & psql issues

From
Daniel Martini
Date:
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 <dmartini@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