Databases

There have been countless times where I have a local snapshot of a site I'm developing, and want to push the database either out to the remote site, or pull it in to my local sandbox.

This script allows you to synchronize a remote MySQL database to/from your local MySQL database using SSH.

Invoke the script without any arguments to read the usage. (Or read below!)

# Copyright (c) 2010 Ruben Rodriguez (cha...@therealcha0s.net)
#
# Permission is hereby granted, free of charge, to any person
# obtaining a copy of this software and associated documentation
# files (the "Software"), to deal in the Software without
# restriction, including without limitation the rights to use,
# copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following
# conditions:
#
# The above copyright notice and this permission notice shall be
# included in all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
# WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
# OTHER DEALINGS IN THE SOFTWARE.

if test -z $1; then
	echo "Usage: db-sync [OPTIONS] DIRECTION HOST DB_NAME [DB_USER] [REMOTE_DB_NAME]"
	echo "[REMOTE_DB_USER]"
	echo
	echo "Bidrectionally synchronize a MySQL database with your local MySQL"
	echo "database."
	echo
	echo "Options:"
	echo "-v, --verbose  Verbose output."
	echo "--remote-args  Arguments to pass to the remote mysql server."
	echo "--local-args   Arguments to pass to the local mysql server."
	echo
	echo "DIRECTION can be 'in' or 'out'. 'in' synchronizes the local database with the"
	echo "remote database, and out synchronizes the remote database with the local"
	echo "database."
	echo
	echo "If DB_USER is not specified, it is copied from DB_NAME."
	echo "If REMOTE_DB_NAME is not specified, it is copied from DB_NAME."
	echo "If REMOTE_DB_USER is not specified, it is copied from REMOTE_DB_NAME."
	echo
	exit
fi

while [ 1 ]; do
	
	case "$1" in
		"-v"|"--verbose")
			verbose=1
			shift
		;;
		"--local-args")
			shift
			
			local_args=$1
			shift
		;;
		"--remote-args")
			shift
			
			remote_args=$1
			shift
		;;
		*)
			break
		;;
	esac	
done

direction=$1
shift

host=$1
shift

db=$1
shift

user=$1
shift
if test -z "$user"; then user=$db; fi

rdb=$1
shift
if test -z "$rdb"; then rdb=$db; fi

ruser=$1
shift
if test -z "$ruser"; then ruser=$rdb; fi

# Make sure if the program dies while the echo is locked (to prevent echo'ing
# sensitive data) that we'll release it.
trap 'stty echo' INT TERM EXIT

stty -echo
stty echonl
	
	echo -n Input the local db password:\ 
	read -r pass
	
	echo -n Input the remote db password:\ 
	read -r rpass
	
stty echo

echo
if [ "$direction" = "out" ]; then
	echo Synchronizing database "$ruser@$host/$rdb from $user@localhost/$db"
	if [ "$verbose" = "1" ]; then
		echo mysqldump $local_args -u $user -p[...] $db \| ssh -C $host "cat | mysql $remote_args -u $ruser -p[...] $rdb"
	fi
	
	mysqldump $local_args -u $user -p"$pass" $db | ssh -C $host "cat | mysql $remote_args -u $ruser -p\'$rpass\' $rdb"
else
	echo Synchronizing database "$user@localhost/$db from $ruser@$host/$rdb"
	if [ "$verbose" = "1" ]; then
		echo ssh -C $host "mysqldump $remote_args -u $ruser -p[...] $rdb" \| mysql $local_args -u $user -p[...] $db
	fi
	
	ssh -C $host "mysqldump $remote_args -u $ruser -p'$rpass' $rdb" | mysql $local_args -u $user -p"$pass" $db
fi

Have you ever tried to grant all privileges on a database in Postgre, only to find that "GRANT ALL PRIVILEGES ON DATABASE foo TO bar" doesn't actually do that?

PgSQL makes it quite hard to grant all privileges on a database to a user, because what they don't tell you up front is that in order to get this done as you'd expect, you have to grant the privileges per table to the user.

I have written a script called pg-help that does all the nasty dirty work for you! You can use it to grant all privileges on a database to a user frighteningly easy. Feel free to comment if you found this script useful. Thanks!

First, I'll show usage:

pg-help grant all mydb myuser

Yes, it's that simple! To specify more granular privileges (and in this case, say don't grant privileges to sequences) try this:

pg-help grant --no-sequence "SELECT, UPDATE" mydb myuser

Piece of cake!

So, here's the script:

#!/bin/bash

## Thanks to the user in #postgresql, RhodiumToad :)

function usage {
	echo "Postgres database-wide privilege operations by cha0s"
	echo "Usage: pg-help [OPTION] {grant|revoke} PRIVILEGES DATABASE USERNAME"
	
	echo "Options:"
	echo "  --help                              Print this usage and exit."  
	echo "  -v, --verbose                       Print the executed SQL."  
	echo "  -nt, --no-table                     Don't affect tables."  
	echo "  -ns, --no-sequence                  Don't affect sequences."  
	echo "  -nv, --no-view                      Don't affect views."  
	echo
	echo "PRIVILEGES may either be 'all' (which is translated to 'all privileges'), or a"
	echo "QUOTED comma-delimited list of privileges to affect. Privileges are:"
	echo "SELECT INSERT UPDATE DELETE RULE REFERENCES TRIGGER"
	echo
	echo "DATABASE is your Postgres database name."
	echo
	echo "USERNAME is your Postgres user name."
	echo
	echo "It is assumed that this script will be run as a user with enough privileges to"
	echo "effect the changes."
	echo
	echo "Report bugs, feature requests, love letters to cha...@therealcha0s.net "
	
	exit
}

## Parse args
until test -z "$1"; do
	case "$1" in
		--help)
			usage
		;;
		--verbose | -v)
			verbose=1
		;;
		--no-table | -nt)
			nt=1
		;;
		--no-sequence | -ns)
			ns=1
		;;
		--no-view | -nv)
			nv=1
		;;
		*)
			break;
		;;
	esac
	shift
done

case "$1" in
	grant | g)
		OP="grant"
		OP_PREP="to"
	;;
	revoke | r)
		OP="revoke"
		OP_PREP="from"
	;;
	*)
		echo "Invalid operation specified." >&2
		usage
	;;
esac

if test -z "$2"; then
	echo "No privileges specified." >&2
	usage
fi

if test "$2" = "all"; then
	OP_PRIV="all privileges"
else
	OP_PRIV=$2
fi

if test -z "$3"; then
	echo "No database specified." >&2
	usage
fi

DB_NAME=$3

if test -z "$4"; then
	echo "No user specified." >&2
	usage
fi

DB_USER=$4

if test -z "$nt"; then
	REL_TABLE="r"
fi
if test -z "$ns"; then
	REL_SEQ="S"
fi
if test -z "$nv"; then
	REL_VIEW="v"
fi

TABLE_SQL="SELECT array_to_string(
	ARRAY(
		SELECT quote_ident(c.relname) 
		FROM pg_catalog.pg_class c 
		LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
		WHERE c.relkind IN ('${REL_TABLE}', '${REL_SEQ}', '${REL_VIEW}')
		AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
		AND pg_catalog.pg_table_is_visible(c.oid)
	), 
	', '
);"

TABLE_LIST=`psql -t -c "${TABLE_SQL}" ${DB_NAME}`
if test -z "$TABLE_LIST" || test "$TABLE_LIST" = " "; then
	echo "No tables were selected based on the parameters specified." >&2
	echo "Invoke with the --help parameter to view usage details." >&2
	exit
fi

OP_SQL="${OP} ${OP_PRIV} on ${TABLE_LIST} ${OP_PREP} ${DB_USER}" 

if test -n "$verbose"; then
	echo $OP_SQL
fi
psql -c "${OP_SQL}" ${DB_NAME}

Enjoy! ^_^