Thread: scripting & psql issues
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
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 | +------------------------------+---------------------------------------------+
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
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.
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
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)
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
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
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).
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
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)
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
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
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
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
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.)
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
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