pg_ls_dir()
pg_ls_dir()
is a system function for examining the contents of a directory on the local filesystem.
pg_ls_dir()
was added in PostgreSQL 8.1.
Usage
pg_ls_dir (dirname
text
[,missing_ok
boolean
,include_dot_dirs
boolean
] )
→ setoftext
pg_ls_dir()
can be used to return the contents of any directory on the local filesystem to which the postgres
system user has access.
It returns the file system objects unsorted, so is usually combined with ORDER BY
.
Beginning with PostgreSQL 9.5, pg_ls_dir()
can optionally return the special directories ".
" and "..
", and optionally not return an error if the specified directory is not found
Permissions
By default pg_ls_dir()
is restricted to superusers, but other users can be granted the EXECUTE
permission to run this function.
Note that unlike the other pg_ls_...()
functions, pg_ls_dir()
can be used to access directories anywhere on the filesystem, not just those used by PostgreSQL, and is therefore not available by default to members of the pg_monitor
default role.
Source code
pg_ls_dir()
is implemented in src/backend/utils/adt/genfile.c.
Change history
- PostgreSQL 9.5
- options
missing_ok
andinclude_dot_dirs
added (commit cb2acb10)
- options
- PostgreSQL 8.1
- added (commit b609695b)
Examples
Basic execution of pg_ls_dir()
:
postgres=# SELECT pg_ls_dir('/') ORDER BY 1 LIMIT 5; pg_ls_dir -------------- bin boot dev etc home (5 rows)
Include the special "dot" directories:
postgres=# SELECT pg_ls_dir(current_setting('data_directory') || '/pg_stat_tmp', FALSE, TRUE) ORDER BY 1; pg_ls_dir ----------------------- . .. db_0.stat db_13832.stat db_16385.stat global.stat pgss_query_texts.stat (7 rows)
Attempt to read a directory which might not exist:
postgres=# SELECT pg_ls_dir('/foo'); ERROR: could not open directory "/foo": No such file or directory
Safely attempt to read a directory which might not exist:
postgres=# SELECT pg_ls_dir('/foo', TRUE, FALSE); pg_ls_dir ----------- (0 rows)
Note that attempting to read a directory which is not accessible to the postgres
system user will always result in an error:
postgres=# SELECT pg_ls_dir('/root'); ERROR: could not open directory "/root": Permission denied postgres=# SELECT pg_ls_dir('/root', TRUE, FALSE); ERROR: could not open directory "/root": Permission denied
References
- PostgreSQL documentation: Generic File Access Functions