Обсуждение: Logging on without prompt for password

Поиск
Список
Период
Сортировка

Logging on without prompt for password

От
"Rossi, Maria"
Дата:

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

 

Re: Logging on without prompt for password

От
Will McCormick
Дата:
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.

 

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

 


Re: Logging on without prompt for password

От
"David G. Johnston"
Дата:
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.

Re: Logging on without prompt for password

От
"Rossi, Maria"
Дата:

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.

 

Re: Logging on without prompt for password

От
Glauco Torres
Дата:


2015-10-20 14:00 GMT-02:00 Will McCormick <wmccormick@gmail.com>:
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.

 



You tried?

.pgpass

#Server 1
127.0.0.1:5432:user:password
#Server 2
127.0.0.1:5433:user:password
#Server 3
127.0.0.1:8899:user:password


pg_hba.conf

# Socket e localhost
local   all         all                               trust
host    all         all         127.0.0.1/32   trust

#Or external
host    all         user    10.10.10.38/32        trust

Regards,
Glauco Torres




 

Re: Logging on without prompt for password

От
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.

 




Your User does not have permission to base template1

Regards,
Glauco Torres

 

Re: Logging on without prompt for password

От
Glauco Torres
Дата:


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?

Re: Logging on without prompt for password

От
Scott Mead
Дата:

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.


You can enter a '*' in any field in .pgpass.  So, 

 hostname:port:database:username:password

127.0.0.1:*:*:postgres:abc

  Would authenticate the postgres user with pass: abc to any database on any port on 127.0.0.1
 
--
Scott Mead
Sr. Architect
OpenSCG, inc

 

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

 


Re: Logging on without prompt for password

От
"Rossi, Maria"
Дата:

Hi,

 

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?

Re: Logging on without prompt for password

От
Scott Mead
Дата:
Adding back -admin

On Tue, Oct 20, 2015 at 2:44 PM, Rossi, Maria <maria.rossi@jackson.com> wrote:

Hi,

 

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?


After modifying pg_hba.conf, did you reload the server?

The other thing to keep in mind is that if you are using unix sockets, you'll need a 'local db user   trust' line, and if you're using remote, you'll need a 'host / hostssl / hostnossl' line for that server.... then, reload (pg_ctl reload)

--
Scott Mead
Sr. Architect
OpenSCG
PostgreSQL, Java & Linux Experts

Desk   : (585) 483-0480
Bridge: (585) 484-8032


 

 

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

 

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>:

Hi,

 

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."

 


Fwd: Logging on without prompt for password

От
"David G. Johnston"
Дата:
OP decided to stop sending messages to the list....

---------- Forwarded message ----------
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>


No, am not using pgbouncer

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
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 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>:

Hi,

 

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


Fwd: Logging on without prompt for password

От
"David G. Johnston"
Дата:
OP decided to stop sending messages to the list....

---------- Forwarded message ----------
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>


On Tue, Oct 20, 2015 at 3:03 PM, Rossi, Maria <maria.rossi@jackson.com> wrote:

No, am not using pgbouncer

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



​You should acquire an understanding of the difference between "host" and "local" - only the former of which uses TCP/IP addresses and ports.

Once that is done the following will make a bit more sense:

host
Name of host to connect to. If this begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default behavior when host is not specified is to connect to a Unix-domain socket in /tmp (or whatever socket directory was specified when PostgreSQL was built). On machines without Unix-domain sockets, the default is to connect to localhost.

Note especially the default behavior on the "Unix-domain" socket system - which you are using.

David J.

Fwd: Logging on without prompt for password

От
"David G. Johnston"
Дата:
OP decided to stop sending messages to the list....

---------- Forwarded message ----------
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>


On Tue, Oct 20, 2015 at 3:24 PM, Rossi, Maria <maria.rossi@jackson.com> wrote:

Thanks for the explanation.  Am relatively new to postgres and its not much used in our shop, hence my difficulty.

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’.



​Can you please bottom-post (or inline) like the rest of us​.

You said you were trying this:

/usr/pgsql-9.3/bin/pg_dumpall" -U bmcap01 -s -x

Since you do not specify a host here it uses the Unix-socket.  The socket also has a port so saying "-p XXXX" doesn't change the how.  Once you added "-h 127..." it then choose the TCP/IP protocol.

Since your pg_hba.conf file section you showed only defines trust for "host" connections on 127... they didn't match you connection until you added "-h"

The overloaded usage of "socket" and "port" and "host" here makes this area ripe for confusion among the uninitiated.

David J.