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
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.
On Fri, Aug 17, 2012 at 4:23 AM, Frank Lanitz <frank@frank.uvena.de> wrote:
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--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com
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>