Обсуждение: psql shell with no password prompt
Hi,
I created several SQL that are automatically executed via windows task scheduler, here is an example
psql -U "dbadmin" -d mydb -f D:\script.sql
It was running well until I changed the user (to open a session) associated to these task.
The script prompt for a password for user dbadmin.
How to avoid that? I guess there is a config so dbadmin will "trust" this new user....
Thanks for your help,
Steve
<Steve.Toutant@inspq.qc.ca> wrote: > The script prompt for a password for user dbadmin. > How to avoid that? I guess there is a config so dbadmin will > "trust" this new user.... You can use pg_hba.conf or pgpass: http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html -Kevin
Thanks Kevin,
Which of these 2 methods is the best practice?
It is working with pgpass.
But not yet with hba.conf
I tried this without success.
host all all 127.0.0.1/32 trust
steve
| "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 27/07/2010 01:17 PM |
|
<Steve.Toutant@inspq.qc.ca> wrote:
> The script prompt for a password for user dbadmin.
> How to avoid that? I guess there is a config so dbadmin will
> "trust" this new user....
You can use pg_hba.conf or pgpass:
http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html
http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html
-Kevin
On Tue, 2010-07-27 at 14:16 -0400, Steve.Toutant@inspq.qc.ca wrote: > > Thanks Kevin, > Which of these 2 methods is the best practice? > > It is working with pgpass. > But not yet with hba.conf > I tried this without success. > host all all 127.0.0.1/32 trust > > > steve Best practice? Use .pgpass, not trust. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
"Joshua D. Drake" <jd@commandprompt.com> wrote: > Best practice? Use .pgpass, not trust. I would take ident over either in Linux; but the OP is on Windows and I don't know if that's an option or, if so, how safe it is. -Kevin
On Tue, Jul 27, 2010 at 12:16 PM, <Steve.Toutant@inspq.qc.ca> wrote: > > Thanks Kevin, > Which of these 2 methods is the best practice? > > It is working with pgpass. > But not yet with hba.conf > I tried this without success. > host all all 127.0.0.1/32 trust You have to reload / restart the pg service for that to take effect. Note that will only affect incoming connections on the same machine.
You can set user and password with environment variables (PGUSER and PGPASSWORD) , also in a Windows batch or program.
SET PGPASSWORD=xxxyyyzzz
psql -U "dbadmin" -d mydb -f D:\script.sql
Regards
Walter
Regards
Walter
On Tue, Jul 27, 2010 at 6:53 PM, <Steve.Toutant@inspq.qc.ca> wrote:
Hi,
I created several SQL that are automatically executed via windows task scheduler, here is an example
psql -U "dbadmin" -d mydb -f D:\script.sql
It was running well until I changed the user (to open a session) associated to these task.
The script prompt for a password for user dbadmin.How to avoid that? I guess there is a config so dbadmin will "trust" this new user....
Thanks for your help,
Steve
However, those are deprecated, and the .pgpass is considered the preferred method. On Tue, Jul 27, 2010 at 3:56 PM, Walter Willmertinger <willmis@gmail.com> wrote: > You can set user and password with environment variables (PGUSER and > PGPASSWORD) , also in a Windows batch or program. > SET PGPASSWORD=xxxyyyzzz > psql -U "dbadmin" -d mydb -f D:\script.sql > > > > Regards > > Walter > > > On Tue, Jul 27, 2010 at 6:53 PM, <Steve.Toutant@inspq.qc.ca> wrote: >> >> Hi, >> I created several SQL that are automatically executed via windows task >> scheduler, here is an example >> psql -U "dbadmin" -d mydb -f D:\script.sql >> >> It was running well until I changed the user (to open a session) >> associated to these task. >> The script prompt for a password for user dbadmin. >> >> How to avoid that? I guess there is a config so dbadmin will "trust" this >> new user.... >> >> Thanks for your help, >> >> Steve > -- To understand recursion, one must first understand recursion.
On Tue, 2010-07-27 at 14:16 -0400, Steve.Toutant@inspq.qc.ca wrote: > > Thanks Kevin, > Which of these 2 methods is the best practice? > > It is working with pgpass. > But not yet with hba.conf > I tried this without success. > host all all 127.0.0.1/32 trust > > > steve Best practice? Use .pgpass, not trust. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
I hope the method with the environment variables will hold on.
RegardsWalter
For security reasons it is much better to use the variable method with PGPASSWORD. I can set the environment hidden from any user by a program.
The .pgpass is readable for any admin, opposed to the statement in the docs: "On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made."
Regards
On Wed, Jul 28, 2010 at 1:02 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
However, those are deprecated, and the .pgpass is considered the
preferred method.--
On Tue, Jul 27, 2010 at 3:56 PM, Walter Willmertinger <willmis@gmail.com> wrote:
> You can set user and password with environment variables (PGUSER and
> PGPASSWORD) , also in a Windows batch or program.
> SET PGPASSWORD=xxxyyyzzz
> psql -U "dbadmin" -d mydb -f D:\script.sql
>
>
>
> Regards
>
> Walter
>
>
> On Tue, Jul 27, 2010 at 6:53 PM, <Steve.Toutant@inspq.qc.ca> wrote:
>>
>> Hi,
>> I created several SQL that are automatically executed via windows task
>> scheduler, here is an example
>> psql -U "dbadmin" -d mydb -f D:\script.sql
>>
>> It was running well until I changed the user (to open a session)
>> associated to these task.
>> The script prompt for a password for user dbadmin.
>>
>> How to avoid that? I guess there is a config so dbadmin will "trust" this
>> new user....
>>
>> Thanks for your help,
>>
>> Steve
>
To understand recursion, one must first understand recursion.