PostgreSQL Database Server Configuration
When you try to connect to a postgresql server from a fresh install(no configuration was changed) you will encounter the following error.
Access to database denied
The server doesn't grant access to the database: the server reports
FATAL: no pg_hba.conf entry for host "192.168.1.123", user "postgres", database "postgres", SSL off
To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.
The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings. You'll probably want to add something like
host all all 192.168.0.0/24 md5
This example grants MD5 encrypted password access to all databases to all users on the private network
192.168.0.0/24.
You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file. After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.
To give access to the database named "databaseName" w/ user name "userName" from any computer on the sub-network 192.168.1.0.xxx, open pg_hba.conf file (found in PostgreSQL path) w/ your favorite text editor and append to following line to the file
host databaseName userName 192.168.1.1 255.255.255.0 md5
look for the following line in postgresql.conf file
change it to
note: hash (#) sign should be remove
Restart the database server.
Post new comment