Table of Contents General Languages Trusted / Untrusted JS Extension install manual in docker (create dedicated image) http Install Docker Use shell Install Docker

You can extend its scope with:

• extensions
• languages

List:

• C
• python
• JS
• Pel

2 flavors, eg. for Perl:

• PL/Perl
• PL/PerlU (Untrusted)

Operation	Trusted	Untrusted
access DB data	X	X
read on FS	X
make http call	X
launch process	X

With trusted language, there is no way to gain OS-level access with the permissions of the server process, hence any unprivileged database user can be trusted to use this language.

Isolation mode:

• PL/PerlU can share data between functions
• PL/PerlU cannot share data with PL/Perl

Normally, PL/Perl is installed as a “trusted” programming language named plperl. In this setup, certain Perl operations are disabled to preserve security. In general, the operations that are restricted are those that interact with the environment. This includes file handle operations, require, and use (for external modules). There is no way to access internals of the database server process or to gain OS-level access with the permissions of the server process, as a C function can do. Thus, any unprivileged database user can be permitted to use this language.

More in API

JS trusted extension

List

• http calls

From here Get:

• source
• control-file
• Makefile

# bash
make install
--psql
CREATE EXTENSION nvlfunc;

From SO

hstore.sql

CREATE EXTENSION hstore;

Dockerfile

FROM postgres:alpine
COPY hstore.sql /docker-entrypoint-initdb.d

Extension repository

# Start a container from PG alpine
docker stop postgres_http
docker rm postgres_http
docker run --detach  --env POSTGRES_HOST_AUTH_METHOD=trust --publish 5432:5432 --name postgres_http postgres:alpine

# Put extension code into container
cd /tmp
git clone https://github.com/pramsey/pgsql-http.git
docker cp ./pgsql-http postgres_http:/tmp

# Install build packages
docker exec  -it postgres_http  bash
apk update && apk upgrade && apk add --update alpine-sdk
apk add --no-cache bash git openssh make cmake curl-dev clang llvm9
cd /tmp/pgsql-http

# Build extension
make
make install

# Test 
psql -U postgres
CREATE EXTENSION http;
SELECT content FROM http_get('http://httpbin.org/ip');
exit
exit

# Create image
docker commit 
docker image tag <PREVIOUS_SHA> pgsql-http:latest

# Use image
docker run --detach  --env POSTGRES_HOST_AUTH_METHOD=trust --publish 5432:5432 --name postgres_http pgsql-http:latest

# Test image
psql postgres://postgres@localhost:5432
CREATE EXTENSION http;
SELECT content FROM http_get('http://httpbin.org/ip');

-- Activate extension
CREATE EXTENSION http;

-- Make GET
SELECT content FROM http_get('http://httpbin.org/ip');

-- Make PUT
SELECT status, content_type, content::json->>'data' AS data
  FROM http_put('http://httpbin.org/put', 'some text', 'text/plain');

https://github.com/petere/plsh

Dockerfile

FROM postgres:13-alpine
RUN apk add --no-cache git g++ make clang llvm10
RUN git clone https://github.com/petere/plsh && \
    cd plsh && \
    make && \
    make install

Build image docker build --tag plsh:latest .

Start container docker run --detach --env POSTGRES_HOST_AUTH_METHOD=trust --publish 5432:5432 --name postgres_sh plsh:latest

Test

psql postgres://postgres@localhost:5432
CREATE EXTENSION IF NOT EXISTS plsh;
CREATE OR REPLACE FUNCTION hello (who TEXT) RETURNS text
LANGUAGE plsh
AS $$
#!/bin/sh
echo "Hello, $1 !"
return 0;
$$;
SELECT hello(who:='world');