Mail archive
alpine-devel

Re: [alpine-devel] Patch to postgresql init script

From: Natanael Copa <natanael.copa_at_gmail.com>
Date: Thu, 13 May 2010 09:10:16 +0200

On Thu, May 13, 2010 at 8:10 AM, Nathan Angelacos <nangel_at_nothome.org> wrote:
>
> Attached is a patch for /etc/init.d/postgresql  and /etc/conf.d/postgresql

Thanks!

> A lbu commit of the /var/lib/postgresql/x.x/data directory takes a very long
> time if the database is running.

And is not safe when you upgrade.

> apk-tools 2.0 includes the ability (/etc/lbu/pre-package.d) to make a
> pg_dumpall of the databases, and then commit the archive.  This is much
> faster.

(lbu is provided by alpine-conf, not apk-tools)

> There has not been a way to automatically restore the backup on reboot.

we added pg-restore initscript not too long ago and AUTO_SETUP to auto
init and empty database.

>  This patch attempts to address that issue
>
> Limitations:  The archive is assumed to be created with
> pg_dumpall -U postgres | gzip -c >/some/path/here.gz
>
> * bzip2 or plain dumps are not supported
>
> You must do a lbu_include /some/path/here.gz
> and also edit /etc/conf.d/postgresql to define LBU_BACKUP=/some/path/here.gz

I'd prefer using uncompressed dump since lbu will compress it again.

> postgresql stores the pg_hba.conf (access controls) and postgresql.conf in
> $PGDATA; so if you have password protection on the postmaster account (a
> good idea); that gets lost.
>
> This script addresses that by moving the files in $PGDATA out of the way,
> restoring the database, and then moving them back.
>
> This way you can do a lbu include /var/lib/postgresql/8.4/data/pg_hba.conf
>  and get the access controls on the restored databases.

This is a feature we dont have with pg-restore.

How do you create the dump if postgres user is password protected?

I was initially thinking it was a good idea to separate the restoring
to other service to make it easy to enable or disable it (rc-update
add pg-restore) but with the pg_hba.conf feature its probably easier
to embed it in same service.

I also think that we want provide a way to do the dump from the init.d
script too, so you from /etc/lbu/pre-package.d/ can do:
'/etc/init.d/postgresql dump' or something. Then you dont need to
worry about .gz .bz2 or whatever since the script does that for you.
You could still configure it via /etc/conf.d/ tough.

Please have a look at the pg-restore from git master. We need to either:

* move the pg_hba.conf protection feature to pg-restore
* replace the pg-restore with your patch (not keep both in any case)
* assume that you have postgres access when connecting from localhost
and keep pg-restore as-is
* add a password variable that is used when connecting to db to do the
dump/restore via pg-restore script.

I wonder if we can assume that if you run as postgres user and connect
via local unix socket, then you have full access to database. That is,
if you su - postgres, then you can psql and pg_dumpall, (or run as
root). But if you run as normal user, lets say ncopa, and does a psql
-U postgres, then you get reject or prompted for password.

The reasoning is that since you are running as root you do have (raw)
access to the database anyway so its no big point try protect the db
from local root user. It does make sense to protect it from other
local users and definitively from remote connections. (i though tcp
connections are off by default?)

So technically, beeing able to do dump/restore as local root user
doesnt need be a bad idea.

I'm not too happy about storing the password as cleartext though.

-- 
Natanael Copa
---
Unsubscribe:  alpine-devel+unsubscribe_at_lists.alpinelinux.org
Help:         alpine-devel+help_at_lists.alpinelinux.org
---
Received on Thu May 13 2010 - 09:10:16 UTC