Обсуждение: Postgres Account Inherit Question

I've configured my 'pg_hba.conf' file to look as follows:

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         192.168.0.0/24        md5

Now I've reloaded / restarted the PostgreSQL daemon however for some
reason when I use the 'postgres' user locally, it never prompts for a
password in 'psql'. I've altered the role to NOINHERIT

postgres=# ALTER ROLE postgres NOINHERIT;
ALTER ROLE

Any other role locally requires a password to even list the database
using 'psql -l' command except the 'postgres' role. Is this normal
behavior or am I missing something here? How can I force the postgres
account to be prompted for a password when communicating to the
database server locally?

On Fri, 2011-10-14 at 18:44 +0200, Julien Rouhaud wrote:
> On Fri, Oct 14, 2011 at 6:38 PM, Carlos Mennens <carlos.mennens@gmail.com>wrote:
>
> > I've configured my 'pg_hba.conf' file to look as follows:
> >
> > # "local" is for Unix domain socket connections only
> > local   all         all                               md5
> > # IPv4 local connections:
> > host    all         all         127.0.0.1/32          md5
> > host    all         all         192.168.0.0/24        md5
> >
> > Now I've reloaded / restarted the PostgreSQL daemon however for some
> > reason when I use the 'postgres' user locally, it never prompts for a
> > password in 'psql'. I've altered the role to NOINHERIT
> >
> > postgres=# ALTER ROLE postgres NOINHERIT;
> > ALTER ROLE
> >
> > Any other role locally requires a password to even list the database
> > using 'psql -l' command except the 'postgres' role. Is this normal
> > behavior or am I missing something here? How can I force the postgres
> > account to be prompted for a password when communicating to the
> > database server locally?
> >
> >
> Hi
> Did you check for a .pgpass file ?

And do you have any other lines before the few ones you give ?


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

On Fri, Oct 14, 2011 at 12:44 PM, Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> Hi
> Did you check for a .pgpass file ?

I'm assuming you're talking about a hidden file in my Linux shell for
the 'postgres' user. I don't see one anywhere. I just had a
.psql_history file which I removed.

On Fri, Oct 14, 2011 at 1:31 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
>> Did you check for a .pgpass file ?
>
> And do you have any other lines before the few ones you give ?

Yes. It reads the config file as follows:


# Database administrative login by UNIX sockets
local   all         postgres                          ident

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         10.1.10.0/24          md5
host    all         all         10.1.11.0/24          md5
host    all         all         192.168.0.0/24        md5
# IPv6 local connections:
host    all         all         ::1/128               md5

On Fri, 2011-10-14 at 13:43 -0400, Carlos Mennens wrote:
> On Fri, Oct 14, 2011 at 12:44 PM, Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > Hi
> > Did you check for a .pgpass file ?
>
> I'm assuming you're talking about a hidden file in my Linux shell for
> the 'postgres' user. I don't see one anywhere. I just had a
> .psql_history file which I removed.
>
> On Fri, Oct 14, 2011 at 1:31 PM, Guillaume Lelarge
> <guillaume@lelarge.info> wrote:
> >> Did you check for a .pgpass file ?
> >
> > And do you have any other lines before the few ones you give ?
>
> Yes. It reads the config file as follows:
>
>
> # Database administrative login by UNIX sockets
> local   all         postgres                          ident
>
> # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
>
> # "local" is for Unix domain socket connections only
> local   all         all                               md5
> # IPv4 local connections:
> host    all         all         127.0.0.1/32          md5
> host    all         all         10.1.10.0/24          md5
> host    all         all         10.1.11.0/24          md5
> host    all         all         192.168.0.0/24        md5
> # IPv6 local connections:
> host    all         all         ::1/128               md5
>

So you have the ident authentication method when the user postgres tries
to connect to any database, through socket. And the ident method relies
on your OS connection, so you don't need any more password than the Unix
one.

All the other users will have the md5 authentication method, which
requires to enter a password, either manually or automatically.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com