Обсуждение: Could not create a tablespace - permission denied

Hi all,

While attempting to create a tablespace as the postgres user under RHEL5
with *no* SELinux enabled, I get the following error:

postgres=# CREATE TABLESPACE fma LOCATION '/home/chandler/fma/db/pgsql';
ERROR:  could not set permissions on directory
"/home/chandler/fma/db/pgsql": Permission denied

The user postgres is able to access the directory, and the user postgres
is able to set permissions on the directory to 0700:

-bash-3.1$ chmod 700 /home/chandler/fma/db/pgsql
-bash-3.1$

Does anyone know what I should do to fix this?

Regards,
Graham
--

Graham Leggett <minfrin@sharp.fm> writes:
> While attempting to create a tablespace as the postgres user under RHEL5
> with *no* SELinux enabled, I get the following error:

> postgres=# CREATE TABLESPACE fma LOCATION '/home/chandler/fma/db/pgsql';
> ERROR:  could not set permissions on directory
> "/home/chandler/fma/db/pgsql": Permission denied

> The user postgres is able to access the directory, and the user postgres
> is able to set permissions on the directory to 0700:

> -bash-3.1$ chmod 700 /home/chandler/fma/db/pgsql

If you can do that from a shell running as postgres, then I think
selinux is not so disabled as you think.  Ordinary file permissions are
applied uniformly to all processes running as a given userid, but
selinux is different.

            regards, tom lane

Tom Lane wrote:

> If you can do that from a shell running as postgres, then I think
> selinux is not so disabled as you think.  Ordinary file permissions are
> applied uniformly to all processes running as a given userid, but
> selinux is different.

SELinux is definitely not present on this machine - it is not installed
at all.

At it turned out, the postgresql server had cached the system user
permissions, and it only started working after postgresql had been
restarted and the cached credentials had been replaced.

Regards,
Graham
--

Graham Leggett <minfrin@sharp.fm> writes:
> At it turned out, the postgresql server had cached the system user
> permissions, and it only started working after postgresql had been
> restarted and the cached credentials had been replaced.

I think this explanation is fiction ... there is no "cacheing of
credentials" in there that I know about.

Maybe the server had not been running under the userid you thought it
was?  But it would have to match the ownership of the data directory,
so it's hard to see how a simple restart would fix it.

[ thinks... ]  Hm, is the tablespace directory mounted over NFS?
I *have* heard of strange cacheing behaviors on NFS.  In fact,
NFS has enough weirdnesses that I wouldn't recommend running a
database over it ...

            regards, tom lane

Tom Lane wrote:

> I think this explanation is fiction ... there is no "cacheing of
> credentials" in there that I know about.
>
> Maybe the server had not been running under the userid you thought it
> was?  But it would have to match the ownership of the data directory,
> so it's hard to see how a simple restart would fix it.
>
> [ thinks... ]  Hm, is the tablespace directory mounted over NFS?
> I *have* heard of strange cacheing behaviors on NFS.  In fact,
> NFS has enough weirdnesses that I wouldn't recommend running a
> database over it ...

The server is a standard REHL5 installed copy of postgresql, running as
the postgres user (as is standard on RHE5). Both the data directory and
the tablespace are running on ext3 filesystems local to the machine,
there are no NFS mounts anywhere. The machine does not have SELinux
installed, never mind enabled.

After the postgres user was granted permission to access the tablespace
directory, and after it was verified that the postgres user was able to
access the tablespace directory, postgresql refused to allow the
tablespace to be created until the postgresql server was restarted.

Regards,
Graham
--

Graham Leggett wrote:

> After the postgres user was granted permission to access the tablespace
> directory, and after it was verified that the postgres user was able to
> access the tablespace directory, postgresql refused to allow the
> tablespace to be created until the postgresql server was restarted.

I am pretty sure you are missing something. I can not reproduce your issue:

jd@scratch:~$ mkdir /tmp/foo
jd@scratch:~$ psql -U postgres
postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'

postgres=# create tablespace foobar location '/tmp/foo';
ERROR:  could not set permissions on directory "/tmp/foo": Operation not
permitted
postgres=#
[1]+  Stopped                 psql -U postgres
jd@scratch:~$ sudo su -
[sudo] password for jd:
root@scratch:~# chown postgres:postgres /tmp/foo
root@scratch:~# exit
logout
jd@scratch:~$ fg
psql -U postgres

postgres=# create tablespace foobar location '/tmp/foo';
CREATE TABLESPACE
postgres=#

But I am glad your problem is resolved.

Joshua D. Drake

Graham Leggett <minfrin@sharp.fm> writes:
> Tom Lane wrote:
>> I think this explanation is fiction ... there is no "cacheing of
>> credentials" in there that I know about.

> The server is a standard REHL5 installed copy of postgresql, running as
> the postgres user (as is standard on RHE5). Both the data directory and
> the tablespace are running on ext3 filesystems local to the machine,
> there are no NFS mounts anywhere. The machine does not have SELinux
> installed, never mind enabled.

> After the postgres user was granted permission to access the tablespace
> directory, and after it was verified that the postgres user was able to
> access the tablespace directory, postgresql refused to allow the
> tablespace to be created until the postgresql server was restarted.

Fascinating.  I think what you're describing must be a kernel bug ---
want to see if you can reproduce it without PG involved?  Just make a
test program that chmod's, sleeps awhile, and chmod's again, and
manually change the permissions while it's sleeping, in the same way
that you did in the problem case.

            regards, tom lane

Joshua D. Drake wrote:

> I am pretty sure you are missing something. I can not reproduce your issue:

Do it like this:

- mkdir /tmp/foo
- chown user:user /tmp/foo
- edit /etc/group and add postgres to the group user
- attempt to access /tmp/foo as the user postgres, at this point a user
logged in as postgres will be able to access the directory.
- attempt to create a tablespace on /tmp/foo, this will fail until
postgresql is restarted, after which the attempt will succeed.

Regards,
Graham
--

Graham Leggett wrote:
> Joshua D. Drake wrote:
>
>> I am pretty sure you are missing something. I can not reproduce your
>> issue:
>
> Do it like this:
>
> - mkdir /tmp/foo
> - chown user:user /tmp/foo
> - edit /etc/group and add postgres to the group user

Aha! there it is. Adding a user to a group doesn't mean anything until
that user reinitializes the environment. That is why restarting
postgresql resolves the issue. When postgresql is started, is actually
launches a new shell as the user and the fact that they are part of the
group is picked up.


Sincerely,

Joshua D. Drake

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Graham Leggett wrote:
>> - edit /etc/group and add postgres to the group user

> Aha! there it is. Adding a user to a group doesn't mean anything until
> that user reinitializes the environment.

In particular, it looks like the list of groups you belong to is
typically only computed at login time (or equivalently su -l, which
is what's happening in the postgres init script), and then just
inherited by all processes launched from that login.  So there is
caching of a sort going on here, but it's in the kernel and there's
nothing we can do about it.

            regards, tom lane