X-Original-To: alpine-devel@lists.alpinelinux.org Delivered-To: alpine-devel@lists.alpinelinux.org Received: from mail.squbes.com (squbes.com [208.74.142.49]) by lists.alpinelinux.org (Postfix) with ESMTP id 933A51EB598 for ; Fri, 14 May 2010 01:32:04 +0000 (UTC) Received: from [10.252.6.46] (c-71-198-150-62.hsd1.ca.comcast.net [71.198.150.62]) (using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: nangel@nothome.org) by mail.squbes.com (Postfix) with ESMTPSA id 8D0D450001CA0 for ; Fri, 14 May 2010 01:32:03 +0000 (UTC) Message-ID: <4BECA826.70805@nothome.org> Date: Thu, 13 May 2010 18:32:22 -0700 From: Nathan Angelacos User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.9) Gecko/20100411 Icedove/3.0.4 X-Mailinglist: alpine-devel Precedence: list List-Id: Alpine Development List-Unsubscribe: List-Post: List-Help: List-Subscribe: MIME-Version: 1.0 To: alpine-devel@lists.alpinelinux.org Subject: Re: [alpine-devel] Patch to postgresql init script References: <4BEB97F0.4060304@nothome.org> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 05/13/10 00:10, Natanael Copa wrote: >> 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. >> 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 ---