Thread: createdb.exe and psql.exe without Promting Password

Hi.

I would like to create an script that includes calls to createdb.exe and
psql.exe. I would like to use them with a user but without asking or
prompting for pasword. For example:
 createdb.exe -T template0 -U MyPosgresUser MyDataBaseName.

I've read that if I define an environment variable like SET
PGPPASSWORD=MyPosgresUserPassword, both calls dont ask for password. I've
tested this variable in different platforms and does not work.
Do you know how so solve it?

Thanks in advance!!
--
View this message in context:
http://www.nabble.com/createdb.exe-and-psql.exe-without-Promting-Password-tp21939250p21939250.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Inigo Barandiaran wrote:
> Hi.
>
> I would like to create an script that includes calls to createdb.exe and
> psql.exe. I would like to use them with a user but without asking or
> prompting for pasword. For example:
>  createdb.exe -T template0 -U MyPosgresUser MyDataBaseName.

http://www.postgresql.org/docs/current/static/libpq-pgpass.html


--
  Richard Huxton
  Archonet Ltd

On 10/02/2009 18:04, Inigo Barandiaran wrote:

> I would like to create an script that includes calls to createdb.exe and
> psql.exe. I would like to use them with a user but without asking or
> prompting for pasword. For example:
>  createdb.exe -T template0 -U MyPosgresUser MyDataBaseName.
>
> I've read that if I define an environment variable like SET
> PGPPASSWORD=MyPosgresUserPassword, both calls dont ask for password. I've
> tested this variable in different platforms and does not work.
> Do you know how so solve it?

What platform(s) did you test it on? Are you sure that the environment
variable was visible to the script?

Another option is to use a .pgpass file:

  http://www.postgresql.org/docs/8.3/static/libpq-pgpass.html

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Thanks Raymon.<br /><br /> I'm using Windows XP.<br /><br /> What does "visible to the script" means?<br /><br />
Thanksin advance!<br /><br /> Best,<br /><blockquote cite="mid:4991C35C.1010907@iol.ie" type="cite"><pre wrap="">On
10/02/200918:04, Inigo Barandiaran wrote:
 
 </pre><blockquote type="cite"><pre wrap="">I would like to create an script that includes calls to createdb.exe and
psql.exe. I would like to use them with a user but without asking or
prompting for pasword. For example:createdb.exe -T template0 -U MyPosgresUser MyDataBaseName.

I've read that if I define an environment variable like SET
PGPPASSWORD=MyPosgresUserPassword, both calls dont ask for password. I've
tested this variable in different platforms and does not work. 
Do you know how so solve it?   </pre></blockquote><pre wrap="">
What platform(s) did you test it on? Are you sure that the environment
variable was visible to the script?

Another option is to use a .pgpass file:
 <a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/8.3/static/libpq-pgpass.html">http://www.postgresql.org/docs/8.3/static/libpq-pgpass.html</a>

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
<a class="moz-txt-link-abbreviated" href="mailto:rod@iol.ie">rod@iol.ie</a>
Galway Cathedral Recitals: <a class="moz-txt-link-freetext"
href="http://www.galwaycathedral.org/recitals">http://www.galwaycathedral.org/recitals</a>
------------------------------------------------------------------
 </pre></blockquote><br />

On Tue, Feb 10, 2009 at 07:22:24PM +0100, IIIigo Barandiaran wrote:
> What does "visible to the script" means?

Each command interpreter has its own set of variables; you need to set
them in the correct one.  For example, starting one copy of "cmd" and
typing "SET var=value" and then closing it and starting another one and
running your "script" won't work because they are different interpreters
(i.e. different instances, or processes, of the same executable, or
program).

If you're executing "SET ..." in one command interpreter and then double
clicking on your script (a .bat file?) then you're effectively starting
another interpreter on that one script, it will exit when it's reached
the end of your script.

--
  Sam  http://samason.me.uk/

On 10/02/2009 21:47, Sam Mason wrote:
> On Tue, Feb 10, 2009 at 07:22:24PM +0100, IIIigo Barandiaran wrote:
>> What does "visible to the script" means?
>
> Each command interpreter has its own set of variables; you need to set
> them in the correct one.  For example, starting one copy of "cmd" and
> typing "SET var=value" and then closing it and starting another one and
> running your "script" won't work because they are different interpreters
> (i.e. different instances, or processes, of the same executable, or
> program).
>
> If you're executing "SET ..." in one command interpreter and then double
> clicking on your script (a .bat file?) then you're effectively starting
> another interpreter on that one script, it will exit when it's reached
> the end of your script.

I couldn't have put it better! :-) You could create a system-wide
environment variable via right-clicking on My Computer, selecting
properties, then clicking "Environment variables" - but this is a
*really* bad idea for storing a password, as it's visible to anyone
using the computer. Far better to use the .pgpass file.

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Thank you all!

and Thanks Sam for the explanation about environment variable. I didn't
know about that :)

I've also tried the option of  .pgpass file, that as far as I understand
in Windows is pgpass.conf. I did a file containing only this line

/*/:/*///:/*///:/MyPostgres///User/:MyPostgres://Password

/I put this file in:
 c:\MyPosgreInstalation  and
 c:\MyPosgreInstalation\bin and
 c:\MyPosgreInstalation\data and
 C:\Documents and Settings\postgres\Application Data\

all without success. What I'm doing wrong? When I try to execute createdb.exe -T template0 -U MyPostgres MyDataBase
alwaysask for password. 

I'm running Windows XP 32 bit.

Thanks in advance for your support.

Best,



> On 10/02/2009 21:47, Sam Mason wrote:
>
>> On Tue, Feb 10, 2009 at 07:22:24PM +0100, IIIigo Barandiaran wrote:
>>
>>> What does "visible to the script" means?
>>>
>> Each command interpreter has its own set of variables; you need to set
>> them in the correct one.  For example, starting one copy of "cmd" and
>> typing "SET var=value" and then closing it and starting another one and
>> running your "script" won't work because they are different interpreters
>> (i.e. different instances, or processes, of the same executable, or
>> program).
>>
>> If you're executing "SET ..." in one command interpreter and then double
>> clicking on your script (a .bat file?) then you're effectively starting
>> another interpreter on that one script, it will exit when it's reached
>> the end of your script.
>>
>
> I couldn't have put it better! :-) You could create a system-wide
> environment variable via right-clicking on My Computer, selecting
> properties, then clicking "Environment variables" - but this is a
> *really* bad idea for storing a password, as it's visible to anyone
> using the computer. Far better to use the .pgpass file.
>
> Ray.
>
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
>

Iñigo Barandiaran wrote:
> Thank you all!
>
> and Thanks Sam for the explanation about environment variable. I didn't
> know about that :)
>
> I've also tried the option of  .pgpass file, that as far as I understand
> in Windows is pgpass.conf. I did a file containing only this line
>
> /*/:/*///:/*///:/MyPostgres///User/:MyPostgres://Password

If you want to run psql logged into your machine as "inigo" and connect
to "mydb1" as user "pguser1" with password "pass1" then the line should be:

  localhost:5432:mydb1:pguser1:pass1

You might want some stars in there

  *:*:*:pguser1:pass1

> /I put this file in:
> c:\MyPosgreInstalation  and   c:\MyPosgreInstalation\bin and
> c:\MyPosgreInstalation\data and
> C:\Documents and Settings\postgres\Application Data\

If you are logged into your machine as "inigo" it would go into:

C:\Documents and Settings\inigo\Application Data\postgresql\pgpass.conf

If you logged into your machine as user "richard" you would need to
create a new pgpass.conf file and put it in richard's "Application Data"
folder.

--
  Richard Huxton
  Archonet Ltd

On Tue, Feb 10, 2009 at 7:04 PM, Inigo Barandiaran
<ibarandiaran@vicomtech.org> wrote:
> I've read that if I define an environment variable like SET
> PGPPASSWORD=MyPosgresUserPassword, both calls dont ask for password. I've
> tested this variable in different platforms and does not work.
> Do you know how so solve it?

As you are dealing with PostgreSQL, and not Pretty Good Privacy, I'd
suggest removing the additional "P": PGPASSWORD, not PGPPASSWORD

But .pgpass is the Right Way to do it, IMHO

Hi Dennis, all.

You are right. I know that sounds stupid, but the problem why the
environment variable didn't work was due that 'P' :). I don't know why but I
didn't realize that I was writing the name of the variable wrong :S
Now, I've tried both options and both works correctly. I think for the
moment will continue with the environment variable way. I only create it
while executing a .bat file for creating the database and create the tables,
and after script execution this variable is automatically destroyed, so I
can't see any security problem using this method, isn't it?

Thanks all of you. This mailing list is great! ;)


Dennis Brakhane-2 wrote:
>
> On Tue, Feb 10, 2009 at 7:04 PM, Inigo Barandiaran
> <ibarandiaran@vicomtech.org> wrote:
>> I've read that if I define an environment variable like SET
>> PGPPASSWORD=MyPosgresUserPassword, both calls dont ask for password. I've
>> tested this variable in different platforms and does not work.
>> Do you know how so solve it?
>
> As you are dealing with PostgreSQL, and not Pretty Good Privacy, I'd
> suggest removing the additional "P": PGPASSWORD, not PGPPASSWORD
>
> But .pgpass is the Right Way to do it, IMHO
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

--
View this message in context:
http://www.nabble.com/createdb.exe-and-psql.exe-without-Promting-Password-tp21939250p21965659.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 2009-02-10, Inigo Barandiaran <ibarandiaran@vicomtech.org> wrote:
>
> Hi.
>
> I would like to create an script that includes calls to createdb.exe and
> psql.exe. I would like to use them with a user but without asking or
> prompting for pasword. For example:
>  createdb.exe -T template0 -U MyPosgresUser MyDataBaseName.
>
> I've read that if I define an environment variable like SET
> PGPPASSWORD=MyPosgresUserPassword, both calls dont ask for password. I've
> tested this variable in different platforms and does not work.
> Do you know how so solve it?

no, because it always works for me.

so I'm guessing what you're doing wrong.
you must do the SET in the same (or parent of the) envrionment as you execute createdb.

doing this will not work:

 system("SET PGPPASSWORD=MyPosgresUserPassword");
 system("createdb.exe -T template0 -U MyPosgresUser MyDataBaseName");

doing this will:

 setenv("PGPASSWORD","MyPosgresUserPassword");
 system("createdb.exe -T template0 -U MyPosgresUser MyDataBaseName");

On 2009-02-10, Iñigo Barandiaran <ibarandiaran@vicomtech.org> wrote:

>
> Thanks Raymon.
>
> I'm using Windows XP.
>
> What does "visible to the script" means?

means doing it right instead of wrong.
for a targeted explanation post example code.