I have just installed postgres on Ubuntu 9.04 using the apt-get
installer. It all happened automatically. I wasn’t prompted for any
password for the postgres account, as is normal (I believe). I also
didn’t need to run initdb, the postgres database etc. was created
automatically.

So I have postgres running okay and now I want to install my database
from existing scripts (I previously had postgres running fine on
WinXP).

For my own convenience I want to install my database in my home
directory,
e.g. /home/gary/mydb
and be able to run the psql utility/interface under my Linux user
account (I haven’t checked if that’s possible yet, permissions-wise).

I’ve considered a way of going about this:-
1. create the /home/gary/mydb directory with my Linux user account and
standard/default permissions on that directory
2. from my Linux user account, run my sql scripts using psql and build
my database
3. subsequently use my database as and when required (but not all the
time)

I also want to be able to startup postgres manually using pg_ctl
rather than have the server run automatically (for reasons of
efficiency). I've arranged for this via sudo to postgres.

I had the idea of creating a new group with members: postgres, gary
and then granting group permissions for the postgres utilities (psql,
pg_ctl, etc.) so that I can startup the server on the fly and use my
database whenever I need to, but without jeopardizing security.
However, I’m not sure if this is the right way to go.
I’m also not 100% sure if I need to:
create a group then chown/chmod/combinations of these – or
something else.

I used to work on Unix systems years ago, so I understand some of the
concepts of Linux, albeit I’m a little rusty on Unix. So what I need
is some general guidance to get me started, particularly with regards
to user groups, accounts and permissions – or alternative
recommendations.

gargoyle47 wrote:
It is no problem to have the database running under the postgres
account if all you want to do is to manipulate the database via psql.
If you want to control the server (stop and start the database)
you have to be the account that owns the database.

If you are willing to do the latter via sudo, I would leave the database
cluster where it is, running in the default location under postgres.
If you have a superuser *database* account you have full control.

The other possibility is to run a second initdb with your personal
user and create a second cluster in your home directory. That way
you don't need sudo to manipulate the server. The server processes
would belong to you.

I personally use both approaches, depending on what I want to do:
If I work in the database, I prefer the first setup which keeps me
from accidentally messing up the database directory.

If I work with the database software, for example to write
PostgreSQL code, I use the second setup because I frequently have to
mess with the database directory or the database software.

Yours,
Laurenz Albe

On Oct 13, 2:22*pm, "Laurenz Albe" <inv... (AT) spam (DOT) to.invalid> wrote:
Thanks for the feedback. That all sounds logical and reasonable.
I have since managed to run psql from my own Linux account and create
my new database.
I have also managed to set up ODBC (after some difficulty) so
accessing the db is now just as I had hoped.
Regards
Gary