Traffic Ops `db/admin` tool depends on passwordless DB connection as `root` user

[zrhoffman]

This Improvement request (usability, performance, tech debt, etc.) affects these Traffic Control components:

• Traffic Ops

Current behavior:

The Traffic Ops db/admin tool relies on the postgres user requiring no password when connecting as root.

trafficcontrol/traffic_ops/app/db/admin.go

Line 307 in 02b9f04

userExistsCmd := exec.Command("psql", "-h", hostIP, "-U", dbSuperUser, "-p", hostPort, "-tAc", "SELECT 1 FROM pg_roles WHERE rolname='"+dbUser+"'")

#7142, which made Traffic Ops run as a non-root user, set PGPASSWORD for the entire binary, which worked for Dev CDN in a Box because the password in dbconf.yml, which this strategy set PGPASSWORD to,

trafficcontrol/dev/traffic_ops/dbconf.yml

Line 21 in 02b9f04

open: host=db port=5432 user=traffic_ops password=twelve12 dbname=traffic_ops_development sslmode=disable

happened to be "twelve12", the same password set for the postgres user of the Postgres server.

trafficcontrol/docker-compose.yml

Line 46 in 02b9f04

- POSTGRES_PASSWORD=twelve12

However, once #7142 was merged, the Cache Config integration tests started failing, because its postgres user password

trafficcontrol/cache-config/testing/docker/variables.env

Line 31 in 02b9f04

POSTGRES_PASSWORD=secretrootpass

is different than its traffic_ops password (which ends up in dbconf.yml).

trafficcontrol/cache-config/testing/docker/variables.env

Lines 35 to 36 in 02b9f04

	DB_USER=traffic_ops
	DB_USER_PASS=twelve

trafficcontrol/cache-config/testing/docker/traffic_ops/run.sh

Line 98 in 02b9f04

open: host=$DB_SERVER port=$DB_PORT user=$DB_USER password=$DB_USER_PASS dbname=$DB_NAME sslmode=disable

We reverted the change to db/admin from #7142 in #7198 to make the Cache Config integration tests pass again without knowing, at the time, why that change made them fail.

As a side note, finding the reason the Cache Config integration tests were failing was not straightforward because the errors go only to a file that is not printed to the to_server container's output anywhere.

trafficcontrol/cache-config/testing/docker/traffic_ops/run.sh

Lines 144 to 145 in 02b9f04

	cd /opt/traffic_ops/app && db/admin --env=production reset >> /var/log/traffic_ops/to_admin.log 2>&1
	cd /opt/traffic_ops/app && db/admin --trafficvault --env=production reset >> /var/log/traffic_ops/tv_admin.log 2>&1

New behavior:

• db/admin should not depend on the postgres user requiring no password when the connecting client is the root user locally.
• In CDN in a Box for Developers, the postgres password should not be the same as the traffic_ops password, because that potentially hides issues like this one.

[ocket8888]

Is

Traffic Ops db/admin tool depends on passwordless DB connection as root user

strictly true, or is it the case that db/admin depends on the calling user to be the root user?

[zrhoffman]

Not sure what the difference between strictly true and db/admin depends on the calling user to be the root user is, but they both seem true to me.

Notice how no password is set for the psql command (and PGPASSWORD is not set).

trafficcontrol/traffic_ops/app/db/admin.go

Line 307 in 02b9f04

userExistsCmd := exec.Command("psql", "-h", hostIP, "-U", dbSuperUser, "-p", hostPort, "-tAc", "SELECT 1 FROM pg_roles WHERE rolname='"+dbUser+"'")

[ocket8888]

If the title is strictly true, then the tool doesn't depend on passwordless DB connections when not run as the root user

[zrhoffman]

It sounds like you know what I mean, feel free to change the title to whatever you feel is accurate

[ocket8888]

I do not. If it's true that db/admin requires you to run it as the root user, that's also an issue IMO.

[zrhoffman]

If the title is strictly true, then the tool doesn't depend on passwordless DB connections when not run as the root user

The tool didn't depend on passwordless DB connections before the revert in #7198 was merged.

If it's true that db/admin requires you to run it as the root user, that's also an issue IMO.

That's not also an issue, it's the issue

[ocket8888]

idk the way I see it there are two issues

• admin should not depend on passwordless authentication for its database connections
• admin should not require that it be run as any particular user

[zrhoffman]

Got it. I'm not sure how to describe one of those 2 points without describing the other, since both cases are related to Postgres default installations, but if you think they should be separated into 2 2 issues, I'll create another one.

[ocket8888]

The number of issues isn't important to me, just want to be sure both are fixed.