title: What permissions are needed in the stock pg_hba.conf file to allow a database role to get in?
body: <p>I have found that setting up the pg_hba.conf file to be very tough.  Here's the common case that wastes my time.  What do I need to do?</p>
  
  <p>First, setup a virtual machine at Amazon, Digital Ocean or a freshly installed Linux distro on my own hardware. I usually use Fedora or CentOS and then get Postgres via this method:</p>
  
  <pre><code>rpm -Uvh http://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
  yum install postgresql96 postgresql96-server postgresql96-contrib postgresql96-devel -y
  /usr/pgsql-9.6/bin/postgresql96-setup initdb
  systemctl enable postgresql-9.6
  systemctl start postgresql-9.6
  echo export PATH=/usr/pgsql-9.6/bin:"$PATH" &gt;&gt; /etc/profile.d/postgresql96.sh
  yum update -y
  </code></pre>
  
  <p>Just to rule a few things out, pop a hole in the firewall (this is dev -- it's OK)</p>
  
  <pre><code>firewall-cmd --permanent --add-port=5432/tcp
  firewall-cmd --reload
  </code></pre>
  
  <p>Create a database user</p>
  
  <pre><code>su postgres
  createuser --interactive -W bob
  &lt;answer yes to make bob a superuser&gt;
  psql
  ALTER USER bob PASSWORD 'whatever';
  </code></pre>
  
  <p>Edit <code>/var/lib/pgsql/9.6/data/postgresql.conf</code> and change <code>#listen_addresses = 'localhost'</code> to <code>listen_addresses = '*'</code></p>
  
  <p>Restart</p>
  
  <pre><code>systemctl restart postgresql-9.6
  </code></pre>
  
  <p>Now, here's where things always go sideways.</p>
  
  <pre><code>su postgres
  psql -U bob -W
  &lt;enter password&gt;
  psql: FATAL:  Ident authentication failed for user "bob"
  </code></pre>
  
  <p>I know I need to do something in pg_hba.conf -- but what to write in there?  By default it says this:</p>
  
  <pre><code># TYPE  DATABASE        USER            ADDRESS                 METHOD
  
  # "local" is for Unix domain socket connections only
  local   all             all                                     peer
  # IPv4 local connections:
  host    all             all             127.0.0.1/32            ident
  # IPv6 local connections:
  host    all             all             ::1/128                 ident
  # Allow replication connections from localhost, by a user with the
  # replication privilege.
  #local   replication     postgres                                peer
  #host    replication     postgres        127.0.0.1/32            ident
  #host    replication     postgres        ::1/128                 ident
  </code></pre>
  
  <p>I have tried this:</p>
  
  <pre><code>host all bob all md5
  </code></pre>
  
  <p>But that doesn't work.</p>
  
  <p>I've tried </p>
  
  <pre><code>host all bob all ident
  </code></pre>
  
  <p>But that doesn't work.</p>
  
  <p>I've tried to connect with pgadmin3 over TCP/IP from a remote box, but that doesn't work either, not that it would since it doesn't work locally.</p>
  
  <p>In the end, I want to be able to login as bob locally and via pgadmin3 remotely.  </p>
  
  <p><strong>UPDATE:</strong> Is there a webpage out there that cleanly explains how to setup the pg_hba.conf file for some typical use-cases?</p>