Home > mailing lists

Thread: Need to run pg_dump from the laptop without entering the password

Need to run pg_dump from the laptop without entering the password

From

M Sarwar

Date:

21 October 2023, 18:07:01

Hello,

Environment:-

Database Postgres 13.7 is running from AWS RDS.

I am trying to run pg_dump from my Windows laptop which has got pgAdmin installed on it.

When I installed pgAdmin, it has pg_dump on the client side.

Now I am trying to make backup using pg_dump from my laptop. I am trying to run as Windows job from the laptop.

I need to schedule the job without entering the password.

I am going to look into scheduling job thru aws which is later and not right now.

Can you please help me in scheduling thru Windows from my laptop without entering the password?

I am having issues with entering the password while running this job.

Thanks,

Sarwar

Re: Need to run pg_dump from the laptop without entering the password

From

Ron

Date:

21 October 2023, 18:18:32

On 10/21/23 10:07, M Sarwar wrote:

P {margin-top:0;margin-bottom:0;}
Hello,

Environment:-
Database Postgres 13.7 is running from AWS RDS.
I am trying to run pg_dump from my Windows laptop which has got pgAdmin installed on it.
When I installed pgAdmin, it has pg_dump on the client side.

Now I am trying to make backup using pg_dump from my laptop. I am trying to run as Windows job from the laptop.
I need to schedule the job without entering the password.
I am going to look into scheduling job thru aws which is later and not right now.
Can you please help me in scheduling thru Windows from my laptop without entering the password?
I am having issues with entering the password while running this job.

https://www.postgresql.org/docs/15/libpq-pgpass.html

The file .pgpass in a user's home directory can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

--
Born in Arizona, moved to Babylonia.

Re: Need to run pg_dump from the laptop without entering the password

From

ramsiddu007

Date:

21 October 2023, 18:24:27

Hey Sarwar,

Thanks for reaching out. If you want to schedule a job from your Windows laptop without entering the password, you can use the pgpass.conf file. This file allows you to manage passwords for PostgreSQL connections.

Here's how you can do it:

1. Open the pgpass.conf file in a text editor.

2. Add a new line in the format: hostname:port:database:username:password or hostname:port:*:username:password

3. Save the file.

After setting up the pgpass.conf file (like C:\Users\<your_user_name>\AppData\Roaming\postgresql\pgpass.conf), you should be able to schedule the job without entering the password.

Thank you!

Best Regards:

Ramanna Gunde

Don't complain about the HEAT, PLANT A Tree

On Sat, 21 Oct 2023 at 20:41, M Sarwar <sarwarmd02@outlook.com> wrote:

Hello,
Environment:-
Database Postgres 13.7 is running from AWS RDS.
I am trying to run pg_dump from my Windows laptop which has got pgAdmin installed on it.
When I installed pgAdmin, it has pg_dump on the client side.
Now I am trying to make backup using pg_dump from my laptop. I am trying to run as Windows job from the laptop.
I need to schedule the job without entering the password.
I am going to look into scheduling job thru aws which is later and not right now.
Can you please help me in scheduling thru Windows from my laptop without entering the password?
I am having issues with entering the password while running this job.
Thanks,
Sarwar

Best Regards:

Ramanna Gunde

Don't complain about the HEAT,

PLANT A .

Re: Need to run pg_dump from the laptop without entering the password

From

M Sarwar

Date:

23 October 2023, 01:31:22

Hello,

I appreciate the responses from Ron and Ramana.

Now Error Message:- "pg_dump: error: connection to database "ngbrpesb" failed: fe_sendauth: no password supplied"

File names attempted:-

C:\Users\N28068\AppData\Roaming\pgAdmin\pgpass.conf
C:\Users\N28068\AppData\Roaming\pgAdmin\.pgpass
C:\Users\N28068\AppData\Roaming\postgresql\pgpass.conf
C:\Users\N28068\AppData\Roaming\postgresql\.pgpass

A.1) On my laptop initially, I had only subdirectory called C:\Users\N28068\AppData\Roaming\pgAdmin and no sub directory called 'postgresql'.

A.2) I tried using the subdirectory pgAdmin first when pg_dump did not have success, I created sub directory postgresql under C:\Users\N28068\AppData\Roaming\

B.3) Initially, I tried to create the file pgpass.conf.

B.4) When I did not have success in the step B.3, I also tried to create a file by name .pgpass as per some of the documentation. In both cases B.3 and B.4, I am getting the same error message as stated above.

I created .pgpass file in C:\Users\N28068\AppData\Roaming\postgresql but when I run pg_dump command I got the same above error message.

I also tried using the file name pgpass.conf from the local directory, C:\Users\N28068\AppData\Roaming\postgresql and I am getting the same error message.

When pgpass.conf file did not run successfully, I changed the file name .pgpass and tried the same again.

Thanks,

Sarwar

From: Ron <ronljohnsonjr@gmail.com>
Sent: Saturday, October 21, 2023 11:18 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Need to run pg_dump from the laptop without entering the password

On 10/21/23 10:07, M Sarwar wrote:

Hello,

Environment:-
Database Postgres 13.7 is running from AWS RDS.
I am trying to run pg_dump from my Windows laptop which has got pgAdmin installed on it.
When I installed pgAdmin, it has pg_dump on the client side.

Now I am trying to make backup using pg_dump from my laptop. I am trying to run as Windows job from the laptop.
I need to schedule the job without entering the password.
I am going to look into scheduling job thru aws which is later and not right now.
Can you please help me in scheduling thru Windows from my laptop without entering the password?
I am having issues with entering the password while running this job.

--
Born in Arizona, moved to Babylonia.

Re: Need to run pg_dump from the laptop without entering the password

From

Erik Wienhold

Date:

23 October 2023, 02:19:37

On 2023-10-23 00:31 +0200, M Sarwar wrote:
> Now Error Message:- "pg_dump: error: connection to database "ngbrpesb" failed: fe_sendauth: no password supplied"

Could be that the hostname in pgpass.conf does not match the hostname in
the connection parameters.  The hostname in pgpass.conf is
case-sensitive (not mentioned in the docs), so it must match the exact
spelling as in the connection parameters.

> File names attempted:-
> 
>   1.  C:\Users\N28068\AppData\Roaming\pgAdmin\pgpass.conf
>   2.  C:\Users\N28068\AppData\Roaming\pgAdmin\.pgpass
>   3.  C:\Users\N28068\AppData\Roaming\postgresql\pgpass.conf

Path #3 should be the correct one if user N28068 runs pg_dump.  You also
wrote that you want to schedule the execution of pg_dump.  Does the
scheduled job also run as that user?

-- 
Erik

Re: Need to run pg_dump from the laptop without entering the password

From

Ron

Date:

23 October 2023, 02:55:19

On 10/22/23 18:19, Erik Wienhold wrote:
[snip]

The hostname in pgpass.conf is

case-sensitive (not mentioned in the docs), so it must match the exact
spelling as in the connection parameters.

That annoys me to. no. end. Hostnames being lowercase here, uppercase there, and mixed case somewhere else and all working just fine except .pgpass regularly bites me when scripting.

--
Born in Arizona, moved to Babylonia.

Re: Need to run pg_dump from the laptop without entering the password

From

Erik Wienhold

Date:

23 October 2023, 04:00:56

On 2023-10-23 01:55 +0200, Ron wrote:
> On 10/22/23 18:19, Erik Wienhold wrote:
> [snip]
> > The hostname in pgpass.conf is
> > case-sensitive (not mentioned in the docs), so it must match the exact
> > spelling as in the connection parameters.
> 
> That annoys me to. no. end.  Hostnames being lowercase here, uppercase
> there, and mixed case somewhere else and all working just fine *except*
> .pgpass regularly bites me when scripting.

I know.  Found a thread [1] of yours when searching for a reference
because I was sure that I read about that behavior somewhere.  But
apparently it's undocumented.  Here's a patch that adds a note.

[1] https://www.postgresql.org/message-id/a5b3740b-a665-7afe-052b-4382d6daf733%40gmail.com

-- 
Erik

Attachment

v1-0001-doc-Add-note-on-case-sensitive-matching-in-passwo.patch

Re: Need to run pg_dump from the laptop without entering the password

From

M Sarwar

Date:

23 October 2023, 04:27:55

Hi Erik,

After seeing your email, I have verified another time now. It is a RDS endpoint.

I see that host naming convention followed correct. Case sensitivity is not an issue.

Yes, N28068 is the user. At this time, I am running the script manually. My plan is to schedule the script thru a job and the owner is going to be N28068 .

Thanks,

Sarwar

From: Erik Wienhold <ewie@ewie.name>
Sent: Sunday, October 22, 2023 7:19 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Need to run pg_dump from the laptop without entering the password

On 2023-10-23 00:31 +0200, M Sarwar wrote:
> Now Error Message:- "pg_dump: error: connection to database "ngbrpesb" failed: fe_sendauth: no password supplied"

Could be that the hostname in pgpass.conf does not match the hostname in
the connection parameters. The hostname in pgpass.conf is
case-sensitive (not mentioned in the docs), so it must match the exact
spelling as in the connection parameters.

> File names attempted:-
>
>   1. C:\Users\N28068\AppData\Roaming\pgAdmin\pgpass.conf
>   2. C:\Users\N28068\AppData\Roaming\pgAdmin\.pgpass
>   3. C:\Users\N28068\AppData\Roaming\postgresql\pgpass.conf

Path #3 should be the correct one if user N28068 runs pg_dump. You also
wrote that you want to schedule the execution of pg_dump. Does the
scheduled job also run as that user?

--
Erik

Re: Need to run pg_dump from the laptop without entering the password

From

M Sarwar

Date:

23 October 2023, 04:30:11

Hi Ron,

Yes, I have verified the host naming convention.

It is a RDS endpoint.

I see that host naming convention followed correct. Case sensitivity is not an issue.

Thanks,

Sarwar

From: Ron <ronljohnsonjr@gmail.com>
Sent: Sunday, October 22, 2023 7:55 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Need to run pg_dump from the laptop without entering the password

On 10/22/23 18:19, Erik Wienhold wrote:
[snip]

The hostname in pgpass.conf is

case-sensitive (not mentioned in the docs), so it must match the exact
spelling as in the connection parameters.

That annoys me to. no. end. Hostnames being lowercase here, uppercase there, and mixed case somewhere else and all working just fine except .pgpass regularly bites me when scripting.

--
Born in Arizona, moved to Babylonia.

Re: Need to run pg_dump from the laptop without entering the password

From

M Sarwar

Date:

23 October 2023, 04:54:25

Hi Ron / Erik,

That did work.

I just corrected the file name from .pgpass to pgpass.cong.
While invoking pg_dump, I just added user name. Earlier, I did not give user name by thinking that it will pick up from pgpass.conf.

Thank you so much!

Sarwar

From: M Sarwar <sarwarmd02@outlook.com>
Sent: Sunday, October 22, 2023 9:30 PM
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Need to run pg_dump from the laptop without entering the password