Thread: Password entry in pgadmin3; pg_hba.conf vs .pgpass. Permissioning issue?
[also posted to Django users Google group] Hi all, Can I bypass the need for password entry in pgadmin3 by changing/locating/permissioning a .pgpass files instead of modifying pg_hba.conf to 'trust'? pg_hba.conf currently set at md5 for everything. 1. I can merrily connect to the postgres server on my local ubuntu machine as user postgres in bash by "su postgres" then "psql". 2. But if I want to be able to use pgadmin 3 GUI without entering a password I need to do this as my user, andyt and this doesn't work (I did chmod 600 this file to give rw access to one owner only). This feels like a permissioning issue but with that chmod 600 restriction on pgpass I'm not sure how to progress. I have been messing around with strace trying to figure out the location of pgpass which is being sought out when I run psql/pgadmin3 but the results are inconclusive. They both (now)seem to be looking in the andyt home directory. Not sure if this was the case earlier, sorry! I was earlier placing .pgpass here there and everythwhere, but in particular /var/lib/postresql. Grateful for any guidance, Regards, Andy Key file contents: 1 pg_hba.conf: local all postgres md5 # local all postgres trust # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # local all all trust local django_db django_dev md5 # local django_db django_dev trust # IPv4 local connections: host all all 127.0.0.1/32 md5 # host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 md5 # host all all ::1/128 trust 2 ,pgpass 127.0.0.1:5432:*:postgres:Password@ :5432:*:django_dev:Password@
Re: Password entry in pgadmin3; pg_hba.conf vs .pgpass. Permissioning issue?
From
Fernando Hevia
Date:
On Mon, Nov 26, 2012 at 9:55 AM, Andrew Taylor <andydtaylor@gmail.com> wrote:
> [also posted to Django users Google group]
>
> Hi all,
>
> Can I bypass the need for password entry in pgadmin3 by
> changing/locating/permissioning a .pgpass files instead of modifying
> pg_hba.conf to 'trust'? pg_hba.conf currently set at md5 for
> everything.
>
> 1. I can merrily connect to the postgres server on my local ubuntu
> machine as user postgres in bash by "su postgres" then "psql".
> 2. But if I want to be able to use pgadmin 3 GUI without entering a
> password I need to do this as my user, andyt and this doesn't work (I
> did chmod 600 this file to give rw access to one owner only). This
> feels like a permissioning issue but with that chmod 600 restriction
> on pgpass I'm not sure how to progress.
>
>
pgAdmin should use ~/.pgpass in unixes. At least that is what the documentation says.
You can make a quick test: create a new server entry with user & password saved. Then check your ~/.pgpass file to see if the new server shows up.
Make sure to comply the pgpass format and permission requirements: http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html
Cheers.
On Tue, Nov 27, 2012 at 5:27 PM, Fernando Hevia <fhevia@gmail.com> wrote: > > On Mon, Nov 26, 2012 at 9:55 AM, Andrew Taylor <andydtaylor@gmail.com> > wrote: >> [also posted to Django users Google group] >> >> Hi all, >> >> Can I bypass the need for password entry in pgadmin3 by >> changing/locating/permissioning a .pgpass files instead of modifying >> pg_hba.conf to 'trust'? pg_hba.conf currently set at md5 for >> everything. >> >> 1. I can merrily connect to the postgres server on my local ubuntu >> machine as user postgres in bash by "su postgres" then "psql". >> 2. But if I want to be able to use pgadmin 3 GUI without entering a >> password I need to do this as my user, andyt and this doesn't work (I >> did chmod 600 this file to give rw access to one owner only). This >> feels like a permissioning issue but with that chmod 600 restriction >> on pgpass I'm not sure how to progress. >> >> > > pgAdmin should use ~/.pgpass in unixes. At least that is what the > documentation says. It does, and the equivalent pgpass file on Windows. > You can make a quick test: create a new server entry with user & password > saved. Then check your ~/.pgpass file to see if the new server shows up. > > Make sure to comply the pgpass format and permission requirements: > http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html Or just login once from pgAdmin and click the "Save Password" option. That'll write the pgpass file for you. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Password entry in pgadmin3; pg_hba.conf vs .pgpass. Permissioning issue?
From
Andrew Taylor
Date:
Hi Guys, Thanks for your help. I have this working but don't completely understand what I did. 1. Here is how the .pgpass file was originally written by pgadmin: 127.0.0.1:5432:*:postgres:Password :5432:*:django_dev:Password 2. This is the amended version which allows pgadmin to automatically connect to the server. All I have done is add line 3. All 3 lines seem to be required to log on to the server. 127.0.0.1:5432:*:postgres:Password :5432:*:django_dev:Password localhost:5432:*:django_dev:Password So questions: What exactly is the difference between 127.0.0.1 and localhost? When there is no IP stated as in the second line what does that do? How come a localhost line wasn't created automatically by pgadmin? Thanks, Andy On Tue, Nov 27, 2012 at 5:43 PM, Dave Page <dpage@pgadmin.org> wrote: > On Tue, Nov 27, 2012 at 5:27 PM, Fernando Hevia <fhevia@gmail.com> wrote: >> >> On Mon, Nov 26, 2012 at 9:55 AM, Andrew Taylor <andydtaylor@gmail.com> >> wrote: >>> [also posted to Django users Google group] >>> >>> Hi all, >>> >>> Can I bypass the need for password entry in pgadmin3 by >>> changing/locating/permissioning a .pgpass files instead of modifying >>> pg_hba.conf to 'trust'? pg_hba.conf currently set at md5 for >>> everything. >>> >>> 1. I can merrily connect to the postgres server on my local ubuntu >>> machine as user postgres in bash by "su postgres" then "psql". >>> 2. But if I want to be able to use pgadmin 3 GUI without entering a >>> password I need to do this as my user, andyt and this doesn't work (I >>> did chmod 600 this file to give rw access to one owner only). This >>> feels like a permissioning issue but with that chmod 600 restriction >>> on pgpass I'm not sure how to progress. >>> >>> >> >> pgAdmin should use ~/.pgpass in unixes. At least that is what the >> documentation says. > > It does, and the equivalent pgpass file on Windows. > >> You can make a quick test: create a new server entry with user & password >> saved. Then check your ~/.pgpass file to see if the new server shows up. >> >> Make sure to comply the pgpass format and permission requirements: >> http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html > > Or just login once from pgAdmin and click the "Save Password" option. > That'll write the pgpass file for you. > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company
Re: Password entry in pgadmin3; pg_hba.conf vs .pgpass. Permissioning issue?
From
Raymond O'Donnell
Date:
On 04/12/2012 16:56, Andrew Taylor wrote: > Hi Guys, > > Thanks for your help. I have this working but don't completely > understand what I did. > > 1. Here is how the .pgpass file was originally written by pgadmin: > > 127.0.0.1:5432:*:postgres:Password > :5432:*:django_dev:Password > > 2. This is the amended version which allows pgadmin to automatically > connect to the server. All I have done is add line 3. All 3 lines seem > to be required to log on to the server. > > 127.0.0.1:5432:*:postgres:Password > :5432:*:django_dev:Password > localhost:5432:*:django_dev:Password Is that second line faulty? Shouldn't the first field contain either a hostname, an IP address or "*", but not simply nothing? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Re: Password entry in pgadmin3; pg_hba.conf vs .pgpass. Permissioning issue?
From
Andrew Taylor
Date:
So I thought.... but all 3 lines seem to be required. Actually that should have been a 4th question. Why do I need all those 3 lines in .pgpass? And why does an asterix for hostname achieve nothing? I tried the below but it doesn't work (at least with the md5 pg_hba.conf settings) *:5432:*:postgres:Password *:5432:*:django_dev:Password Cheers, Andy On Tue, Dec 4, 2012 at 5:01 PM, Raymond O'Donnell <rod@iol.ie> wrote: > On 04/12/2012 16:56, Andrew Taylor wrote: >> Hi Guys, >> >> Thanks for your help. I have this working but don't completely >> understand what I did. >> >> 1. Here is how the .pgpass file was originally written by pgadmin: >> >> 127.0.0.1:5432:*:postgres:Password >> :5432:*:django_dev:Password >> >> 2. This is the amended version which allows pgadmin to automatically >> connect to the server. All I have done is add line 3. All 3 lines seem >> to be required to log on to the server. >> >> 127.0.0.1:5432:*:postgres:Password >> :5432:*:django_dev:Password >> localhost:5432:*:django_dev:Password > > Is that second line faulty? Shouldn't the first field contain either a > hostname, an IP address or "*", but not simply nothing? > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > rod@iol.ie
Re: Password entry in pgadmin3; pg_hba.conf vs .pgpass. Permissioning issue?
From
Guillaume Lelarge
Date:
On Tue, 2012-12-04 at 16:56 +0000, Andrew Taylor wrote: > Hi Guys, > > Thanks for your help. I have this working but don't completely > understand what I did. > > 1. Here is how the .pgpass file was originally written by pgadmin: > > 127.0.0.1:5432:*:postgres:Password > :5432:*:django_dev:Password > > 2. This is the amended version which allows pgadmin to automatically > connect to the server. All I have done is add line 3. All 3 lines seem > to be required to log on to the server. > > 127.0.0.1:5432:*:postgres:Password > :5432:*:django_dev:Password > localhost:5432:*:django_dev:Password > > > So questions: > What exactly is the difference between 127.0.0.1 and localhost? localhost can be anything. On my computer, it is ::1 (the IPv6 equivalent to 127.0.0.1). > When there is no IP stated as in the second line what does that do? I've never tried that. I would guess it is the default unix domain socket. > How come a localhost line wasn't created automatically by pgadmin? pgAdmin registers what you enter in the dialog. It doesn't "guess" things. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Re: Password entry in pgadmin3; pg_hba.conf vs .pgpass. Permissioning issue?
From
Guillaume Lelarge
Date:
On Tue, 2012-12-04 at 17:33 +0000, Andrew Taylor wrote: > So I thought.... but all 3 lines seem to be required. > For a single connection, only one line is required. > Actually that should have been a 4th question. Why do I need all those > 3 lines in .pgpass? Depends on your configuration, but only one line is required per connection. > And why does an asterix for hostname achieve > nothing? > It should (and actually, it does to me). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com