Обсуждение: Postgres 9.1 client authentication for local, no password required?
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.htmlthis 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
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
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:
My guess would be that you have a .pgpass file on your first user's homeOn 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.
>
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
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
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:No, I don't think so. The only tool who can create this file is pgAdmin.
> 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?
>
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
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?wujekOn 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:No, I don't think so. The only tool who can create this file is pgAdmin.
> 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?
>
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
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