Providing the password to psql from a script

[Willem Herremans]

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='blabl a'
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFALorIY5T wig3Ge+YRAnm8AJ 9z8brBxVqYWOYO5 twJZJEM/YKnsgCfamJC
JpstcQXoDLhlgwV cbADsn38=
=w5AL
-----END PGP SIGNATURE-----

[Willem Herremans]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

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:d atabsae:usernam e: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

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAL0DrY5T wig3Ge+YRAlRbAJ 0UMDN652jeC29bC JlUTxrUf38MagCg g7ml
WOB0UcpdNNincH9 msIdn+DY=
=sqNF
-----END PGP SIGNATURE-----

[Willem Herremans]

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:userna me: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: wi************* *@belgacom.net
Dennenlaan 55
B-2520 Ranst Tel. : [phone]
BELGIUM

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Troy Campano]

Try this...i'm using this with pg_dump
echo -n -e "username\npass word\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,

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

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

---------------------------(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)