Postgres in bash-mode

I used to be able to do:

(echo "login\npassword\n"; bench.sh) | psql -h system database

Don't really know if it works anymore though. Started using SSL
related tools.

--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Alexander Pucher" <pucher@atlas.gis.univie.ac.at>
To: "pgsql-hackers" <pgsql-hackers@postgresql.org>
Sent: Thursday, January 10, 2002 3:07 AM
Subject: [HACKERS] Postgres in bash-mode

Hi,

I hope, that this is not too off topic for this list, but anyway, it
should be easy for any PG-expert.

I need to run a shell script that logs in to Postgresql, executes a
query and logs off again.

In MySQL, I did it like this:

'mysql -u user -ppassword < script.sh'

My problem is that I can't find a way to put the password in an

'psql'

statement at the prompt.

Any suggestions are welcome!

mfg
ALEX

--
________________________________________________________

Institut fuer Geographie und Regionalforschung
Universit�t Wien
Kartografie und Geoinformation

Departement of Geography and Regional Research
University of Vienna
Cartographie and GIS

Universitaetstr. 7, A-1010 Wien, AUSTRIA

Tel: (+43 1) 4277 48644
Fax: (+43 1) 4277 48649
E-mail: pucher@atlas.gis.univie.ac.at

FTP: ftp://ftp.gis.univie.ac.at
WWW: http://www.gis.univie.ac.at/karto
________________________________________________________

"He that will not apply new remedies must expect new evils; for time

is the greatest innovator"--Francis Bacon

---------------------------(end of

broadcast)---------------------------

Alexander Pucher writes:

In MySQL, I did it like this:

'mysql -u user -ppassword < script.sh'

Then you might as well not have any authentication at all, because every
user on your system can then read the password off the "ps" output.

My problem is that I can't find a way to put the password in an 'psql'
statement at the prompt.

You can put it into the environment variable PGPASSWORD, but that *might*
suffer from the same problems depending on your OS. If you want real
noninteractive login you will have to use a different authentication
method, such as ident.

--
Peter Eisentraut peter_e@gmx.net

On Thu, Jan 10, 2002 at 09:07:50AM +0100, Alexander Pucher wrote:

Hi,

I hope, that this is not too off topic for this list, but anyway, it
should be easy for any PG-expert.

I need to run a shell script that logs in to Postgresql, executes a
query and logs off again.

In MySQL, I did it like this:

'mysql -u user -ppassword < script.sh'

My problem is that I can't find a way to put the password in an 'psql'
statement at the prompt.

Any suggestions are welcome!

"Don't do that"

You CAN do something like
% psql -f dbscript database
Password: <typeity>
<stuff happens>
129195981 INSERT
%

--
David Terrell | "I went into Barnes and Noble to look for a
Prime Minister, Nebcorp | book on A.D.D., but I got bored and left."
dbt@meat.net | - Benjy Feen
http://wwn.nebcorp.com/ |

On Sat, 2002-01-12 at 03:32, David Terrell wrote:

On Thu, Jan 10, 2002 at 09:07:50AM +0100, Alexander Pucher wrote:

Hi,

I hope, that this is not too off topic for this list, but anyway, it
should be easy for any PG-expert.

I need to run a shell script that logs in to Postgresql, executes a
query and logs off again.

In MySQL, I did it like this:

'mysql -u user -ppassword < script.sh'

My problem is that I can't find a way to put the password in an 'psql'
statement at the prompt.

Any suggestions are welcome!

"Don't do that"

You CAN do something like
% psql -f dbscript database
Password: <typeity>
<stuff happens>
129195981 INSERT

Just a bit of extra info. Passwords on the command line are sniffable.
You can obsure them somewhat, but AFAIK there is no way, or at least no
general way to secure them fully.

If you absolutely need to do something like this, look into expect.

--
Karl

On 01/12/2002 06:03:44 PM Karl DeBisschop wrote:

On Sat, 2002-01-12 at 03:32, David Terrell wrote:

On Thu, Jan 10, 2002 at 09:07:50AM +0100, Alexander Pucher wrote:

I need to run a shell script that logs in to Postgresql, executes a
query and logs off again.

My problem is that I can't find a way to put the password in an

'psql'

statement at the prompt.

If you absolutely need to do something like this, look into expect.

Woul be useful if there's an example expect script for this somewhere in
the distribution or documentation. Lots of people don't know expect, and
don't want to learn it. They just want to automate database tasks.

Maarten

----

Maarten Boekhold, maarten.boekhold@reuters.com

Reuters Consulting / TIBCO Finance Technology Inc.
Dubai Media City
Building 1, 5th Floor
PO Box 1426
Dubai, United Arab Emirates
tel:+971(0)4 3918300 ext 249
fax:+971(0)4 3918333
mob:+971(0)505526539

------------------------------------------------------------- ---
Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.

On Sun, 2002-01-13 at 01:20, Maarten.Boekhold@reuters.com wrote:

On 01/12/2002 06:03:44 PM Karl DeBisschop wrote:

On Sat, 2002-01-12 at 03:32, David Terrell wrote:

On Thu, Jan 10, 2002 at 09:07:50AM +0100, Alexander Pucher wrote:

I need to run a shell script that logs in to Postgresql, executes a
query and logs off again.

My problem is that I can't find a way to put the password in an

'psql'

statement at the prompt.

If you absolutely need to do something like this, look into expect.

Woul be useful if there's an example expect script for this somewhere in
the distribution or documentation. Lots of people don't know expect, and
don't want to learn it. They just want to automate database tasks.

Before you forga ahead with expect, perhaps you als want to read the
docs for pg_hba.conf.

As I said, expect can be secure if uoy are careful (make sure noone else
can read the scipt). But it's not alot of fun to maintain.

There are other options. If you want to avoid admiin hassles, I'd
suggest looking into ident on a close set of machines. If the machine in
question is not close by, then try ssh to make them seem closer.

Basically, you can trust identd if and only if you know that its your
identd. But with identd, you can have a large number of scripts that
continue to work after you change the password. (Note that you cannot
use identd on the ub=nix socket).

We are presently revamping our own security. If I had a good example of
a system in a final state, I'd post it. I don't know, but maybe someone
else can. Or maybe in a few weeks I'll post ours, if I can assure myself
that disclosure won't reduce scurity (iff well designed, that should be
the case, I think).

Karl

On Sun, Jan 13, 2002 at 06:41:06AM -0500, Karl DeBisschop wrote:

Basically, you can trust identd if and only if you know that its your
identd. But with identd, you can have a large number of scripts that
continue to work after you change the password. (Note that you cannot
use identd on the ub=nix socket).

We are presently revamping our own security. If I had a good example of
a system in a final state, I'd post it. I don't know, but maybe someone
else can. Or maybe in a few weeks I'll post ours, if I can assure myself
that disclosure won't reduce scurity (iff well designed, that should be
the case, I think).

7.2 can run something like ident but better (guaranteed accurate) on
the unix socket on BSD and linux.

--
David Terrell | "The fact that you can't name the place
dbt@meat.net | you're going to die doesn't mean you
http://wwn.nebcorp.com/ | shouldn't pay attention to your health." -whg3

On Sun, 2002-01-13 at 06:43, David Terrell wrote:

On Sun, Jan 13, 2002 at 06:41:06AM -0500, Karl DeBisschop wrote:

Basically, you can trust identd if and only if you know that its your
identd. But with identd, you can have a large number of scripts that
continue to work after you change the password. (Note that you cannot
use identd on the ub=nix socket).

7.2 can run something like ident but better (guaranteed accurate) on
the unix socket on BSD and linux.

That's cool. Looks like I just got my rationale for doing the 7.2
upgrade sooner rather than later.

--
Karl