Re: database in different location owned by different user

bradawk <vanordenb(at)varentech(dot)com> writes:
> I'm very new to PostgreSQL. So, I apologize if this seems trivial. :)
> I have a RHEL 5 system on which I have installed PostgreSQL 8.4.9 from rpms.
> The developers use the 'appdev' account for all of their common activities.
> All of their files/applications I restrict to the file system under /tp. I
> want to give appdev full rights over a database hosted at /tp/db. The /tp
> directory (and all below it are owned by appdev). I've done:

> chkconfig --level 5 postgresql on
> service postgresql initdb
> service postgresql start
> su - postgres
> psql -d template1 -U postgres
> CREATE ROLE appdev SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN PASSWORD
> 'somepassw';
> \q
> service postgresql restart
> su - appdev
> psql -d template1 -U appdev

> I'm not really sure how to create the datbase. I tried:

> CREATE TABLESPACE Jira OWNER appdev LOCATION '/tp/db';
> but it tells me that it could not set permissions on "tp/db."

> I tried:

> CREATE DATABASE jira WITH OWNER = appdev TEMPLATE = DEFAULT TABLESPACE =
> Jira;

> Any clues on how I create a database in a non-standard location owned by a
> user other than postgres?

Well, you can't, and I really think you're trying to do something that's
rather pointless. There is no reason whatsoever to give your users
direct access to the database files, and lots of excellent reasons not
to. There isn't anything they can do with direct file access except
break the database.

If you want to keep the files physically under /tp/db for filesystem
management reasons, that's fine, but the files and database directories
need to be owned by user postgres. Alternatively, you could run the
database server as some other userid that will own those files and
directories, but I don't see any really good reason not to use the
postgres account.

BTW, if you have SELinux turned on, you might need to tweak its
configuration a bit to make it allow the postgresql daemon to access
files outside the normal database playpen of /var/lib/pgsql. If it
seems that the filesystem permissions are proper to allow the postgres
daemon to touch something, but it's giving you permissions failures
anyway, check the kernel logs for avc denials.

regards, tom lane