Thread: why not using a mountpoint as PGDATA?

why not using a mountpoint as PGDATA?

From
Luca Ferrari
Date:
I apparently cannot find an answer in the documentation, however initdb states:

initdb: directory "/mnt/data1" exists but is not empty
It contains a dot-prefixed/invisible file, perhaps due to it being a
mount point.
Using a mount point directly as the data directory is not recommended.
Create a subdirectory under the mount point.

and effectively /mnt/data1 is a mountpoint, therefore but belongs to
the user of the cluster, therefore the only difference I can see is
having '..' belonging to root at the mountpoint or the cluster user
within a subdirectory.
What's wrong with using a mountpoint?

Thanks,
Luca


Re: why not using a mountpoint as PGDATA?

From
Julien Rouhaud
Date:
On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari <fluca1978@gmail.com> wrote:
>
> What's wrong with using a mountpoint?

You can see most obvious reasons at
https://bugzilla.redhat.com/show_bug.cgi?id=1247477


Re: why not using a mountpoint as PGDATA?

From
Luca Ferrari
Date:
On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> You can see most obvious reasons at
> https://bugzilla.redhat.com/show_bug.cgi?id=1247477

Thanks, I didn't see the lost+found problem because I'm on UFS (I'm
wondering if this applies also to ZFS datasetes with mount point).
Same story for the accidentally umount, since FreeBSD seems enough
friendly to avoid umount while the database is running, but it could
definetely happen with some brute force or on another operating
system.
I see the upgrade problem being a lot more general in this sense.
However, all are good points.

Luca


Re: why not using a mountpoint as PGDATA?

From
"Peter J. Holzer"
Date:
On 2019-02-27 12:33:02 +0100, Julien Rouhaud wrote:
> On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari <fluca1978@gmail.com> wrote:
> >
> > What's wrong with using a mountpoint?
>
> You can see most obvious reasons at
> https://bugzilla.redhat.com/show_bug.cgi?id=1247477

I see only one good reason there: The fact that pg_upgrade needs write
access to the parent directory. Of course that alone might suffice.

The other reasons aren't good IMHO.

The first one (initdb checks for an empty directory) is more "We
disallow it, therefore it is a bad idea" than a reason for disallowing
it.

The second is just wrong: You can have a non-root owned mount-point on
any Unixoid system I've worked with. (And I don't see why that would be
a security problem)

The third is wrong at least on Debian: All server processes have
/var/lib/postgresql/$version/$cluster as their working directory, so it
cannot be unmounted while the database is up. Even if you could, the
server would either immediately lose access to all files (in which case
you could recover) or it would keep access to all files (so, not a
problem). Plus being in a subdirectory wouldn't change that. Maybe it's
a potential problem with other layouts.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Attachment

Re: why not using a mountpoint as PGDATA?

From
Tom Lane
Date:
Luca Ferrari <fluca1978@gmail.com> writes:
> On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>> You can see most obvious reasons at
>> https://bugzilla.redhat.com/show_bug.cgi?id=1247477

> Thanks, I didn't see the lost+found problem because I'm on UFS (I'm
> wondering if this applies also to ZFS datasetes with mount point).
> Same story for the accidentally umount, since FreeBSD seems enough
> friendly to avoid umount while the database is running, but it could
> definetely happen with some brute force or on another operating
> system.

The case that I can recall most clearly was actually in the other
direction: during system bootup, some NFS volume that was being abused
this way (mount point == data dir) was slow to mount.  Compounding the
problem, postgres was being started through some init script that would
helpfully run initdb if it saw the specified data directory was empty.
So, rather than failing like a properly paranoid DBA would wish, it
ran initdb and then started the postmaster.  A bit later, the NFS volume
came online, replacing the mount-point directory, and now the postmaster
had a devil's brew of open files on the local volume containing the mount
point and open files on the NFS volume.  It didn't take long for that to
translate into hopeless catalog corruption.  I don't think they ever got
their data back.

The true value of data-dir-at-least-one-level-down is so that it will
fail to be there at all if the file system isn't mounted.  You need that,
and you do *not* want automated attempts to "repair" that.

            regards, tom lane


Re: why not using a mountpoint as PGDATA?

From
"Peter J. Holzer"
Date:
On 2019-02-27 10:42:12 -0500, Tom Lane wrote:
> Luca Ferrari <fluca1978@gmail.com> writes:
> > On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >> You can see most obvious reasons at
> >> https://bugzilla.redhat.com/show_bug.cgi?id=1247477
[...]
> The case that I can recall most clearly was actually in the other
> direction: during system bootup, some NFS volume that was being abused
> this way (mount point == data dir) was slow to mount.  Compounding the
> problem, postgres was being started through some init script that would
> helpfully run initdb if it saw the specified data directory was empty.
> So, rather than failing like a properly paranoid DBA would wish, it
> ran initdb and then started the postmaster.

Ouch.

I wonder though why that directory was writable by the postgres user.
But maybe the helpful start script chown'ed it to fix the "wrong"
permissions.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Attachment

Re: why not using a mountpoint as PGDATA?

From
Joe Conway
Date:
On 2/27/19 11:49 AM, Peter J. Holzer wrote:
> On 2019-02-27 10:42:12 -0500, Tom Lane wrote:
>> Luca Ferrari <fluca1978@gmail.com> writes:
>> > On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>> >> You can see most obvious reasons at
>> >> https://bugzilla.redhat.com/show_bug.cgi?id=1247477
> [...]
>> The case that I can recall most clearly was actually in the other
>> direction: during system bootup, some NFS volume that was being abused
>> this way (mount point == data dir) was slow to mount.  Compounding the
>> problem, postgres was being started through some init script that would
>> helpfully run initdb if it saw the specified data directory was empty.
>> So, rather than failing like a properly paranoid DBA would wish, it
>> ran initdb and then started the postmaster.
>
> Ouch.
>
> I wonder though why that directory was writable by the postgres user.
> But maybe the helpful start script chown'ed it to fix the "wrong"
> permissions.

FWIW, if you want to read the whole gory details of that incident, here
it is:

https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


Attachment

Re: why not using a mountpoint as PGDATA?

From
Ron
Date:
On 2/27/19 12:43 PM, Joe Conway wrote:
> On 2/27/19 11:49 AM, Peter J. Holzer wrote:
>> On 2019-02-27 10:42:12 -0500, Tom Lane wrote:
>>> Luca Ferrari <fluca1978@gmail.com> writes:
>>>> On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>>>>> You can see most obvious reasons at
>>>>> https://bugzilla.redhat.com/show_bug.cgi?id=1247477
>> [...]
>>> The case that I can recall most clearly was actually in the other
>>> direction: during system bootup, some NFS volume that was being abused
>>> this way (mount point == data dir) was slow to mount.  Compounding the
>>> problem, postgres was being started through some init script that would
>>> helpfully run initdb if it saw the specified data directory was empty.
>>> So, rather than failing like a properly paranoid DBA would wish, it
>>> ran initdb and then started the postmaster.
>> Ouch.
>>
>> I wonder though why that directory was writable by the postgres user.
>> But maybe the helpful start script chown'ed it to fix the "wrong"
>> permissions.
> FWIW, if you want to read the whole gory details of that incident, here
> it is:
>
> https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba

What in the world was that SuSE maintainer -- and the people who should have 
been looking over his shoulder -- thinking???


-- 
Angular momentum makes the world go 'round.


Re: why not using a mountpoint as PGDATA?

From
Tom Lane
Date:
Ron <ronljohnsonjr@gmail.com> writes:
> On 2/27/19 12:43 PM, Joe Conway wrote:
>> FWIW, if you want to read the whole gory details of that incident, here
>> it is:
>> https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba

> What in the world was that SuSE maintainer -- and the people who should have
> been looking over his shoulder -- thinking???

Well, I wouldn't blame them *that* much.  Up till that time, I don't think
anyone fully understood the hazards of auto-initdb in a startup script
(certainly none of the PG developers did).  But this has been in our
folklore long enough now that anybody who's still doing that definitely
should get yelled at ...

            regards, tom lane


Re: why not using a mountpoint as PGDATA?

From
raf@raf.org
Date:
Peter J. Holzer wrote:

> On 2019-02-27 12:33:02 +0100, Julien Rouhaud wrote:
> > On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari <fluca1978@gmail.com> wrote:
> > >
> > > What's wrong with using a mountpoint?
> > 
> > You can see most obvious reasons at
> > https://bugzilla.redhat.com/show_bug.cgi?id=1247477
> 
> I see only one good reason there: The fact that pg_upgrade needs write
> access to the parent directory. Of course that alone might suffice.
> 
> The other reasons aren't good IMHO.
> 
> The first one (initdb checks for an empty directory) is more "We
> disallow it, therefore it is a bad idea" than a reason for disallowing
> it.
> 
> The second is just wrong: You can have a non-root owned mount-point on
> any Unixoid system I've worked with. (And I don't see why that would be
> a security problem)
> 
> The third is wrong at least on Debian: All server processes have
> /var/lib/postgresql/$version/$cluster as their working directory, so it
> cannot be unmounted while the database is up. Even if you could, the
> server would either immediately lose access to all files (in which case
> you could recover) or it would keep access to all files (so, not a
> problem). Plus being in a subdirectory wouldn't change that. Maybe it's
> a potential problem with other layouts.
> 
>         hp
> 
> -- 
>    _  | Peter J. Holzer    | we build much bigger, better disasters now
> |_|_) |                    | because we have much more sophisticated
> | |   | hjp@hjp.at         | management tools.
> __/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

I didn't know you weren't supposed to do that.
I've been using ecryptfs mountpoints for my data
directories for ages without problem. Ecryptfs doesn't
create lost+found and the underlying directory is owned
by the postgres user and so the mountpoint is as well
(even though it is mounted by root).

However, the parent directory is root-owned so I guess
pg_upgrade wouldn't work. But I've never used
pg_upgrade so I've never encountered that problem.

Also, if it's not mounted, the underlying directory
isn't empty but postgresql can tell something's not
right and it won't start if told to do so prematurely.

But the next time I upgrade, I might put the data
directory in a sub-directory of the mountpoint in case
I ever want to start using pg_upgrade. It would also
mean I only need a single ecryptfs mountpoint. At the
moment, I create a new one for each major upgrade which
seems silly.

cheers,
raf