Accessing database from different machines

  • Go to Postgres installation directory-> 9.6 -> data.
  • Postgres installation is usually located at the following location:
    • On Linux: /var/lib/pgsql
    • On Windows: C:/Program Files/PostgreSQL

Steps

  • Edit the postgresql.conf as shown below:
  • Edit pg_hba.conf in the IPv4 section as shown below:
  • To allow any user from any host to connect to the database “postgres”.
  • To allow any user from any host with IP address 192.168.2.x to connect to database “postgres”.

Commonly used authentication methods

  1. Trust: Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication.
  2. Reject: Reject the connection unconditionally. This is useful for “filtering out” certain hosts from a group, for example, a reject line could block a specific host from connecting, while a later line allows the remaining hosts in a specific network to connect.
  3. md5: Require the client to supply an MD5-encrypted password for authentication.
  4. Password: Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks.
  5. Peer: Obtain the client’s operating system username from the operating system and check if it matches the requested database username. This is only available for local connections. Refer section 19.3.7 for details.
  6. Cert: Authenticate using SSL client certificates. Refer section 19.3.10 for details. For more details refer to https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html.