Thread: Problem with pl/python procedure connecting to the internet
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 listen_addresses = '*' (in postgresql.conf) and host all all 0.0.0.0/0 trust (in pg_hba.conf) 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. 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? Many thanks in advance. Igor Sosa Mayor -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::
listen_addresses should only affect the interfaces that the postgres server is listening to connections from. as long as your app is on the same machine, and uses localhost:someport to connect to the postgres server, then the default listen_addresses='localhost' should be sufficient.' if you want clients to connect from other systems, then listen_addresses = '*' is appropriate. listen_addresses should have no impact on what your plpython app can connect to outside of postgres, unless you're running the 'safe' version of pl*** -- john r pierce, recycling bits in santa cruz
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.klaver@aklaver.com
On 08/21/2015 12:34 AM, John R Pierce wrote: > listen_addresses should only affect the interfaces that the postgres > server is listening to connections from. as long as your app is on the > same machine, and uses localhost:someport to connect to the postgres > server, then the default listen_addresses='localhost' should be > sufficient.' > > if you want clients to connect from other systems, then listen_addresses > = '*' is appropriate. > > listen_addresses should have no impact on what your plpython app can > connect to outside of postgres, unless you're running the 'safe' version > of pl*** There is no safe(trusted) version of plpython, it is only available as plpythonu(ntrusted). So you are allowed to run with scissors:) > > > -- Adrian Klaver adrian.klaver@aklaver.com
John R Pierce <pierce@hogranch.com> writes: > listen_addresses should only affect the interfaces that the postgres > server is listening to connections from. as long as your app is on > the same machine, and uses localhost:someport to connect to the > postgres server, then the default listen_addresses='localhost' should > be sufficient.' > > if you want clients to connect from other systems, then > listen_addresses = '*' is appropriate. > > listen_addresses should have no impact on what your plpython app can > connect to outside of postgres, unless you're running the 'safe' > version of pl*** thanks a lot for your answer. I also thought that this config should not be important, but it seems to be necessary. In any case: server and app are on the same machine (my laptop), but for some reason the plpython procedure cannot connect to the internet. It works very unreliable. Have you maybe other hints? Many thanks in advance. -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::
Adrian Klaver <adrian.klaver@aklaver.com> writes: > Are you talking about getting data into the procedure, or out of it, > or both? [...] (I reduce your email only to this question in order to be clearer; sorry for this). thanks for your answer. To your questions: my setup is very easy and primitive: 1. postgres is in my laptop; I'm the only user and I will be the only user of the system; 2. the script is also in my laptop; 3. the only thing which connects to the internet is the plpython I wrote: it gets coordinates from Openstreetmap. The code I'm using is the following: http://pastie.org/10366558 Then a trigger uses this code to insert the coordinates in a table. Maybe I'm doing something wrong... -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::
On 08/21/2015 08:38 AM, Igor Sosa Mayor wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: > >> Are you talking about getting data into the procedure, or out of it, >> or both? > > [...] > (I reduce your email only to this question in order to be clearer; sorry > for this). > > thanks for your answer. > > To your questions: my setup is very easy and primitive: > 1. postgres is in my laptop; I'm the only user and I will be the only > user of the system; > 2. the script is also in my laptop; > 3. the only thing which connects to the internet is the plpython I > wrote: it gets coordinates from Openstreetmap. The code I'm using is > the following: http://pastie.org/10366558 > > Then a trigger uses this code to insert the coordinates in a table. > Maybe I'm doing something wrong... Does the code work reliably outside Postgres? It seems you are reliant on the availability of the OpenStreetMap service and its ability to provide a timely response. So what exactly is the trigger event, INSERT, UPDATE, both? Is it absolutely necessary that this happen in a trigger? > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > Does the code work reliably outside Postgres? > It seems you are reliant on the availability of the OpenStreetMap > service and its ability to provide a timely response. > > So what exactly is the trigger event, INSERT, UPDATE, both? > > Is it absolutely necessary that this happen in a trigger? yes, it works reliable. I can also add a timeout option, but it does not make any difference. As for the trigger: well, I don't know. I wanted to be triggered every time I add a city in the table 'ciudad'.postgres deals Somehow I have the impression postgres is trying to make a connection without having to rights to get the answer (I'm on linux). I dont understand exactly which process (and with which rights) is executing the plpython procedure... Thanks in any case for your answer. -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::
On 8/22/2015 12:23 AM, Igor Sosa Mayor wrote: > Somehow I have the impression postgres is trying to make a connection > without having to rights to get the answer (I'm on linux). I dont > understand exactly which process (and with which rights) is executing > the plpython procedure... > > Thanks in any case for your answer. each client connection creates a postgresql server process. pl-anything runs in that context, along with all sql for that client. say... is by any chance selinux running in enforcing mode ? you might try temporarily setting it to 'permissive' with the setenforce command, and see if your python works. -- john r pierce, recycling bits in santa cruz if this is a reply to a list, I'm subscribed to that list, please reply to the list not me personally.
John R Pierce <pierce@hogranch.com> writes: > On 8/22/2015 12:23 AM, Igor Sosa Mayor wrote: >> Somehow I have the impression postgres is trying to make a connection >> without having to rights to get the answer (I'm on linux). I dont >> understand exactly which process (and with which rights) is executing >> the plpython procedure... >> >> Thanks in any case for your answer. > > each client connection creates a postgresql server process. > pl-anything runs in that context, along with all sql for that client. > > > say... is by any chance selinux running in enforcing mode ? you > might try temporarily setting it to 'permissive' with the setenforce > command, and see if your python works. thanks. No selinux is running. Is there a way to check whether a process has the rights to make a connection to the internet (TCP or something like that)?
On 08/22/2015 12:23 AM, Igor Sosa Mayor wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: > >> Does the code work reliably outside Postgres? >> It seems you are reliant on the availability of the OpenStreetMap >> service and its ability to provide a timely response. >> >> So what exactly is the trigger event, INSERT, UPDATE, both? >> >> Is it absolutely necessary that this happen in a trigger? > > yes, it works reliable. I can also add a timeout option, but it does not > make any difference. > > As for the trigger: well, I don't know. I wanted to be triggered every > time I add a city in the table 'ciudad'.postgres deals Aargh, I was not paying attention. Is the function you show here: http://pastie.org/10366558 the actual function you are using in the trigger? If so, there is no mechanism for getting the location coordinates into the table, if that is what you want. If that is not what you want, then what is the function supposed to do? If it is not the trigger function, what is the actual function? > > Somehow I have the impression postgres is trying to make a connection > without having to rights to get the answer (I'm on linux). I dont > understand exactly which process (and with which rights) is executing > the plpython procedure... > > Thanks in any case for your answer. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > Is the function you show here: > > http://pastie.org/10366558 > > the actual function you are using in the trigger? > > If so, there is no mechanism for getting the location coordinates into > the table, if that is what you want. > this is the function and there is a trigger which gets the coordinates into the table. It works perfectly when postgres is able to make a connection and get the data. But this is exactly what works only rarely... And I dont know the reason... PS: this is the trigger, but as I said, it works if there is a connection... http://pastie.org/10368578 -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::
On 08/22/2015 10:16 AM, Igor Sosa Mayor wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: > > >> Is the function you show here: >> >> http://pastie.org/10366558 >> >> the actual function you are using in the trigger? >> >> If so, there is no mechanism for getting the location coordinates into >> the table, if that is what you want. >> > > this is the function and there is a trigger which gets the coordinates > into the table. It works perfectly when postgres is able to make a > connection and get the data. But this is exactly what works only > rarely... And I dont know the reason... What is your trigger definition? Please post here not on Pastie. So if you just run the Postgres function on its own, not via the trigger, does it always return a value? Are you taking lack of coordinates in the table as evidence of no connection or are you determining that some other way? Does anything show up in the Postgres logs when the trigger runs? Have you tried using plpy.notice in plpythonu and RAISE NOTICE in plpgsql to track what is happening? Also from here: http://geopy.readthedocs.org/en/latest/#module-geopy.geocoders geocode() exactly_one (bool) – Return one result or a list of results, if available. Not sure what is going to happen to your code if it gets a list instead of a single value. > > PS: this is the trigger, but as I said, it works if there is a > connection... > http://pastie.org/10368578 FYI, just include code snippets in the post. This keeps the code in the thread and makes the thread not dependent on an external source. To that end: CREATE TYPE coordenadas AS (lat numeric, lon numeric); CREATE OR REPLACE FUNCTION geocodificar(direccion text) returns coordenadas AS $$ from geopy.geocoders import Nominatim geoloc = Nominatim() location = geoloc.geocode(direccion) return(location.latitude, location.longitude) $$ LANGUAGE 'plpython2u'; CREATE OR REPLACE FUNCTION anadirgeocoord() RETURNS TRIGGER AS $$ DECLARE coord coordenadas; BEGIN SELECT * INTO coord from geocodificar(NEW.ciudad || ' ' || NEW.pais) ; NEW.latitute := (coord).lat; NEW.longitude := (coord).lon; RETURN NEW; END; $$ LANGUAGE plpgsql; > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> Are you taking lack of coordinates in the table as evidence of no
> connection or are you determining that some other way?
thanks again for your answer. The evidence is very easy. I get this[1].
BUT: if I try exactly the same python code in a console there is no
error and the coordinates are there.
> Have you tried using plpy.notice in plpythonu and RAISE NOTICE in
> plpgsql to track what is happening?
Yes, if the connection works, I get the correct answer. As I say: for
some reason postges can not make the connection OR get the answer (but
not always, it has worked only 3-4 times). All other things are working:
internet connection, python code...
[1]
ERROR: geopy.exc.GeocoderServiceError: <urlopen error [Errno -2] Nombre o servicio desconocido>
CONTEXTO: Traceback (most recent call last):
PL/Python function "geocodificar", line 4, in <module>
location = geoloc.geocode(direccion)
PL/Python function "geocodificar", line 190, in geocode
PL/Python function "geocodificar", line 159, in _call_geocoder
función PL/Python «geocodificar»
sentencia SQL: «SELECT * from geocodificar(NEW.ciudad || ' ' || NEW.pais)»
función PL/pgSQL anadirgeocoord() en la línea 5 en sentencia SQL
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Igor Sosa Mayor > Sent: Sonntag, 23. August 2015 07:22 > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Problem with pl/python procedure connecting to the > internet > > Adrian Klaver <adrian.klaver@aklaver.com> writes: > > > Are you taking lack of coordinates in the table as evidence of no > > connection or are you determining that some other way? > > thanks again for your answer. The evidence is very easy. I get this[1]. > BUT: if I try exactly the same python code in a console there is no error and > the coordinates are there. > > > Have you tried using plpy.notice in plpythonu and RAISE NOTICE in > > plpgsql to track what is happening? > > Yes, if the connection works, I get the correct answer. As I say: for some > reason postges can not make the connection OR get the answer (but not > always, it has worked only 3-4 times). All other things are working: > internet connection, python code... > > > [1] > ERROR: geopy.exc.GeocoderServiceError: <urlopen error [Errno -2] Nombre > o servicio desconocido> > CONTEXTO: Traceback (most recent call last): > PL/Python function "geocodificar", line 4, in <module> > location = geoloc.geocode(direccion) > PL/Python function "geocodificar", line 190, in geocode > PL/Python function "geocodificar", line 159, in _call_geocoder función > PL/Python «geocodificar» > sentencia SQL: «SELECT * from geocodificar(NEW.ciudad || ' ' || > NEW.pais)» > función PL/pgSQL anadirgeocoord() en la línea 5 en sentencia SQL Are you sure that you really use exactly the same code? The indication "survice unknown" seems to point to an error in the URL and not to an authorization problem. Maybe some encoding problem? The irritating thing is that you mention that it works sometimes and sometimes not. Getting back to the encoding problem idea, is there a difference if you call the function with a name containing special characters (e.g. "é", "à", "ñ") or not? Regards Charles
"Charles Clavadetscher" <clavadetscher@swisspug.org> writes: > Are you sure that you really use exactly the same code? The indication > "survice unknown" seems to point to an error in the URL and not to an > authorization problem. Maybe some encoding problem? THnaks for your hints. Yes, exactly the same code. Exactly the same query: two consoles, one with postgres, other with ipython. Postgres gives the error; ipython works. > The irritating thing is that you mention that it works sometimes and > sometimes not. Getting back to the encoding problem idea, is there a > difference if you call the function with a name containing special > characters (e.g. "é", "à", "ñ") or not? No special characters in the query. To be honest: it worked only 2-3 times at work and I thought 'finally I managed to do it work'. But since then, it is not working anymore... In any case: I'm pretty sure it is a problem of postgres which is not able to make the connection. If I put a parameter timeout=15, postgres gives immediately the same error.
Hi Weird. According to http://geopy.readthedocs.org/en/latest/#geopy.exc.GeocoderServiceError this is the most generic exception, only used when there is not a more specific one (which include by the way problems caused by connectivity and authorization). The message of the exception is supposed to be the one delivered by the original application. In this case: "Nombre o servicio desconocido": I am wondering if there is an error message matching the one you receive in PostgreSQL. > > Are you sure that you really use exactly the same code? The indication > > "survice unknown" seems to point to an error in the URL and not to an > > authorization problem. Maybe some encoding problem? > > THnaks for your hints. > > Yes, exactly the same code. Exactly the same query: two consoles, one with > postgres, other with ipython. Postgres gives the error; ipython works. Somehow I expected that answer. I am not a python expert, but it happens that different pieces of code at the end do not use the same libraries or framework code by configuration. But if you are positive on that, no more discussion. > > The irritating thing is that you mention that it works sometimes and > > sometimes not. Getting back to the encoding problem idea, is there a > > difference if you call the function with a name containing special > > characters (e.g. "é", "à", "ñ") or not? > > No special characters in the query. To be honest: it worked only 2-3 times at > work and I thought 'finally I managed to do it work'. But since then, it is not > working anymore... Well, if it worked a few times and then never again, then something must have changed in your system. This could also be a possible reason for the error. Sorry for asking obvious things, but it happened to me to oversee some of them in the past: Are you making the calls from the same computer? > In any case: I'm pretty sure it is a problem of postgres which is not able to > make the connection. If I put a parameter timeout=15, postgres gives > immediately the same error. So it looks like a timeout problem with a confusing error message. A possible way to follow this up would be to call another webservice from a python function in PG and see if it behaves the same. Another way to analyze the problem could be to sniff the network traffic and see what really happens when you make the function call. This could help at least to discard network problems and may lead to some useful information. I hope that you will find a solution. Bye Charles
On 08/23/2015 12:09 AM, Igor Sosa Mayor wrote:
> "Charles Clavadetscher" <clavadetscher@swisspug.org> writes:
>
>> Are you sure that you really use exactly the same code? The indication
>> "survice unknown" seems to point to an error in the URL and not to an
>> authorization problem. Maybe some encoding problem?
>
> THnaks for your hints.
>
> Yes, exactly the same code. Exactly the same query: two consoles, one
> with postgres, other with ipython. Postgres gives the error; ipython
> works.
>
>> The irritating thing is that you mention that it works sometimes and
>> sometimes not. Getting back to the encoding problem idea, is there a
>> difference if you call the function with a name containing special
>> characters (e.g. "é", "à", "ñ") or not?
>
> No special characters in the query. To be honest: it worked only 2-3
> times at work and I thought 'finally I managed to do it work'. But since
> then, it is not working anymore...
>
> In any case: I'm pretty sure it is a problem of postgres which is not
> able to make the connection. If I put a parameter timeout=15,
> postgres gives immediately the same error.
What happens if you run the function below directly in psql and not
through the trigger?
CREATE OR REPLACE FUNCTION geocodificar(direccion text)
returns coordenadas
AS $$
from geopy.geocoders import Nominatim
geoloc = Nominatim()
location = geoloc.geocode(direccion)
return(location.latitude, location.longitude)
$$
LANGUAGE 'plpython2u';
Hmm, just noticed plpython2u.
Do you have both Python 2 and Python 3 on this machine?
Did you create both the 2u and 3u variants of plpythonu?
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
It might be a dumb thought, but...
Did you run the script from ipython as postgres user?
External programs called by triggers run as postgres, and maybe your postgres user doesn't have rights to reach the remote service.
Regards,
Sandor
On 23 August 2015 at 09:09, Igor Sosa Mayor <joseleopoldo1792@gmail.com> wrote:
"Charles Clavadetscher" <clavadetscher@swisspug.org> writes:
> Are you sure that you really use exactly the same code? The indication
> "survice unknown" seems to point to an error in the URL and not to an
> authorization problem. Maybe some encoding problem?
THnaks for your hints.
Yes, exactly the same code. Exactly the same query: two consoles, one
with postgres, other with ipython. Postgres gives the error; ipython
works.
> The irritating thing is that you mention that it works sometimes and
> sometimes not. Getting back to the encoding problem idea, is there a
> difference if you call the function with a name containing special
> characters (e.g. "é", "à", "ñ") or not?
No special characters in the query. To be honest: it worked only 2-3
times at work and I thought 'finally I managed to do it work'. But since
then, it is not working anymore...
In any case: I'm pretty sure it is a problem of postgres which is not
able to make the connection. If I put a parameter timeout=15,
postgres gives immediately the same error.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 08/23/2015 12:09 AM, Igor Sosa Mayor wrote:
> "Charles Clavadetscher" <clavadetscher@swisspug.org> writes:
>
>> Are you sure that you really use exactly the same code? The indication
>> "survice unknown" seems to point to an error in the URL and not to an
>> authorization problem. Maybe some encoding problem?
>
> THnaks for your hints.
>
> Yes, exactly the same code. Exactly the same query: two consoles, one
> with postgres, other with ipython. Postgres gives the error; ipython
> works.
>
>> The irritating thing is that you mention that it works sometimes and
>> sometimes not. Getting back to the encoding problem idea, is there a
>> difference if you call the function with a name containing special
>> characters (e.g. "é", "à", "ñ") or not?
>
> No special characters in the query. To be honest: it worked only 2-3
> times at work and I thought 'finally I managed to do it work'. But since
> then, it is not working anymore...
>
> In any case: I'm pretty sure it is a problem of postgres which is not
> able to make the connection. If I put a parameter timeout=15,
> postgres gives immediately the same error.
I tried it here, using your functions, and could not get it to fail:
Running as postgres user.
test=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.2 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)
Python 2. I did change the plpython2u to plpythonu in geocodificar as I
have only the one version of plpythonu installed.
test=# create table lat_long(ciudad text, pais text, latitute numeric
not null, longitude numeric not null);
create trigger lat_lon before insert or update on lat_long for each row
execute procedure anadirgeocoord();
test=# insert into lat_long (ciudad, pais) values ('Bellingham', 'USA');
INSERT 0 1
test=# insert into lat_long (ciudad, pais) values ('Seattle', 'USA');
INSERT 0 1
test=# insert into lat_long (ciudad, pais) values ('London', 'UK');
INSERT 0 1
test=# insert into lat_long (ciudad, pais) values ('Perth', 'AU');
INSERT 0 1
test=# select * from lat_long ;
ciudad | pais | latitute | longitude
------------+------+-------------+--------------
Bellingham | USA | 48.754402 | -122.4788602
Seattle | USA | 47.6038321 | -122.3300624
London | UK | 51.5073219 | -0.1276474
Perth | AU | -31.9527121 | 115.8604796
(4 rows)
test=# update lat_long set pais= 'UK' where ciudad ='Bellingham';
UPDATE 1
test=# select * from lat_long ;
ciudad | pais | latitute | longitude
------------+------+-------------+--------------
Seattle | USA | 47.6038321 | -122.3300624
London | UK | 51.5073219 | -0.1276474
Perth | AU | -31.9527121 | 115.8604796
Bellingham | UK | 55.1443709 | -2.2549272
(4 rows)
So it is something to do with the setup on your laptop.
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
"Charles Clavadetscher" <clavadetscher@swisspug.org> writes: >> Yes, exactly the same code. Exactly the same query: two consoles, one with >> postgres, other with ipython. Postgres gives the error; ipython works. > > Somehow I expected that answer. I am not a python expert, but it happens > that different pieces of code at the end do not use the same libraries or > framework code by configuration. But if you are positive on that, no more > discussion. Well, I put the same code... what exactly python does is another question... > Sorry for asking obvious things, but it happened to me to oversee some of > them in the past: Are you making the calls from the same computer? obvious things are often the most important ones. But: yes, this is all the time my laptop. And I did not change the config of postgres since I put the configurations I mentioned in my first email. > So it looks like a timeout problem with a confusing error message. > A possible way to follow this up would be to call another webservice from a > python function in PG and see if it behaves the same. I had the same idea... but a small script querying the IMDB database works perfectly in the console, but gives an error in PG. THe error is not so obvious like in the geocoder case, but in any case the movie data are not loaded. > Another way to analyze the problem could be to sniff the network traffic and > see what really happens when you make the function call. This could help at > least to discard network problems and may lead to some useful > information. GOod idea. I see wireshark changed a lot since the last time I used it... if I'm using it the right way, the thing is pointing in the direction I thought: there is NO CONNECTION from PG to the internet when I use the function. I dont see any packages. From the python console there is a connection to nominatim (openstreetmap). I think there is exactly the problem: PG does not make any attempt (or it does but it fails even before any package is being sent) to the internet. THnaks for your ideas and help. -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::
Adrian Klaver <adrian.klaver@aklaver.com> writes: > What happens if you run the function below directly in psql and not > through the trigger? exactly the same error. > Do you have both Python 2 and Python 3 on this machine? I have both. > Did you create both the 2u and 3u variants of plpythonu? I tried both... in my system (archlinux) there is plpython2u and plpythonu. I think the 2nd one is python3.
s d <daku.sandor@gmail.com> writes: > It might be a dumb thought, but... thanks. There are not dumb thoughts with such problems... > Did you run the script from ipython as postgres user? > External programs called by triggers run as postgres, and maybe your > postgres user doesn't have rights to reach the remote service. i don't understand exactly (it is the 1. time I write a procedure even in PG). It is so: 1. I run ipython as my user (igor); 2. I run postgres both as user and as postgres. In both cases the same error. PG does not run ipython, because ipython is just a console for python. PG should only run python (2 or 3). IN any case, important is your last thought... I dont see in the doc of PG in the chapter of rules and privileges nothing about privileges to make internet connections... -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::
Adrian Klaver <adrian.klaver@aklaver.com> writes: > I tried it here, using your functions, and could not get it to fail: well... i'm happy my function is working... my 1. procedure in PG... Now maybe I'm able to get it working in my laptop... > So it is something to do with the setup on your laptop. yes, or the network I'm in... may I ask if you are running the fucntion in a server?
On 08/23/2015 09:53 AM, Igor Sosa Mayor wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: > >> What happens if you run the function below directly in psql and not >> through the trigger? > > exactly the same error. > >> Do you have both Python 2 and Python 3 on this machine? > > I have both. > >> Did you create both the 2u and 3u variants of plpythonu? > > I tried both... in my system (archlinux) there is plpython2u and > plpythonu. I think the 2nd one is python3. How did they get there? Or more to the point how where they built? What happens if you change your geocodificar to use plpythonu? > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 08/23/2015 09:53 AM, Igor Sosa Mayor wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: > >> What happens if you run the function below directly in psql and not >> through the trigger? > > exactly the same error. > >> Do you have both Python 2 and Python 3 on this machine? > > I have both. > >> Did you create both the 2u and 3u variants of plpythonu? > > I tried both... in my system (archlinux) there is plpython2u and > plpythonu. I think the 2nd one is python3. > > Meant to add: What is the default system Python? > -- Adrian Klaver adrian.klaver@aklaver.com
On 08/23/2015 10:02 AM, Igor Sosa Mayor wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: > >> I tried it here, using your functions, and could not get it to fail: > > well... i'm happy my function is working... my 1. procedure in PG... Now > maybe I'm able to get it working in my laptop... > >> So it is something to do with the setup on your laptop. > > yes, or the network I'm in... may I ask if you are running the fucntion > in a server? It is running in the Postgres server, if that is what you are asking? The machine it is running on is my home desktop unit, so nothing special. > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 23 August 2015 at 18:59, Igor Sosa Mayor <joseleopoldo1792@gmail.com> wrote:
s d <daku.sandor@gmail.com> writes:
> It might be a dumb thought, but...
thanks. There are not dumb thoughts with such problems...
> Did you run the script from ipython as postgres user?
> External programs called by triggers run as postgres, and maybe your
> postgres user doesn't have rights to reach the remote service.
i don't understand exactly (it is the 1. time I write a procedure even
in PG). It is so:
1. I run ipython as my user (igor);
2. I run postgres both as user and as postgres. In both cases the same
error. PG does not run ipython, because ipython is just a console for
python. PG should only run python (2 or 3).
IN any case, important is your last thought... I dont see in the doc of
PG in the chapter of rules and privileges nothing about privileges to
make internet connections...
Ok. You run IPython, as Igor, so your script runs in it as the user igor, with igor's permissions.
When you run the same script from PG it runs as the OS user postgres(regardless of which PG user fired it), with the permissions of the OS user postgres.
And maybe, just maybe the postgres user can't get trough your firewall, iptables rule, or something.
By the way: What OS do you use?
If it's possible try to log into your OS as postgres, start IPython and run your script. If it fails we have a suspect.
Regards,
Sandor
Adrian Klaver <adrian.klaver@aklaver.com> writes: >> I tried both... in my system (archlinux) there is plpython2u and >> plpythonu. I think the 2nd one is python3. > > How did they get there? > > Or more to the point how where they built? just installed with postgresql which comes with archlinux. I did not built anything. But, really: I dont think the problem is plpython2u o 3: 1. I have in my system both and both have the same libraries (in this case, geopy); 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the internet work perfectly. The problem is the connection, either the setup in my laptop or the setup in my network (but I get the same problem at home and at work...) > What happens if you change your geocodificar to use plpythonu? exactly the same. I tested all combinations... (thanks again!)
Adrian Klaver <adrian.klaver@aklaver.com> writes: > What is the default system Python? python3. But see my other answer. I dont think this is the problem.
On 08/23/2015 10:38 AM, Igor Sosa Mayor wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: > >>> I tried both... in my system (archlinux) there is plpython2u and >>> plpythonu. I think the 2nd one is python3. >> >> How did they get there? >> >> Or more to the point how where they built? > > just installed with postgresql which comes with archlinux. I did not > built anything. But, really: I dont think the problem is plpython2u o 3: > 1. I have in my system both and both have the same libraries (in this > case, geopy); > 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the > internet work perfectly. > > The problem is the connection, either the setup in my laptop or the > setup in my network (but I get the same problem at home and at work...) You are using the same machine, the laptop, in both locations, correct? If so then the problem is some sort of access control issue. When you try an INSERT, what shows in?: 1) The Postgres logs. 2) The system log. > >> What happens if you change your geocodificar to use plpythonu? > > exactly the same. I tested all combinations... > > (thanks again!) > > > -- Adrian Klaver adrian.klaver@aklaver.com
Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> Or more to the point how where they built?
> just installed with postgresql which comes with archlinux. I did not
> built anything. But, really: I dont think the problem is plpython2u o 3:
> 1. I have in my system both and both have the same libraries (in this
> case, geopy);
> 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the
> internet work perfectly.
Well, that hardly proves that Python code that *does* connect to the net
would work. The possibility that you're using a different Python version
inside Postgres and it's broken for network access is one you should take
very seriously.
However, what this smells like to me is a permissions problem. I think
you were way too quick to dismiss the idea that SELinux (or something
just like it) is restricting outbound internet connections from Postgres.
It's standard for SELinux to be configured so that network-accessible
daemons like Postgres are locked down harder than the very same code
would be treated when being invoked from the command line --- and network
access would be one of the prime candidates to be disabled by default.
Have you poked around under /var/log/ to see if the kernel logs anything
when the connection attempt doesn't work? For that matter, have you
checked the postmaster log to see what Postgres logs about it?
regards, tom lane
On 8/23/2015 10:49 AM, Tom Lane wrote:
However, what this smells like to me is a permissions problem. I think you were way too quick to dismiss the idea that SELinux (or something just like it) is restricting outbound internet connections from Postgres. It's standard for SELinux to be configured so that network-accessible daemons like Postgres are locked down harder than the very same code would be treated when being invoked from the command line --- and network access would be one of the prime candidates to be disabled by default. Have you poked around under /var/log/ to see if the kernel logs anything when the connection attempt doesn't work? For that matter, have you checked the postmaster log to see what Postgres logs about it?
also, `getenforce` ... if it comes back enabled, as root, do...
setenforce permissive
and try your trigger again (don't reboot or restart anything, just do it)
-- john r pierce, recycling bits in santa cruz
In cases like this I normally restart the progresql under strace/truss etc and then wade through the output, it will normally tell me which process was invoked. On 23/08/15 18:49, Tom Lane wrote: > Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes: >> Adrian Klaver <adrian.klaver@aklaver.com> writes: >>> Or more to the point how where they built? >> just installed with postgresql which comes with archlinux. I did not >> built anything. But, really: I dont think the problem is plpython2u o 3: >> 1. I have in my system both and both have the same libraries (in this >> case, geopy); >> 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the >> internet work perfectly. > Well, that hardly proves that Python code that *does* connect to the net > would work. The possibility that you're using a different Python version > inside Postgres and it's broken for network access is one you should take > very seriously. > > However, what this smells like to me is a permissions problem. I think > you were way too quick to dismiss the idea that SELinux (or something > just like it) is restricting outbound internet connections from Postgres. > It's standard for SELinux to be configured so that network-accessible > daemons like Postgres are locked down harder than the very same code > would be treated when being invoked from the command line --- and network > access would be one of the prime candidates to be disabled by default. > > Have you poked around under /var/log/ to see if the kernel logs anything > when the connection attempt doesn't work? For that matter, have you > checked the postmaster log to see what Postgres logs about it? > > regards, tom lane > >
Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes: > 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? I want to update the situation in this email following up my own email. Sorry for not answering every question (in any case: I don't have SElinux or similar). The situation is very strange. It works now perfectly BUT ONLY after restarting PG. I'm playing with the log options in PG and I don't see in the logs nothing strange (PG starts without problems). But I see that the log options in PG are really rich. Could maybe someone tell me which could be the best options to find the problem? I will be offline now during 24h, but I will try to make some experiments in the meantime. Thanks again for your help. -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::
On 08/23/2015 01:15 PM, Igor Sosa Mayor wrote: > Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes: > >> 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? > > I want to update the situation in this email following up my own email. > Sorry for not answering every question (in any case: I don't have > SElinux or similar). > > The situation is very strange. It works now perfectly BUT ONLY after > restarting PG. I'm playing with the log options in PG and I don't see in > the logs nothing strange (PG starts without problems). As I understand it you are running everything on a laptop. Are you saying the laptop has been running non-stop the three days this discussion has been going on and Postgres was never shut down till now? > > But I see that the log options in PG are really rich. Could maybe > someone tell me which could be the best options to find the problem? I > will be offline now during 24h, but I will try to make some experiments > in the meantime. From here: http://www.postgresql.org/docs/9.4/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT I would at least log: log_connections log_disconnections log_statement 'mod' log_line_prefix %u %m %p So does looking back in the logs, either Postgres or system, show anything? > > Thanks again for your help. > > -- Adrian Klaver adrian.klaver@aklaver.com
Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes: > Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes: > >> 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? I'm again with the last update. The problem seems to be that for some reason PG does not realize that there is a network connection. More precisely: 1. I start the computer 2. PG starts and I can use it normally, EXCEPT from the plpython procedure which does not connect 3. then I restart PG (with systemd) and it works. PG seems to "see" that there is a connection. Maybe it has something to do with systemd? I'm trying to get all debug information, but I can't see anything strange in the log. In order not to full the list with an attachment, I post the log here: http://pastie.org/10373991 Any ideas? Thanks in advance!
Adrian Klaver <adrian.klaver@aklaver.com> writes: >> But I see that the log options in PG are really rich. Could maybe >> someone tell me which could be the best options to find the problem? I >> will be offline now during 24h, but I will try to make some experiments >> in the meantime. > > From here: > http://www.postgresql.org/docs/9.4/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT thanks. I answered you indirectly in the other email.
Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes:
> Maybe it has something to do with systemd? I'm trying to get all debug
A little more information: the unit of postgresql in my systemd looks
like this[1]. That means, it is started of course after the network (but
maybe there is not any connection avalaible?)
[1]
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
TimeoutSec=120
User=postgres
Group=postgres
Environment=PGROOT=/var/lib/postgres
SyslogIdentifier=postgres
PIDFile=/var/lib/postgres/data/postmaster.pid
ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data
ExecStart= /usr/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120
ExecReload=/usr/bin/pg_ctl -s -D ${PGROOT}/data reload
ExecStop= /usr/bin/pg_ctl -s -D ${PGROOT}/data stop -m fast
# Due to PostgreSQL's use of shared memory, OOM killer is often overzealous in
# killing Postgres, so adjust it downward
OOMScoreAdjust=-200
# Additional security-related features
PrivateTmp=true
ProtectSystem=full
NoNewPrivileges=true
[Install]
WantedBy=multi-user.target
Dave Potts <dave.potts@pinan.co.uk> writes: > In cases like this I normally restart the progresql under strace/truss > etc and then wade through the output, it will normally tell me which > process was invoked. Thanks for the hint. I answered you indirectly in other email.
On 08/25/2015 01:30 AM, Igor Sosa Mayor wrote: > Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes: > >> Igor Sosa Mayor <joseleopoldo1792@gmail.com> writes: >> >>> 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? > > I'm again with the last update. The problem seems to be that for some > reason PG does not realize that there is a network connection. > > More precisely: > 1. I start the computer > 2. PG starts and I can use it normally, EXCEPT from the plpython > procedure which does not connect At this point can you connect to the Postgres server on your laptop from another machine? > 3. then I restart PG (with systemd) and it works. PG seems to "see" that > there is a connection. Best guess, since this is a laptop, the network is not connected until NetworkManager(or something similar) is active. On my laptop that happens late in the startup sequence, after Postgres starts. This is especially true if you are connecting to a wireless AP. > > Maybe it has something to do with systemd? I'm trying to get all debug > information, but I can't see anything strange in the log. In order not > to full the list with an attachment, I post the log here: > http://pastie.org/10373991 FYI, include the time in your log prefix, it gives some idea of how far apart the events are happening. Also logging NOTICE and above is a good place to start. DEBUG buries you in mass of detail which may or may not be relevant. > > Any ideas? > > Thanks in advance! > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > Best guess, since this is a laptop, the network is not connected until > NetworkManager(or something similar) is active. On my laptop that > happens late in the startup sequence, after Postgres starts. This is > especially true if you are connecting to a wireless AP. I can not see in the logs anything strange. Since it works well as soon as I restart PG, I will live with this small problem for now. Thanks a lot for your help. -- :: Igor Sosa Mayor :: joseleopoldo1792@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :: ::