Re: sudo/access to the postres OS user

On Thu, Aug 15, 2013 at 4:51 AM, Birchall, Austen <
austen(dot)birchall(at)metoffice(dot)gov(dot)uk> wrote:

> 9.2 on Red Hat 6
>
> Our OS Sys Admin have decided to withdraw my (DBA) access to the OS
> postgres account - so instead of
> ssh/logging in to a DB host I I have to login in as 'myself' and then gain
> access to psql etc. via sudo
> such as by doing
> PSQL access as the postgres user
> sudo -u postgres /usr/bin/psql <command line options>
> which I have been granted permissions to do.
>
> I have the following questions regarding this:
>
> 1. Is this standard/best practise?
>

It's fairly standard so they can audit who ran psql and when. It is,
however, a pain in the butt.

> 2. In simple terms what is justification for dong this - looking at
> it from a DBA point of view?
>

None. It's so the SA team can audit your access. There is no benefit to
you.

> 3. As myself I current have no permissions on the ..../psql/data
> directory and its sub-directories including the log files, which IMHO I
> need in order to be able to function as a DBA - is there a preferred way in
> which I can be granted/gain this access other than by granting rights on
> files at an individual level?
>

Your SAs are [I can't say in polite company]. Have them create a dba group
and put all the DBAs in it. Then setup postgres to run as postgres:dba and
chown the entire tree postgres:dba and add group write permission where
ever it's missing, group read and execute for all directories, and remove
all group write permissions. This will allow you to read any file and
enter into and list out any directories, but not change anything. Then
chmod the psql command 700 (or 500) owned by postgres. This prevents any
user from running the utility other than the postgres user which you're
assuming when you use sudo.

This gives you the ability, as yourself, to read logs, examine files, etc,
but without being able to make any changes to anything (you might
compromise with them on the *.conf files, getting them to add write
permissions so you can make database configuration changes). You might
also ask them for sudo access to whatever utilities you're using to
start/stop the database so you, as a DBA, can shut it down, bounce it,
start it, etc.

All of this should have been setup as part of revoking your shell access.
Were there any discussions leading up to this? All of this should have
been covered in those discussions and implemented before your shell access
was revoked (which it should not have been, you need shell access to manage
a PostgreSQL database). I can understand removing any ability to control
or access the database without sudo (that audit trail), but I can't
understand hamstringing your ability to access logs and configuration files.

> Happy to (try to) explain further if none of this makes much sense.
>

The goal makes sense. The implementation makes none.

> Thanks in advance as always
>
> Austen
>
>
> Austen Birchall Senior Database Administrator
> Met Office
>
>
> --
> 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
>

--
Chris

"If you wish to make an apple pie from scratch, you must first invent the
Universe." -- Carl Sagan