Connecting to a Ruby on Rails PostgreSQL database from a local network (LAN/external) took some time, so I’ll share the solution here.
database.yml
development:
adapter: postgresql
encoding: unicode
database: englishtool_development
pool: 5
username: englishtool
password: password1
host: 192.168.99.101
test:
adapter: postgresql
encoding: unicode
database: englishtool_test
pool: 5
username: englishtool
password: password1
host: 192.168.99.101Error occurred…
$ rake db:setup
could not connect to server: Connection refused
Is the server running on host "192.168.99.101" and accepting
TCP/IP connections on port 5432?
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"englishtool_development", "pool"=>5, "username"=>"englishtool", "password"=>"password1", "host"=>"192.168.99.101"}
rake aborted!
PG::ConnectionBad: could not connect to server: Connection refused
Is the server running on host "192.168.99.101" and accepting
TCP/IP connections on port 5432?
Tasks: TOP => db:setup => db:schema:load_if_ruby => db:createSolution
(Assuming you can connect normally from localhost on the local machine.)
- Modify postgresql.conf file
PostgreSQL by default only allows connections from the local host.
sudo vim /etc/postgresql/10/main/postgresql.conf
# Add this line
listen_addresses = '*'- Configure which clients can connect
sudo vim /etc/postgresql/10/main/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 md5
+ # local network
+ # host all all 192.168.0.0/16 md5
+ host all all 0.0.0.0/0 trustThis allows all IPs without authentication, so security is wide open, but it’s fine for a development environment.
For the IP part, 192.168.0.0/16 should also work. I haven’t verified it, so I can’t say for sure. The authentication part should also be fine with md5.
- restart
sudo /etc/init.d/postgresql restart
Verification
pgweb --url postgres://englishtool:[email protected]:5432/englishtool_development
$ rake db:setup
Database 'englishtool_development' already exists
Database 'englishtool_test' already exists
-- enable_extension("plpgsql")
-> 0.1347s
-- enable_extension("plpgsql")
-> 1.4060s