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 YourEmailAddres sHere" to ma*******@postg resql.org)

[Christopher Browne]

The world rejoiced as di********@yaho o.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***@cbbrown e.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.u chile.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*******@postg resql.org

[Oliver Elphick]

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

Hello list,

Citing Alvaro Herrera <al******@dcc.u chile.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.u k> 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(clearte xt_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_passw ord, 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.u k> 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(clearte xt_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_passw ord, 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_passw ord || 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**@wildenhai n.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_passw ord || 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