Skip to content

Crash when one of the multiple databases is the same as the user #4

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
Amoki opened this issue Dec 13, 2017 · 4 comments
Open

Crash when one of the multiple databases is the same as the user #4

Amoki opened this issue Dec 13, 2017 · 4 comments

Comments

@Amoki
Copy link

Amoki commented Dec 13, 2017

Snippet of my config :

    postgres:
        image: postgres:10-alpine
        container_name: postgres
        ports:
            - 5432:5432
        volumes:
            - "./.data/postgresql:/var/lib/postgresql/data"
            - ./utils/postgres:/docker-entrypoint-initdb.d
        environment:
            POSTGRES_MULTIPLE_DATABASES: "service_database, service_database_test"
            POSTGRES_PASSWORD: "password"
            POSTGRES_USER: "service_database"

In this case, the postgres image will create a database named "service_database" by default, then the multi db script will try to create "service_database" and "service_database_test" but will fail on "service_database" because the database already exists.

The workaround is easy: remove "service_database" from POSTGRES_MULTIPLE_DATABASES and eventually using POSTGRES_DB but it seems hacky to have to use multiple variables.

@Amoki Amoki changed the title Crash when one of the multiple database is the same as the user Crash when one of the multiple databases is the same as the user Dec 13, 2017
@mrts
Copy link
Owner

mrts commented Dec 13, 2017

Thanks for reporting this!

Let it simmer a bit as a reasonable workaround exists - adding logic for checking if DB name matches user name can be added to the script, but I think we can postpone the effort a bit until more people vote for this.

@mrts
Copy link
Owner

mrts commented Dec 16, 2017

I think this is actually another documentation issue like #5 - would you like to provide a README update like #7 for "fixing" #5 ?

@drackp2m
Copy link

drackp2m commented Sep 1, 2023

This script works fine for me @Amoki. <3

#!/bin/bash

set -e
set -u

function user_exists() {
		local user=$1

		result=$(psql -U "$POSTGRES_USER" -tAc "SELECT 1 FROM pg_roles WHERE rolname = '${user}'" 'postgres')

		if [ -z "$result" ]; then
				return 1
		else
				return 0
		fi
}

function database_exists() {
		local database=$1

		result=$(psql -U "$POSTGRES_USER" -tAc "SELECT 1 FROM pg_database WHERE datname = '${database}'" postgres)

		if [ -z "$result" ]; then
				return 1
		else
				return 0
		fi
}

function create_user_and_database() {
		local database=$1

		echo ""
		echo "Creating '$database' database"
		echo ""

		if user_exists "$database"; then
				echo "Role '$database' already exists. Skipping..."
		else
				echo "Role '${database}' does not exist"

				psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" <<-EOSQL
					CREATE USER $database WITH PASSWORD '$POSTGRES_PASSWORD';
				EOSQL
		fi

		if database_exists "$database"; then
				echo "Database '$database' already exists. Skipping..."
		else
				echo "Database '${database}' does not exist"

				psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" <<-EOSQL
						CREATE DATABASE $database;
				EOSQL
		fi

		psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" <<-EOSQL
			GRANT SET ON PARAMETER session_replication_role TO $database;
			ALTER DATABASE $database OWNER TO $database;
		EOSQL

		echo "Successful database creation '$database'"
		echo ""
}

if [ -n "$POSTGRES_MULTIPLE_DATABASES" ]; then
	echo "Multiple database creation requested: $POSTGRES_MULTIPLE_DATABASES"
	for db in $(echo $POSTGRES_MULTIPLE_DATABASES | tr ',' ' '); do
		create_user_and_database $db
	done
	echo "Multiple databases created"
fi

@GirishBhutiya
Copy link

@drackp2m script works fine at my end but original script give me error.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants