Postgresql – How to get YAML Python Library in PostgreSQL

plpythonpostgresqlpostgresql-10

I would like to use YAML in some plpython code, but YAML is not included in the python3 extension for PostgreSQL.

My 'import yaml' gets an error that it cannot find yaml.

On my regular Python3 install I did 'pip3 install yaml' which worked fine.

How can I get yaml installed into PostgreSQL?

Thanks.

Some more info for clarification:

Here is the start of a function defined in PG:

-- Default audit trigger
create or replace function sys_audit() returns trigger language plpython3u AS $$
  from sys import path
  path.append('/usr/local/lib/ez-python-library/PostgreSQL/bin');

  from datetime import datetime
  from CommonRowFunctions import getPkValue, getRowValue, getRowChanges

  keyVal    = ''
  modData   = 'unknown'
...

The module 'CommonRowFunctions' tries to use YAML to configure logging. This module lives in my python library (external to PostgreSQL). This all works if I use a properties file for the log config, but using a dictionary is the preferred method and YAML makes that very easy.

Best Answer

This is a problem specific to EnterpriseDB. PostgreSQL does not ship Python, it merely ships a contrib module which your distribution is set to build against the system's Python. In the case of Windows, there is no Python for them to build against because there is no distribution. You can get any version of Python when you download "python" (more properly cpython). So instead, EnterpriseDB bundles a version of Python they provide as a distribution of PostgreSQL. Previously, that were bundling a statically linked Active State Python, now they're bundling cPython 3.3 (which they can dynamically link in). This is all from the docs here.

To configure your system to use EnterpriseDB Python though, you need to first follow a few steps. From the docs on configuration

After installing Language Pack, you must set the following variables: set PYTHONHOME=C:\edb\languagepack-9.6\x64\Python-3.3

Use the following commands to add Python, Perl and Tcl to your search path:
set PATH= C:\edb\LanguagePack-9.6\x64\Python-3.3\bin: C:\edb\LanguagePack-9.6\x64\Perl-5.20\bin: C:\edb\LanguagePack-9.6\x64\Tcl-8.5\bin:%PATH%
After setting the system-specific steps required to configure Language Pack on Windows, restart the Advanced Server database server; see Section 5 for detailed information about restarting the server.

After you configure that pip and easy_install should work fine.

Related Solutions

PostgreSQL PL/Python – How to Return Multiple Columns as Composite Types

The simple way to do that would be to call your bar() function like this:

postgres@[local]:5432:postgres:=# SELECT * FROM bar();
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

Time: 0.622 ms
postgres@[local]:5432:postgres:=#

Which returns as a record type in this context, with two rows and two columns.

Hope that helps. =)

PostgreSQL – Why is PL/Python Untrusted?

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.

Best Answer

Related Solutions

PostgreSQL PL/Python – How to Return Multiple Columns as Composite Types

PostgreSQL – Why is PL/Python Untrusted?

Related Question