Обсуждение: Providing the password to psql from a script
I am developing a client application for postgreSQL in Tcl/Tk (see http://gborg.postgresql.org/project/pfm ). It mainly uses PgTcl or pgintcl. I don't have any problems with those, but I am also trying to call psql from my application for SQL statements typed directly by the user. I have used the Tcl command set psqlChannel [open "|psql $dbname" RDWR] to create a channel that effectively becomes the input/output channel for psql. By writing to that channel, SQL statements are sent to psql, by reading from that channel, the results are received from psql. That works fine, as long as psql does not prompt for a password. The problem is that psql does not use this channel for prompting for or reading the password. Instead, the password is prompted for on, and read from, the terminal from which the tcl application was started and that is not what I want, because the Tcl application has already received the password from the user. It is, as if psql does not use stdout and stdin for the password. It would already be helpful if I knew how to make a shell script that first reads the password and then provides that password to psql, such that the user is not prompted for the password again. I have already tried bash constructions like PASSWORD='blabla' psql $dbname <<EOF $PASSWORD EOF But the result is still that psql prompts for the password on the terminal from which the script is run. Does anybody know how to solve this problem? Thanks in advance, -- Willem Herremans
On Sat, Feb 14, 2004 at 05:11:14PM +0100, Willem Herremans wrote: > I have used the Tcl command > > set psqlChannel [open "|psql $dbname" RDWR] > > to create a channel that effectively becomes the input/output channel > for psql. By writing to that channel, SQL statements are sent to psql, > by reading from that channel, the results are received from psql. > > That works fine, as long as psql does not prompt for a password. The > problem is that psql does not use this channel for prompting for or > reading the password. Instead, the password is prompted for on, and read > from, the terminal from which the tcl application was started and that > is not what I want, because the Tcl application has already received the > password from the user. It is, as if psql does not use stdout and stdin > for the password. Look in the manpage for psql, there are several ways to stop it asking for passwords, including the PGPASS environment variable, tne .pgpass file and setting the user as trust in the config. Any of these will do what you want... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > If the Catholic church can survive the printing press, science fiction > will certainly weather the advent of bookwarez. > http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow
Вложения
Martijn van Oosterhout wrote: >On Sat, Feb 14, 2004 at 05:11:14PM +0100, Willem Herremans wrote: > > >>I have used the Tcl command >> >> set psqlChannel [open "|psql $dbname" RDWR] >> >>to create a channel that effectively becomes the input/output channel >>for psql. By writing to that channel, SQL statements are sent to psql, >>by reading from that channel, the results are received from psql. >> >>That works fine, as long as psql does not prompt for a password. The >>problem is that psql does not use this channel for prompting for or >>reading the password. Instead, the password is prompted for on, and read >>from, the terminal from which the tcl application was started and that >>is not what I want, because the Tcl application has already received the >>password from the user. It is, as if psql does not use stdout and stdin >>for the password. >> >> > >Look in the manpage for psql, there are several ways to stop it asking for >passwords, including the PGPASS environment variable, tne .pgpass file and >setting the user as trust in the config. > >Any of these will do what you want... > >Hope this helps, > > I am afraid this does not help me very much. The manpage of psql neither mentions the PGPASS environment variable, nor the .pgpass file. I understand that, if the server is configured such that the client can authenticate wihout password, that psql does not prompt for a password, but it would be a serious limitation for my application that it cannot support authenitication by means of password. Kind regards, -- Willem Herremans
On Sun, 15 Feb 2004, Willem Herremans wrote: > Martijn van Oosterhout wrote: > > >On Sat, Feb 14, 2004 at 05:11:14PM +0100, Willem Herremans wrote: > > > > > >>I have used the Tcl command > >> > >> set psqlChannel [open "|psql $dbname" RDWR] > >> > >>to create a channel that effectively becomes the input/output channel > >>for psql. By writing to that channel, SQL statements are sent to psql, > >>by reading from that channel, the results are received from psql. > >> > >>That works fine, as long as psql does not prompt for a password. The > >>problem is that psql does not use this channel for prompting for or > >>reading the password. Instead, the password is prompted for on, and read > >>from, the terminal from which the tcl application was started and that > >>is not what I want, because the Tcl application has already received the > >>password from the user. It is, as if psql does not use stdout and stdin > >>for the password. > >> > >> > > > >Look in the manpage for psql, there are several ways to stop it asking for > >passwords, including the PGPASS environment variable, tne .pgpass file and > >setting the user as trust in the config. > > > >Any of these will do what you want... > > > >Hope this helps, > > > > > I am afraid this does not help me very much. > > The manpage of psql neither mentions the PGPASS environment variable, > nor the .pgpass file. It's actually in the libpq documentation... The file .pgpass in a user's home directory is a file that can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). This file should have lines of the following format: hostname:port:databsae:username:password Each of the first four fields may be a literal value, or * which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or \, escape this character with \. The permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass If the permissions are less strict than this, the file will be ignored.
On Sun, Feb 15, 2004 at 10:27:07AM +0100, Willem Herremans wrote: > I am afraid this does not help me very much. > > The manpage of psql neither mentions the PGPASS environment variable, > nor the .pgpass file. Ok, so they're mentioned in the documentation, one under environment variables. The other is mentioned in there too, aswell as briefly on the manpage for pg_dumpall. A quick google should find the details for you. > I understand that, if the server is configured such that the client can > authenticate wihout password, that psql does not prompt for a password, > but it would be a serious limitation for my application that it cannot > support authenitication by means of password. You could always redirect the password into stderr, though that's a bit of a hack. Better just use one of the above supported methods. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > If the Catholic church can survive the printing press, science fiction > will certainly weather the advent of bookwarez. > http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow
Вложения
Stephan Szabo wrote: >On Sun, 15 Feb 2004, Willem Herremans wrote: > > > >>Martijn van Oosterhout wrote: >> >> >> >>>On Sat, Feb 14, 2004 at 05:11:14PM +0100, Willem Herremans wrote: >>> >>> >>> >>> >>>>I have used the Tcl command >>>> >>>> set psqlChannel [open "|psql $dbname" RDWR] >>>> >>>>to create a channel that effectively becomes the input/output channel >>>>for psql. By writing to that channel, SQL statements are sent to psql, >>>>by reading from that channel, the results are received from psql. >>>> >>>>That works fine, as long as psql does not prompt for a password. The >>>>problem is that psql does not use this channel for prompting for or >>>>reading the password. Instead, the password is prompted for on, and read >>>>from, the terminal from which the tcl application was started and that >>>>is not what I want, because the Tcl application has already received the >>>>password from the user. It is, as if psql does not use stdout and stdin >>>>for the password. >>>> >>>> >>>> >>>> >>>Look in the manpage for psql, there are several ways to stop it asking for >>>passwords, including the PGPASS environment variable, tne .pgpass file and >>>setting the user as trust in the config. >>> >>>Any of these will do what you want... >>> >>>Hope this helps, >>> >>> >>> >>> >>I am afraid this does not help me very much. >> >>The manpage of psql neither mentions the PGPASS environment variable, >>nor the .pgpass file. >> >> > >It's actually in the libpq documentation... > >The file .pgpass in a user's home directory is a file >that can contain passwords to be used if the connection requires a >password (and no password has been specified otherwise). >This file should have lines of the following format: >hostname:port:databsae:username:password > >Each of the first four fields may be a literal value, or * which matches >anything. The password field from the first line that matches the >current connection parameters will be used. (Therefore, put more-specific >entries first when you are using wildcards.) >If an entry needs to contain : or >\, escape this character with \. > >The permissions on .pgpass must disallow any >access to world or group; achieve this by the command >chmod 0600 ~/.pgpass >If the permissions are less strict than this, the file will be ignored. > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > Marvellous!. That solves my problem! Thank you. Willem Herremans -- Willem Herremans E-mail: willem.herremans@belgacom.net Dennenlaan 55 B-2520 Ranst Tel. : +32 3 485 64 09 BELGIUM
Try this...i'm using this with pg_dump echo -n -e "username\npassword\n" | pg_dump -u -v dbname > dbname.pgdump thanks! On Sat, 2004-02-14 at 15:53, Martijn van Oosterhout wrote: > On Sat, Feb 14, 2004 at 05:11:14PM +0100, Willem Herremans wrote: > > I have used the Tcl command > > > > set psqlChannel [open "|psql $dbname" RDWR] > > > > to create a channel that effectively becomes the input/output channel > > for psql. By writing to that channel, SQL statements are sent to psql, > > by reading from that channel, the results are received from psql. > > > > That works fine, as long as psql does not prompt for a password. The > > problem is that psql does not use this channel for prompting for or > > reading the password. Instead, the password is prompted for on, and read > > from, the terminal from which the tcl application was started and that > > is not what I want, because the Tcl application has already received the > > password from the user. It is, as if psql does not use stdout and stdin > > for the password. > > Look in the manpage for psql, there are several ways to stop it asking for > passwords, including the PGPASS environment variable, tne .pgpass file and > setting the user as trust in the config. > > Any of these will do what you want... > > Hope this helps,
Willem Herremans wrote: > I am developing a client application for postgreSQL in Tcl/Tk (see > http://gborg.postgresql.org/project/pfm ). > > It mainly uses PgTcl or pgintcl. I don't have any problems with those, > but I am also trying to call psql from my application for SQL statements > typed directly by the user. > > I have used the Tcl command > > set psqlChannel [open "|psql $dbname" RDWR] > > to create a channel that effectively becomes the input/output channel > for psql. By writing to that channel, SQL statements are sent to psql, > by reading from that channel, the results are received from psql. > > That works fine, as long as psql does not prompt for a password. The > problem is that psql does not use this channel for prompting for or > reading the password. Instead, the password is prompted for on, and read > from, the terminal from which the tcl application was started and that > is not what I want, because the Tcl application has already received the > password from the user. It is, as if psql does not use stdout and stdin > for the password. You need to look in the libpq section of the manuals. There is a PGPASSWORD environment variable, but that is deprecated. The preferred method is to create a ~/.pgpass file containing password information. -- Richard Huxton Archonet Ltd