PostgreSQL

From Admin-SIG

Simple Private Network Setup

Many people are using PostGres for very small, mostly private installations. Those of us who are not database professionals can find the stacks of documentation about things like users, access, and privalages rather intimidating. The following is a summary of some of the basic changes I made to install a functional, but not very secure, database server for my private network.

In the main postgres configuration file (/etc/postgresql/postgresql.conf on Debian), see that the line:

         tcpip_socket = true

is present to allow network connections to the database. The default configuration allows access only to local clients.

Now you will need some access settings in pg_hba.conf (/etc/postgresql/pg_hba.conf on Debian). Something about my systems is not configured properly for IDENT authentication on my network. I did not want to take the time to figure out how to use nd5 or kerberos, so I just opened up the database. Security is only by user name, no password needed. Most default setups to allow some basic local connections which I will not repeat. Here are the ones I added:

       # Allow all local TCP connections via loopback:
       host    all     all     127.0.0.1/32    trust

       # Allow any authenticated user on the network to access the server by same username (can be spoofed)
       host    all     all     192.168.1.0     255.255.255.0   trust

This is followed by the reject line to reject any other attempted connections. I also commented out the IPv6 entries, since I do not use that.

Testing

First make sure you can connect at all. As user postgres, see if you can connect with the command:

      psql dbName

If this demonstrates that the database dbName exists, try again as a typical user, joeUser, who should have access to this database. If that works, move on to TCP/IP connections. As your regular user, connect with the command

      psql -h localhost dbName joeUser

This demonstrates that TCP/IP connections work, at least for local loopback connections, as allowed by the first addition to pg_hba.conf.

Now test TCP/IP from another node on the network, as joeUser:

      psql -h postgreSQLserverName dbName joeUser

After establishing that this connection can be made with the trust access setting, you can experiment with stronger security settings like IDENT or true encrypted password authentication methods.