Thread: Pass password in scripts?

Pass password in scripts?

From

"Neil Aggarwal"

Date:

15 February 2007, 00:13:46

Hello:

I have several scripts I would like to run against a database.

With mysql, I can pass the password to the database in
the command line call to the terminal client.

Is there a way to do that with psql?

There is no corresponding user account for
the database user account.

Thanks,
    Neil


--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

Re: Pass password in scripts?

From

"Burak Seydioglu"

Date:

15 February 2007, 00:28:36

I have two solutions that might help.

PHP:

system("echo -n -e \"".$password."\n\" | pg_dump -W --host=".$host."
-U ".$user." ".$database." > ".$backup_file,$output);

Try replacing pg_dump command with "psql -d database -U user"

BASH:

PGDATABASE=database
PGUSER=user
PGPASSWORD=password

export PGUSER PGPASSWORD

HTH,

Burak

On 2/14/07, Neil Aggarwal <neil@jammconsulting.com> wrote:
> Hello:
>
> I have several scripts I would like to run against a database.
>
> With mysql, I can pass the password to the database in
> the command line call to the terminal client.
>
> Is there a way to do that with psql?
>
> There is no corresponding user account for
> the database user account.
>
> Thanks,
>         Neil
>
>
> --
> Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
> FREE! Eliminate junk email and reclaim your inbox.
> Visit http://www.spammilter.com for details.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

Re: Pass password in scripts?

From

Jan Danielsson

Date:

15 February 2007, 00:32:10

Neil Aggarwal wrote:
> Hello:
>
> I have several scripts I would like to run against a database.
>
> With mysql, I can pass the password to the database in
> the command line call to the terminal client.
>
> Is there a way to do that with psql?

   You mean like:

$ psql --user=foobar --password=boofar < script.sql

   Another possibility, if you don't want the password to be visible to
other users on the system, is to use the ~/.pgpass file. See:
http://www.postgresql.org/docs/8.1/static/libpq-pgpass.html

--
Kind regards,
Jan Danielsson

Attachment

signature.asc

Re: Pass password in scripts?

From

Tom Lane

Date:

15 February 2007, 00:35:10

"Neil Aggarwal" <neil@JAMMConsulting.com> writes:
> With mysql, I can pass the password to the database in
> the command line call to the terminal client.

Really?  That's an enormous security hole, because anyone running "ps"
on the system can see your password.

> Is there a way to do that with psql?

Consider using a ~/.pgpass file instead.  Much safer.

            regards, tom lane

Re: Pass password in scripts?

From

"Neil Aggarwal"

Date:

15 February 2007, 00:55:41

Tom:

There is no ~ directory for the db user.
The scripts run as root.

Should I put it in the home directory for root?

Thanks,
    Neil

--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, February 14, 2007 7:35 PM
To: Neil Aggarwal
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

"Neil Aggarwal" <neil@JAMMConsulting.com> writes:
> With mysql, I can pass the password to the database in
> the command line call to the terminal client.

Really?  That's an enormous security hole, because anyone running "ps"
on the system can see your password.

> Is there a way to do that with psql?

Consider using a ~/.pgpass file instead.  Much safer.

            regards, tom lane

Re: Pass password in scripts?

From

"Neil Aggarwal"

Date:

15 February 2007, 00:59:34

Jan:

When I try that, I get:

/usr/local/pgsql/bin/psql: option `--password' doesn't allow an argument

Thanks,
    Neil


--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Jan Danielsson
Sent: Wednesday, February 14, 2007 7:32 PM
To: Neil Aggarwal
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

Neil Aggarwal wrote:
> Hello:
>
> I have several scripts I would like to run against a database.
>
> With mysql, I can pass the password to the database in
> the command line call to the terminal client.
>
> Is there a way to do that with psql?

   You mean like:

$ psql --user=foobar --password=boofar < script.sql

   Another possibility, if you don't want the password to be visible to
other users on the system, is to use the ~/.pgpass file. See:
http://www.postgresql.org/docs/8.1/static/libpq-pgpass.html

--
Kind regards,
Jan Danielsson

Re: Pass password in scripts?

From

"Phillip Smith"

Date:

15 February 2007, 01:00:41

Correct - whoever is running the script (and therefore psql), psql will look
in their home directory.

-p

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Neil Aggarwal
Sent: Thursday, 15 February 2007 12:55
To: 'Tom Lane'
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

Tom:

There is no ~ directory for the db user.
The scripts run as root.

Should I put it in the home directory for root?

Thanks,
    Neil

--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, February 14, 2007 7:35 PM
To: Neil Aggarwal
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

"Neil Aggarwal" <neil@JAMMConsulting.com> writes:
> With mysql, I can pass the password to the database in
> the command line call to the terminal client.

Really?  That's an enormous security hole, because anyone running "ps"
on the system can see your password.

> Is there a way to do that with psql?

Consider using a ~/.pgpass file instead.  Much safer.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: Pass password in scripts?

From

"Andrej Ricnik-Bay"

Date:

15 February 2007, 01:03:38

On 2/15/07, Neil Aggarwal <neil@jammconsulting.com> wrote:

> There is no ~ directory for the db user.
That's easily fixed...

> The scripts run as root.
Bad idea?

> Should I put it in the home directory for root?


> Thanks,
>         Neil
Cheers,
Andrej

Re: Pass password in scripts?

From

"Neil Aggarwal"

Date:

15 February 2007, 01:05:28

Phillip:

That worked.

Thanks,
    Neil

--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Phillip Smith
Sent: Wednesday, February 14, 2007 8:01 PM
To: 'Neil Aggarwal'; 'Tom Lane'
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

Correct - whoever is running the script (and therefore psql), psql will look
in their home directory.

-p

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Neil Aggarwal
Sent: Thursday, 15 February 2007 12:55
To: 'Tom Lane'
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

Tom:

There is no ~ directory for the db user.
The scripts run as root.

Should I put it in the home directory for root?

Thanks,
    Neil

--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, February 14, 2007 7:35 PM
To: Neil Aggarwal
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

"Neil Aggarwal" <neil@JAMMConsulting.com> writes:
> With mysql, I can pass the password to the database in
> the command line call to the terminal client.

Really?  That's an enormous security hole, because anyone running "ps"
on the system can see your password.

> Is there a way to do that with psql?

Consider using a ~/.pgpass file instead.  Much safer.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by
Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Re: Pass password in scripts?

From

"Phillip Smith"

Date:

15 February 2007, 01:08:58

As Andrej pointed out, I'd still want to question (from a security point of
view) why you would want to run the script as root though...

~p

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Neil Aggarwal
Sent: Thursday, 15 February 2007 13:05
To: 'Phillip Smith'; 'Tom Lane'
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

Phillip:

That worked.

Thanks,
    Neil

--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Phillip Smith
Sent: Wednesday, February 14, 2007 8:01 PM
To: 'Neil Aggarwal'; 'Tom Lane'
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

Correct - whoever is running the script (and therefore psql), psql will look
in their home directory.

-p

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Neil Aggarwal
Sent: Thursday, 15 February 2007 12:55
To: 'Tom Lane'
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

Tom:

There is no ~ directory for the db user.
The scripts run as root.

Should I put it in the home directory for root?

Thanks,
    Neil

--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, February 14, 2007 7:35 PM
To: Neil Aggarwal
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

"Neil Aggarwal" <neil@JAMMConsulting.com> writes:
> With mysql, I can pass the password to the database in
> the command line call to the terminal client.

Really?  That's an enormous security hole, because anyone running "ps"
on the system can see your password.

> Is there a way to do that with psql?

Consider using a ~/.pgpass file instead.  Much safer.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by
Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: Pass password in scripts?

From

"Neil Aggarwal"

Date:

15 February 2007, 01:17:44

Andrej:

You are right, running the script as root is
insecure.

This is a development machine so I just log in
and do everthing as root.  It is convenient
since it is not a production machine and dedicated
to me.

Thanks,
    Neil

--
Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com
FREE! Eliminate junk email and reclaim your inbox.
Visit http://www.spammilter.com for details.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Andrej Ricnik-Bay
Sent: Wednesday, February 14, 2007 8:04 PM
To: Neil Aggarwal
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Pass password in scripts?

On 2/15/07, Neil Aggarwal <neil@jammconsulting.com> wrote:

> There is no ~ directory for the db user.
That's easily fixed...

> The scripts run as root.
Bad idea?

> Should I put it in the home directory for root?


> Thanks,
>         Neil
Cheers,
Andrej

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

Re: Pass password in scripts?

From

Jan Danielsson

Date:

15 February 2007, 01:53:49

Neil Aggarwal wrote:
> When I try that, I get:
>
> /usr/local/pgsql/bin/psql: option `--password' doesn't allow an argument
>
>> Is there a way to do that with psql?
>
>    You mean like:
> $ psql --user=foobar --password=boofar < script.sql

   Silly me -- I read the manual page too fast. --password or -W doesn't
take an argument. It just tells the client that you want to enter a
password. This makes since, since passing a password is bad for security.

   I guess you're stuck using .pgpass, which is a better solution anyway.

--
Kind regards,
Jan Danielsson

Attachment

signature.asc