Behaviour change between 9.5.20 and 9.5.21?
vpzomtrrfrt opened this issue · 48 comments
When running docker run --rm postgres:9.5.20-alpine, the database successfully starts up, but with docker run --rm postgres:9.5.21-alpine, I get this output instead:
Error: Database is uninitialized and superuser password is not specified.
You must specify POSTGRES_PASSWORD for the superuser. Use
"-e POSTGRES_PASSWORD=password" to set it in "docker run".
You may also use POSTGRES_HOST_AUTH_METHOD=trust to allow all connections
without a password. This is *not* recommended. See PostgreSQL
documentation about "trust":
https://www.postgresql.org/docs/current/auth-trust.html
Is this an expected change?
Based on 42ce743 it seems like it's intentional, but I find it odd to change something like that on a minor release
I just observed the same issue when bumping 9.6.16 => 9.6.17.
See also #658, the PR that seems to have introduced this
We were convinced to make it more secure by default and were aware that this would break some users. Apologies for breaking changes. 🙇♂️ We felt that the increased security out-weighed the breakage. See #658 and #580.
If you know that you want to be insecure (i.e. any one can connect without a password from anywhere), then POSTGRES_HOST_AUTH_METHOD=trust is how you opt in to that.
We really recommend setting a password and it was really a bad decision by me in the beginning to continue to allow running the database without a password (#31 (comment) & #36)
I wish a breaking change was not pushed out on a Friday. This broke our CI/CD pipeline, which I had to fix on a Saturday.
Where in a CI/CD pipeline does one place those lines -e POSTGRES_PASSWORD=password or POSTGRES_HOST_AUTH_METHOD=trust ?
As always emotions fly high when a breaking change is introduced, causing work for others ;)
I appreciate the effort that you put into maintaining this image!
Maybe one can put the fix to add "POSTGRES_HOST_AUTH_METHOD=trust" somewhere more prominent. I get it that its part of the docker output when you start the image, yet sadly not if you start it with "-d". Then you are just wondering why the next docker command complains that the database is not up ;)
@CloudArchipelago: It depends on what pipeline you are using. The two that I'm familiar with is GitHub and BitBucket:
- For GitHub, I think you put the
POSTGRES_HOST_AUTH_METHOD=trustin theenvsection shown here: https://github.com/actions/example-services/blob/master/.github/workflows/postgres-service.yml - We use BitBucket, and it goes in the
environmentsection shown here: https://community.atlassian.com/t5/Bitbucket-questions/How-do-I-use-Postgres-in-Bitbucket-Pipelines/qaq-p/461910
(Of course, these articles and examples will need to be updated, since they no longer work as shown.)
I don't understand exactly how these pipelines actually configure and start the Postgres service in docker. So I don't know where the -e POSTGRES_PASSWORD=password would go.
And to be honest, I don't fully understand exactly what POSTGRES_HOST_AUTH_METHOD=trust does either since it's the first time I'm seeing it. I can probably figure it out in a few hours, but y'know, it's the long weekend. I got family to take care of, out of town guests visiting, housework to do, shopping to do, don't got time to spend on this...
Was or is there consideration to switch to something like semantic versioning? If that was the case then this change, given it's intentional and a breaking changes, would cause a major revision change and everyone could deal with it accordingly. If such a change comes through not as a major, not even as a minor but merely as a patch release (z in x.y.z), then it is highly surprising and that is what trips up processes. Just putting it out there for consideration.
Tried @bxparks proposed workaround for our CircleCI build (we use Github) and it worked. Simply add POSTGRES_HOST_AUTH_METHOD: trust.
- image: circleci/postgres:9.6-postgis-ram
environment:
POSTGRES_USER: 'username'
POSTGRES_HOST_AUTH_METHOD: trust
We were just burnt by this change and would give a big +1 to adopting semver to help mitigate this in the future.
However, I'm not posting just to pile on about semver, I have one other thing I'd like to point out. When our CI pipelines starting running with this newer container image (without POSTGRES_HOST_AUTH_METHOD or POSTGRES_PASSWORD) set the postgres container looked like it was coming up and staying up correctly. Because it wasn't clear the postgres container was the issue we spent a lot of time and effort looking in the wrong place.
In future, if a change is made where the database effectively doesn't work, the container should not just sit there happily doing nothing when it's actually broken. Had the postgres container been more noticeably broken we could have saved most of the time and effort we spent looking in the wrong place for the cause of the issue.
As a slight aside, is there an official we can monitor to know about future significant or breaking changes to this container in future?
Same here, broken our CI and local development
Just change the PostgreSQL image to a more specific version, not the latest. E.g. changing to 9.5.18 fixed the problem for me
Can I also agree that some more documentation on this would have been great. Perhaps a changes section on docker hub? Our local CI broke due to this and while the change is sensible and fixing the issue important some more sign posting would have been great.
How about making it a major release then instad of defining it a patch release?
It's a breaking change after all.
This is not a change you want to make on a minor release, you morons... That's why semver exists.
Edit: you can downvote this comment all you want. Hell, please, go on and use all the available emojis. I couldn't care less. I hate when people randomly decide to make breaking changes without issuing warnings, which then makes me waste my time figuring out who-broke-what-and-where.
This ins't something that anybody asked for, and making the change in that way was absolutely the no-no way. drops mic
"Moron" he said ... not sure why you have to lose your manners ... @alexandernst Isn't it your fault that you did not pin the version?
Just about the versioning. The docker tag for example "9.5.21" means "Postgres 9.5", with Docker Hub Release 21. So the docker images are not semver versioned and therefore it is everyones duty to pin the exact version if he cannot live with something breaking now and then ...
One could introduce semver, producing docker tags like "9.5.1.2.3" but I am pretty sure that is not what you want. As one could assume this kind of versioning and maintaining all releases is extremely time consuming and people would still not pin "9.5.1.2.3" and instead use "9.5"...
Oh, it my fault that now we're introducing breaking changes in minor versions and without issuing a proper notice? Sure :)
PD: How about no?
to be honest, it would make more sense not to use something that looks a little like semver if you aren't going to follow its standards (which is totally understandable choice).
perhaps 9.5-build.21 would be a better approach to maintain semver but keep the releasing mechanism?
postgres:9.5.21 contains PostgreSQL version 9.5.21: https://www.postgresql.org/docs/9.5/release-9-5-21.html
+1 @chickahoona for remaining civil. No one needs to be calling our gracious maintainers names.
@Nadock docker-compose ps will show that the database failed to start:
@yosifkit this was the right decision to make in the long run, but could have been rolled out better. Maybe a warning message for a couple weeks, then push the change?
In any case, the fix is easy once you find this issue- POSTGRES_HOST_AUTH_METHOD: trust.
Not sure if this will help anyone and I apologize if this is the wrong place to post this, but here is how I solved the issue without having to set POSTGRES_HOST_AUTH_METHOD: trust (which was not recommended above)
- For my Rails setup I added a password to the test database in the
database.ymlfile:
test:
adapter: postgresql
encoding: unicode
database: mytestdatabase
pool: 10
username:
password: <%= ENV['POSTGRES_TEST_DB_PASSWORD'] %>
- Then I added that as a CCI env variable to the CCi
config.ymlfile:
docker:
- image: circleci/ruby:2.6.5-node
environment:
BUNDLE_JOBS: 3
BUNDLE_RETRY: 3
BUNDLE_PATH: vendor/bundle
PGHOST: 127.0.0.1
PGUSER: my-user
RAILS_ENV: test
- image: postgres:9.6-alpine
environment:
POSTGRES_USER: my-user
POSTGRES_DB: mytestdatabase
POSTGRES_PASSWORD: $POSTGRES_TEST_DB_PASSWORD
- Then I went to the CCi dashboard for my project and added that password to
POSTGRES_TEST_DB_PASSWORDas an environment variable. This worked perfectly for me. This was a SUPER annoying issue and I hope this will help others.
Does it really matter if one is using the trust directive if it’s just running tests in a container that then is being shut down? Asking for a friend 👀🤭
+1 @chickahoona for remaining civil. No one needs to be calling our gracious maintainers names.
Yes, thank you! Well said! Everyone who helps maintaining Postgres and the containers for them are human beings. They deserve to be treated with respect.
Does it really matter if one is using the trust directive if it’s just running tests in a container that then is being shut down? Asking for a friend 👀🤭
This is probably true, I just opted for a "better safe than sorry" approach, mainly given that I've seen it mentioned above that that fix was not necessarily recommended. Plus, it was super easy to implement.
I am utilizing a docker-compose setup on a Django application, and it seems this change is preventing the db service from even starting. I keep getting
psycopg2.OperationalError: could not connect to server: Connection refused Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 8458?
because of course it's not running. But I tried adding a password entry to my .env, to my docker-compose.yml and even in the DATABASES entry in my settings.py, with no success.
Any suggestions to where I could add those lines?
@vitorcsantos adding the password as environment var in the compose file worked for me
https://hub.docker.com/_/postgres
Tried @bxparks proposed workaround for our CircleCI build (we use Github) and it worked. Simply add
POSTGRES_HOST_AUTH_METHOD: trust.- image: circleci/postgres:9.6-postgis-ram environment: POSTGRES_USER: 'username' POSTGRES_HOST_AUTH_METHOD: trust
Work for me!
services:
- postgres
RAILS_ENV: test
POSTGRES_DB: database_name_example
POSTGRES_USER: 'example'
POSTGRES_PASSWORD: 'example'
POSTGRES_HOST_AUTH_METHOD: trust
@daniellessio thanks for the suggestion but that's exactly what I was trying to no effect at the time I wrote that post. There still seems to be something wrong, and I can't tell exactly why. I have that page open for the past hour, while I try to figure it out. Maybe I should go read it again.
@vitorcsantos same here, we're working with docker-compose too. Adding the postgres db password as an environment variable to the image (as @daniellessio suggested) didn't seem to fix it either. I also tried setting POSTGRES_HOST_AUTH_METHOD=trust.
When I ssh into the build-agent on AWS, I tried to set up a manual connection to the db with and I get a similar OperationalError: psycopg2.OperationalError: could not translate host name "db" to address: Name or service not known
This broke our Buildkite pipeline and I've been trying to get it back to run for the past couple of hours
If you are having issues connecting Django to PostgreSQL via Docker for the first time, then add the following instructions to your docker-compose.yml file:
environment: POSTGRES_HOST_AUTH_METHOD: trust
This solve the connection issue for me.
Please also be aware that "This is not recommended. See PostgreSQL documentation about "trust": https://www.postgresql.org/docs/current/auth-trust.html"
I was having the same issue and I am using docker-compose.yml I have added POSTGRES_HOST_AUTH_METHOD: trust in the docker-compose.yml to make it work on CircleCI. The code of docker-compose.yml is.
services:
db:
image: postgres:9.6
environment:
POSTGRES_HOST_AUTH_METHOD: trust
web:
environment:
- RAILS_ENV=test
- DATABASE_URL=postgres://postgres:@db
- ENVIRONMENT=${ENVIRONMENT}
ports:
- "3003:80"
depends_on:
- db
Managed to fix it on my end. Turns out the problem wasn't just setting up a password, but also the connection setup on the project's environment variables. For some reason, my connection was set to look in the host machine for the database with the wrong port. I have no idea how it was working previously to this.
Changing the line in the .env file made it work again.
DATABASE_URL=psql://postgres:@db:5432/postgres
FWIW this also hit us using postgres:9.4
@jawadqur: This docker page (https://hub.docker.com/_/postgres?tab=tags) says that a new docker image was pushed out on Friday for the following versions: 9, 9.4. 9.4.26, 9.5, 9.5.21, 9.6, 9.6.17, 10, 10.12, 11, 11.7, 12, 12.2, latest. It seems likely that all of them contain this breaking change.
We fixed it on our end too! Found that both solutions work independently:
- Setting a POSTGRES_PASSWORD in the docker-compose.yml file to access the db
db:
image: postgres
ports:
- "5432:5432"
environment:
- POSTGRES_PASSWORD: some_passwordThen, using the same password to establish the db connection in our test running script:
try:
conn = psycopg2.connect(dbname="postgres", user="postgres", port=5432, password="some_password", host="db")
except psycopg2.OperationalError:
sys.exit(-1)
sys.exit(0)- Set POSTGRES_HOST_AUTH_METHOD=trust in the db image's environment variables (not recommended)
environment:
- POSTGRES_HOST_AUTH_METHOD: trustEnded up going with solution 1 as it is recommended.
Another thing was we had to actually kill all existing buildkite build-agents EC2 instances persisting on AWS. By doing so, the environment variables were able to be set properly. Good ol' case of: "Have you tried turning it off and on again?"
In GitLab add the variable like this.
variables:
POSTGRES_HOST_AUTH_METHOD: trust
In GitLab add the variable like this.
variables: POSTGRES_HOST_AUTH_METHOD: trust
Setting up a password also works.
.gitlab-ci.yml
variables:
POSTGRES_PASSWORD: "some_password"
.env.gitlab
DB_PASSWORD=some_password
Supplying a username and password as environment variables to docker run is not working for me.
$ docker run postgres -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password
Error: Database is uninitialized and superuser password is not specified.
You must specify POSTGRES_PASSWORD for the superuser. Use
"-e POSTGRES_PASSWORD=password" to set it in "docker run".
You may also use POSTGRES_HOST_AUTH_METHOD=trust to allow all connections
without a password. This is *not* recommended. See PostgreSQL
documentation about "trust":
https://www.postgresql.org/docs/current/auth-trust.html
$ docker run
[. . .]
Usage: docker run [OPTIONS] IMAGE [COMMAND] [ARG...]Anything after the [image] is a command and arguments for the command, the -e option needs to go before [image]
Doing docker run -d --name postgres -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password postgres
Will have it run in the background (-d) and you can get a shell in the container using the --name postgres. So docker exec -it postgres bash
Works for me adding this to postgres container.
environment: POSTGRES_HOST_AUTH_METHOD: "trust"
Not sure if this will help anyone and I apologize if this is the wrong place to post this, but here is how I solved the issue without having to set
POSTGRES_HOST_AUTH_METHOD: trust(which was not recommended above)
@QuantumWaver
It works for me. Thanks!
No idea why setting POSTGRES_HOST_AUTH_METHOD=trust doesn't work for me. 🤔
I got PG::ConnectionBad: FATAL: password authentication failed for user "postgres" when I tried to rake db:migrate.
I had to set a password and added POSTGRES_PASSWORD=password to my docker-compose.yml.
@yosifkit I've submitted a PR in which updated the docs and put more emphasis on how to aptly set the environment variables. Merge it as you deem fit.
Per https://www.postgresql.org/docs/current/auth-trust.html:
trust authentication is appropriate and very convenient for local connections on a single-user workstation.
Is there a way to suppress the warning about trust in this situation? As mentioned, we limit this to local workstations, we'd like to avoid showing the error unnecessarily.
I think note, that "POSTGRES_HOST_AUTH_METHOD='trust' is not recommended" only actual if you have forward postgre post from your container to outside.
If your service works only in docker network (no port forward specified in docker-compose file) I think it's pretty safe to trust anything from local docker network
I tripped up on this too. It looks like it has broken Compose getting started guides too: https://docs.docker.com/compose/django/
I solved my issue using below:
- POSTGRES_HOST_AUTH_METHOD=md5
- POSTGRES_INITDB_ARGS=--auth-host=md5