Re: database in different location owned by different user

Great,

I changed the file owner and group to postgres. I then created a subdirectory /tp/db/jira (because the create tablespace command was complaining the directory wasn't empty because of the lost+found directory). I then ran :

CREATE TABLESPACE Jira OWNER appdev LOCATION '/tp/db/jira';

and that worked. I think I can now turn this over to the db admin and tell him to issue:

SET default_tablespace = Jira;

before creating tables and databases. Thanks for the help everyone!

Regards,

Brad Van Orden

________________________________________
From: fluca1978(at)gmail(dot)com [fluca1978(at)gmail(dot)com] on behalf of Luca Ferrari [fluca1978(at)infinito(dot)it]
Sent: Monday, June 04, 2012 7:39 AM
To: Van Orden, Brad; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] database in different location owned by different user

You have to tjink that the on-disk data must be owned by the
postgresql user, as much as apache data is owned by the apache user,
since we are talking about files accessed by a daemon. Then you can
create several database users (using create role) and give them the
appropriate permissions (grants) to work on a database, a table, and
so on.

On Mon, Jun 4, 2012 at 12:51 PM, Van Orden, Brad
<VanOrdenB(at)varentech(dot)com> wrote:
> 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
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice