Skip to content

Have DB creation script account for username #375

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.

Sign up for GitHub

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

Merged
merged 2 commits into from
Jan 7, 2022
Merged

Have DB creation script account for username #375

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
54297f1
Have DB creation script account for username
drewmarshburn Jan 3, 2022
1708238
Merge remote-tracking branch 'upstream/main' into db-create
drewmarshburn Jan 6, 2022
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
27 changes: 16 additions & 11 deletions deploy/charts/firefly/scripts/ff-db-migrations.sh
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -3,29 +3,34 @@
# Install deps
apk add postgresql-client curl jq

echo "Provided connection string: '${PSQL_URL}'"

# Extract the database name from the end of the PSQL URL, and check it's there
DB_NAME=`echo ${PSQL_URL} | sed 's/^.*\///'`
COLONS=`echo -n $DB_NAME | sed 's/[^:]//g'`
DB_PARAMS=`echo ${PSQL_URL} | sed 's!^.*/!!'`
DB_NAME=`echo ${DB_PARAMS} | sed 's!?.*!!'`
echo "Database name: '${DB_NAME}'"
USER_NAME=`echo ${PSQL_URL} | sed 's!^.*//!!' | sed 's!:.*$!!'`
echo "Username: '${USER_NAME}'"
Copy link
Contributor

@onelapahead onelapahead Jan 3, 2022

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Does this still work if the DB doesn't require auth? Know thats a bit unlikely / harder to test.

Copy link
Contributor Author

@drewmarshburn drewmarshburn Jan 3, 2022

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Yeah that's a good point. Now, we need connection strings in at least this format:

"postgresql://[username]:[password]/[database]"

Looking at their doc (https://www.postgresql.org/docs/current/libpq-connect.html), supporting all formats of connection strings would be pretty difficult. Wondering what we need to support here? @peterbroadhurst and @hfuss any insights from what you've previously deployed? It may also be easier to provide the PSQL connection info as separate chart values instead of winding up with all this sed echo fun | sed 's/fun/suffering/'.

Copy link
Contributor

@onelapahead onelapahead Jan 6, 2022

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I think assuming the PSQL string will have basic auth creds is fine for now. Most users are going to have a setup like that when using the chart.

COLONS=`echo -n $DB_NAME | sed 's/[^:]//g'`
if [ -z "${DB_NAME}" ] || [ -n "${COLONS}" ]
then
echo "Postgres URL does not appear to contain a database name"
echo "Error: Postgres URL does not appear to contain a database name (required)."
exit 1
fi

# Build a URL that doesn't have the database name
PSQL_URL_NO_DB=`echo ${PSQL_URL} | sed "s/\/${DB_NAME}//"`

# Check we can connect to the PSQL Server
until psql -c "SELECT 1;" ${PSQL_URL_NO_DB}; do
echo "Waiting for database..."
# Check we can connect to the PSQL server using the default "postgres" database
PSQL_SERVER=`echo ${PSQL_URL} | sed "s!${DB_PARAMS}!!"`postgres
echo "PSQL server URL: '${PSQL_SERVER}'"
until psql -c "SELECT 1;" ${PSQL_SERVER}; do
echo "Waiting for PSQL server connection..."
sleep 1
done

# Create the database if it doesn't exist
if ! psql -c "SELECT datname FROM pg_database WHERE datname = '${DB_NAME}';" ${PSQL_URL_NO_DB} | grep ${DB_NAME}
if ! psql -c "SELECT datname FROM pg_database WHERE datname = '${DB_NAME}';" ${PSQL_SERVER} | grep ${DB_NAME}
then
psql -c "CREATE DATABASE ${DB_NAME};" ${PSQL_URL_NO_DB}
echo "Database '${DB_NAME}' does not exist; creating."
psql -c "CREATE DATABASE \"${DB_NAME}\" WITH OWNER \"${USER_NAME}\";" ${PSQL_SERVER}
fi

# Wait for the database itself to be available
Expand Down