A lot of stolon errors in db

Digging into it, this is coming from admin.ListUsers in fly’s postgres-ha repository.

This error is due to one of the users in your database not having a password set!


Here’s how to fix it using psql:

  1. Connect to your DB with psql
  2. Run the query below. This is straight from the ha-postgres repo, the exact same query ha-postgres runs!
select u.usename,
		usesuper as superuser,
		userepl as repluser,
		a.rolpassword as passwordhash,
    (
		select array_agg(d.datname::text order by d.datname)
			from pg_database d
				WHERE datistemplate = false
				AND has_database_privilege(u.usename, d.datname, 'CONNECT')
	) as 
		allowed_databases
	from 
		pg_user u join pg_authid a on u.usesysid = a.oid 
	order by u.usename;
  1. Check which of the users does not have a passwordhash set.
  2. Give that user a password by entering \password <username of the user missing a password>
  3. Important! Restart your database fly.io app. I had to restart mine for the changes to be picked up.
8 Likes