Thread: Best practice non privilege postgres-user

Hi folks,

I'm looking for some kind of best practice for a non-privilege postgres
user. As not all operations can be done within psql you might need
access to postgres- on command line from time to time. Currently this is
done via root-privvileges and »su - postgres« directly on database
server - which is might not the best idea. Therefor our goal is to limit
access to a little number of people on the first hand and don't
necessary give them root-privileges on the databse server. We
experimented a bit with sudo but had issues with some of the
environmental variables. So my question is: do you have any best
practice how to manage this? Is there any golden rule for this?

Cheers,
Frank

Frank Lanitz wrote:
> I'm looking for some kind of best practice for a non-privilege
postgres
> user. As not all operations can be done within psql you might need
> access to postgres- on command line from time to time. Currently this
is
> done via root-privvileges and >su - postgres< directly on database
> server - which is might not the best idea. Therefor our goal is to
limit
> access to a little number of people on the first hand and don't
> necessary give them root-privileges on the databse server. We
> experimented a bit with sudo but had issues with some of the
> environmental variables. So my question is: do you have any best
> practice how to manage this? Is there any golden rule for this?

When you say "access to postgres on command line", I assume that you
mean "shell access as PostgreSQL OS user".

One easy way would be to setup ssh and either give the OS password
of "postgres" to the trusted people or configure ssh to accept only
certain certificates.

There are other ways to authenticate; I guess the best solution
will depend on your environment and your needs.

If you mean "access to PostgreSQL via psql as superuser", there
is no need for shell access to the database machine itself.
Again you can either hand out the password or set up some
more advanced authentication method.

Yours,
Laurenz Albe

On Fri, 17 Aug 2012 08:53:05 -0400
Moshe Jacobson <moshe@neadwerx.com> wrote:

> I do not know of anything that can't be done from within psql.
> We use non-privileged user roles in postgres for day-to-day
> operations. When I need to modify the schema, I become postgres (you
> can do \c - postgres) and do what I need to do, then revert back to
> my regular user.

It's not only about the things that can be done from within psql. At
least originally. Some of our currently workflows are basing on real
shell access.

Cheers,
Frank
--
Frank Lanitz <frank@frank.uvena.de>