postgres services running on debian server

[tgs]

Hi,

if I check the current running services on my Debian 6 server where I have installed PostgreSQL 9.2, I get:

Code: Select all

1698 ?        Ss     0:00 postgres: postgres servoy_repository 127.0.0.1(48444) idle 
1731 ?        Ss     0:00 postgres: postgres servoy_repository 127.0.0.1(54091) idle 
1737 ?        Ss     0:00 postgres: postgres core_data 127.0.0.1(54092) idle         
1738 ?        Ss     0:00 postgres: postgres log_data 127.0.0.1(54093) idle           
1740 ?        Ss     0:00 postgres: postgres spedition 127.0.0.1(54095) idle         
9608 ?        Ss     0:00 postgres: postgres postgres 91.58.182.122(50950) idle       
9609 ?        Ss     0:00 postgres: postgres spedition 91.58.182.122(50951) idle

Why are two servoy_repository entries in the list and why do the db's postgres and spedition have an IP that is different to the one of the other db's?

It would be nice if anybody could make this more clear.

[ngervasi]

Postgres spawns one process per database connection, presuming that your servoy server is running on localhost, the first processes are handling those connections (2 for the repository database are open), the other 2 connections are probably form you logging in using Pgadmin or another db tool from your computer.
Check the admin pages of your servoy server to see how many connections are currently open and check if 91.58.182.122 is your ip address.
For safety reasons remember to configure pg_hba.conf so that only the allowed hosts can connect and not the entire world.

[tgs]

Hi Nicola,

thank you for the explanation. I was worry about that there is running more than one instance of the db.

For safety reasons remember to configure pg_hba.conf so that only the allowed hosts can connect and not the entire world.

You are absolutely right, but how should I set the allowed hosts if the IP is daily changing, because of ADSL without static IP-address?

Regards

[ngervasi]

You are absolutely right, but how should I set the allowed hosts if the IP is daily changing, because of ADSL without static IP-address?

The safest setup is to allow connections only from localhost.
To connect from your pc you can use a reverse SSH tunnel to forward all the traffic on a local port to a remote server port.

If you are using MacOS X or any other unix like OS on your client pc you can do it by simply opening a terminal and start the tunnel using the following:

Code: Select all

ssh -N yourUserName@yourServer.yourDomain.xxx -L 6666/127.0.0.1/5432

Once the tunnel is open you can access the remote DB by pointing PgAdmin (or any other client) to localhost:6666 . All the traffic will be encrypted and redirected to your server on port 5432 but the connection will appear coming from the server itself.

If you are using Windows there are a lot of nice utilities for SSH Reverse tunnells, just search Google.

The other option (not recommended) is to configure pg_hba.conf so that the postgres user (and only that) is allowed to access the db from any IP. In this case I would recommend using SSL and a very strong password. Check postgres documentation for this.

[tgs]

Hi Nicola,

I have changed to the reverse SSH tunnel solution and it works perfect!

Thank you again
Thomas

[ngervasi]

You're welcome, security is never enough.