PostgreSQL – Why is PL/Python Untrusted?

plpythonpostgresqlpythonSecurity

According to the docs:

PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted variant plpython might become available in the future if a secure execution mechanism is developed in Python.

Why exactly is it difficult to develop a secure execution mechanism for Python but not for other languages such as Perl?

Best Answer

It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for some info on the problems, as well as:

The limitations aren't anything to do with PostgreSQL its self, they're inherent to the CPython interpreter implementation or possibly even the Python language its self.

Some other languages have checked runtimes, like Perl, Java, JavaScript and Lua. Most of them have faced a series of security issues as such confined execution environments are very hard to protect against all possible jailbreak exploits.

There's really nothing stopping PostgreSQL from adding a semitrusted Python interpreter, since rexec is "good enough" for many purposes. PostgreSQL doesn't tend to be keen on only-mostly-kinda-good-enough-maybe though. It would probably only be accepted if marked superuser-only, but you could always then grant access to it for specific users. It'd be better than untrusted Python.

Personally I think PL/V8 or similar is the future here, and would like to see it move toward being supported in core.

I've also vaguely explored the idea of a trusted Mono that can load "safe" assemblies written in C#, VB.NET, IronPython, or whatever but haven't been able to do much on that topic.

Related Solutions

Python MySQL Connector – Connection Pooling

I did some experimentation in console trying to figure out why this was happening for me as well. In the end, I searched right into the file folder for the package looking to ensure that the pooling file was present in the mysql.connector package version I had.

Sure enough there it was.

In the end, I simply changed my import statement. I had been using

import mysql.connector
pool = mysql.connector.pooling.MySQLConnectionPool(**config)

After playing in console for a bit I realized that it would be happy if I just used;

import mysql.connector
from mysql.connector import pooling
pool = pooling.MySQLConnectionPool(**config)

Worked fine. Couldn't tell you "why", but it worked.

Postgresql – Can’t connect to PostgreSQL instance using Python

Since you did not specify the host argument of che connect call, you are connecting via a unix named pipe instead of TCP/IP, so the error message might be slightly incorrect. BTW, the database should have opened a pipe named /var/run/postgresql/.s.PGSQL.5432. Can you check if it exists using command ls -la /var/run/postgresql/.s*? You might find there a pipe with a different number, or you might find and empty directory. In the first case, just add port=number (using the right number) to the connect call; in the latter case, find the correct directory for your postgresql and add host=path (using the right path) to the connect call.

In order to find the correct path, connect via psql and give command \conninfo. In will display the correct path, as in:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

P.S. It might also be a problem of the syntax you used. It should be psycopg2.connect(dbname="tournament")

Best Answer

Related Solutions

Python MySQL Connector – Connection Pooling

Postgresql – Can’t connect to PostgreSQL instance using Python

Related Question