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 md5Save 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 0You should now be able to connect using:
psql -U <DBMAINUSER> -d <DBNAME> -h 127.0.0.1