Home » Blogs » sandip's blog

PostgreSQL role based password authentication

Wed, 12/16/2009 - 14:30 — sandip

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&quot;}
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

You should now be able to connect using:

psql -U <DBMAINUSER> -d <DBNAME>  -h 127.0.0.1

Bookmark/Search this post with
  • del.icio.us
  • Digg
  • Facebook
  • identi.ca
  • LinkedIn
  • SlashDot
  • StumbleUpon
  • Technorati
  • Twitter
Comment