`pg_ls_dir` can query some directories, but not others
Copying here a question I asked on StackOverflow:
https://stackoverflow.com/questions/58846076
=======================================
On my system, `/home` and `/etc` have exactly the same permissions:
```
$ ls -ld /home /etc
drwxr-xr-x 67 root root 4096 Nov 13 15:59 /etc
drwxr-xr-x 3 root root 4096 Oct 18 13:45 /home
```
However, Postgres can read one, but not the other:
```
test=# select count(*) from (select pg_ls_dir('/etc')) a;
count
-------
149
(1 row)
test=# select count(*) from (select pg_ls_dir('/home')) a;
ERROR: could not open directory "/home": Permission denied
```
Even though the user the DB is running as can, in fact, run `ls /home`:
```
$ sudo -u postgres ls /home > /dev/null && echo "ls succeeded"
ls succeeded
```
What is going on?
My postgres version is 11.5, running on Arch Linux.
Import Notes
Reply to msg id not found: 52753F90-45C7-4BE5-BF0A-BEB02B74628F@umanwizard.comReference msg id not found: 52753F90-45C7-4BE5-BF0A-BEB02B74628F@umanwizard.com
On 11/13/19 2:32 PM, Brennan Vincent wrote:
Copying here a question I asked on StackOverflow:
https://stackoverflow.com/questions/58846076=======================================
On my system, `/home` and `/etc` have exactly the same permissions:
```
$ ls -ld /home /etc
drwxr-xr-x 67 root root 4096 Nov 13 15:59 /etc
drwxr-xr-x 3 root root 4096 Oct 18 13:45 /home
```However, Postgres can read one, but not the other:
```
test=# select count(*) from (select pg_ls_dir('/etc')) a;
count
-------
149
(1 row)test=# select count(*) from (select pg_ls_dir('/home')) a;
ERROR: could not open directory "/home": Permission denied
```Even though the user the DB is running as can, in fact, run `ls /home`:
```
$ sudo -u postgres ls /home > /dev/null && echo "ls succeeded"
ls succeeded
```What is going on?
Works here(Postgres 11.5, openSuSE Leap 15):
drwxr-xr-x 149 root root 12288 Nov 13 15:24 etc/
drwxr-xr-x 4 root root 4096 Jun 7 2018 home/
production_(postgres)# select count(*) from (select pg_ls_dir('/etc')) a;
count
-------
339
(1 row)
production_(postgres)# select count(*) from (select pg_ls_dir('/home')) a;
count
-------
2
(1 row)
SELinux (or equivalent) in play?
My postgres version is 11.5, running on Arch Linux.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Nov 13, 2019, at 6:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/13/19 2:32 PM, Brennan Vincent wrote:
Copying here a question I asked on StackOverflow:
https://stackoverflow.com/questions/58846076
=======================================
On my system, `/home` and `/etc` have exactly the same permissions:
```
$ ls -ld /home /etc
drwxr-xr-x 67 root root 4096 Nov 13 15:59 /etc
drwxr-xr-x 3 root root 4096 Oct 18 13:45 /home
```
However, Postgres can read one, but not the other:
```
test=# select count(*) from (select pg_ls_dir('/etc')) a;
count
-------
149
(1 row)
test=# select count(*) from (select pg_ls_dir('/home')) a;
ERROR: could not open directory "/home": Permission denied
```
Even though the user the DB is running as can, in fact, run `ls /home`:
```
$ sudo -u postgres ls /home > /dev/null && echo "ls succeeded"
ls succeeded
```
What is going on?Works here(Postgres 11.5, openSuSE Leap 15):
drwxr-xr-x 149 root root 12288 Nov 13 15:24 etc/
drwxr-xr-x 4 root root 4096 Jun 7 2018 home/production_(postgres)# select count(*) from (select pg_ls_dir('/etc')) a;
count
-------
339
(1 row)production_(postgres)# select count(*) from (select pg_ls_dir('/home')) a;
count
-------
2
(1 row)SELinux (or equivalent) in play?
My postgres version is 11.5, running on Arch Linux.
--
Adrian Klaver
adrian.klaver@aklaver.com
Mystery solved: Arch’s bundled systemd service file for postgresql sets `ProtectHome=true`, which runs the daemon in a file system namespace that blocks access to /home .