Python not calling an external program part 3
Question: I have been having problems trying to run an external program from a python
program that was generated from a trigger in a postgres 9.2 database. The
trigger works. It writes to a file. I had tried just running the external
program but the permissions would not allow it to run. I was able to create a
folder (using os.system(“mkdir”) ). The owner of the folder is NETWORK
SERVICE.

I need to run a program called sdktest. When I try to run it no response
happens so I think that means that the python program does not have enough
permissions (with an owner of NETWORK SERVICE) to run it.

I have been having my program copy files that it needs into that directory so
they would have the correct permissions and that has worked to some degree but
the program that I need to run is the last one and it is not running because
it does not have enough permissions.

My python program runs a C++ program called PG_QB_Connector which calls
sdktest.

Is there any way I can change the owner of the process to be a “normal” owner?
Is there a better way to do this? Basically I just need to have this C++
program have eniough perms to run correctly.

BTW, when I run the C++ program by hand, the line that runs the sdktest
program runs correctly, however, when I run it from the postgres/python it
does not do anything...

I have Windows 7, python 3.2. The other 2 questions that I asked about this
are located [here](http://stackoverflow.com/questions/14278109/python-not-
calling-external-program) and
[here](http://stackoverflow.com/questions/14364928/python-not-calling-an-
external-program-part-2)

The python program:

    
    
    CREATE or replace FUNCTION scalesmyone (thename text)
    RETURNS int
    AS $$
    a=5
    f = open('C:\\JUNK\\frompython.txt','w')
    f.write(thename)
    f.close()
    import os
    os.system('"mkdir C:\\TEMPWITHOWNER"')
    os.system('"mkdir C:\\TEMPWITHOWNER\\addcustomer"')
    os.system('"copy  C:\\JUNK\\junk.txt C:\\TEMPWITHOWNER\\addcustomer"')
    os.system('"copy  C:\\BATfiles\\junk6.txt   C:\\TEMPWITHOWNER\\addcustomer"')
    os.system('"copy  C:\\BATfiles\\run_addcust.bat   C:\\TEMPWITHOWNER\\addcustomer"')
    os.system('"copy  C:\\Workfiles\\PG_QB_Connector.exe  C:\\TEMPWITHOWNER\\addcustomer"')
    os.system('"copy  C:\\Workfiles\\sdktest.exe  C:\\TEMPWITHOWNER\\addcustomer"')
    import subprocess
    return_code = subprocess.call(["C:\\TEMPWITHOWNER\\addcustomer\\PG_QB_Connector.exe", '"hello"'])
    $$ LANGUAGE plpython3u;
    

The C++ program that is called from the python program and calls sdktest.exe
is below

    
    
    command = "copy C:\\Workfiles\\AddCustomerFROMWEB.xml C:\\TEMPWITHOWNER\\addcustomer\\AddCustomerFROMWEB.xml";
    system(command.c_str());
    
    
    //everything  except for the qb file is in my local folder
    command = "C:\\TEMPWITHOWNER\\addcustomer\\sdktest.exe  \"C:\\Users\\Public\\Documents\\Intuit\\QuickBooks\\Company Files\\Shain Software.qbw\"  C:\\TEMPWITHOWNER\\addcustomer\\AddCustomerFROMWEB.xml C:\\TEMPWITHOWNER\\addcustomer\\outputfromsdktestofaddcust.xml";
    system(command.c_str());
    


Answer: It sounds like you want to invoke a command-line program from within a
PostgreSQL trigger or function.

A usually-better alternative is to have the trigger send a `NOTIFY` and have a
process with a PostgreSQL connection `LISTEN`ing for notifications. When a
notification comes in, the process can start your program. This is the
approach I would recommend; it's a lot cleaner and it means your program
doesn't have to run under PostgreSQL's user ID. See
[`NOTIFY`](http://www.postgresql.org/docs/current/static/sql-notify.html) and
[`LISTEN`](http://www.postgresql.org/docs/current/static/sql-listen.html).

If you really need to run commands from inside Pg:

You can use
[`PL/Pythonu`](http://www.postgresql.org/docs/current/static/plpython.html)
with `os.system` or `subprocess.check_call`;
[`PL/Perlu`](http://www.postgresql.org/docs/current/static/plperl.html) with
`system()`; etc. All these can run commands from inside Pg if you need to. You
can't invoke programs directly from PostgreSQL, you need to use one of the
'untrusted' (meaning fully privileged, not sandboxed) procedural languages to
invoke external executables. PL/TCL can probably do it too.

**Update** :

Your Python code as shown above has several problems:

  * Using `os.system` in Python to copy files is just wrong. Use the `shutil` library: <http://docs.python.org/3/library/shutil.html> to copy files, and the simple `os.mkdir` command to create directories. 
  * The double-layered quoting looks wrong; didn't you mean to quote only each argument not the whole command? You should be using `subprocess.call` instead of `os.system` anyway. 
  * Your final `subprocess.call` invocation appears OK, but fails to check the error code so you'll never know if it went wrong; you should use `subprocess.check_call` instead.

The C++ code also appears to fail to check for errors from the `system()`
invocations so you'll never know if the command it runs fails.

Like the Python code, copying files in C++ by using the `copy` shell command
is generally wrong. Microsoft Windows provides the
[`CopyFile`](http://msdn.microsoft.com/en-
us/library/windows/desktop/aa363851%28v=vs.85%29.aspx) function for this;
equivalents or alternatives exist on other platforms and you can use portable-
but-less-efficient stream copying too.

