Thread: 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
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)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
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
<br /><font face="sans-serif" size="2">On 01/12/2002 06:03:44 PM Karl DeBisschop wrote:<br /> > On Sat, 2002-01-12 at03:32, David Terrell wrote:<br /> > > On Thu, Jan 10, 2002 at 09:07:50AM +0100, Alexander Pucher wrote:<br /> >> > I need to run a shell script that logs in to Postgresql, executes a<br /> > > > query and logs offagain.<br /></font><br /><font face="sans-serif" size="2">> > > My problem is that I can't find a way to putthe password in an 'psql'<br /> > > > statement at the prompt.<br /></font><br /><font face="sans-serif" size="2">>If you absolutely need to do something like this, look into expect.<br /></font><br /><font face="sans-serif"size="2">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.</font><br/><br /><font face="sans-serif" size="2">Maarten</font><br /><font face="sans-serif" size="2"><br /> ----<br/><br /> Maarten Boekhold, maarten.boekhold@reuters.com<br /><br /> Reuters Consulting / TIBCO Finance TechnologyInc.<br /> Dubai Media City<br /> Building 1, 5th Floor<br /> PO Box 1426<br /> Dubai, United Arab Emirates<br/> tel:+971(0)4 3918300 ext 249<br /> fax:+971(0)4 3918333<br /> mob:+971(0)505526539</font><code><font size="3"><br/><br /> ------------------------------------------------------------- ---<br /> Visit our Internet site at http://www.reuters.com<br/><br /> Any views expressed in this message are those of the individual<br /> sender, except wherethe sender specifically states them to be<br /> the views of Reuters Ltd.<br /></font></code>
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