mgabarda.com
Docker images for postgres extended with management bash scripts.
1#!/usr/bin/env bash
2
3
4### Creates a read only user.
5###
6### Usage:
7### $ docker-compose -f <environment>.yml (exec |run --rm) postgres createreaduser
8
9
10set -o errexit
11set -o nounset
12
13working_dir="$(dirname ${0})"
14source "${working_dir}/_sourced/constants.sh"
15source "${working_dir}/_sourced/messages.sh"
16
17if [ -z "$POSTGRES_READ_ONLY_USER" ] || [ -z "$POSTGRES_READ_ONLY_PASSWORD" ] ; then
18 message_error "The environment variables POSTGRES_READ_ONLY_USER and POSTGRES_READ_ONLY_PASSWORD should be configured."
19 exit 1
20fi
21
22export PGHOST="${POSTGRES_HOST}"
23export PGPORT="${POSTGRES_PORT}"
24export PGUSER="${POSTGRES_USER}"
25export PGPASSWORD="${POSTGRES_PASSWORD}"
26export PGDATABASE="${POSTGRES_DB}"
27
28message_info "Creating read only user for '${POSTGRES_DB}' database..."
29
30psql -tc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = '${POSTGRES_READ_ONLY_USER}'" | grep -q 1 || psql -c "CREATE ROLE \"${POSTGRES_READ_ONLY_USER}\" WITH LOGIN PASSWORD '${POSTGRES_READ_ONLY_PASSWORD}' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';"
31
32message_info "Grant permission to '${POSTGRES_READ_ONLY_USER}' user..."
33
34psql -v ON_ERROR_STOP=1 <<-EOSQL
35 GRANT CONNECT ON DATABASE ${POSTGRES_DB} TO "${POSTGRES_READ_ONLY_USER}";
36 GRANT USAGE ON SCHEMA public TO "${POSTGRES_READ_ONLY_USER}";
37 GRANT SELECT ON ALL TABLES IN SCHEMA public TO "${POSTGRES_READ_ONLY_USER}";
38 GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "${POSTGRES_READ_ONLY_USER}";
39EOSQL
40
41message_success "The user '${POSTGRES_READ_ONLY_USER}' has been created with read-only permissions."