Using PostgreSQL’s adminpack extension to write files

In PostgreSQL you have several ways of working with files and you can find quite some examples here on our blog, e.g. Modifying pg_hba.conf from inside PostgreSQL, Can I do it with PostgreSQL? – 4 – External tables or Working with files on the filesystem in PostgreSQL. There is also copy which allows you to load or unload tables and there is the adminpack extension which is the topic of this post.

As adminpack comes as an extension it obviously needs to be added to the database you want to use it in:

postgres=# dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# create extension adminpack;
CREATE EXTENSION
postgres=# dx
                        List of installed extensions
   Name    | Version |   Schema   |               Description               
-----------+---------+------------+-----------------------------------------
 adminpack | 2.1     | pg_catalog | administrative functions for PostgreSQL
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Basically adminpack adds theses functions for working with files:

• pg_file_write: writes or appends to files
• pg_file_sync: forces the flush of a given file (new in PostgreSQL 13)
• pg_file_rename: renames a file
• pg_file_unlink: deletes a file
• pg_logdir_ls : lists the log files in the log directory

To have something to work with let’s start by creating a simple file and add some text to it:

postgres=# select pg_file_write('/var/tmp/a.txt','test test', 'false');
 pg_file_write 
---------------
             9
(1 row)

The last parameter of the function specifies if you want to append to a file or not. Doing exactly the same again will give you an error as the file is already existing:

postgres=# select pg_file_write('/var/tmp/a.txt','test test', 'false');
ERROR:  file "/var/tmp/a.txt" exists

Looking at the content of the file it contains exactly what we just wrote (everything else would be very surprising anyway):

postgres=# select pg_read_file('/var/tmp/a.txt');
 pg_read_file 
--------------
 test test
(1 row)

Note that pg_read_file is not coming with the adminpack extension but is there by default. Adding additional content to the file is done using the same function but switching the append parameter to true:

postgres=# select pg_file_write('/var/tmp/a.txt','blubb blubb', 'true');
 pg_file_write 
---------------
            11
(1 row)

postgres=# select pg_read_file('/var/tmp/a.txt');
     pg_read_file     
----------------------
 test testblubb blubb
(1 row)

Renaming the file is easy as well:

postgres=# select pg_file_rename('/var/tmp/a.txt','/var/tmp/b.txt');
 pg_file_rename 
----------------
 t
(1 row)

postgres=# ! cat /var/tmp/b.txt
test testblubb blubbpostgres=# 

… and finally deleting when you’re done:

postgres=# select pg_file_unlink('/var/tmp/b.txt');
 pg_file_unlink 
----------------
 t
(1 row)

postgres=# ! ls /var/tmp/b.txt
ls: cannot access '/var/tmp/b.txt': No such file or directory
postgres=# 

All quite easy and convenient to use. But there are some security considerations. As I am connected as a superuser I can easily do all that stuff. But that also means I can potentially destroy my cluster:

postgres=# ! echo $PGDATA
/u02/pgdata/DEV
postgres=# ! ls /u02/pgdata/DEV
base              pg_commit_ts  pg_ident.conf  pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  pg_xact               postmaster.opts
current_logfiles  pg_dynshmem   pg_log         pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.auto.conf  postmaster.pid
global            pg_hba.conf   pg_logical     pg_replslot   pg_stat       pg_tblspc    pg_wal       postgresql.conf

postgres=# select pg_file_write('/u02/pgdata/DEV/postmaster.opts','do not do that',true);
 pg_file_write 
---------------
            14
(1 row)

postgres=# ! cat /u02/pgdata/DEV/postmaster.opts
/u01/app/postgres/product/DEV/db_1/bin/postgres "-D" "/u02/pgdata/DEV"
do not do that

In this case nothing really happens as that file is not super important but potentially you can write to all the files in $PGDATA and all the files in the operating system the postgres user has access to.

Standard users, however, do not have access to these functions by default:

postgres=# create user u with login password 'u';
CREATE ROLE
postgres=# c postgres u
You are now connected to database "postgres" as user "u".
postgres=> select pg_file_write('/var/tmp/c.txt','dummy',false);
ERROR:  permission denied for function pg_file_write

If you really trust a specific user you may grant access to these functions:

postgres=# c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant EXECUTE on function pg_file_write to u;
GRANT
postgres=# c postgres u
You are now connected to database "postgres" as user "u".
postgres=> ! pwd
/home/postgres
postgres=> select pg_file_write('a.txt','dummy',true);
 pg_file_write 
---------------
             5
(1 row)

postgres=> ! ls /u02/pgdata/DEV/a.txt
/u02/pgdata/DEV/a.txt
postgres=> 

If you do it like that the user can write files in $PGDATA only. Writing to another location in the operating system will not work:

postgres=> select pg_file_write('/var/tmp/a.txt','dummy',true);
ERROR:  absolute path not allowed
postgres=> select pg_file_write('../../../var/tmp/a.txt','dummy',true);
ERROR:  path must be in or below the current directory

If you want to have that as well for a specific user you need to give additional permissions:

postgres=> c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant pg_write_server_files to u;
GRANT ROLE
postgres=# c postgres u
You are now connected to database "postgres" as user "u".
postgres=> select pg_file_write('/var/tmp/a.txt','dummy',true);
 pg_file_write 
---------------
             5
(1 row)

But again, be careful with such powerful permissions.