POSTGRES_USER should not be superuser

Please make the user / database setup in line with the mysql image. Default database owned by superuser is most likely not what most would expect from a service.

this is a bad idea. Please leave it as a superuser. A lot of applications need to be able to enable extensions such as uuid support. There is no clear way to do something like that without the user being a superuser.

An example /docker-entrypoint-initdb.d/pg_buffercache.sh, where POSTGRES_SUPERUSER=postgres. Not hard at all...

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username="$POSTGRES_SUPERUSER" --dbname="$POSTGRES_DB" <<-'EOSQL'
	CREATE EXTENSION IF NOT EXISTS pg_buffercache;
EOSQL

Common sense tells us that using the superuser by default is a security risk.

I think we're too late to change the default here (and tend to agree that by default, SUPERUSER is probably expected), but I'm definitely +1 to adding an easy way to opt out of that! (something like POSTGRES_USER_NOT_SUPER -- definitely want to hear alternate name suggestions for that)

I'd love to hear from Postgres people on whether they think a default of superuser is "to be expected". (Is it a safe default? Because otherwise it should be treated as a security issue and fixed.) I mean, it'd be great to hear it from the horse's mouth...

…

On 26 Jan 2017 07:31, "Tianon Gravi" ***@***.***> wrote: I think we're too late to change the default here (and tend to agree that by default, SUPERUSER is probably expected), but I'm definitely +1 to adding an easy way to opt out of that! (something like POSTGRES_USER_NOT_SUPER -- definitely want to hear alternate name suggestions for that) — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#175 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAhf6z1A-bLrL2Ms6NH79XkyRGRbgUq_ks5rV9tggaJpZM4JLTjI> .

Any news on this, would be great to have an option to create a user without superuser rights.

The security model of PostgreSQL is complex but in operation

The DBA needs to become a superuser at times but all other humans / processes can be forced to have the weakest permissions that enable them to fulfill the tasks dictated by their role.
PostgreSQL servers (before Docker, anyhow) usually aren't sitting out on the open internet with an IP address known or discoverable by an attacker. They'd be connected to a Django or Rails or similar web application framework that did all the communication with end users and/or provided an API to them and connect to PostgreSQL via some object-relation mapping.

So I as a system integrator deploying PostgreSQL servers in containers using an "official" image would expect that I could log into the command line of a running container with docker exec as a PostgreSQL super-user if I had to fix something, at least in development and test. That might not be necessary in production. But I wouldn't expect port 5432 to be accessible outside of the local Docker network to any account in the PostgreSQL server.

I don't understand that argument, because you can easily do (where POSTGRES_SUPERUSER=postgres):

psql --username="$POSTGRES_SUPERUSER"

Things should definitely be secure by default.

PostgreSQL servers (before Docker, anyhow) usually aren't sitting out on the open internet with an IP address known or discoverable by an attacker.

Security by obscurity?

@teohhanhui

@teohhanhui More like only allowing intra-server connections via Unix sockets - PostgreSQL service isn't even listening on localhost or the internet. With Docker, that corresponds to only listening on the internal Docker network.

I can't see a reason to host a PostgreSQL server listening on the internet unless you're a cloud provider and you're selling the service. If you're using PostgreSQL you should be the only one capable of connecting to it.

@znmeb

@znmeb You are right about your arguments, but you are still increasing the attack surface - if my app/service does not need super-user rights at runtime (and it doesn't) it should not have them.

@Globegitter

@Globegitter Absolutely! A lot of this is a carry-over from the Linux-Apache-RDBMS-PHP model where everything lived on a single server and only Apache talked to the user and only PHP talked to Apache and the RDBMS. In the Docker model, you have a lot more flexibility.

@teohhanhui

How about a way to pass in a list of extensions to create / enable via something more generic of what @teohhanhui recommended?

I agree superuser is likely a security issue... but anything that requires a superuser needs to have a way to have those actions performed without users forking and editing the postgres base container. (aka Bob's old-postgresql-with-uuid-suppport)

#!/bin/bash
set -e

for EXT in $POSTGRES_EXTENSIONS; do
	psql -v ON_ERROR_STOP=1 --username="$POSTGRES_SUPERUSER" --dbname="$POSTGRES_DB" <<-'EOSQL'
		CREATE EXTENSION IF NOT EXISTS $EXT;
	EOSQL
done

That's a pretty minimal example that needs more work though.
Maybe validate aginst pg_available_extensions and display a friendly warning?

Then in the environment:
POSTGRES_EXTENSIONS="uuid pg_buffercache etc"

@avongluck-r1soft

@avongluck-r1soft I don't see how that's better than #175 (comment)

We shouldn't create more shortcuts to do the same thing. But maybe it'd be great to have helper scripts like docker-php-ext-install, perhaps docker-postgres-ext-create.

@embray

@embray actually, according to @oschusler (who created the dockerfile I quoted) the script was heavily inspired by the script on the dockerhub readme.

Cpt. Obvious here who just wants to note that USER postgres used and explained in #175 (comment) above seems obsoleted by the current pg 13+ images as the daemon already runs as non-root with UID 999, which is a postgres user in the container.

using /docker-entrypoint-initdb.d/... to get exactly the desired behavior rather than adding even more custom behavior to the default image

I totally get this, and I'm sure there are requests to add env var configs for all sorts of things. However, this specific use case (wanting to create a non-superuser in addition to the superuser specified by POSTGRES_USER) happens pretty much 100% of the time in my shop and I can only assume/hope that pattern is universal. Absolutely this can be done using docker-entrypoint-initdb.d scripts (that's what we do), but since it's such a common use case/best practice it would be really nice to be able to do this with an env var like masterBrog or quantumkoen suggested (POSTGRES_DB_USER/POSTGRES_NON_ROOT_USER).

Exactly what @shane-axiom said - this is (and very much should be) a common enough use case to justify including environment variables to let us do this in a couple lines in a docker-compose file (for example) vs having to write an entrypoint script every time.

What if we added something like DOCKER_INITDB_EXTRA_SQL or something for a block of explicit SQL code to invoke in addition to (/ after) /docker-entrypoint-initdb.d?

What if we added something like DOCKER_INITDB_EXTRA_SQL or something for a block of explicit SQL code to invoke in addition to (/ after) /docker-entrypoint-initdb.d?

Makes sense to me and would cover a wide range of use cases.

What if we added something like DOCKER_INITDB_EXTRA_SQL or something for a block of explicit SQL code to invoke in addition to (/ after) /docker-entrypoint-initdb.d?

That could be useful, but I still think there should be an environment variable explicitly for this use case. Ideally almost every time this image is used this environment variable would be used, as it's a significant security benefit and very few apps need superuser access to the database. Therefore, I feel it's certainly worth its own environment variable.

I particularly like #175 (comment) 's suggestion we use POSTGRES_SUPERUSER together with POSTGRES_USER to create the nonprivileged user. Doesn't break anybody's existing setups and allows a superuser and an unprivileged user to be created together.

I'm adding notes to our deployment tools about how using POSTGRES_USER to create the users used by apps is bad practice due to security concerns etc and thought I'd ping here. I agree that ideally we'd have a dedicated env var for non-superuser creation since it's an almost universal use case.

In the meantime, is there any reason not to put together a PR for DOCKER_INITDB_EXTRA_SQL so that we have some non-file based approach to address this?

This is so weird to see the lack of one of the most commmon sense and not advanced feature in a docker image of the "most advanced" DB. Considering how well PG configuration is designed in general, it was a huge surprise for me.

For lack of a DOCKERDB_INITDB_EXTRA_SQL env var, the suggested approach to override the entrypoint and put the creation of initdb scripts into the Docker command is pretty clever:

docker run --rm -p 5432:5432 \
  -e POSTGRES_PASSWORD=asdf \
  --entrypoint "" \
  --name stupid-pg-tricks \
  postgres:16.1 \
  /bin/bash -c "echo CREATE ROLE nonsuper WITH LOGIN PASSWORD \'test\' >> /docker-entrypoint-initdb.d/50-create-nonsuper-user.sql && exec docker-entrypoint.sh postgres"

Quote management gets nasty etc, but it's possible if you really can't/don't want to template separate initdb files.

(Code for demo only, use a hashed password in CREATE ROLE or else your plain text password will be visible to anyone who can inspect the container, etc)

You can also easily adjust that to make it use an environment variable for slightly simpler quoting (and significantly simpler quoting if you're using YAML like in Compose or k8s); docker run --env FOO="CREATE ... 'test'" ... postgres bash -c 'echo "$FOO" > /docker-entrypoint-initdb.d/50-foo-env.sql && exec "$@"' -- docker-entrypoint.sh postgres

(Also using exec "$@" + -- to make arguments to postgres easier to quote correctly)

@yosifkit

I too think the current default behavior and the available "workarounds" are not ideal for the reasons that were already mentioned.
Therefore I would like to follow up on @yosifkit's suggestions.

In hindsight the POSTGRES_USER env was a poor choice of name. At this point, I think that we would break too many users by changing what access the POSTGRES_USER has by default, but as @tianon mentioned in January we are open to adding a NON_SUPER user creation similar to the MySQL, MariaDB, and PerconaDB images.

I see a two options that are mostly backwards compatible (new env names are up for debate):

add a POSTGRES_USER_NOT_SUPER env to create a regular database user with all permissions to the database specified by POSTGRES_DB (GRANT ALL ON DATABASE ... TO ...) and leave POSTGRES_USER unchanged

add a POSTGRES_SUPERUSER env and if it is provided along with POSTGRES_USER then POSTGRES_USER will not be a super user (but will have full permissions to POSTGRES_DB). If the POSTGRES_SUPERUSER is not provided then POSTGRES_USER will still be a superuser as it is currently

Even though is more complicated I am partial to number two. Basically if you only supply a single user (via POSTGRES_SUPERUSER or POSTGRES_USER) it will be a superuser, but if you supply both, then the logical one will be super and the other not. Plus we don't have to come up with a name for the non-super user variable. (the only debate I see is POSTGRES_SUPERUSER vs POSTGRES_SUPER_USER)

I like option two and would like to elaborate on it:

Introduce POSTGRES_SUPERUSER variable which is optional and defaults to POSTGRES_USER. It will be used as --username for initdb
every other variable stays unchanged (at least for now)
if POSTGRES_SUPERUSER == POSTGRES_USER: POSTGRES_USER is used as the initdb bootstrap superuser as it is now -> no breaking change but print a warning to the logs like "POSTGRES_USER has superuser permissions. Consider using a non-superuser for your application."
if POSTGRES_SUPERUSER != POSTGRES_USER: POSTGRES_USER will be created as an additional, ordinary user, with POSTGRES_PASSWORD and GRANT ALL ON DATABASE POSTGRES_DB. POSTGRES_SUPERUSER will have no password set and therefore can't use password authentication (POSTGRES_SUPERUSER_PASSWORD could be introduced but I think that use case is too rare)
maybe some time in the future change the default value for POSTGRES_SUPERUSER to "postgres" and make POSTGRES_USER required.

That is a very good plan! - I'm also in favor of option 2.

@yosifkit

Just curious if there has been any progress on this or if there is still interest?

I liked @yosifkit's proposal, but think that @masterBrog's is even more sensible:

@yosifkit just putting it out there, could there be another option:

Add two new users POSTGRES_DB_USER and POSTGRES_DB_SUPERUSER and then deprecate POSTGRES_USER. Gives backwards compatibility, encourages a move away from default superuser using deprecation, and has transparent/clear behaviour.

I just came upon this problem today, and would have loved for this to be a feature.

I think it would be convenient (and I agree with others that it would encourage better security hygiene) if something along these lines were implemented.

However, this is not prohibitively difficult to do as-is using a .sql initialization script: https://hub.docker.com/_/postgres#initialization-scripts
(Those docs even include an example of setting up a non-superuser.)
The initialization script can be injected (without overriding the entrypoint or building a new image) using a secret or bind mount.

Navigation Menu

Repository navigation

POSTGRES_USER should not be superuser #175

17 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

POSTGRES_USER should not be superuser #175

Description

Activity

avongluck-r1soft commented on Jan 19, 2017

teohhanhui commented on Jan 20, 2017

teohhanhui commented on Jan 20, 2017

tianon commented on Jan 25, 2017

teohhanhui commented on Jan 26, 2017

Globegitter commented on Mar 10, 2017

znmeb commented on Mar 10, 2017

teohhanhui commented on Mar 13, 2017

teohhanhui commented on Mar 13, 2017

znmeb commented on Mar 14, 2017

Globegitter commented on Mar 14, 2017

znmeb commented on Mar 14, 2017

avongluck-r1soft commented on Apr 18, 2017

avongluck-r1soft commented on Apr 18, 2017

teohhanhui commented on Apr 20, 2017

17 remaining items

quantumkoen commented on Apr 11, 2019

tuttle commented on Mar 13, 2021

srstsavage commented on Oct 1, 2021

virtualdxs commented on Nov 9, 2021

tianon commented on Feb 17, 2022

srstsavage commented on Feb 17, 2022

virtualdxs commented on Feb 17, 2022

srstsavage commented on Apr 6, 2022

aristofun commented on Jun 16, 2022

srstsavage commented on Jan 18, 2024

tianon commented on Jan 18, 2024

jojoob commented on Feb 17, 2025

peterthomassen commented on Feb 17, 2025

victor-sm commented on Nov 13, 2025

PaulSD commented on Jan 4, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions