Re: cannot access directory /pg_tblspc/*

Hi everybody,
I've got a problem starting a database under fedora core 5.
I've created a database 'test' in a custom tablespace /bdd/data (/bdd is
root of a partition -ext3-). i.e.:
#df
/dev/hdb1 14877060 4913288 9195868 35% /
/dev/hdb2 31738420 212100 29888092 1% /bdd
/dev/hdb5 30407804 176288 28661968 1% /bdd_backup

The problem is when I want to access my database 'test' once the system is
up, it says it cannot access directory /pg_tblspc/<xxx> where <xxx> is a
number.
This file links to /bdd/data (drwx------ 3 postgres postgres 4096 jun 16
19:17 data )

Other databases can be accessed without any problem, this only happens to
'test', which has a different tablespace.

This error can be solved by MANUALLY restarting the service 'postgresql'
('service postgresql restart').

I'd like to have it automatically launched at boot time, but I can't
figure why this error happens.

I'd really appreciate any help. Thank you.

Charlie <perezchar(at)gmail(dot)com> writes:
> The problem is when I want to access my database 'test' once the system is
> up, it says it cannot access directory /pg_tblspc/<xxx> where <xxx> is a
> number.

I suspect it says more than that. Please provide the complete, exact
error message.

> This error can be solved by MANUALLY restarting the service 'postgresql'
> ('service postgresql restart').

That seems pretty strange. Is it possible your boot sequence is trying
to launch the database before the /bdd volume is mounted? We've seen
some pretty strange bug reports that turned out to be due to the
postmaster connecting to a working directory that was then hidden by a
subsequent mount operation ...

regards, tom lane

On Fri, Jun 16, 2006 at 21:10:44 +0200,
Charlie <perezchar(at)gmail(dot)com> wrote:
> Hi everybody,
> I've got a problem starting a database under fedora core 5.
> I've created a database 'test' in a custom tablespace /bdd/data (/bdd is
> root of a partition -ext3-). i.e.:
> #df
> /dev/hdb1 14877060 4913288 9195868 35% /
> /dev/hdb2 31738420 212100 29888092 1% /bdd
> /dev/hdb5 30407804 176288 28661968 1% /bdd_backup
>
> The problem is when I want to access my database 'test' once the system is
> up, it says it cannot access directory /pg_tblspc/<xxx> where <xxx> is a
> number.
> This file links to /bdd/data (drwx------ 3 postgres postgres 4096 jun 16
> 19:17 data )

Are you running selinux in enforcement mode? If so it might help to know
what policy you are using.

the error says (translated from spanish):
"it wasn't possible to access directory <<pg_tblspc/16388/16389>>:
permission denied."

The directory is mounted when the service is called, as I have tested
inserting some 'ls' statements before postmaster is called.

I think this is more a problem of user permissions. any idea?

> Charlie <perezchar(at)gmail(dot)com> writes:
>> The problem is when I want to access my database 'test' once the system
>> is
>> up, it says it cannot access directory /pg_tblspc/<xxx> where <xxx> is a
>> number.
>
> I suspect it says more than that. Please provide the complete, exact
> error message.
>
>> This error can be solved by MANUALLY restarting the service 'postgresql'
>> ('service postgresql restart').
>
> That seems pretty strange. Is it possible your boot sequence is trying
> to launch the database before the /bdd volume is mounted? We've seen
> some pretty strange bug reports that turned out to be due to the
> postmaster connecting to a working directory that was then hidden by a
> subsequent mount operation ...
>
> regards, tom lane

Thanks a lot, I disabled security for postgresql service and it's running
perfectly now.
Will you please tell me what was selinux doing that prevented it from
working properly? Thanx a lot again!

> On Fri, Jun 16, 2006 at 21:10:44 +0200,
> Charlie <perezchar(at)gmail(dot)com> wrote:
>> Hi everybody,
>> I've got a problem starting a database under fedora core 5.
>> I've created a database 'test' in a custom tablespace /bdd/data (/bdd is
>> root of a partition -ext3-). i.e.:
>> #df
>> /dev/hdb1 14877060 4913288 9195868 35% /
>> /dev/hdb2 31738420 212100 29888092 1% /bdd
>> /dev/hdb5 30407804 176288 28661968 1% /bdd_backup
>>
>> The problem is when I want to access my database 'test' once the system
>> is
>> up, it says it cannot access directory /pg_tblspc/<xxx> where <xxx> is a
>> number.
>> This file links to /bdd/data (drwx------ 3 postgres postgres 4096 jun
>> 16
>> 19:17 data )
>
> Are you running selinux in enforcement mode? If so it might help to know
> what policy you are using.

Charlie <perezchar(at)gmail(dot)com> writes:
> Thanks a lot, I disabled security for postgresql service and it's running
> perfectly now.
> Will you please tell me what was selinux doing that prevented it from
> working properly? Thanx a lot again!

OK, the problem is probably that SELinux tries to prevent daemon
processes from accessing parts of the filesystem that they're not
supposed to access --- this is so that if someone manages to break into
that service process, the amount of damage they can do using it is
limited.

In the case of postgres, the standard selinux policy says that only
stuff under /var/lib/pgsql should be accessed during normal operation.
If you want to have a custom tablespace somewhere else, you need to add
that tablespace's directory to the selinux policy entry for postgres.
Messing with selinux policy entries is not something I've ever had to do,
but I think it's not hard if you read the documentation.

The reason it worked after manually restarting the postmaster is
probably that a process launched from a terminal window is not
considered a system daemon and so selinux uses a weaker policy for it.
Did you use /sbin/service to restart the postmaster, or did you do a
manual "pg_ctl start" or some such? If you used /sbin/service I
would've expected the system-daemon policy to apply still ...

regards, tom lane