Patroni: Automatically prefix localhost connections

[jberkus]

So, not making much progress. When trying to start a master node I get this:

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/lib/postgresql/9.4/bin/postgres -D /pgdata/datacd p

or
/usr/lib/postgresql/9.4/bin/pg_ctl -D /pgdata/datacd p -l logfile start

waiting for server to start....LOG: database system was shut down at 2016-01-08 01:15:51 UTC
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
done
server started
2016-01-08 01:15:52,558 INFO: established a new patroni connection to the postgres cluster
2016-01-08 01:15:53,411 INFO: established a new patroni connection to the postgres cluster
2016-01-08 01:15:54,164 INFO: established a new patroni connection to the postgres cluster
2016-01-08 01:15:55,267 INFO: established a new patroni connection to the postgres cluster
2016-01-08 01:15:56,309 INFO: established a new patroni connection to the postgres cluster
2016-01-08 01:15:56,311 INFO: removing initialize key after failed attempt to initialize the cluster
2016-01-08 01:15:56,320 INFO: renaming data directory to /pgdata/datacd p_2016-01-08-01-15-56
2016-01-08 01:15:56,321 ERROR: Error communicating with Postgresql. Will try again later
Traceback (most recent call last):

... and things fail from there. The thing is, PostgreSQL is running, as subsequent attempts to start it fail due to port 5432 already being bound.

[jberkus]

Here's the configuration file:

scope: &scope testcluster
ttl: &ttl 30
loop_wait: &loop_wait 10
restapi:
  listen: 172.17.0.25:8001
  connect_address: 172.17.0.25:8001
  auth: 'admin:admin'
  certfile: /etc/ssl/certs/ssl-cert-snakeoil.pem
  keyfile: /etc/ssl/private/ssl-cert-snakeoil.key
etcd:
  scope: *scope
  ttl: *ttl
  host: etcd:2379
postgresql:
  name: dbnode1
  scope: *scope
  listen: 172.17.0.25:5432
  connect_address: 172.17.0.25:5432
  data_dir: /pgdata/datacd p
  maximum_lag_on_failover: 10485760 # 10 megabyte in bytes
  use_slots: True
  pgpass: /tmp/pgpass0
  create_replica_methods:
    - basebackup
  pg_hba:
  - local all all  trust
  - host all all 0.0.0.0/0 md5
  - hostssl all all 0.0.0.0/0 md5
  replication:
    username: replicator
    password: replicate
    network:  172.17.0.25/16
  superuser:
    username: patroni
    password: super
  admin:
    username: admin
    password: admin
  parameters:
    archive_mode: "off"
    archive_command: mkdir -p ../wal_archive && cp %p ../wal_archive/%f
    wal_level: hot_standby
    max_wal_senders: 10
    hot_standby: "on"
    max_replication_slots: 7
    wal_keep_segments: 5

[jberkus]

I strongly suspect that the problem is the password. That is, patroni does it's intial connection as user "postgres", no password. Which is bad, because there's lots of times that's not going to work, especially since patroni.yml only accepts 1 listen-address.

[jberkus]

Actually, it's kind of a chronic fail that Patroni doesn't have separate connections for the local patroni as opposed to remote users. WTF is up with that?

[jberkus]

So, here's the underlying issue: if "Listen" is set to anything other than "0.0.0.0", which maps to "*" in postgresql.conf, then initialization will fail.

Given this, maybe it shouldn't be a configurable parameter?

[alexeyklyukin]

You can specify multiple comma-separated addresses in the listen parameter, with the port separated by colon from the last one, i.e. listen: 127.0.0.1,1.2.3.4:5432. The first of those will be used for local connections.

[alexeyklyukin]

Futhermore, Patroni is just using libpq defaults when connecting to the 'local' address. It chooses the database 'postgres', since it's the only non-template database that is there by default on most PostgreSQL installations.

[alexeyklyukin]

I updated the docs with the information on listen parameter above.

[jberkus]

Well, if we NEED to have local available for initialization to work, why not automatically prefix it regardless of what's in the parameter? That is, if the user just puts a public IP address, automatically prefix "localhost" to it?

[jberkus]

Renaming this.

[feikesteenbergen]

Or, make sure we can always connect using sockets instead of hosts?

[jberkus]

Feike: correct. There's no reason I can imagine to not allow local socket connections for the "postgres" user or other superuser.

[LappleApple]

@feikesteenbergen @CyberDem0n @alexeyklyukin Have you guys decided on accepting @jberkus's suggestion to resolve this one?

[alexeyklyukin]

We need to look into this further.

[anad0116]

Hi guys I have a situation where I need to do Authorization for rest calls to patroni pods .....only when the call is from a service .. If it's from a pod , then no auth is needed .

[jberkus]

The parent issue here was long ago fixed, so closing.

@anad0116 if you want help with your problem, create a new issue.