Configuring a PostgreSQL server
Download the recommended version of the postgreSQL DBMS directly in the official website:
After installing, it is necessary to configure the range of IP addresses of the client machines that will access the server and the authentication method.
In order to do this, edit the pg_hba.conf file; in postgres version 8.4 the file is in this directory: postgresql\8.4\data.
Each recordm identifies the connection type, a range of Client IP addresses, a database name, a user name, and the authentication method;
Type: Connection (local, host, hostssl, hostnossl)
Database: Specifies which databases this record corresponds to. The all value corresponds to all databases;
User: Specifies which PostgreSQL users this record corresponds to. The all corresponds to all users.
CIDR Address: Specifies the range of client machines IP addresses this record corresponds to;
Authentication method: Specifies the authentication method to be used to connect using this record.
CIDR_ADDRESS: Standard notation with dots, and CIDR mask length. The mask indicates the number of bits that identifies the network address.
A typical address is 172.20.143.89/32 for one host only, or 172.20.143.0/24 for a network.
This field is only valid for type=host, hostssl e hostnossl.
Defining ips with access to server
Add in the #IPv4 local connections item
TYPE |
DATABASE |
USER |
CIDR-ADDRESS |
METHOD |
host |
all |
all |
127.0.0.1/32 |
md5 |
host |
all |
all |
192.168.1.0/24 |
md5 |
<network addresses> = 192.168.1.0/24
This means that all machines with IP address containing 192.168.1.X will have access to the postgres server.
Another approach is to indicate the exact IP address of each client machine; in order to do this, just indicate each one of them in CIDR ADDRESS finishing with /32, accordingly to the example below:
TYPE |
DATABASE |
USER |
CIDR-ADDRESS |
METHOD |
host |
all |
all |
127.0.0.1/32 |
md5 |
host |
all |
all |
192.168.1.1/32 |
Md5 |
host |
all |
all |
192.168.1.5/32 |
Md5 |
host |
all |
all |
192.168.1.10/32 |
Md5 |
host |
all |
all |
192.168.254.1/32 |
md5 |
The row in blue is enabling access to the DBMS by a machine located in another network.
Authentication
It is the process where the server establishes the client identity and defines if the client application has permission to connect using the provided user name.
Method: indicates the authentication method.
md5, crypt and password are based on a password and differ on how the password is sent through the connection. The md5 is the only one to support encrypted passwords.
trust, any user connected to the server is authorized to access the database without authentication.
Reject, the connection is rejected unconditionally. It rejects by filtering some hosts of a group.
Krb5, Kerberos v5 is used to para authenticate the user and it is available for TCP/IP connections only. (This is a safe system, suitable for distributed computing on public networks.
Ident, the user name of the client operating system is obtained: (for TCP/IP connections) contacting the identification server in the client and (for local connection) from the operating system.