Thread: Specifying psql password on command line

I've read the documentation for the psql commands as well as the createdb
and dropdb commands.  It looks like there is no way to specify the password
on the command line.  The password is always provided in the form of a stdin
prompt.

This makes it very difficult to use the commands from a shell script or a
cron job.    The same with redirecting to/from a file or piping it
somewhere.  It seems like there must be some way to specify the password on
the command line.

Am I missing something?  Any workarounds?

Thanks,
Tauren

preety simple.. In ur shell script use

psql -h localhost ur_db -U ur_user_name <<EOF
ur_passwd
ur_sql_script.sql
EOF

is this is too much of a botheration u can set the environment variables
PGUSER and PGPASSWD

Hope this helps
Anand


On Tue, Jun 05, 2001 at 05:59:28PM -0700, Tauren Mills wrote:
>I've read the documentation for the psql commands as well as the createdb
>and dropdb commands.  It looks like there is no way to specify the password
>on the command line.  The password is always provided in the form of a stdin
>prompt.
>
>This makes it very difficult to use the commands from a shell script or a
>cron job.    The same with redirecting to/from a file or piping it
>somewhere.  It seems like there must be some way to specify the password on
>the command line.
>
>Am I missing something?  Any workarounds?
>
>Thanks,
>Tauren
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

"Tauren Mills" <tauren@servlets.net> writes:
> I've read the documentation for the psql commands as well as the createdb
> and dropdb commands.  It looks like there is no way to specify the password
> on the command line.  The password is always provided in the form of a stdin
> prompt.

Quite deliberately ... if passwords were provided on the command line,
then anyone else on your machine could read them off with 'ps'.

            regards, tom lane

"Tauren Mills" <tauren@servlets.net> writes:
>> Quite deliberately ... if passwords were provided on the command line,
>> then anyone else on your machine could read them off with 'ps'.

> I was actually wondering if that was the reason.  I know that with MySQL,
> the mysql program immediately changes the command line that is displayed
> with 'ps' so that you simply see 'xxxxxx' for the password.  However, there
> is an instant that ps could report the password.

More to the point, changing the command line seen by 'ps' is a highly
nonportable operation.  It doesn't work on all the platforms we support,
and we don't trust it for anything critical even on the platforms where
it seems to work.

> I would think that there would be some way to do this, however.  For
> instance, specify a file path that contains the password.  It seems like
> someone out there must be running the command line programs from an
> automated script.  How are you doing it?  Using something like 'expect'?

I think the common locution is

    echo password | psql ...

This of course is not real secure against ps spying either, but at least
the echo process only runs for a millisecond or two.  Possibly better:
cat a file containing the password into psql.

A far better solution is not to depend on password-based authorization
in the first place.  Consider ident-based auth, if the scripts will run
on trustworthy hosts.  Or if you are running the scripts on the same
host as the postmaster, you might be able to do something with
filesystem access restrictions for the postmaster's socket file.

            regards, tom lane

> > I've read the documentation for the psql commands as well as
> the createdb
> > and dropdb commands.  It looks like there is no way to specify
> the password
> > on the command line.  The password is always provided in the
> form of a stdin
> > prompt.
>
> Quite deliberately ... if passwords were provided on the command line,
> then anyone else on your machine could read them off with 'ps'.

I was actually wondering if that was the reason.  I know that with MySQL,
the mysql program immediately changes the command line that is displayed
with 'ps' so that you simply see 'xxxxxx' for the password.  However, there
is an instant that ps could report the password.

I would think that there would be some way to do this, however.  For
instance, specify a file path that contains the password.  It seems like
someone out there must be running the command line programs from an
automated script.  How are you doing it?  Using something like 'expect'?

I'd love to see an example script if anyone has one.

Thanks,
Tauren