====== Postgresql create database user ======

===== Autoriser les connections authentifiées en local pour posgresql =====

<code bash>

export PGSQL_DB_NAME=db_mydbname
export PGSQL_USERNAME=myusername
export PGSQL_USERPWD=mypassword


apt install postgresql
cp -a /etc/postgresql/13/main/pg_hba.conf /etc/postgresql/13/main/pg_hba.conf_
sed -i "s/local   all             all                                     peer/local   all             all                                     md5/g" /etc/postgresql/11/main/pg_hba.conf
service postgresql stop
service postgresql start


cd /
/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}\""

</code>

===== Test =====

<code bash>
/bin/su postgres --command "psql --command=\"\d\" --username=${PGSQL_USERNAME} --dbname=${PGSQL_DB_NAME}"
</code>

===== 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 =====

<code bash>
su - postgres
psql -U postgres
\l
DROP DATABASE xys;
\q
</code>

===== Cheat Sheets =====
  * http://www.ixany.org/docs/PostgreSQL_commandes_mysql.html
  * https://tomcam.github.io/postgres/