Re: `pg_ls_dir` can query some directories, but not others

> On Nov 13, 2019, at 6:33 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)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 .