Postgresql create database user

Autoriser les connections authentifiées en local pour posgresql

# cp -a /etc/postgresql/9.4/main/pg_hba.conf /etc/postgresql/9.4/main/pg_hba.conf_
# sed -i "s/local all all peer/local all all md5/g" /etc/postgresql/9.4/main/pg_hba.conf
# service postgresql stop
# service postgresql start

PGSQL_DB_NAME=db_horde
PGSQL_USERNAME=hordeu
PGSQL_USERPWD=password

/bin/su postgres --command "/usr/bin/createuser --no-superuser --no-createdb --no-createrole --login ${PGSQL_USERNAME}"
/bin/su postgres --command "/usr/bin/psql --command=\"ALTER USER ${PGSQL_USERNAME} PASSWORD '${PGSQL_USERPWD}'\""
/bin/su postgres --command "/usr/bin/createdb --encoding UTF8 --owner ${PGSQL_USERNAME} ${PGSQL_DB_NAME}"
/bin/su postgres --command "/usr/bin/psql --command \"GRANT ALL PRIVILEGES ON DATABASE ${PGSQL_DB_NAME} to ${PGSQL_USERNAME}\""

Test

# /bin/su postgres --command "psql --command=\"\d\" --username=${PGSQL_USERNAME} --dbname=${PGSQL_DB_NAME}"

MEMO

drop database : /bin/su postgres –command “/usr/bin/dropdb ${PGSQL_DB_NAME}”
drop user : /bin/su postgres –command “/usr/bin/dropuser ${PGSQL_USERNAME}”
list databases : /bin/su postgres –command “/usr/bin/psql –command \”\list\“”
list tables : /bin/su postgres –command “/usr/bin/psql –command \”\connect ${PGSQL_DB_NAME};\d;\“”
list users : /bin/su postgres –command “/usr/bin/psql –command \”\du\“”
list privileges : /bin/su postgres –command “/usr/bin/psql –command \”\l\“”

DROP DB

# su - postgres
# psql -U postgres
$ \l
$ DROP DATABASE xys;
$ \q