Обсуждение: Logging on without prompt for password
Hi,
Is there a way of enabling a user to logon without being prompted for password? I know there is the .pgpass file, but this file requires the port# and my server has several postgres clusters with different port#. So, am not sure if I can use the .pgpass file.
And at the pg_hba.conf file, the id already has ‘trust’ method, but it is still being prompted for password.
This id will be used for automated scanning and needs to run pg_dumpall.
Thanks.
Maria A Rossi
Database Administration
PGDS (US)
email: maria.rossi@us.pgds.com
cell phone: 517.256.4392
work phone: 517.367.3099
work phone extension: 23099
Hi,
Is there a way of enabling a user to logon without being prompted for password? I know there is the .pgpass file, but this file requires the port# and my server has several postgres clusters with different port#. So, am not sure if I can use the .pgpass file.
And at the pg_hba.conf file, the id already has ‘trust’ method, but it is still being prompted for password.
This id will be used for automated scanning and needs to run pg_dumpall.
Thanks.
Maria A Rossi
Database Administration
PGDS (US)
email: maria.rossi@us.pgds.com
cell phone: 517.256.4392work phone: 517.367.3099
work phone extension: 23099
Is there a way of enabling a user to logon without being prompted for password? I know there is the .pgpass file, but this file requires the port# and my server has several postgres clusters with different port#. So, am not sure if I can use the .pgpass file.
And at the pg_hba.conf file, the id already has ‘trust’ method, but it is still being prompted for password.
Here’s what the automated discovery is using:
/usr/pgsql-9.3/bin/pg_dumpall" -U bmcap01 -s -x
And it got this error:
pg_dumpall: could not connect to database "template1": could not connect to server: No such file or directory
Then, I retried the pg_dumpall with the –p (for port#) and it prompted for the password.
Thanks.
Maria A Rossi
Database Administration
PGDS (US)
email: maria.rossi@jackson.com
cell phone: 517.256.4392
work phone: 517.367.3099
work phone extension: 23099
From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, October 20, 2015 12:07 PM
To: Rossi, Maria <maria.rossi@jackson.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Logging on without prompt for password
On Tue, Oct 20, 2015 at 10:38 AM, Rossi, Maria <maria.rossi@jackson.com> wrote:
Is there a way of enabling a user to logon without being prompted for password? I know there is the .pgpass file, but this file requires the port# and my server has several postgres clusters with different port#. So, am not sure if I can use the .pgpass file.
In this situation you would have a single line for each cluster in the .pgpass file.
And at the pg_hba.conf file, the id already has ‘trust’ method, but it is still being prompted for password.
We can give better advice if you provide the command line invocations you are using.
David J.
Have you tried PGPASSWORD env variable and unset it. I find it especially useful when scripting.On Tue, Oct 20, 2015 at 10:38 AM, Rossi, Maria <maria.rossi@jackson.com> wrote:Hi,
Is there a way of enabling a user to logon without being prompted for password? I know there is the .pgpass file, but this file requires the port# and my server has several postgres clusters with different port#. So, am not sure if I can use the .pgpass file.
And at the pg_hba.conf file, the id already has ‘trust’ method, but it is still being prompted for password.
This id will be used for automated scanning and needs to run pg_dumpall.
Thanks.
Here’s what the automated discovery is using:
/usr/pgsql-9.3/bin/pg_dumpall" -U bmcap01 -s -x
And it got this error:
pg_dumpall: could not connect to database "template1": could not connect to server: No such file or directory
Then, I retried the pg_dumpall with the –p (for port#) and it prompted for the password.
Thanks.
Your User does not have permission to base template1
Glauco Torres
2015-10-20 14:15 GMT-02:00 Rossi, Maria <maria.rossi@jackson.com>:Here’s what the automated discovery is using:
/usr/pgsql-9.3/bin/pg_dumpall" -U bmcap01 -s -x
And it got this error:
pg_dumpall: could not connect to database "template1": could not connect to server: No such file or directory
Then, I retried the pg_dumpall with the –p (for port#) and it prompted for the password.
Thanks.
Hi,
Is there a way of enabling a user to logon without being prompted for password? I know there is the .pgpass file, but this file requires the port# and my server has several postgres clusters with different port#. So, am not sure if I can use the .pgpass file.
And at the pg_hba.conf file, the id already has ‘trust’ method, but it is still being prompted for password.
This id will be used for automated scanning and needs to run pg_dumpall.
Thanks.
Maria A Rossi
Database Administration
PGDS (US)
email: maria.rossi@us.pgds.com
cell phone: 517.256.4392work phone: 517.367.3099
work phone extension: 23099
We will try the .pgpass file at the user’s home directory, using *.* for port as suggested by Scott Mead. This way I don’t have to worry about setting the PGPASSWORD env var for the user.
I still need to create a role for the user, correct? It needs admin to be able to rung pg_dumpall.
Thanks everyone.
Maria A Rossi
Database Administration
PGDS (US)
email: maria.rossi@us.pgds.com
cell phone: 517.256.4392
work phone: 517.367.3099
work phone extension: 23099
From: Glauco Torres [mailto:torres.glauco@gmail.com]
Sent: Tuesday, October 20, 2015 12:19 PM
To: Rossi, Maria <maria.rossi@jackson.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Logging on without prompt for password
2015-10-20 14:17 GMT-02:00 Glauco Torres <torres.glauco@gmail.com>:
2015-10-20 14:15 GMT-02:00 Rossi, Maria <maria.rossi@jackson.com>:
Here’s what the automated discovery is using:
/usr/pgsql-9.3/bin/pg_dumpall" -U bmcap01 -s -x
And it got this error:
pg_dumpall: could not connect to database "template1": could not connect to server: No such file or directory
Then, I retried the pg_dumpall with the –p (for port#) and it prompted for the password.
Thanks.
Are you using pgbouncer?
Back to my original question, though. Any ideas why it is still prompting for password even though it had ‘trust’ in the pg_hba.conf? The .pgpass or the PGPASSWORD is only a work-around for that, I think. ‘Trust’ is supposed to:
Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication
So, I shouldn’t need to set-up .pgpass or PGPASSWORD.
Any thoughts?
Scott Mead
Sr. Architect
OpenSCG
PostgreSQL, Java & Linux Experts
Thanks.
Maria A Rossi
Database Administration
PGDS (US)
email: maria.rossi@us.pgds.com
cell phone: 517.256.4392work phone: 517.367.3099
work phone extension: 23099
From: Glauco Torres [mailto:torres.glauco@gmail.com]
Sent: Tuesday, October 20, 2015 1:54 PM
To: Rossi, Maria <maria.rossi@jackson.com>
Subject: Re: [ADMIN] Logging on without prompt for password
2015-10-20 15:50 GMT-02:00 Glauco Torres <torres.glauco@gmail.com>:
2015-10-20 15:44 GMT-02:00 Rossi, Maria <maria.rossi@jackson.com>:
We will try the .pgpass file at the user’s home directory, using *.* for port as suggested by Scott Mead. This way I don’t have to worry about setting the PGPASSWORD env var for the user.
I still need to create a role for the user, correct? It needs admin to be able to rung pg_dumpall.
Thanks everyone.
The manual helps a lot in these types of doubts
"Also you will need superuser privileges to execute the saved script in order to be allowed to add users and groups, and to create databases."
http://www.postgresql.org/docs/current/static/app-pg-dumpall.html
Sorry,
O copied the wrong part "Since pg_dumpall reads tables from all databases you will most likely have to connect as a database superuser in order to produce a complete dump."
From: Rossi, Maria <maria.rossi@jackson.com>
Date: Tue, Oct 20, 2015 at 3:03 PM
Subject: RE: [ADMIN] Logging on without prompt for password
To: Glauco Torres <torres.glauco@gmail.com>
Cc: "scottm@openscg.com" <scottm@openscg.com>, "David G. Johnston" <david.g.johnston@gmail.com>
Pg_dumpall is running with in the server
Here’s a portion of my pg_hba.conf
host all bmcap01 127.0.0.1/32 trust
host all psgdba 127.0.0.1/32 trust
I tried this one, this is working: (ye-hey!!)
pg_dumpall -p 40101 -h 127.0.0.1 -U psgdba
Thank you again. Maria A Rossi Database Administration PGDS (US) email: maria.rossi@us.pgds.com work phone: 517.367.3099
cell phone: 517.256.4392
work phone extension: 23099
From: Glauco Torres [mailto:torres.glauco@gmail.com]
Sent: Tuesday, October 20, 2015 2:53 PM
To: Rossi, Maria <maria.rossi@jackson.com>
Cc: scottm@openscg.com; David G. Johnston <david.g.johnston@gmail.com>
Subject: Re: [ADMIN] Logging on without prompt for password
2015-10-20 16:44 GMT-02:00 Rossi, Maria <maria.rossi@jackson.com>:
Back to my original question, though. Any ideas why it is still prompting for password even though it had ‘trust’ in the pg_hba.conf? The .pgpass or the PGPASSWORD is only a work-around for that, I think. ‘Trust’ is supposed to:
Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication
So, I shouldn’t need to set-up .pgpass or PGPASSWORD.
Any thoughts?
Do you use any connection pool (pgbouncer)?
Pg_dumpall command is being running within the server? or you are connecting to another machine?
The best is you pass the command that you are running and also show your pg_hba.conf
if you are running from within the own server and want to let the pg_hba in trust for local connections
your pg_hba.conf this like this?
# Socket and localhost
place all all trust
host all all 127.0.0.1/32 trust
Remember you have to connect the port of the postmaster
pg_dumpall -p 5432 -h 127.0.0.1 -U postgres
Regards,
Glauco Torres
From: David G. Johnston <david.g.johnston@gmail.com>
Date: Tue, Oct 20, 2015 at 3:13 PM
Subject: Re: [ADMIN] Logging on without prompt for password
To: "Rossi, Maria" <maria.rossi@jackson.com>
Cc: Glauco Torres <torres.glauco@gmail.com>, "scottm@openscg.com" <scottm@openscg.com>
Pg_dumpall is running with in the server
Here’s a portion of my pg_hba.conf
host all bmcap01 127.0.0.1/32 trust
host all psgdba 127.0.0.1/32 trust
I tried this one, this is working: (ye-hey!!)
pg_dumpall -p 40101 -h 127.0.0.1 -U psgdba
From: David G. Johnston <david.g.johnston@gmail.com>
Date: Tue, Oct 20, 2015 at 3:35 PM
Subject: Re: [ADMIN] Logging on without prompt for password
To: "Rossi, Maria" <maria.rossi@jackson.com>
But was I using a Unix socket? I thought I was using host. The name did not start with a ‘/’, that’s why am thinking ‘host’.