Обсуждение: `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.
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 filesystem namespace that blocks access to /home .