Permission problem with create tablespace

"Andras Simon" <szajmi@gmail.com> writes:

I'm trying to create a new tablespace in a directory that postgres
owns, but PG says it cannot set permissions on this directory.

...

This is on Fedora Core 5 (x86), psql 8.1.4.

SELinux, most likely.

-Doug

"Andras Simon" <szajmi@gmail.com> writes:

I'm trying to create a new tablespace in a directory that postgres
owns, but PG says it cannot set permissions on this directory.
...
This is on Fedora Core 5 (x86), psql 8.1.4.

Do you have selinux enabled? The default selinux policy disallows the
postgres daemon from writing anywhere outside the standard
/var/lib/pgsql/data directory tree. Check for "avc denied" messages in
the kernel log to verify this.

To use a nondefault tablespace, you'd want to tweak the policy to allow
postgres to write that directory tree too. I'm afraid I know too little
about selinux to explain exactly what to do though ... need to learn
that someday ...

A quick and dirty solution is to put selinux into non-enforcing mode,
but if this machine is exposed to the internet at all, that's probably
not a good answer.

regards, tom lane

On 10/17/06, Douglas McNaught <doug@mcnaught.org> wrote:

"Andras Simon" <szajmi@gmail.com> writes:

I'm trying to create a new tablespace in a directory that postgres
owns, but PG says it cannot set permissions on this directory.

...

This is on Fedora Core 5 (x86), psql 8.1.4.

SELinux, most likely.

That's it!

Oct 17 02:58:57 localhost kernel: audit(1161046737.127:24): avc: denied { setattr } for pid=21374 comm="postmaster" name="mspace" dev=hdb6 ino=2424835 scontext=system_u:system_r:postgresql_t:s0 tcontext=user_u:object_r:usr_t:s0 tclass=dir

Thanks,

Andras

On 10/17/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

To use a nondefault tablespace, you'd want to tweak the policy to allow
postgres to write that directory tree too. I'm afraid I know too little
about selinux to explain exactly what to do though ... need to learn
that someday ...

Me too...

A quick and dirty solution is to put selinux into non-enforcing mode,
but if this machine is exposed to the internet at all, that's probably
not a good answer.

Yes, I'd like to avoid that if at all possible.

Andras

I'm trying to create a new tablespace in a directory that postgres
owns, but PG says it cannot set permissions on this directory.

[root@localhost ~]# su - postgres
-bash-3.1$ mkdir /opt/home/pgdata/mspace/
-bash-3.1$ psql
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# CREATE TABLESPACE mspace LOCATION '/opt/home/pgdata/mspace';
ERROR: could not set permissions on directory
"/opt/home/pgdata/mspace": Permission denied
postgres=# \q
-bash-3.1$ chmod 700 /opt/home/pgdata/mspace/
-bash-3.1$ whoami
postgres
-bash-3.1$

This is on Fedora Core 5 (x86), psql 8.1.4.

Andras

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Check for SELinux permissions

Leonel

On 10/16/06 20:17, Douglas McNaught wrote:

"Andras Simon" <szajmi@gmail.com> writes:

I'm trying to create a new tablespace in a directory that postgres
owns, but PG says it cannot set permissions on this directory.

...

This is on Fedora Core 5 (x86), psql 8.1.4.

SELinux, most likely.

In my case, it is *not* SELinux, since I'm not running it...

Debian Sid
PostgreSQL 8.1.5 (distro version 8.1.5-1)

As you can see from the attached file, user "me" is a Superuser and
is a member of group postgres, and *is* able to create files in that
directory.

File /var/log/postgresql/postgresql-8.1-main.log gives the same
error that psql does, and there's nothing in syslog.

Any help appreciated.

--
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.

On 10/17/06, Leonel Nunez <lnunez@enelserver.com> wrote:

Correction to my prior mail

do this as root :

chcon system_u:object_r:postgresql_db_t /opt/home/pgdata/mspace

When you need to know what config has any directory for SELinux
do a:

ls -lZ /your/dir

and if you need /your/otherdir the same set the configuration for
SELinux with chcon

check the /var/lib/pgsql and you get :
[root@fedora ~]# ls -lZ /var/lib/pgsql/
drwx------ postgres postgres system_u:object_r:var_lib_t backups
drwx------ postgres postgres system_u:object_r:postgresql_db_t data
-rw------- postgres postgres system_u:object_r:postgresql_log_t
pgstartup.lo

we set the SELinux permissions to yourdir as the permissions that
/var/lib/pgsql/data has

Thanks, this is exactly what I was looking for! To try it, I have to
wait for the next reboot, because in order to get moving, I checked

SELinux > Modify SELinux Policy > SELinux Service Protection > Disable
SELinux Protection for postgresql daemon

in system-config-securitylevel, and these kind of changes don't seem
to take effect until the next reboot (although I think they should; I
see 'avc: denied...' messages when I'm doing this, so there just may
be some other SELinux problems here).

Thanks a lot,

Andras

Ron Johnson <ron.l.johnson@cox.net> writes:

~$ touch /data/02/share/database/testing.testing
~$ dir /data/02/share/database
total 8
drwxrwxr-x 2 me postgres 4096 2006-10-16 21:53 ./
drwxrwxr-x 16 me people 4096 2006-10-16 21:38 ../
-rw-r--r-- 1 me me 0 2006-10-16 21:53 testing.testing

dupe_filenames=# create tablespace thisisatest
dupe_filenames-# owner me
dupe_filenames-# location '/data/02/share/database';
ERROR: could not set permissions on directory "/data/02/share/database":
Operation not permitted

The specified directory has to be owned by the postgres operating system
user, not by anyone else. (The SQL-level notion of ownership is not
relevant --- the SQL owner might not correspond to any OS user at all.)

regards, tom lane