dockerfile postgresl create role

Question

PostgreSQL 10

I'm trying to create a role in a docker container on launch

I've tried creating the role like so:

gosu postgres pg_ctl start -D /var/lib/postgresql/data

if [ "${PGUSER}" != 'postgres' ]; then
  gosu postgres psql --command="CREATE USER ${PGUSER} SUPERUSER WITH PASSWORD '${PGPASSWORD}';" --echo-hidden --echo-queries
fi

gosu postgres pg_ctl stop -w -D /var/lib/postgresql/data

This still gives me FATAL: role "grafadmin" does not exist

When attempting to create a database with:

gosu postgres createdb -O grafadmin grafana

I've also tried:

cat > ${HOMEDIR}/user.sql <<SQL
DO
$body$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_catalog.pg_user
      WHERE  username = '${PGUSER}') THEN
      CREATE ROLE ${PGUSER} WITH SUPERUSER PASSWORD '${PGPASSWORD}';
   END IF;
END
$body$;
SQL
cat ${HOMEDIR}/user.sql
gosu postgres psql --file="${HOMEDIR}/user.sql" --echo-hidden --echo-queries

I'm probably overlooking something simple ...

score 0 · Answer 1 · answered Oct 24 '17 at 18:14

Prefer CREATE USER instead of CREATE ROLE when you create user that can log in. It will add an extra parameter LOGIN. Without it you cannot login.

cat > ${HOMEDIR}/user.sql <<SQL
DO
$body$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_catalog.pg_user
      WHERE  username = '${PGUSER}') THEN
      CREATE USER ${PGUSER} WITH SUPERUSER PASSWORD '${PGPASSWORD}';
   END IF;
END
$body$;
SQL
cat ${HOMEDIR}/user.sql
gosu postgres psql --file="${HOMEDIR}/user.sql" --echo-hidden --echo-queries

for more see : https://www.postgresql.org/docs/10/static/sql-createuser.html

dockerfile postgresl create role

1 Answers1