Thread: passing passords to pgsql/pg_create/pg_dump programmatically

Hello,

I have written a program perl/Tkprogram, based on postgres. For
maintenance reasons in my program I use these commands:

      `dropdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
      `createdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
      `pg_dump -U postgres -h $DATABASE_SERVER $DATABASE_NAME | psql -
U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;

my @psqlOutput = `psql -l -U postgres -h $_`;


my program has a graphic interface but whenever I use these command,
postgres ask for passord in the command line. How can I give these
commands the right passowrd programmatically or how can I   interact
with these to give the passwords by a graphic box?

Thanks and best refards,

Filippo

filippo wrote:
> Hello,
>
> I have written a program perl/Tkprogram, based on postgres. For
> maintenance reasons in my program I use these commands:
>
>       `dropdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
>       `createdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
>       `pg_dump -U postgres -h $DATABASE_SERVER $DATABASE_NAME | psql -
> U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
>
> my @psqlOutput = `psql -l -U postgres -h $_`;
>
>
> my program has a graphic interface but whenever I use these command,
> postgres ask for passord in the command line. How can I give these
> commands the right passowrd programmatically or how can I   interact
> with these to give the passwords by a graphic box?

You can use the PGPASSWORD environment variable, or the ~/.pgpass file
(which can actually be somewhere else by using the PGPASSFILE
environment variable, allowing you to store passwords in /tmp or
something like that, to avoid destroying a .pgpass file the user may
have).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

* "filippo" <filippo2991@virgilio.it> wrote:
>
> Hello,
>
> I have written a program perl/Tkprogram, based on postgres. For
> maintenance reasons in my program I use these commands:
>
>       `dropdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
>       `createdb -U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
>       `pg_dump -U postgres -h $DATABASE_SERVER $DATABASE_NAME | psql -
> U postgres -h $BACKUP_SERVER $BACKUP_DATABASE_NAME`;
>
> my @psqlOutput = `psql -l -U postgres -h $_`;
>
>
> my program has a graphic interface but whenever I use these command,
> postgres ask for passord in the command line. How can I give these
> commands the right passowrd programmatically or how can I   interact
> with these to give the passwords by a graphic box?

$ dropdb --help
$ createdb --help
$ pg_dump --help

As you can see, all of these command line utilities accept the -W
parameter followed by the password.

Alternatively you can configure PostgreSQL to allow connections
from certain clients without password authentication:

http://www.postgresql.org/docs/8.2/static/client-authentication.html

--
Lars Haugseth

"If anyone disagrees with anything I say, I am quite prepared not only to
 retract it, but also to deny under oath that I ever said it." -Tom Lehrer

On 20/03/2007 14:04, Lars Haugseth wrote:

> As you can see, all of these command line utilities accept the -W
> parameter followed by the password.

Correct me if I'm wrong, but doesn't -W simply force a prompt from the
password? - it doesn't accept the password on the command line.

Using the .pgpass file allows command line utilities to run unattended
without having to supply a password manually.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------