[vhffs-dev] [652] Added users database mirroring script to use with libnss-sqlite.

[subversion@xxxxxxxxx]

Revision: 652
Author:   beuss
Date:     2007-07-02 20:45:18 +0000 (Mon, 02 Jul 2007)

Log Message:
-----------
Added users database mirroring script to use with libnss-sqlite.

Added Paths:
-----------
    trunk/vhffs-backend/src/nss/
    trunk/vhffs-backend/src/nss/nss-mirror.pl


Added: trunk/vhffs-backend/src/nss/nss-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/nss/nss-mirror.pl	2007-07-02 10:48:54 UTC (rev 651)
+++ trunk/vhffs-backend/src/nss/nss-mirror.pl	2007-07-02 20:45:18 UTC (rev 652)
@@ -0,0 +1,114 @@
+#!/bin/perl
+# Copyright (c) vhffs project and its contributors
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions
+# are met:
+#
+# 1. Redistributions of source code must retain the above copyright
+#   notice, this list of conditions and the following disclaimer.
+#2. Redistributions in binary form must reproduce the above copyright
+#   notice, this list of conditions and the following disclaimer in
+#   the documentation and/or other materials provided with the
+#   distribution.
+#3. Neither the name of vhffs nor the names of its contributors
+#   may be used to endorse or promote products derived from this
+#   software without specific prior written permission.
+#
+#THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
+#"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
+#LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
+#FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
+#COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
+#INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
+#BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+#LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
+#CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
+# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
+# ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
+# POSSIBILITY OF SUCH DAMAGE.
+
+
+# **** WARNING ***** This file should be owned by root and chmoded 500
+use DBI;
+
+# TODO Set us!
+my $PG_DB_HOST = 'localhost';           # Host running pg
+my $PG_DB_PORT = 5432;                  # Port on which pg is listening
+my $PG_DB_NAME = 'vhffs';               # VHFFS' database name
+my $PG_DB_USER = 'vhffs';               # VHFFS' database user
+my $PG_DB_PASS = 'vhffs';               # VHFFS' database password
+my $ST_DB_FILE = '/var/db/auth.sqlite'; # SQLite shadow database
+
+# Let's open pg connection
+my $pg_dbh = DBI->connect("DBI:Pg:dbname=$PG_DB_NAME;host=$PG_DB_HOST;port=$PG_DB_PORT", $PG_DB_USER, $PG_DB_PASS) 
+    or die("Unable to open pg connection\n");
+
+# SQLite connection now
+my $st_dbh = DBI->connect("DBI:SQLite:dbname=$ST_DB_FILE", '', '')
+    or die("Unable to open SQLite connection\n");
+
+# Ok, we have to fetch everything from pg and put it into SQLite. Use a
+# transaction to speedup things.
+
+$st_dbh->{AutoCommit} = 0;
+
+$st_dbh->do(q{CREATE TEMP TABLE tmp_shadow(uid INTEGER PRIMARY KEY, gid INTEGER, username TEXT NOT NULL, passwd TEXT NOT NULL, gecos TEXT NOT NULL default '', shell TEXT NOT NULL,  homedir TEXT NOT NULL)})
+    or die("Unable to create temporary shadow table\n");
+
+$st_dbh->do(q{CREATE TEMP TABLE tmp_groups(gid INTEGER PRIMARY KEY, groupname TEXT NOT NULL, passwd TEXT NOT NULL DEFAULT '')})
+    or die("Unable to create temporary groups table\n");
+
+$st_dbh->do(q{CREATE TEMP TABLE tmp_user_group(uid INTEGER, gid INTEGER, CONSTRAINT pk_user_groups PRIMARY KEY(uid, gid))})
+    or die("Unable to create temporary user_group table\n");
+
+my $users = $pg_dbh->selectall_arrayref(q{SELECT u.uid, u.gid, u.username, u.passwd, u.shell, u.homedir FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id = u.object_id WHERE o.state = 6},
+    { Slice => {} });
+my $sth = $st_dbh->prepare(q{INSERT INTO tmp_shadow(uid, gid, username, passwd, shell, homedir) VALUES(?, ?, ?, ?, ?, ?)})
+    or die("Unable to prepare shadow insert statement\n");;
+
+foreach(@$users) {
+    $sth->execute($_->{uid}, $_->{gid}, $_->{username}, $_->{passwd}, $_->{shell}, $_->{homedir})
+        or die("Unable to insert shadow\n");
+}
+$sth->finish();
+
+my $groups = $pg_dbh->selectall_arrayref(q{SELECT g.gid, g.groupname, g.passwd FROM vhffs_groups g INNER JOIN vhffs_object o ON o.object_id = g.object_id WHERE o.state = 6}, { Slice => {} })
+    or die("Unable to select groups\n");
+$sth = $st_dbh->prepare(q{INSERT INTO tmp_groups(gid, groupname, passwd) VALUES(?, ?, ?)})
+    or die("Unable to prepare groups insert statement\n");
+
+foreach(@$groups) {
+    $sth->execute($_->{gid}, $_->{groupname}, ($_->{passwd} or ''))
+        or die("unable to insert groups\n");
+}
+$sth->finish();
+
+my $user_group = $pg_dbh->selectall_arrayref(q{SELECT uid, gid FROM vhffs_user_group WHERE state = 6},{ Slice =>{} })
+    or die("Unable to select user_group association\n");
+$sth = $st_dbh->prepare(q{INSERT INTO tmp_user_group(uid, gid) VALUES(?, ?)})
+    or die("Unable to prepare user_group insert statement\n");
+
+foreach(@$user_group) {
+    $sth->execute($_->{uid}, $_->{gid})
+        or die("Unable to insert user_group\n");
+}
+$sth->finish();
+
+# Required to avoid warning "closing dbh with active statement handles"
+undef $sth;
+
+$pg_dbh->disconnect();
+
+$st_dbh->do(q{DELETE FROM shadow WHERE uid NOT IN(SELECT uid FROM tmp_shadow)});
+$st_dbh->do(q{DELETE FROM groups WHERE gid NOT IN(SELECT gid FROM tmp_groups)});
+$st_dbh->do(q{DELETE FROM user_group WHERE NOT EXISTS(SELECT * FROM tmp_user_group 
+WHERE tmp_user_group.uid = user_group.uid AND tmp_user_group.gid = user_group.gid)});
+
+$st_dbh->do(q{INSERT OR REPLACE INTO shadow SELECT * FROM tmp_shadow});
+$st_dbh->do(q{INSERT OR REPLACE INTO groups SELECT * FROM tmp_groups});
+$st_dbh->do(q{INSERT OR IGNORE INTO user_group SELECT * FROM tmp_user_group});
+
+$st_dbh->commit();
+$st_dbh->disconnect();