Thread: BUG #15810: Using custom directory on external HDD gives permission error

The following bug has been logged on the website:

Bug reference:      15810
Logged by:          Omer Ozarslan
Email address:      omerfaruko@gmail.com
PostgreSQL version: 11.3
Operating system:   Debian 10 (buster)
Description:

Dear developers,

I have an external HDD formatted as exFAT. I am trying to create a postgres
database on it. It doesn't allow me to start database on that directory due
to a permission error.

Steps to reproduce:
```
omer@omer:~$ psql --version
psql (PostgreSQL) 11.3 (Debian 11.3-1)
omer@omer:~$ lsblk -no mountpoint,fstype /dev/sda1
/media/omer/My Book exfat
omer@omer:~$ /usr/lib/postgresql/11/bin/initdb /media/omer/My\
Book/ris/postgres_data
The files belonging to this database system will be owned by user "omer".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /media/omer/My Book/ris/postgres_data ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... 2019-05-16 11:06:01.342 CDT [29197] FATAL:
data directory "/media/omer/My Book/ris/postgres_data" has invalid
permissions
2019-05-16 11:06:01.342 CDT [29197] DETAIL:  Permissions should be u=rwx
(0700) or u=rwx,g=rx (0750).
child process exited with exit code 1
initdb: removing data directory "/media/omer/My Book/ris/postgres_data"
omer@omer:~$ /usr/lib/postgresql/11/bin/initdb -n /media/omer/My\
Book/ris/postgres_data
Running in no-clean mode.  Mistakes will not be cleaned up.
The files belonging to this database system will be owned by user "omer".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /media/omer/My Book/ris/postgres_data ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers ... 400kB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... 2019-05-16 11:06:11.135 CDT [29250] FATAL:
data directory "/media/omer/My Book/ris/postgres_data" has invalid
permissions
2019-05-16 11:06:11.135 CDT [29250] DETAIL:  Permissions should be u=rwx
(0700) or u=rwx,g=rx (0750).
child process exited with exit code 1
initdb: data directory "/media/omer/My Book/ris/postgres_data" not removed
at user's request
omer@omer:~$ /usr/lib/postgresql/11/bin/pg_ctl -D /media/omer/My\
Book/ris/postgres_data start
waiting for server to start....2019-05-16 11:06:33.811 CDT [29255] FATAL:
data directory "/media/omer/My Book/ris/postgres_data" has invalid
permissions
2019-05-16 11:06:33.811 CDT [29255] DETAIL:  Permissions should be u=rwx
(0700) or u=rwx,g=rx (0750).
 stopped waiting
pg_ctl: could not start server
Examine the log output.
omer@omer:~$ ls -al /media/omer/My\ Book/ris/postgres_data
total 24576
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 .
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 ..
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 base
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 global
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_commit_ts
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_dynshmem
-rwxrwxrwx 1 omer omer    4513 May 16 11:06 pg_hba.conf
-rwxrwxrwx 1 omer omer    1636 May 16 11:06 pg_ident.conf
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_logical
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_multixact
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_notify
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_replslot
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_serial
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_snapshots
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_stat
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_stat_tmp
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_subtrans
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_tblspc
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_twophase
-rwxrwxrwx 1 omer omer       3 May 16 11:06 PG_VERSION
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_wal
drwxrwxrwx 1 omer omer 1048576 May 16 11:06 pg_xact
-rwxrwxrwx 1 omer omer      88 May 16 11:06 postgresql.auto.conf
-rwxrwxrwx 1 omer omer   23902 May 16 11:06 postgresql.conf
```

Here is where I found this error on source code:

https://github.com/postgres/postgres/blob/4b1fcb43d070ba8b34ea01d5a657630d76e4b33f/src/backend/utils/init/miscinit.c#L161

Is there some way to circumvent this check without changing format of the
HDD?

Thanks,
Omer

On Thu, May 16, 2019 at 04:15:39PM +0000, PG Bug reporting form wrote:
> Is there some way to circumvent this check without changing format
> of the HDD?

That's weird.  If the data folder fed to initdb already exists and is
empty, then permissions on the path are enforced to 0700 or 0750 (if
using --allow-group-access) automatically to prevent this error when
starting Postgres for bootstrapping.  If the path does not exist, then
the data folder is created with proper permissions.  In any case, you
should not see this error.

Are you using some specific mounting options?
--
Michael

Michael Paquier <michael@paquier.xyz> writes:
> On Thu, May 16, 2019 at 04:15:39PM +0000, PG Bug reporting form wrote:
>> Is there some way to circumvent this check without changing format
>> of the HDD?

> That's weird.  If the data folder fed to initdb already exists and is
> empty, then permissions on the path are enforced to 0700 or 0750 (if
> using --allow-group-access) automatically to prevent this error when
> starting Postgres for bootstrapping.

I think we do a chmod but we don't then check to see if stat reports
the correct permissions.  IIUC, the reporter is using a filesystem
that lacks permissions altogether and is just ignoring the chmod.
So later checks fail.

I'm kind of disinclined to provide a way to skip the permissions
check though, especially in view of the fact that there's no good
reason to use a Windows filesystem as native storage for Unix.
There are other things that are likely to not work either, eg
symlinks for tablespaces.

            regards, tom lane

Omer Ozarslan <omerfaruko@gmail.com> writes:
> On Thu, May 16, 2019 at 6:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm kind of disinclined to provide a way to skip the permissions
>> check though, especially in view of the fact that there's no good
>> reason to use a Windows filesystem as native storage for Unix.

> Would it be possible to provide a way to skip permission check and then let
> it fail in case user needs to use these specific features? I don't expect
> to use tablespaces for example. Does lack of these permissions (or other
> unix fs features like symlinks) bring some limitations to the point the
> database cannot function at all?

We consider it insecure (because it is) and we won't support it.
This is not very different from the complaints we occasionally get
about Postgres not being willing to run as root.  That is also a bad
idea, and we won't support that either, regardless of any argument
that might be made that it-makes-sense-in-my-niche-use-case.

In both cases, it's not that hard to hack the check out of the
source code if you are bound and determined to do so.

            regards, tom lane