Thread: 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, 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 ?
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