Question 287826 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to debug postgres when there are no users?

What to do if there appear to be no users on the server: any attempt to connect to the server gives FATAL: role ".." does not exist, including the role "postgres". It’s only possible to connect in single user mode and the pg_user table (view?) appears to be empty (select count(*) from pg_user; is null).

Below I assumed the database process doesn’t start — it does. It’s just that the systemctl start postgresql command hangs because after starting the postgres server it then runs a shell script which loops psql to connect to the server, and this never returns as there is no postgres role..

Original question

I’ve somehow managed to create a situation where there are no users, not even the superuser postgres. I can get into single user mode with postgres --single -D /var/lib/postgresql/data but nothing else works, and the systemd service loops on the error FATAL: role "postgres" does not exist.

Whit might have created this and how to fix it?

  • OS: NixOS, 23.05.4603.3e10c80821de (Stoat)
  • Postgres version: postgres (PostgreSQL) 14.9, installed via the nixos module

Tried thus far

  • postgres -D /var/lib/postgresql/data does start the server, but there is no way to get in, user postgres does not help, sudo neither
  • postgres --single -D /var/lib/postgresql/mnt_14 gets me into the single user mode (this is totally new to me). In there running select count(*) from pg_user; gives "0" as the result, inserting into that table doesn’t work.
  • in single user mode none of the backslash commands work, difficult to explore the situation
Tags:

2

Answers


  1. 0

    The only way I can think to drop all users is a direct DELETE to pg_authid. Whoever does that deserves to suffer. Still, here is how to create the role postgres again:

    • start the server in single-user mode

    • run this statement:

      CREATE ROLE postgres SUPERUSER LOGIN SYSID 10

    • exit the session (which stops the server)

    The trick here is that the bootstrap superuser (usually postgres always has object ID 10, so creating a superuser with that object ID is the right thing.

    Since a lot of things could be broken in that database if you deleted other rows from pg_authid, you should try and get the server into a condition where you can pg_dump the data, then restore them to a healthy cluster.

    Login or Signup to reply.
  2. 0

    Try creating a new cluster and then a database. Since you had tried to run server from absolute path, try creating the cluster from an absolute path. If you are on bin directory in the terminal run initdb command in your bin directory to create a cluster and then createdb to create a database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search