PostgreSQL role based password authentication

Submitted by sandip on Wed, 12/16/2009 - 14:30

To manage/setup role based password authentication for postgresql database.

Modify "/var/lib/pgsql/data/pg_hba.conf" and include:

host    samerole         all         127.0.0.1/32         md5

Save the below script and run:

./db_setup.sh <DBNAME> <DBMAINUSER> <DBMAINUSERPASS> create

#!/bin/bash
# db_setup.sh
 
USAGE="Usage: $0 <DBNAME> <DBMAINUSER> <DBMAINUSERPASS> <create|drop>"
DBNAME=${1?"$USAGE"}
DBMAINUSER=${2?"$USAGE"}
DBMAINUSERPASS=${3?"$USAGE"}
 
# Create new database + main user
#
create_db_and_mainuser() {
psql -U postgres template1 -f - <<EOT
 
CREATE ROLE ${DBNAME} NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
CREATE ROLE ${DBMAINUSER} NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ENCRYPTED PASSWORD '${DBMAINUSERPASS}';
GRANT ${DBNAME} TO ${DBMAINUSER};
CREATE DATABASE ${DBNAME} WITH OWNER=${DBMAINUSER};
 
EOT
}
 
# Remove database + main user 
#
drop_db_and_mainuser() {
psql -U postgres template1 -f - <<EOT
 
-- TERMINATE CONNECTIONS OF ALL USERS CONNECTED TO <DBNAME>
DROP DATABASE ${DBNAME};
DROP ROLE ${DBMAINUSER};
DROP ROLE ${DBNAME};
 
EOT
}
 
# Main
case "$4" in
  create)
    create_db_and_mainuser
    ;;
  drop)
    drop_db_and_mainuser
    ;;
  *) 
    echo $USAGE
    exit 1
    ;;
esac
 
exit 0