Re: [Gnumed-devel] Problems dumping database (Mac limitation on su -c)

[Karsten Hilbert]

On Thu, May 26, 2011 at 11:28:51PM -0700, Jim Busser wrote:

> *****************************************
> In the backup script where it says
> 
> # You need to allow root to access the GNUmed database as
> # user "gm-dbo" by either editing pg_hba.conf or using a
> # .pgpass file.
> 
> Does the above mean only ensuring to have, in place, the line
> 
>       local samegroup +gm-logins md5

It means that root must be able to access the database. It
doesn't matter *how* that is ensured. It can be the user
manually supplying passwords at the appropriate time or it
can be PostgreSQL to be set up to allow passwordless access
or it can be the OS set up to store passwords in the
appropriate .pgpass. Or else mechanisms I did not think of.

> and is this truly anything to do with root? It seems that once a server 
> administrator would set up the backup files for example in
> 
>       /etc/gnumed
> 
> then a regular user only needs to know the (postgres and) gm-dbo passwords to 
> be able to dump the database
> 
> ??

They would also need to be able to "su/sudo" to "postgres".

The script is written for root.

> In the backup script top comment section, just below "You need to allow 
> root…" but above "anacron", could you insert
> 
>       Mac users, pending a FIXME, need to comment-out the sanity check
> 
> Above the line
> 
>       CONF="/etc/gnumed/gnumed-backup.conf" 
> 
> can you insert
> 
>       # Ensure that the following has been properly updated for
>       # the desired version v_ of the database, and other params

I added some text but we are getting into the realms of
telling doctors to consider pain killers should the patient
be in pain.

> The backup script seemed capable, when needing the
> passwords for database users postgres and gm-dbo, to evoke
> prompts from the command line *except* that the prompts I
> was given were uninformative…

That's a known limitation of su/sudo/psql.

>       Password:       <--- wanting postgres
>       Password for user gm-dbo:       <--- wanting gm-dbo
>       Password:       <--- wanting… gm-dbo !!!!!!!!!!!!!
> 
> … can the above be improved by echoing, to the command line in advance of 
> (and after) each above step, what might be expected, for example:
> 
>       Initiating a postgres db user step…
>       < here there may, or may not, come a prompt>
> 
>       Initiating a GNUmed db owner (e.g. gm-dbo) step…
>       < here there may, or may not, come a prompt>
> 
>       Initiating a GNUmed db owner (e.g. gm-dbo) step…
>       < here there may, or may not, come a prompt>
> 
> because by the above method, when no prompt is received, the user attends 
> only to the last line.

This would render the script less useful to cron/anacron.

When used as intended (run by root, passwords supplied by
.pgpass) no prompts are expected and no output is echoed
unless there is a problem which results in a mail to root.

> *****************************************
> A question about the data security safety of .pgpass files…

        http://www.postgresql.org/docs/current/static/libpq-pgpass.html

> … since anyone with sudo access could reset postgres and accordingly the 
> gm-dbo passwords, is it no loss of security to store a .pgpass under a root 
> directory?

Yes.

> … backing up as root however implies a backup to
> 
>       /var/root/gnumed/backup
> 
> which would maybe a bit strange?

It seems, indeed, strange why MacOSX would put root's
homedir under /var.

If the default of ~/.gnumed/backup/ (note the ".") is not
desired it could be configured in /etc/gnumed/gnumed-backup.conf:

        # where to eventually store the backup
        BACKUP_DIR="${HOME}/.gnumed/backup/"

> Is it envisioned that a typical user backup would be as a user other than 
> root?

No, but that depends on site policy. The script will run
just fine given appropriate permissions.

> When you commented in the config file as follows
> 
>       #------------------------------------------------------------
>       # those need to be changed in most if not all cases
>       #------------------------------------------------------------ 
> 
> can you please change the two occurrences of
> 
>       "those…"
> 
> to
> 
>       "the following"

Done.

> Can the conf file URL to the 8.1 manual be replaced by the one to the 
> (admittedly) marginally clearer one in 8.4
> 
>       http://www.postgresql.org/docs/8.4/static/libpq-pgpass.html

It now points to the "current" version:

        http://www.postgresql.org/docs/current/static/libpq-pgpass.html

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346