Thread: Postgres 9.1 client authentication for local, no password required?

Postgres 9.1 client authentication for local, no password required?

From
Wujek Srujek
Date:
Hi. I am using Postgres 9.1 on Ubuntu 11.10 64bit. I have a question about client authentication.
After installing the server, and setting the postgres password to encrypted 'postgres', I made sure I can log in like that. Then, I edited the /etc/postgres/9.1/main/pg_hba.conf file to contain just this single like:

local   all             all                                     md5

According to these sources:
http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

this means (at least that's how I understand it):
1. local - it allows only connections using unix domain sockets
2. first all - access to all databases
3. second all - for every user
4. md5 - requires providing a password for a login

But now, I am trying to connect as a normal user:

psql -d postgres -U postgres

and it connects without ever asking for a password! (The password works fine when I force it with -W, so this part is ok.)

If I add a line for TCP/IP connections (with 'host' at the beginning) it does ask for the password, so it looks like the behavior I am experiencing has something to do with domain socket, but I am not sure.

The user that I installed Postgres with and tried logging in was the same, and it was in the admin group, so it had the sudoer privilage. I thought it had something to do with that, so I created another user, who wasn't a sudoer - and I had to give the password. But then, when I added the admin group to the user (which adds it to sudoers on my machine), I still had to specify the password (and sudo works fine), which would imply that it was a dead end.

The thing makes me a little nervous, because I apparently can't configure my server correctly, there is something that I don't understand here ;d I would like to ask you about what is this strange behavior caused by.

wujek

Re: Postgres 9.1 client authentication for local, no password required?

From
Guillaume Lelarge
Date:
On Thu, 2012-01-05 at 20:56 +0100, Wujek Srujek wrote:
> Hi. I am using Postgres 9.1 on Ubuntu 11.10 64bit. I have a question about
> client authentication.
> After installing the server, and setting the postgres password to encrypted
> 'postgres', I made sure I can log in like that. Then, I edited the
> /etc/postgres/9.1/main/pg_hba.conf file to contain just this single like:
>
> local   all             all                                     md5
>
> According to these sources:
> http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
> http://www.postgresql.org/docs[...]uth-methods.html#AUTH-PASSWORD
>
> this means (at least that's how I understand it):
> 1. local - it allows only connections using unix domain sockets
> 2. first all - access to all databases
> 3. second all - for every user
> 4. md5 - requires providing a password for a login
>
> But now, I am trying to connect as a normal user:
>
> psql -d postgres -U postgres
>
> and it connects without ever asking for a password! (The password works
> fine when I force it with -W, so this part is ok.)
>
> If I add a line for TCP/IP connections (with 'host' at the beginning) it
> does ask for the password, so it looks like the behavior I am experiencing
> has something to do with domain socket, but I am not sure.
>
> The user that I installed Postgres with and tried logging in was the same,
> and it was in the admin group, so it had the sudoer privilage. I thought it
> had something to do with that, so I created another user, who wasn't a
> sudoer - and I had to give the password. But then, when I added the admin
> group to the user (which adds it to sudoers on my machine), I still had to
> specify the password (and sudo works fine), which would imply that it was a
> dead end.
>

My guess would be that you have a .pgpass file on your first user's home
directory, and not on the new one.

Sot, first, try to check if there is a $HOME/.pgpass file for your first
user.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org


Re: Postgres 9.1 client authentication for local, no password required?

From
Wujek Srujek
Date:
Hi. Yes, there is such a file, and yes, it is the 'special' thing I was looking for. Thanks you very much for the tip, I did some reading about it.
Who created this feature for me? Could it be the ubuntu installation process?

wujek

On Thu, Jan 5, 2012 at 9:51 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
On Thu, 2012-01-05 at 20:56 +0100, Wujek Srujek wrote:
> Hi. I am using Postgres 9.1 on Ubuntu 11.10 64bit. I have a question about
> client authentication.
> After installing the server, and setting the postgres password to encrypted
> 'postgres', I made sure I can log in like that. Then, I edited the
> /etc/postgres/9.1/main/pg_hba.conf file to contain just this single like:
>
> local   all             all                                     md5
>
> According to these sources:
> http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
> http://www.postgresql.org/docs[...]uth-methods.html#AUTH-PASSWORD
>
> this means (at least that's how I understand it):
> 1. local - it allows only connections using unix domain sockets
> 2. first all - access to all databases
> 3. second all - for every user
> 4. md5 - requires providing a password for a login
>
> But now, I am trying to connect as a normal user:
>
> psql -d postgres -U postgres
>
> and it connects without ever asking for a password! (The password works
> fine when I force it with -W, so this part is ok.)
>
> If I add a line for TCP/IP connections (with 'host' at the beginning) it
> does ask for the password, so it looks like the behavior I am experiencing
> has something to do with domain socket, but I am not sure.
>
> The user that I installed Postgres with and tried logging in was the same,
> and it was in the admin group, so it had the sudoer privilage. I thought it
> had something to do with that, so I created another user, who wasn't a
> sudoer - and I had to give the password. But then, when I added the admin
> group to the user (which adds it to sudoers on my machine), I still had to
> specify the password (and sudo works fine), which would imply that it was a
> dead end.
>

My guess would be that you have a .pgpass file on your first user's home
directory, and not on the new one.

Sot, first, try to check if there is a $HOME/.pgpass file for your first
user.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org


Re: Postgres 9.1 client authentication for local, no password required?

From
Guillaume Lelarge
Date:
On Thu, 2012-01-05 at 22:00 +0100, Wujek Srujek wrote:
> Hi. Yes, there is such a file, and yes, it is the 'special' thing I was
> looking for. Thanks you very much for the tip, I did some reading about it.
> Who created this feature for me? Could it be the ubuntu installation
> process?
>

No, I don't think so. The only tool who can create this file is pgAdmin.
At least, that's the only tool I know :) Did you use it?


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org


Re: Postgres 9.1 client authentication for local, no password required?

From
Wujek Srujek
Date:
Yes, pgAdmin is pretty much the only thing I use after initially installing and setting the password for the 'postgres' user.
When does it do it? How did I trigger that?

wujek

On Thu, Jan 5, 2012 at 10:18 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
On Thu, 2012-01-05 at 22:00 +0100, Wujek Srujek wrote:
> Hi. Yes, there is such a file, and yes, it is the 'special' thing I was
> looking for. Thanks you very much for the tip, I did some reading about it.
> Who created this feature for me? Could it be the ubuntu installation
> process?
>

No, I don't think so. The only tool who can create this file is pgAdmin.
At least, that's the only tool I know :) Did you use it?

Re: Postgres 9.1 client authentication for local, no password required?

From
Wujek Srujek
Date:
Ok, I got it - it adds a new entry when I create a new server connection, set it to remember the password, and log in succesfully. It creates the file for the first time it wants to add an entry, if the file is not there yet.

Thank you for the good tip in the right direction.

wujek

On Thu, Jan 5, 2012 at 10:23 PM, Wujek Srujek <wujek.srujek@googlemail.com> wrote:
Yes, pgAdmin is pretty much the only thing I use after initially installing and setting the password for the 'postgres' user.
When does it do it? How did I trigger that?

wujek


On Thu, Jan 5, 2012 at 10:18 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
On Thu, 2012-01-05 at 22:00 +0100, Wujek Srujek wrote:
> Hi. Yes, there is such a file, and yes, it is the 'special' thing I was
> looking for. Thanks you very much for the tip, I did some reading about it.
> Who created this feature for me? Could it be the ubuntu installation
> process?
>

No, I don't think so. The only tool who can create this file is pgAdmin.
At least, that's the only tool I know :) Did you use it?


Re: Postgres 9.1 client authentication for local, no password required?

From
Guillaume Lelarge
Date:
On Thu, 2012-01-05 at 22:23 +0100, Wujek Srujek wrote:
> Yes, pgAdmin is pretty much the only thing I use after initially installing
> and setting the password for the 'postgres' user.
> When does it do it? How did I trigger that?
>

It does that when you tell it to record your password (checkbox in the
server properties dialog, and in the connection dialog too IIRC). Which
is neat because it's the usual password file for PostgreSQL clients.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org