Re: Postgresql 8.0.3, symbolic link to /var/lib/pgsql/data/base

I'm trying to move my databases to another drive.

With postmaster off, I create:

/home/pgsql

mv /var/lib/pgsql/data/base /home/pgsql/

I make sure that /home/pgsql is owned by postgres, and only 'rwx' for
the owner. All the permissions look fine.

from /var/lib/pgsql/data, I do

ln -s /home/pgsql/base

Of course the link permissions look like:

lrwxrwxrwx 1 postgres postgres 16 Sep 28 14:21 base -> /home/pgsql/base

The postmaster starts, but attempts to access the database complain that
permission is denied:

$ psql -l
psql: FATAL: could not access directory "/var/lib/pgsql/data/base/1":
Permission denied

Any clues about why this won't work?
I've searched the archives, but most references were to invalid
permissions on the directories and I'm pretty sure I've got those right.

All my pgsql dirs look pretty much like:
drwx------ 3 postgres postgres 4096 Sep 28 14:21 pgsql

Both in the original /var/lib/pgsql area, and the /home/pgsql area.

Hi Jeffrey

Jeffrey Tenny wrote:

> I'm trying to move my databases to another drive.
>
> With postmaster off, I create:
>
> /home/pgsql
>
> mv /var/lib/pgsql/data/base /home/pgsql/
>
>
> I make sure that /home/pgsql is owned by postgres, and only 'rwx' for
> the owner. All the permissions look fine.
>
> from /var/lib/pgsql/data, I do
>
> ln -s /home/pgsql/base
>
> Of course the link permissions look like:
>
> lrwxrwxrwx 1 postgres postgres 16 Sep 28 14:21 base -> /home/pgsql/base
>
>
> The postmaster starts, but attempts to access the database complain
> that permission is denied:
>
> $ psql -l
> psql: FATAL: could not access directory "/var/lib/pgsql/data/base/1":
> Permission denied
>
>
> Any clues about why this won't work?
> I've searched the archives, but most references were to invalid
> permissions on the directories and I'm pretty sure I've got those right.
>
> All my pgsql dirs look pretty much like:
> drwx------ 3 postgres postgres 4096 Sep 28 14:21 pgsql
>
> Both in the original /var/lib/pgsql area, and the /home/pgsql area.
>

Try to issue chown -R postgres.postgres /home/pgsql

Sergiusz

Jeffrey Tenny <jeffrey(dot)tenny(at)comcast(dot)net> writes:
> I'm trying to move my databases to another drive.

On what platform?

One theory that fits the available facts is that you're running on a
machine with SELinux enabled; the usual selinux policy forbids the
postmaster from accessing files outside /var/lib/pgsql.

regards, tom lane

The platform is Redhat 9, so no SELinux.

Tom Lane wrote:
> Jeffrey Tenny <jeffrey(dot)tenny(at)comcast(dot)net> writes:
>
>>I'm trying to move my databases to another drive.
>
>
> On what platform?
>
> One theory that fits the available facts is that you're running on a
> machine with SELinux enabled; the usual selinux policy forbids the
> postmaster from accessing files outside /var/lib/pgsql.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Sorry, I take that back. The platform is Fedora Core 4, so SELinux may
be a concern, though during the installation I selected all the minimal
options for SELinux because I wasn't prepared to deal with it.

Apologies for the previous incorrect answer, I was typing it on a Redhat
9 machine :-)

Jeffrey Tenny wrote:
> The platform is Redhat 9, so no SELinux.
>
> Tom Lane wrote:
>
>> Jeffrey Tenny <jeffrey(dot)tenny(at)comcast(dot)net> writes:
>>
>>> I'm trying to move my databases to another drive.
>>
>>
>>
>> On what platform?
>>
>> One theory that fits the available facts is that you're running on a
>> machine with SELinux enabled; the usual selinux policy forbids the
>> postmaster from accessing files outside /var/lib/pgsql.
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>

Sergiusz Jarczyk wrote:
>
> Try to issue chown -R postgres.postgres /home/pgsql
>

I checked the ownership, and it was already ok because aside from
the 'root' creation of the alternate directory location and assigning it
the correct ownership and permissions, I did the 'mv' of the old
directory to the new location logged in as 'postgres'.

Ditto the symlink creation (did it as 'postgres').

Hi again
Have you tried to run the postmaster with the new location by hand, i.e.:
postmaster -i -D /home/pgsql

Sergiusz

I've been focusing on the potentially simpler case of just relocating
the 'base' directory, so that I don't have to worry about whether
postmaster is finding my config files and such.

I was hoping the symlink would be a pretty transparent operation,
and it doesn't seem that other people have had difficulty doing similar
th ings.

Sergiusz Jarczyk wrote:
> Hi again
> Have you tried to run the postmaster with the new location by hand, i.e.:
> postmaster -i -D /home/pgsql
>
> Sergiusz
>
>

Jeffrey Tenny <jeffrey(dot)tenny(at)comcast(dot)net> writes:
> Sorry, I take that back. The platform is Fedora Core 4, so SELinux may
> be a concern, though during the installation I selected all the minimal
> options for SELinux because I wasn't prepared to deal with it.

Better check what /usr/sbin/getenforce tells you, then... or look in
/var/log/messages to see if the postmaster's operations are being
refused.

regards, tom lane

Tom Lane wrote:
> Jeffrey Tenny <jeffrey(dot)tenny(at)comcast(dot)net> writes:
>
>>Sorry, I take that back. The platform is Fedora Core 4, so SELinux may
>>be a concern, though during the installation I selected all the minimal
>>options for SELinux because I wasn't prepared to deal with it.
>
>
> Better check what /usr/sbin/getenforce tells you, then... or look in
> /var/log/messages to see if the postmaster's operations are being
> refused.
>

Interesting. getenforce says "Enforcing".
There are no suspicious messages in /var/log/messages or any other log I
can find there.

Here's the /var/lib/pgsql/data directory ('.')

-bash-3.00$ ls -ldZ
drwx------ postgres postgres system_u:object_r:postgresql_db_t .

Here are the children:

-bash-3.00$ ls -ldZ *
lrwxrwxrwx postgres postgres user_u:object_r:postgresql_db_t base ->
/home/pgsql/base
drwx------ postgres postgres root:object_r:postgresql_db_t global
drwx------ postgres postgres root:object_r:postgresql_db_t pg_clog
-rw------- postgres postgres user_u:object_r:postgresql_db_t pg_hba.conf
-rw------- postgres postgres user_u:object_r:postgresql_db_t pg_hba.conf~
-rw------- postgres postgres root:object_r:postgresql_db_t pg_ident.conf
drwx------ postgres postgres root:object_r:postgresql_db_t pg_log
drwx------ postgres postgres root:object_r:postgresql_db_t pg_subtrans
drwx------ postgres postgres root:object_r:postgresql_db_t pg_tblspc
-rw------- postgres postgres root:object_r:postgresql_db_t PG_VERSION
drwx------ postgres postgres root:object_r:postgresql_db_t pg_xlog
-rw------- postgres postgres user_u:object_r:postgresql_db_t
postgresql.conf
-rw------- postgres postgres user_u:object_r:postgresql_db_t
postgresql.conf~
-rw------- postgres postgres root:object_r:postgresql_db_t
postmaster.opts

-bash-3.00$ ls -ldZ /home/pgsql
drwx------ postgres postgres root:object_r:user_home_dir_t /home/pgsql
-bash-3.00$ ls -ldZ /home/pgsql/base
drwx------ postgres postgres root:object_r:postgresql_db_t
/home/pgsql/base
-bash-3.00$ ls -ldZ /home/pgsql/base/*
drwx------ postgres postgres root:object_r:postgresql_db_t
/home/pgsql/base/1
drwx------ postgres postgres root:object_r:postgresql_db_t
/home/pgsql/base/17229
drwx------ postgres postgres system_u:object_r:postgresql_db_t
/home/pgsql/base/17230
drwx------ postgres postgres system_u:object_r:postgresql_db_t
/home/pgsql/base/29144
drwx------ postgres postgres system_u:object_r:postgresql_db_t
/home/pgsql/base/29149

Somewhere in here is probably the clue, and a 'chcon' is probably
necessary. I'm clueless about selinux though and have no idea what to do.

Tom Lane wrote:
> Jeffrey Tenny <jeffrey(dot)tenny(at)comcast(dot)net> writes:
>
>>Sorry, I take that back. The platform is Fedora Core 4, so SELinux may
>>be a concern, though during the installation I selected all the minimal
>>options for SELinux because I wasn't prepared to deal with it.
>
>
> Better check what /usr/sbin/getenforce tells you, then... or look in
> /var/log/messages to see if the postmaster's operations are being
> refused.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

I tried a few:

chcon -t postgresql_db_t /home/pgsql

type things. No luck.

It's interesting that there are no permission denials
logged in /var/log/messages, but postgresql
says it can't access /var/lib/pgsql/data/base
because permission is denied (in the postgres log).

I played with the symlink chcon user, and the /home/pgsql user
as well (again, chcon, not chown).

It's clearly selinux though. If I do this:

/usr/sbin/setenforce 0

Then everything works peachy for my testing.

But turning off selinux is probably a one way road,
since all files created in that environment won't have their
context set. So I really need to solve this in the correct fashion.

For now I'll run with the database files under /var/lib/pgsql/data
until I (or someone else) can figure out the selinux/postgresql magic.

Jeffrey Tenny <jeffrey(dot)tenny(at)comcast(dot)net> writes:
> It's clearly selinux though. If I do this:
> /usr/sbin/setenforce 0
> Then everything works peachy for my testing.

OK, that's pretty conclusive as to the source of the issue.

> It's interesting that there are no permission denials
> logged in /var/log/messages, but postgresql
> says it can't access /var/lib/pgsql/data/base
> because permission is denied (in the postgres log).

I've run into this more than a few times myself. What I've been told
when I complained about it is "that's normal because selinux log
messages are rate-limited to avoid inflating the log files too much".
Which is plausible in itself, but rate-limiting to zero is not helpful.
Feel free to file another gripe in Red Hat's bugzilla.

regards, tom lane