Обсуждение: BUG #13188: .pgpass does not work
The following bug has been logged on the website: Bug reference: 13188 Logged by: wt Email address: bzb.dev001@gmail.com PostgreSQL version: 9.4.1 Operating system: linux Description: Tried to use ~/.pgpass file to automatically specify the password when I use the psql console app. according to the info from "PostgreSQL 9.4.0 Documentation", Section 31.15. The Password File. Postgresql is configured to use md5 for login. file: pg_hba.conf ----------------- local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5 file: ~/.pgpass --------------- #hostname : port : database : username : password * : 5432 : * : postgres : somepassword When I exec psql without specifying the password, I cannot login... $ psql --host=localhost --port=5432 --username=postgres --no-password psql: fe_sendauth: no password supplied
On Tuesday, April 28, 2015, <bzb.dev001@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13188 > Logged by: wt > Email address: bzb.dev001@gmail.com <javascript:;> > PostgreSQL version: 9.4.1 > Operating system: linux > Description: > > Tried to use ~/.pgpass file to automatically specify the password when I > use > the psql console app. according to the info from "PostgreSQL 9.4.0 > Documentation", Section 31.15. The Password File. > > Postgresql is configured to use md5 for login. > > > file: pg_hba.conf > ----------------- > local all all md5 > host all all 127.0.0.1/32 md5 > host all all ::1/128 md5 > > > file: ~/.pgpass > --------------- > #hostname : port : database : username : password > * : 5432 : * : postgres : somepassword > > > When I exec psql without specifying the password, I cannot login... > > $ psql --host=localhost --port=5432 --username=postgres --no-password > psql: fe_sendauth: no password supplied > > > Best guess you don't have the correct permissions set. http://www.postgresql.org/docs/devel/static/libpq-pgpass.html Last paragraph. I would also remove the padding white space. The syntax is documented without and while the documentation is non-specific I've personally always avoided it and have never had a problem. David J.
On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com <bzb.dev001@gmail.com= > wrote: > On 2015-04-29 01:06 AM, David G. Johnston wrote: > > On Tuesday, April 28, 2015, <bzb.dev001@gmail.com> wrote: > >> >> file: ~/.pgpass >> --------------- >> #hostname : port : database : username : password >> * : 5432 : * : postgres : somepassword >> >> >> When I exec psql without specifying the password, I cannot login... >> >> $ psql --host=3Dlocalhost --port=3D5432 --username=3Dpostgres --no-passw= ord >> psql: fe_sendauth: no password supplied >> >> >> > I would also remove the padding white space. The syntax is documented > without and while the documentation is non-specific I've personally alway= s > avoided it and have never had a problem. > > =E2=80=8BAnd excessive whitespace? Have you confirmed that you are able to connect by supplying the password manually? David J.=E2=80=8B
On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com <bzb.dev001@gmail.com= > wrote: I have even changed the ownership of the file to postgres:postgres since I'm logged on as another user and .pgpass is located in this user's home directory. Yet it still does not work. > > > $ ls -l .pgpass > -rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass > =E2=80=8BSorry, I missed this the first time reading. This is your problem= . The "postgres" Linux user and the "postgres" PostgreSQL user are not related to each other - particularly when using md5 authentication. The permissions on the .pgpass file must be of the user executing "psql" - which you've indicated is not "postgres". So, likely your first attempt failed because you had whitespace. You changed the permissions and now have two problems. You then fix the whitespace and are back to a single problem. Change the permissions back and it should now work. David J. =E2=80=8B
On 2015-04-29 01:06 AM, David G. Johnston wrote: > On Tuesday, April 28, 2015, <bzb.dev001@gmail.com > <mailto:bzb.dev001@gmail.com>> wrote: > > The following bug has been logged on the website: > > Bug reference: 13188 > Logged by: wt > Email address: bzb.dev001@gmail.com <javascript:;> > PostgreSQL version: 9.4.1 > Operating system: linux > Description: > > Tried to use ~/.pgpass file to automatically specify the password > when I use > the psql console app. according to the info from "PostgreSQL 9.4.0 > Documentation", Section 31.15. The Password File. > > Postgresql is configured to use md5 for login. > > > file: pg_hba.conf > ----------------- > local all all md5 > host all all 127.0.0.1/32 <http://127.0.0.1/32> > md5 > host all all ::1/128 md5 > > > file: ~/.pgpass > --------------- > #hostname : port : database : username : password > * : 5432 : * : postgres : somepassword > > > When I exec psql without specifying the password, I cannot login... > > $ psql --host=localhost --port=5432 --username=postgres --no-password > psql: fe_sendauth: no password supplied > > > > Best guess you don't have the correct permissions set. > > http://www.postgresql.org/docs/devel/static/libpq-pgpass.html > > Last paragraph. > > I would also remove the padding white space. The syntax is documented > without and while the documentation is non-specific I've personally > always avoided it and have never had a problem. > > David J. The permission settings for .pgpass is correct. I have even changed the ownership of the file to postgres:postgres since I'm logged on as another user and .pgpass is located in this user's home directory. Yet it still does not work. $ ls -l .pgpass -rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass $ psql --host=localhost --port=5432 --username=postgres --no-password psql: fe_sendauth: no password supplied
On 2015-04-29 11:50 AM, David G. Johnston wrote: > On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com > <mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com > <mailto:bzb.dev001@gmail.com>>wrote: > > On 2015-04-29 01:06 AM, David G. Johnston wrote: >> On Tuesday, April 28, 2015, <bzb.dev001@gmail.com >> <mailto:bzb.dev001@gmail.com>> wrote: >> >> >> file: ~/.pgpass >> --------------- >> #hostname : port : database : username : password >> * : 5432 : * : postgres : somepassword >> >> >> When I exec psql without specifying the password, I cannot >> login... >> >> $ psql --host=localhost --port=5432 --username=postgres >> --no-password >> psql: fe_sendauth: no password supplied >> >> >> >> I would also remove the padding white space. The syntax is >> documented without and while the documentation is non-specific >> I've personally always avoided it and have never had a problem. > > > â And excessive whitespace? > > Have you confirmed that you are able to connect by supplying the > password manually? > > David J.â > Yes, I have removed the spaces in the .pgpass file. Yes, I can also connect to the postgresql server using a password. I was wondering. I have configured postgresql server to use md5 as specified in the pg_hba.conf below. Do you think that I need to put a md5 encrypted string of my password in the .pgpass file or do I simply put my password in clear text? local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5
On 2015-04-29 12:04 PM, David G. Johnston wrote: > > On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com > <mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com > <mailto:bzb.dev001@gmail.com>> wrote: > I have even changed the ownership of the file to postgres:postgres > since I'm logged on as another user and .pgpass is located in this > user's home directory. Yet it still does not work. > > > $ ls -l .pgpass > -rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass > > > âSorry, I missed this the first time reading. This is your problem. > The "postgres" Linux user and the "postgres" PostgreSQL user are not > related to each other - particularly when using md5 authentication. > The permissions on the .pgpass file must be of the user executing > "psql" - which you've indicated is not "postgres". > > So, likely your first attempt failed because you had whitespace. You > changed the permissions and now have two problems. You then fix the > whitespace and are back to a single problem. Change the permissions > back and it should now work. > > David J. > > â I've changed back the owner of .pgpass to the account that I've logged in as. This is the account that I'm using to run psql. All the spaces in .pgpass is removed. Checked that the permission settings of .pgpass is ok. restarted the server... $ sudo service postgresql restart Unfortunately, it is still not working. $ psql --host=localhost --port=5432 --username=postgres --no-password psql: fe_sendauth: no password supplied Incidentally, the postgresql server is running as 'postgres' user.
On Wed, Apr 29, 2015 at 11:23 AM, bzb.dev001@gmail.com <bzb.dev001@gmail.co= m > wrote: > On 2015-04-29 12:04 PM, David G. Johnston wrote: > > > On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com < > bzb.dev001@gmail.com> wrote: > I have even changed the ownership of the file to postgres:postgres since > I'm logged on as another user and .pgpass is located in this user's home > directory. Yet it still does not work. >> >> >> $ ls -l .pgpass >> -rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass >> > > =E2=80=8BSorry, I missed this the first time reading. This is your prob= lem. > The "postgres" Linux user and the "postgres" PostgreSQL user are not > related to each other - particularly when using md5 authentication. The > permissions on the .pgpass file must be of the user executing "psql" - > which you've indicated is not "postgres". > > So, likely your first attempt failed because you had whitespace. You > changed the permissions and now have two problems. You then fix the > whitespace and are back to a single problem. Change the permissions back > and it should now work. > > I've changed back the owner of .pgpass to the account that I've logged in > as. This is the account that I'm using to run psql. > All the spaces in .pgpass is removed. > Checked that the permission settings of .pgpass is ok. > restarted the server... > > $ sudo service postgresql restart > > Unfortunately, it is still not working. > > $ psql --host=3Dlocalhost --port=3D5432 --username=3Dpostgres --no-passw= ord > psql: fe_sendauth: no password supplied > > Incidentally, the postgresql server is running as 'postgres' user. > > =E2=80=8BI am out of ideas. I am not currently running 9.4.x but if the pr= oblem was a bug within PostgreSQL there would be more than a single report. David J.
On Wed, Apr 29, 2015 at 11:23 AM, bzb.dev001@gmail.com <bzb.dev001@gmail.com
> wrote:
>
> I've changed back the owner of .pgpass to the account that I've logged in
> as. This is the account that I'm using to run psql.
> All the spaces in .pgpass is removed.
> Checked that the permission settings of .pgpass is ok.
> restarted the server...
>
> $ sudo service postgresql restart
>
> Unfortunately, it is still not working.
>
> $ psql --host=localhost --port=5432 --username=postgres --no-password
> psql: fe_sendauth: no password supplied
>
>
I wonder if it is even finding the file in the first place.
strace psql --host=localhost --port=5432 --username=postgres
--no-password 2>&1 | fgrep pgpass
This is what I get:
stat("/home/jjanes/.pgpass", {st_mode=S_IFREG|0600, st_size=196, ...}) = 0
open("/home/jjanes/.pgpass", O_RDONLY) = 3
For me it found the file and opened it successfully.
Cheers,
Jeff
On 2015-04-29 03:32 PM, David G. Johnston wrote: > On Wed, Apr 29, 2015 at 11:23 AM, bzb.dev001@gmail.com > <mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com > <mailto:bzb.dev001@gmail.com>>wrote: > > On 2015-04-29 12:04 PM, David G. Johnston wrote: >> >> On Wed, Apr 29, 2015 at 8:09 AM, bzb.dev001@gmail.com >> <mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com >> <mailto:bzb.dev001@gmail.com>> wrote: >> I have even changed the ownership of the file to >> postgres:postgres since I'm logged on as another user and .pgpass >> is located in this user's home directory. Yet it still does not >> work. >> >> >> $ ls -l .pgpass >> -rw------- 1 postgres postgres 79 Apr 29 10:24 .pgpass >> >> >> â Sorry, I missed this the first time reading. This is your >> problem. The "postgres" Linux user and the "postgres" PostgreSQL >> user are not related to each other - particularly when using md5 >> authentication. The permissions on the .pgpass file must be of >> the user executing "psql" - which you've indicated is not "postgres". >> >> So, likely your first attempt failed because you had whitespace. >> You changed the permissions and now have two problems. You then >> fix the whitespace and are back to a single problem. Change the >> permissions back and it should now work. >> > I've changed back the owner of .pgpass to the account that I've > logged in as. This is the account that I'm using to run psql. > All the spaces in .pgpass is removed. > Checked that the permission settings of .pgpass is ok. > restarted the server... > > $ sudo service postgresql restart > > Unfortunately, it is still not working. > > $ psql --host=localhost --port=5432 --username=postgres --no-password > psql: fe_sendauth: no password supplied > > Incidentally, the postgresql server is running as 'postgres' user. > > > âI am out of ideas. I am not currently running 9.4.x but if the > problem was a bug within PostgreSQL there would be more than a single > report. > > David J. No problem. Thanks for you help.
On 2015-04-29 04:41 PM, Jeff Janes wrote:
> On Wed, Apr 29, 2015 at 11:23 AM, bzb.dev001@gmail.com
> <mailto:bzb.dev001@gmail.com> <bzb.dev001@gmail.com
> <mailto:bzb.dev001@gmail.com>> wrote:
>
>>
> I've changed back the owner of .pgpass to the account that I've
> logged in as. This is the account that I'm using to run psql.
> All the spaces in .pgpass is removed.
> Checked that the permission settings of .pgpass is ok.
> restarted the server...
>
> $ sudo service postgresql restart
>
> Unfortunately, it is still not working.
>
> $ psql --host=localhost --port=5432 --username=postgres --no-password
> psql: fe_sendauth: no password supplied
>
>
> I wonder if it is even finding the file in the first place.
>
> strace psql --host=localhost --port=5432 --username=postgres
> --no-password 2>&1 | fgrep pgpass
>
> This is what I get:
>
> stat("/home/jjanes/.pgpass", {st_mode=S_IFREG|0600, st_size=196, ...}) = 0
> open("/home/jjanes/.pgpass", O_RDONLY) = 3
>
> For me it found the file and opened it successfully.
>
> Cheers,
>
> Jeff
I finally figured out why it didn't work. the port# specified in my
.pgpass was incorrect.
My stupidity.
Thanks to all for the help.