Secure database by restricting Postgres and Bety user accounts

[Chris-Schnaufer]

The minimal needed permissions should be given to the Bety account after installation. The postgres user should be restricted to prevent accidental or intentional problems

[Chris-Schnaufer]

Refer to #616 for additional context

[Chris-Schnaufer]

It looks like the instance of postgres used in the Pecan installation allows all hosts from anywhere to connect as trusted users. It is the last line in the pg_hba.conf file is host all all all trust which allows this. One command that shows this is psql -U postgres -qAt -c "show hba_file" | xargs grep -v -E '^[[:space:]]*#'. This means that setting passwords on any database account is useless as that check is bypassed; their permissions are still in effect fortunately. Note that the postgres user is available on all postgresql instances as a superuser and in this case a password is irrelevant.

Is this something that would be desirable to change for all instances, not just in our case (at UA)?

In the same vein, he local access allows all users as well. Perhaps this can be restricted to only bety and postgres users?

More information on the pg_hbe.config file can be found on the Postgresql documentation site (for example, https://www.postgresql.org/docs/10/auth-pg-hba-conf.html)

[Chris-Schnaufer]

It looks like the Bety web needs "host all all all trust". I need to investigate to see if ...11 would be a better fit (IP from docker inspect command)

[gsrohde]

'^[[:space:]]*$'?

---

When reading the pg_hba.conf file, keep in mind the following:

The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

So, for example, as I understand it, if there were a line

host all all all md5

before the line

host all all all trust

the latter would never be reached. In other words, just because the last line is host all all all trust, this doesn't necessarily mean all hosts from anywhere can connect. Whether this is true for the Pecan installation I could only tell by looking at the whole file. (Also relevant is the setting of listen_addresses in postgresql.conf.)

Of course having such a line is potentially dangerous: if someone mistakenly deletes or comments out a preceding line, that could inadvertently open up access more than wanted.

[Chris-Schnaufer]

You are correct, that is how the pg_hba.conf file operates. The contents of the pg_hba.conf file for the docker instance first enables the loopback and local IP4 and IP6 addresses with trust. The last entry is host all all all trust. The absence of other entries gives full permissions.

One can do the following in the running container to confirm:

1. Download the docker instance and start it detached: docker run mdillon/postgis:9.5 -d
2. Connect to the running image by first finding its container id: docker ps
3. Start a session with the container: docker exec -it <container id> /bin/bash
4. You are now in the container and able to query the pg_hba.conf file.

[robkooper]

I have less of an issue with this in the docker framework. I don't think you should let people access the docker container directly and execute code there. Can you see what happens if you expose the port (5432) and connect to it remotely can you connect with username/password?

[Chris-Schnaufer]

We have done this and the machine running the container is able to connect to the postgres database with no changes to the pg_hba.conf file (using the default settings which allow everything)

[robkooper]

experimented a bit with setting PG_PASSWORD variable but this breaks the create initial database.

What seems to work, and should become more standard, is to do the initialize of the database. Next you connect to the postgres database docker-compose exec postgres /bin/bash and run the following to force remote connections to use md5 sed -i 's/all trust/all md5/' /var/lib/postgres/data/pg_hba.conf'. Next stop the postgres container docker-compose down postgresand start itdocker-compose up -d postgres`. You now need a password to connect.

Keep in mind, if you can connect to the running docker image nothing with prevent you from connecting to the database. You still should be able to connect to the database with no password if you use docker-compose exec postgres psql -U postgres

Nevermind, ignore this. This failed. All of this will require the load.bety.sh script to be able to take the username/password for both bety and postgres from the command-line. Will look at that in more detail once #609 is merged.