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