pg_read_file() is a system function for reading the contents of a text file on the local filesystem.
pg_read_file() was added in PostgreSQL 8.1.
pg_read_file() can be used to return the contents of any text file on the local filesystem to which the
postgres system user has access.
pg_read_file() is restricted to superusers, but other users can be granted the
EXECUTE permission to run this function.
pg_read_file() is implemented in src/backend/utils/adt/genfile.c.
Read the PostgreSQL instance's
postgres=# SELECT pg_read_file(current_setting('data_directory') || '/postmaster.pid'); pg_read_file --------------------- 3568221 + /var/lib/pgsql/data+ 1608776039 + 5432 + /tmp + * + 113475654 37224658+ ready + (1 row)
Attempt to read a non-existent file:
postgres=# SELECT pg_read_file('/foo/bar.txt'); ERROR: could not open file "/foo/bar.txt" for reading: No such file or directory
Attempt to read a file for which permissions are not available:
postgres=# SELECT pg_read_file('/root/password.txt'); ERROR: could not open file "/root/password.txt" for reading: Permission denied
Attempting to read a binary file will probably result in an encoding error of some kind:
postgres=# SELECT pg_read_file(current_setting('data_directory') || '/global/pg_control'); ERROR: invalid byte sequence for encoding "UTF8": 0xb9