external_file

Overview

Fixed schema external_file; superuser required.

Version

Build

You can build the RPM / DEB packages for external_file using pig build:

pig build pkg external_file         # build RPM / DEB packages

Install

You can install external_file directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install external_file;          # Install for current active PG version

pig ext install -y external_file -v 18  # PG 18
pig ext install -y external_file -v 17  # PG 17
pig ext install -y external_file -v 16  # PG 16
pig ext install -y external_file -v 15  # PG 15
pig ext install -y external_file -v 14  # PG 14

dnf install -y external_file_18       # PG 18
dnf install -y external_file_17       # PG 17
dnf install -y external_file_16       # PG 16
dnf install -y external_file_15       # PG 15
dnf install -y external_file_14       # PG 14

apt install -y postgresql-18-external-file   # PG 18
apt install -y postgresql-17-external-file   # PG 17
apt install -y postgresql-16-external-file   # PG 16
apt install -y postgresql-15-external-file   # PG 15
apt install -y postgresql-14-external-file   # PG 14

Create Extension:

CREATE EXTENSION external_file;

Usage

Source: README, Release v1.2

external_file stores file locators as (directory, filename) pairs and accesses server-side files through PostgreSQL lo_* helpers rather than direct file reads.

Basic workflow

CREATE EXTENSION external_file;

INSERT INTO directories(directory_name, directory_path)
VALUES ('temporary', '/tmp/');

INSERT INTO directory_roles(directory_name, directory_role, directory_read, directory_write)
VALUES ('temporary', 'app_reader', true, false);

SELECT writeEfile('\x48656c6c6f0a', ('temporary', 'hello.txt'));
SELECT readEfile(efilename('temporary', 'hello.txt'));
SELECT copyEfile(('temporary', 'hello.txt'), ('temporary', 'hello-copy.txt'));

Core objects

• directories: maps an alias to an on-server directory path.
• directory_roles: grants read/write rights on that alias to roles.
• efilename(directory, filename): constructs an efile locator.
• readEfile(efile): reads the target file into bytea.
• writeEfile(bytea, efile): writes bytea to the target file.
• copyEfile(src, dest): copies one external file to another.
• getEfilePath(efile, need_read, need_write): resolves the full path and checks access.

Caveats

• Creating the extension requires a PostgreSQL superuser.
• Upstream creates all objects in the external_file schema by default.
• The PostgreSQL OS user still needs filesystem read/write permission on the target directory.
• Filenames must not contain / or \; access is intentionally mediated through the directory tables.