Access PostgreSQL server from LAN

Question

I've been trying to edit pg_hba.conf file in order to be able to access the server using just the IP address with, so far, no success.

For example, I can access using «localhost», but I want to access using the IP address that my router gave me which is something like 192.168.1.X

This is mi pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix domain socket connections only local   all             all                                     trust # IPv4 local connections: host    all             all             127.0.0.1/32            trust # IPv6 local connections: host    all             all             ::1/128                 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local   replication     postgres                                trust #host    replication     postgres        127.0.0.1/32            trust #host    replication     postgres        ::1/128                 trust host all all 0.0.0.0/0 trust 

Any help?

Answer 1

First, edit the postgresql.conf file, and set listen_addresses. The default value of 'localhost' will only listen on the loopback adaptor. You can change it to '*', meaning listen on all addresses, or specifically list the IP address of the interfaces you want it to accept connections from. Note that this is the IP address which the interface has allocated to it, which you can see using ifconfig or ip addr commands.

You must restart postgresql for the changes to listen_addresses to take effect.

Next, in pg_hba.conf, you will need an entry like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD host    {dbname}        {user}          192.168.1.0/24          md5 

{dbname} is the database name you are allowing access to. You can put "all" for all databases.

{user} is the user who is allowed to connect. Note that this is the postgresql user, not necessarily the unix user.

The ADDRESS part is the network address and mask that you want to allow. The mask I specified will work for 192.168.1.x as you requested.

The METHOD part is the authentication method to use. There are a number of options there. md5 means it will use an md5 hashed password. 'trust' which you had in your sample means no authentication at all - this is definitely not recommended.

Changes to pg_hba.conf will take effect after reloading the server. You can to this using pg_ctl reload (or via the init scripts, depending on your OS distro).