Use latest PostgreSQL helpers

5 files changed, 169 insertions, 83 deletions

diff --git a/scripts/_common.sh b/scripts/_common.sh
index 39b0631..dce035f 100644
--- a/scripts/_common.sh
+++ b/scripts/_common.sh
@@ -1,73 +1,161 @@
 #!/bin/bash
 
-# Create a db without password
+#=================================================
 #
-# usage: ynh_mysql_create_user user
-# | arg: user - the user name to create
-ynh_psql_create_db_without_password() {
-	db=$1
-    sudo su -c "psql" postgres <<< \
-    "CREATE USER $db CREATEDB;"
+# POSTGRES HELPERS
+#
+# Point of contact : Jean-Baptiste Holcroft <jean-baptiste@holcroft.fr>
+#=================================================
+
+# Create a master password and set up global settings
+# Please always call this script in install and restore scripts
+#
+# usage: ynh_psql_test_if_first_run
+
+ynh_psql_test_if_first_run() {
+	if [ -f /etc/yunohost/psql ];
+	then
+		echo "PostgreSQL is already installed, no need to create master password"
+	else
+		pgsql=$(ynh_string_random)
+		pg_hba=""
+		echo "$pgsql" >> /etc/yunohost/psql
+
+		if [ -e /etc/postgresql/9.4/ ]
+		then
+			pg_hba=/etc/postgresql/9.4/main/pg_hba.conf
+		elif [ -e /etc/postgresql/9.6/ ]
+		then
+			pg_hba=/etc/postgresql/9.6/main/pg_hba.conf
+		else
+			ynh_die "postgresql shoud be 9.4 or 9.6"
+		fi
+
+		systemctl start postgresql
+		sudo --login --user=postgres psql -c"ALTER user postgres WITH PASSWORD '$pgsql'" postgres
+
+		# force all user to connect to local database using passwords
+		# https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html#EXAMPLE-PG-HBA.CONF
+		# Note: we can't use peer since YunoHost create users with nologin
+		#  See: https://github.com/YunoHost/yunohost/blob/unstable/data/helpers.d/user
+		sed -i '/local\s*all\s*all\s*peer/i \
+		local all all password' "$pg_hba"
+		systemctl enable postgresql
+		systemctl reload postgresql
+	fi
 }
 
-# Create a user
+# Open a connection as a user
 #
-# usage: ynh_mysql_create_user user pwd [host]
-# | arg: user - the user name to create
-# | arg: pwd - the password to identify user by
-ynh_psql_create_user() {
-        sudo su -c "psql" postgres <<< \
-        "CREATE USER ${1} WITH PASSWORD '${2}';"
+# example: ynh_psql_connect_as 'user' 'pass' <<< "UPDATE ...;"
+# example: ynh_psql_connect_as 'user' 'pass' < /path/to/file.sql
+#
+# usage: ynh_psql_connect_as user pwd [db]
+# | arg: user - the user name to connect as
+# | arg: pwd - the user password
+# | arg: db - the database to connect to
+ynh_psql_connect_as() {
+	user="$1"
+	pwd="$2"
+	db="$3"
+	sudo --login --user=postgres PGUSER="$user" PGPASSWORD="$pwd" psql "$db"
 }
 
-# Create a user without password
+# # Execute a command as root user
 #
-# usage: ynh_mysql_create_user user pwd [host]
-# | arg: user - the user name to create
-ynh_psql_create_user_without_password() {
-        sudo su -c "psql" postgres <<< \
-        "CREATE USER ${1};"
+# usage: ynh_psql_execute_as_root sql [db]
+# | arg: sql - the SQL command to execute
+# | arg: db - the database to connect to
+ynh_psql_execute_as_root () {
+	sql="$1"
+	sudo --login --user=postgres psql <<< "$sql"
+}
+
+# Execute a command from a file as root user
+#
+# usage: ynh_psql_execute_file_as_root file [db]
+# | arg: file - the file containing SQL commands
+# | arg: db - the database to connect to
+ynh_psql_execute_file_as_root() {
+	file="$1"
+	db="$2"
+	sudo --login --user=postgres psql "$db" < "$file"
 }
 
-# Create a database and grant optionnaly privilegies to a user
+# Create a database, an user and its password. Then store the password in the app's config
 #
-# usage: ynh_mysql_create_db db [user [pwd]]
+# After executing this helper, the password of the created database will be available in $db_pwd
+# It will also be stored as "psqlpwd" into the app settings.
+#
+# usage: ynh_psql_setup_db user name [pwd]
+# | arg: user - Owner of the database
+# | arg: name - Name of the database
+# | arg: pwd - Password of the database. If not given, a password will be generated
+ynh_psql_setup_db () {
+	db_user="$1"
+	app="$1"
+	db_name="$2"
+	new_db_pwd=$(ynh_string_random)	# Generate a random password
+	# If $3 is not given, use new_db_pwd instead for db_pwd.
+	db_pwd="${3:-$new_db_pwd}"
+	ynh_psql_create_db "$db_name" "$db_user" "$db_pwd"	# Create the database
+	ynh_app_setting_set "$app" psqlpwd "$db_pwd"	# Store the password in the app's config
+}
+
+# Create a database and grant privilegies to a user
+#
+# usage: ynh_psql_create_db db [user [pwd]]
 # | arg: db - the database name to create
 # | arg: user - the user to grant privilegies
-# | arg: pwd - the password to identify user by
+# | arg: pwd  - the user password
 ynh_psql_create_db() {
-    db=$1
-    # grant all privilegies to user
-    if [[ $# -gt 1 ]]; then
-        ynh_psql_create_user ${2} "${3}"
-        sudo su -c "createdb -O ${2} $db" postgres
-    else
-        sudo su -c "createdb $db" postgres
-    fi
-
+	db="$1"
+	user="$2"
+	pwd="$3"
+	ynh_psql_create_user "$user" "$pwd"
+	sudo --login --user=postgres createdb --owner="$user" "$db"
 }
 
-# Drop a role
+# Drop a database
 #
-# usage: ynh_mysql_drop_role db
+# usage: ynh_psql_drop_db db
 # | arg: db - the database name to drop
-ynh_psql_drop_role() {
-	sudo su -c "psql" postgres <<< \
-	"DROP ROLE ${1};"
+# | arg: user - the user to drop
+ynh_psql_remove_db() {
+	db="$1"
+	user="$2"
+	sudo --login --user=postgres dropdb "$db"
+	ynh_psql_drop_user "$user"
 }
 
-# Drop a database
+# Dump a database
 #
-# usage: ynh_mysql_drop_db db
-# | arg: db - the database name to drop
-ynh_psql_drop_db() {
-    sudo su -c "dropdb ${1}" postgres
+# example: ynh_psql_dump_db 'roundcube' > ./dump.sql
+#
+# usage: ynh_psql_dump_db db
+# | arg: db - the database name to dump
+# | ret: the psqldump output
+ynh_psql_dump_db() {
+	db="$1"
+	sudo --login --user=postgres pg_dump "$db"
+}
+
+
+# Create a user
+#
+# usage: ynh_psql_create_user user pwd [host]
+# | arg: user - the user name to create
+ynh_psql_create_user() {
+	user="$1"
+	pwd="$2"
+        sudo --login --user=postgres psql -c"CREATE USER $user WITH PASSWORD '$pwd'" postgres
 }
 
 # Drop a user
 #
-# usage: ynh_mysql_drop_user user
+# usage: ynh_psql_drop_user user
 # | arg: user - the user name to drop
 ynh_psql_drop_user() {
-    sudo su -c "dropuser ${1}" postgres
+	user="$1"
+	sudo --login --user=postgres dropuser "$user"
 }
diff --git a/scripts/backup b/scripts/backup
index ffcac0e..d4014c7 100644
--- a/scripts/backup
+++ b/scripts/backup
@@ -39,9 +39,8 @@ ynh_backup "/etc/apt/sources.list.d/yarn.list" "apt_yarn.list"
 sudo sed -i "s@__FINALPATH__@$final_path@g" /etc/nginx/conf.d/${domain}.d/${app}.conf
 
 # Backup db
-sudo su - postgres <<COMMANDS
-pg_dump --role=mastodon -U postgres --no-password mastodon_production > mastodon_db.sql
-COMMANDS
-ynh_backup "/var/lib/postgresql/${app}_db.sql" "${app}_db.sql"
+db_name=$(ynh_sanitize_dbid "$app")
+ynh_psql_dump_db "$db_name"
+
 # Fix backup fail on yunohost 2.6
 #ynh_secure_remove /var/lib/postgresql/mastodon_db.sql
diff --git a/scripts/install b/scripts/install
index 5b1acaa..4a23715 100644
--- a/scripts/install
+++ b/scripts/install
@@ -116,31 +116,16 @@ ynh_install_app_dependencies \
 # TODO: use non-official https://github.com/YunoHost-Apps/Experimental_helpers/blob/master/postgres/postgres
 # TODO: this commands doesn't looks like a requirement, you may fully remove it
 # Set UTF8 encoding by default
-su -c "psql" postgres <<< \
-			"update pg_database set datistemplate='false' where datname='template1';"
-su -c "psql" postgres <<< \
-			"drop database template1;"
-su -c "psql" postgres <<< \
-			"create database template1 encoding='UTF8' template template0;"
-su -c "psql" postgres <<< \
-			"update pg_database set datistemplate='true' where datname='template1';"
 
-# Create DB without password
-ynh_psql_create_db_without_password "$app"
-systemctl restart postgresql
+ynh_psql_test_if_first_run
+
+db_user=$(ynh_sanitize_dbid "$app")
+db_name=$(ynh_sanitize_dbid "$app")
+db_pwd=$(ynh_string_random)
+ynh_app_setting_set $app db_pwd $db_pwd
+ynh_psql_setup_db "$db_user" "$db_name" "$db_pwd"
 
-#=================================================
-# DOWNLOAD, CHECK AND UNPACK SOURCE
-#=================================================
 
-# TODO: dont su as $app, work root and set corrects rights at the end of install
-# Download all sources rbenv, ruby and mastodon
-(
-	su $app
-	git clone https://github.com/rbenv/rbenv.git $final_path/.rbenv
-	git clone https://github.com/rbenv/ruby-build.git $final_path/.rbenv/plugins/ruby-build
-	git clone https://github.com/tootsuite/mastodon.git $final_path/live
-)
 
 #=================================================
 # NGINX CONFIGURATION
@@ -161,6 +146,19 @@ cp ../conf/nginx.conf /etc/nginx/conf.d/$domain.d/$app.conf
 # Create user unix
 adduser $app --home /opt/$app --gecos "First Last,RoomNumber,WorkPhone,HomePhone" --disabled-password --disabled-login
 
+#=================================================
+# DOWNLOAD, CHECK AND UNPACK SOURCE
+#=================================================
+
+# TODO: dont su as $app, work root and set corrects rights at the end of install
+# Download all sources rbenv, ruby and mastodon
+(
+	su $app
+	git clone https://github.com/rbenv/rbenv.git $final_path/.rbenv
+	git clone https://github.com/rbenv/ruby-build.git $final_path/.rbenv/plugins/ruby-build
+	git clone https://github.com/tootsuite/mastodon.git $final_path/live
+)
+
 # Switch branch to tagged release
 cd $final_path/live
 version=$(curl -s https://api.github.com/repos/tootsuite/mastodon/releases/latest | grep tag_name | cut -d\" -f4)
@@ -208,8 +206,9 @@ ln -s /opt/mastodon/.rbenv/versions/2.5.0/bin/ruby /usr/bin/ruby || true
 cp -a $final_path/live/.env.production.sample $final_path/live/.env.production
 sed -i "s@REDIS_HOST=redis@REDIS_HOST=127.0.0.1@g" "${final_path}/live/.env.production"
 sed -i "s@DB_HOST=db@DB_HOST=/var/run/postgresql@g" "${final_path}/live/.env.production"
-sed -i "s@DB_USER=postgres@DB_USER=${app}@g" "${final_path}/live/.env.production"
-sed -i "s@DB_NAME=postgres@DB_NAME=${app}_production@g" "${final_path}/live/.env.production"
+sed -i "s@DB_USER=postgres@DB_USER=${db_user}@g" "${final_path}/live/.env.production"
+sed -i "s@DB_NAME=postgres@DB_NAME=${db_name}@g" "${final_path}/live/.env.production"
+sed -i "s@DB_PASS=@DB_PASS=${db_name}@g" "${final_path}/live/.env.production"
 sed -i "s@LOCAL_DOMAIN=example.com@LOCAL_DOMAIN=${domain}@g" "${final_path}/live/.env.production"
 
 language="$(echo $language | head -c 2)"
diff --git a/scripts/remove b/scripts/remove
index 6683c07..e60ed37 100644
--- a/scripts/remove
+++ b/scripts/remove
@@ -65,8 +65,9 @@ then
 fi
 
 # delete postgresql database & user
-ynh_psql_drop_db "${app}_production"
-ynh_psql_drop_role "${app}"
+db_user=$(ynh_sanitize_dbid "$app")
+db_name=$(ynh_sanitize_dbid "$app")
+ynh_psql_remove_db "$db_name" "$db_user"
 
 # Remove Debian package
 sudo apt-get remove --purge -y yarn
diff --git a/scripts/restore b/scripts/restore
index 8ca0b5a..8bad156 100644
--- a/scripts/restore
+++ b/scripts/restore
@@ -107,15 +107,14 @@ sudo chown -R $app: "$final_path"
 # Debug
 sudo ls -alh "$final_path"
 
-# Set UTF8 encoding by default
-sudo su -c "psql" postgres <<< \
-			"update pg_database set datistemplate='false' where datname='template1';"
-sudo su -c "psql" postgres <<< \
-			"drop database template1;"
-sudo su -c "psql" postgres <<< \
-			"create database template1 encoding='UTF8' template template0;"
-sudo su -c "psql" postgres <<< \
-			"update pg_database set datistemplate='true' where datname='template1';"
+# Restore PostgreSQL database
+db_user=$(ynh_sanitize_dbid "$app")
+db_name=$(ynh_sanitize_dbid "$app")
+db_pwd=$(ynh_app_setting_get "$app" db_pwd)
+
+ynh_psql_test_if_first_run
+ynh_psql_setup_db "$db_name" "$db_name" "$db_pwd"
+ynh_psql_execute_file_as_root ./db.sql "$db_name"
 
 # Install rbenv
 sudo su - $app <<COMMANDS