Unsure of impact of --username and --dbname

[jamielinux]

Hi! I'm probably being dense but...

pg_autoctl create postgres has --username and --dbname options, but I can't tell from the docs why I would change these from the default (ie, if they are important part of functionality or if they are just a user convenience thing for tailoring pg_hba.conf and auto-creating user/database for the downstream application).

• The defaults are postgres and postgres.
• The tutorial uses ha-admin and app-db.

I can see that passing these options will alter the pg_hba.conf rules that are added to a non-monitor node, for instance:

host  replication  "pgautofailover_replicator"  dev-01  scram-sha-256  # Auto-generated by pg_auto_failover
host  "app-db"     "pgautofailover_replicator"  dev-01  scram-sha-256  # Auto-generated by pg_auto_failover
host  "app-db"     "ha-user"                    dev-01  scram-sha-256  # Auto-generated by pg_auto_failover

1. IIRC only the first rule is required for normal streaming replication. Is the second rule important for pg_auto_failover functionality?
2. Replication is done by the pgautofailover_replicator user. Is the ha-user user used by pg_auto_failover for something else? I can see pg_auto_failover creates this user if it doesn't exist, and gives it superuser privileges.
3. pg_auto_failover creates app-db if it doesn't exist and sets the owner to ha-user, but it doesn't seem to modify the db (it just shows it on show uri). If I leave --dbname and --username as the defaults (postgres) and then manually create the databases the downstream application needs (and adding the relevant pg_hba.conf entries as I would for a traditional setup), does pg_auto_failover still behave the same (apart from show uri having the wrong database name)?

Would be grateful for any guidance. I can potentially improve the docs (for anyone else confused in the same way I am) once I understand more!

[jamielinux]

So we set up pg_auto_failover with the default --username postgres --dbname postgres, and then manually created another user and DB for the application (eg, appuser, appdb). We manage pg_hba.conf manually. Everything seems to work fine.

From what I can tell, the --username and --dbname options are for the convenience of automatic pg_hba.conf entries, and that's maybe the only thing these options affect. If you manage pg_hba.conf yourself anyway, there doesn't seem to be any disadvantage to using the pg_auto_failover defaults of postgres / postgres.

[DimCitus]

Hi @jamielinux ; the --username and --dbname options are indeed for convenience, and have security implications too so some people might want to make sure to harden then in a way to avoid creating spurious entries there. Also, when using Citus it's important to pick the target database your application will actually use, because of maintenance operations that are only implemented in that database at failover time (calling citus_update_node is only supported for a single Citus database in a pg_autoctl formation).

[jamielinux]

Thank you so much for the clarification!