Use latest PostgreSQL helpers

1 files changed, 132 insertions, 44 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"
 }