|
| 1 | +#! /bin/sh |
| 2 | + |
| 3 | +# pg_dumpaccounts |
| 4 | +# |
| 5 | +# Dumps the "pg_shadow" and "pg_group" tables, which belong to the |
| 6 | +# whole installation rather than any one individual database. |
| 7 | +# |
| 8 | +# $Header: /cvsroot/pgsql/contrib/pg_dumpaccounts/Attic/pg_dumpaccounts.sh,v 1.1 2000/11/02 18:20:12 wieck Exp $ |
| 9 | + |
| 10 | +CMDNAME=`basename$0` |
| 11 | + |
| 12 | +# substituted at build |
| 13 | +VERSION='@VERSION@' |
| 14 | +MULTIBYTE='@MULTIBYTE@' |
| 15 | +bindir='@bindir@' |
| 16 | + |
| 17 | +# |
| 18 | +# Find out where we're located |
| 19 | +# |
| 20 | +PGPATH= |
| 21 | +ifecho"$0"| grep'/'> /dev/null2>&1;then |
| 22 | +# explicit dir name given |
| 23 | + PGPATH=`echo$0| sed's,/[^/]*$,,'`# (dirname command is not portable) |
| 24 | +else |
| 25 | +# look for it in PATH ('which' command is not portable) |
| 26 | +fordirin`echo"$PATH"| sed's/:/ /g'`;do |
| 27 | +# empty entry in path means current dir |
| 28 | + [ x"$dir"= x ]&& dir='.' |
| 29 | +if [-f"$dir/$CMDNAME" ];then |
| 30 | + PGPATH="$dir" |
| 31 | +break |
| 32 | +fi |
| 33 | +done |
| 34 | +fi |
| 35 | + |
| 36 | +# As last resort use the installation directory. We don't want to use |
| 37 | +# this as first resort because depending on how users do release upgrades |
| 38 | +# they might temporarily move the installation tree elsewhere, so we'd |
| 39 | +# accidentally invoke the newly installed versions of pg_dump and psql. |
| 40 | +if [ x"$PGPATH"= x"" ];then |
| 41 | + PGPATH=$bindir |
| 42 | +fi |
| 43 | + |
| 44 | +# |
| 45 | +# Look for needed programs |
| 46 | +# |
| 47 | +forprogin psql;do |
| 48 | +if [!-x"$PGPATH/$prog" ];then |
| 49 | +echo"The program$prog needed by$CMDNAME could not be found. It was" |
| 50 | +echo"expected at:" |
| 51 | +echo"$PGPATH/$prog" |
| 52 | +echo"If this is not the correct directory, please start$CMDNAME" |
| 53 | +echo"with a full search path. Otherwise make sure that the program" |
| 54 | +echo"was installed successfully." |
| 55 | +exit 1 |
| 56 | +fi |
| 57 | +done |
| 58 | + |
| 59 | +# |
| 60 | +# to adapt to System V vs. BSD 'echo' |
| 61 | +# |
| 62 | +ifecho'\\'| grep'\\\\'>/dev/null2>&1 |
| 63 | +then |
| 64 | + BS='\' dummy='\'# BSD |
| 65 | +else |
| 66 | + BS='\\'# System V |
| 67 | +fi |
| 68 | +# The dummy assignment is necessary to prevent Emacs' font-lock |
| 69 | +# mode from going ballistic when editing this file. |
| 70 | + |
| 71 | + |
| 72 | +usage= |
| 73 | + |
| 74 | +# |
| 75 | +# Scan options. We're interested in the -h (host) and -p (port) options. |
| 76 | +# |
| 77 | +while [$#-gt 0 ];do |
| 78 | +case$1in |
| 79 | + --help) |
| 80 | + usage=t |
| 81 | +break |
| 82 | + ;; |
| 83 | + --version) |
| 84 | +echo"pg_dumpaccounts (PostgreSQL)$VERSION" |
| 85 | +exit 0 |
| 86 | + ;; |
| 87 | +--host|-h) |
| 88 | +connectopts="$connectopts -h$2" |
| 89 | +shift |
| 90 | +;; |
| 91 | + -h*) |
| 92 | + connectopts="$connectopts$1" |
| 93 | + ;; |
| 94 | + --host=*) |
| 95 | + connectopts="$connectopts -h"`echo$1| sed's/^--host=//'` |
| 96 | + ;; |
| 97 | +--port|-p) |
| 98 | +connectopts="$connectopts -p$2" |
| 99 | +shift |
| 100 | +;; |
| 101 | + -p*) |
| 102 | + connectopts="$connectopts$1" |
| 103 | + ;; |
| 104 | + --port=*) |
| 105 | + connectopts="$connectopts -p"`echo$1| sed's/^--port=//'` |
| 106 | + ;; |
| 107 | +*) |
| 108 | +echo"unknown option '$1'" |
| 109 | +usage=t |
| 110 | + ;; |
| 111 | +esac |
| 112 | +shift |
| 113 | +done |
| 114 | + |
| 115 | + |
| 116 | +if ["$usage" ];then |
| 117 | +echo"$CMDNAME dumps users and groups of a PostgreSQL database cluster." |
| 118 | +echo |
| 119 | +echo"Usage:" |
| 120 | +echo"$CMDNAME [ -c ] [ -h host ] [ -p port ]" |
| 121 | +echo |
| 122 | +echo"Options:" |
| 123 | +echo" -h, --host <hostname> server host name" |
| 124 | +echo" -p, --port <port> server port number" |
| 125 | +echo |
| 126 | +echo"Report bugs to <pgsql-bugs@postgresql.org>." |
| 127 | +exit 0 |
| 128 | +fi |
| 129 | + |
| 130 | + |
| 131 | +PSQL="${PGPATH}/psql$connectopts" |
| 132 | + |
| 133 | + |
| 134 | +echo"--" |
| 135 | +echo"-- pg_dumpaccounts ($VERSION)$connectopts" |
| 136 | +echo"--" |
| 137 | +echo"${BS}connect template1" |
| 138 | + |
| 139 | +# |
| 140 | +# Dump users (but not the user created by initdb) |
| 141 | +# |
| 142 | +echo"DELETE FROM pg_shadow WHERE usesysid NOT IN (SELECT datdba FROM pg_database WHERE datname = 'template1');" |
| 143 | +echo |
| 144 | + |
| 145 | +$PSQL -d template1 -At<<__END__ |
| 146 | +SELECT |
| 147 | + 'CREATE USER "' || usename || '" WITH SYSID ' || usesysid |
| 148 | + || CASE WHEN passwd IS NOT NULL THEN ' PASSWORD ''' || passwd || '''' else '' end |
| 149 | + || CASE WHEN usecreatedb THEN ' CREATEDB'::text ELSE ' NOCREATEDB' END |
| 150 | + || CASE WHEN usesuper THEN ' CREATEUSER'::text ELSE ' NOCREATEUSER' END |
| 151 | + || CASE WHEN valuntil IS NOT NULL THEN ' VALID UNTIL '''::text |
| 152 | + || CAST(valuntil AS TIMESTAMP) || '''' ELSE '' END || ';' |
| 153 | +FROM pg_shadow |
| 154 | +WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template1'); |
| 155 | +__END__ |
| 156 | +echo |
| 157 | + |
| 158 | +# |
| 159 | +# Dump groups |
| 160 | +# |
| 161 | +echo"DELETE FROM pg_group;" |
| 162 | +echo |
| 163 | + |
| 164 | +$PSQL -d template1 -At -F'' -c'SELECT * FROM pg_group;'| \ |
| 165 | +whileread GRONAME GROSYSID GROLIST;do |
| 166 | +echo"CREATE GROUP\"$GRONAME\" WITH SYSID${GROSYSID};" |
| 167 | + raw_grolist=`echo"$GROLIST"| sed's/^{\(.*\)}$/\1/'| tr','''` |
| 168 | +foruseridin$raw_grolist;do |
| 169 | + username=`$PSQL -d template1 -At -c"SELECT usename FROM pg_shadow WHERE usesysid =${userid};"` |
| 170 | +echo" ALTER GROUP\"$GRONAME\" ADD USER\"$username\";" |
| 171 | +done |
| 172 | +done |
| 173 | + |
| 174 | +exit 0 |
