5 2

[alpine-devel] Patch to postgresql init script

Nathan Angelacos
Details
Message ID
<4BEB97F0.4060304@nothome.org>
Sender timestamp
1273731056
DKIM signature
missing
Download raw message
Attached is a patch for /etc/init.d/postgresql  and /etc/conf.d/postgresql

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

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.

There has not been a way to automatically restore the backup on reboot. 
  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


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.
Natanael Copa
Details
Message ID
<AANLkTil1zE-gV-MDqpB2Pyteqemf5oD1kMTvwg2zYdjv@mail.gmail.com>
In-Reply-To
<4BEB97F0.4060304@nothome.org> (view parent)
Sender timestamp
1273734616
DKIM signature
missing
Download raw message
On Thu, May 13, 2010 at 8:10 AM, Nathan Angelacos <nangel@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@lists.alpinelinux.org
Help:         alpine-devel+help@lists.alpinelinux.org
---
Nathan Angelacos
Details
Message ID
<4BECA826.70805@nothome.org>
In-Reply-To
<AANLkTil1zE-gV-MDqpB2Pyteqemf5oD1kMTvwg2zYdjv@mail.gmail.com> (view parent)
Sender timestamp
1273800742
DKIM signature
missing
Download raw message
On 05/13/10 00:10, Natanael Copa wrote:

<snip>

>> 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)
>

#apk info -W /sbin/lbu
/sbin/lbu is owned by alpine-conf-2.3-r0

Whoops. I stand corrected.


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

<snip>

>> 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 expecting that would be left as a job for the local admin.

>
> 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 looked at pg-restore, and understand what you are attempting; but it 
won't work currently:

* $PGDATA/p*.conf required modification to use postgresql with anything 
other than local "trust" access.  That means we must either
	A) manage them as part of pg-restore
	B) move them out of the way during a postgresql setup
In either case we must handle the conf files.

* The standard way to back up a file in the apkovl is lbu include; doing 
so means $PGDATA will exist when postgresql is started, so the autosetup 
will fail through, but there's no data, and postgresql will fail to 
start.   pg-restore depends on postgresql, and will then fail - there's 
no way to restart postgresql from a restore.   Options:
	A) pg-restore manages those files itself (non-standard)
	B) auto-setup checks for $PGDATA/base, and manages the conf files (also 
non-standard, but the non-standard part is limited to postgresql, 
instead of system wide)

>
> 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.
>

Agreed.  If pg-restore has access to /etc/conf.d/postgresql, I think we 
can safely assume your pg_hba.conf must allow "trusted" authentication 
over unix-sockets for the postmaster.     Then pg-restore can use the 
same su -l $PGUSER .... trick that the main postgresql script does.


Before going of and coding a solution, I propose:

  1 Backup p*.conf files using standard Alpine Linux methods (lbu 
include)  This means $PGDATA/p*.conf files may exist before services are 
started.
  2 Keep the postgresql AUTO_SETUP code, but set AUTO_SETUP as the 
default (that way, apk add postgresql && rc-service postgresql start 
"just works")
  3 Enhance postgresql setup to check for $PGDATA/base not $PGDATA; if 
$PGDATA exists, but $PGDATA/base does not, handle any existing $PGDATA/* 
files gracefully.
  4 Use the pg-restore script as before; it can now depend on postgresql 
even with local changes to the $PGDATA/p*.conf
  5 Have pg-restore source /etc/conf.d/postgresql for the PGUSER 
variable, and depend on a local unix-socket "trust" mechanism for 
restoring the database.
  6 use the pg-restore stop function to make a backup (if defined in 
/etc/conf.d/pg-restore); start to restore a backup.

The Alternative proposal is to put the functions inside of postgresql 
init script itsef.  This eliminates point 5 above, but does not really 
simplify anything else.


Comments?


---
Unsubscribe:  alpine-devel+unsubscribe@lists.alpinelinux.org
Help:         alpine-devel+help@lists.alpinelinux.org
---
Natanael Copa
Details
Message ID
<AANLkTinpAt3bWKJtMf6eUkL56sDm90ICwK4zb2Fcfc9M@mail.gmail.com>
In-Reply-To
<4BECA826.70805@nothome.org> (view parent)
Sender timestamp
1273819220
DKIM signature
missing
Download raw message
On Fri, May 14, 2010 at 3:32 AM, Nathan Angelacos <nangel@nothome.org> wrote:
<snip>

>> I'm not too happy about storing the password as cleartext though.
>>
>
> Agreed.  If pg-restore has access to /etc/conf.d/postgresql,

I see no problem with sourcing /etc/conf.d/postgresql manually from
pg-restore sice they are both provided by same package.

> I think we can
> safely assume your pg_hba.conf must allow "trusted" authentication over
> unix-sockets for the postmaster.     Then pg-restore can use the same su -l
> $PGUSER .... trick that the main postgresql script does.

yes. I think we want use  the su -l $PGUSER trick.

>
> Before going of and coding a solution, I propose:
>
>  1 Backup p*.conf files using standard Alpine Linux methods (lbu include)
>  This means $PGDATA/p*.conf files may exist before services are started.
>  2 Keep the postgresql AUTO_SETUP code, but set AUTO_SETUP as the default
> (that way, apk add postgresql && rc-service postgresql start "just works")
>  3 Enhance postgresql setup to check for $PGDATA/base not $PGDATA; if
> $PGDATA exists, but $PGDATA/base does not, handle any existing $PGDATA/*
> files gracefully.
>  4 Use the pg-restore script as before; it can now depend on postgresql even
> with local changes to the $PGDATA/p*.conf
>  5 Have pg-restore source /etc/conf.d/postgresql for the PGUSER variable,
> and depend on a local unix-socket "trust" mechanism for restoring the
> database.
>  6 use the pg-restore stop function to make a backup (if defined in
> /etc/conf.d/pg-restore); start to restore a backup.

I think this sounds good.

> The Alternative proposal is to put the functions inside of postgresql init
> script itsef.  This eliminates point 5 above, but does not really simplify
> anything else.
>
> Comments?

I think its nice to have the scripts separated, but no strog feelings
there really. Do what you think is best.

Thanks alot for following this up.

-- 
Natanael Copa


---
Unsubscribe:  alpine-devel+unsubscribe@lists.alpinelinux.org
Help:         alpine-devel+help@lists.alpinelinux.org
---
Nathan Angelacos
Details
Message ID
<4BF0EEFB.2090108@nothome.org>
In-Reply-To
<AANLkTinpAt3bWKJtMf6eUkL56sDm90ICwK4zb2Fcfc9M@mail.gmail.com> (view parent)
Sender timestamp
1274081019
DKIM signature
missing
Download raw message
On 05/13/10 23:40, Natanael Copa wrote:
> On Fri, May 14, 2010 at 3:32 AM, Nathan Angelacos<nangel@nothome.org>  wrote:
> <snip>

>> Comments?
>
> I think its nice to have the scripts separated, but no strog feelings
> there really. Do what you think is best.
>
> Thanks alot for following this up.
>

attached.
Natanael Copa
Details
Message ID
<AANLkTikYr4s_kvbMDTeN31aNPGHvgkO0LrFG3GmpAo8Z@mail.gmail.com>
In-Reply-To
<4BF0EEFB.2090108@nothome.org> (view parent)
Sender timestamp
1274123287
DKIM signature
missing
Download raw message
On 5/17/10, Nathan Angelacos <nangel@nothome.org> wrote:
> attached.

commited with nangel as author.

thanks!

-- 
Natanael Copa


---
Unsubscribe:  alpine-devel+unsubscribe@lists.alpinelinux.org
Help:         alpine-devel+help@lists.alpinelinux.org
---