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

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

On Thu, May 16, 2019 at 6:43 PM Michael Paquier <michael@paquier.xyz> wrote:

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?

I plug the HDD in and it gets mounted automatically. I'm not entirely sure
about default options used by Debian (Gnome 3?) for mounting it. These
might be relevant:

$ mount -t fuseblk
/dev/sda1 on /media/omer/My Book type fuseblk
(rw,nosuid,nodev,relatime,user_id=0,group_id=0,default_permissions,allow_other,blksize=4096,uhelper=udisks2)

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.

I agree using exFAT on a linux system isn't ideal. I have an external HDD I
occasionally use with both Win7 and Debian hosts. I needed to store a few
TBs of data, so the available space in my HDD didn't suffice. I decided to
host it on external HDD. I had figured out support on linux for modifying
exFAT was way better than support on Windows for modifying ext4, hence I
had preferred going with exFAT for the external HDD format to begin with.

This database won't face production. I will be the only person submitting
queries. So, I hope performance degradation caused by using FUSE would be
acceptable in my case.

There are other things that are likely to not work either, eg
symlinks for tablespaces.

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?

Best,
Omer

On Thu, May 16, 2019 at 6:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

Yes, this is likely the case btw:

```
omer@omer:/media/omer/My Book/ris/postgres_data$ ls -al postgresql.conf
-rwxrwxrwx 1 omer omer 23902 May 16 11:06 postgresql.conf
omer@omer:/media/omer/My Book/ris/postgres_data$ chmod u=rwx,g=rx,o=
postgresql.conf
omer@omer:/media/omer/My Book/ris/postgres_data$ echo $?
0
omer@omer:/media/omer/My Book/ris/postgres_data$ ls -al postgresql.conf
-rwxrwxrwx 1 omer omer 23902 May 16 11:06 postgresql.conf
```

Best,
Omer

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

I wasn't actually complaining, but thanks anyway. Will try to do so.

Best,
Omer