Re: database in different location owned by different user

Hi, thanks for the response. There are only a handful of developers. They are using this same account to manage the current Sybase database. We are getting rid of Sybase. So, I just don't want to make this system unique. That is why I want the appdev user account to be able to manage/own the database. I'm not worried about "damage" to the database. The DB admin is in that group. It is their application. If they screw it up, they have to fix it. :) I'm OK with leaving the postgres account as the "master" database admin account, but I thought the "CREATE ROLE" was the proper method for creating a new user account?

SELinux is only running in permissive mode.

Regards,

Brad Van Orden
Varen Technologies
240-373-2671

________________________________________
From: Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, June 01, 2012 3:05 PM
To: Van Orden, Brad
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] 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