Re: Problem with pl/python procedure connecting to the internet

On 08/20/2015 10:30 PM, Igor Sosa Mayor wrote:
> Hi,
>
> I'm a beginner with pgsql and have the following problem. I have written
> a pl/python procedure to query geolocations (with the library geopy). It
> inserts them into a table. All this works perfectly.
>
> The problem is the configuration with the network connections. I'm using
> pgsql in a private laptop. In order to let the procedure get results
> from the internet I had to put

This part I am uncertain about.

Are you talking about getting data into the procedure, or out of it, or
both?

>
> listen_addresses = '*' (in postgresql.conf) and
> host all all 0.0.0.0/0 trust (in pg_hba.conf)

I would at least change trust to md5, which would require a password.
You can further restrict by user and to database. If you set up
SSL(http://www.postgresql.org/docs/9.4/interactive/ssl-tcp.html) you can
use hostssl.

>
> I don't think this is a good solution. Moreover: it doesn't work in all
> places (work, home, etc.), since I imagine it depends on the fact that
> the port is open.

listen_addresses just applies to what address Postgres listens to, in
this case all IP interfaces. The port it listens on is set by port =. In
any case this applies to the server, not to what your laptop can see.
That is probably shaped by any firewalls you are behind and changing
Postgres settings is not going to really help that.

>
> My question is therefore:
> 1. is there a way to permit the pl/python to connect to the internet all
> the time and with a better configuration?
> 2. or should I forget the procedure and write a python script outside
> the database?

Not seeing that is going to help, you still have to connect to whatever
machine is hosting the script. Even if the script is on your laptop it
will still need to connect to the remote database and you are back to
trying to connect to Postgres.

>
> Many thanks in advance.
>
> Igor Sosa Mayor
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com