CODEWITHSUNDEEP
postgresqlnixos
mherzl
mherzl

Reputation: 6220

How to view available postgres users, without having a user to log in?

I am using postgres, facing the error:

$ psql -U postgres
psql: FATAL: role "postgres" does not exist

I get a similar error when attempting to connect using my username, and do not know what user I can use to connect. Running $ psql -l, attempting to list users, also fails with the same error since I do not have a user to connect and run the command.

Is there any way to view the available users, when I don't know of a user name that can connect to run $ psql -l?

Is there perhaps a default role that I can use to create my 'postgres' and username roles? Or could I maybe use sudo to look into /var/db/postgresql (my postgres data directory) for the postgres user names?

I am running NixOS 17.03.

Upvotes: 3

Views: 4285

Answers (1)

mherzl
mherzl

Reputation: 6220

Following the suggestions of the comments I shut down my postgres server and restarted in single user mode to run select * from pg_authid;, and got the following:

$ sudo -u postgres postgres --single -D /var/db/postgresql/
[sudo] password for matthew:

PostgreSQL stand-alone backend 9.4.11
backend> select * from pg_authid;
         1: rolname     (typeid = 19, len = 64, typmod = -1, byval = f)
         2: rolsuper    (typeid = 16, len = 1, typmod = -1, byval = t)
         3: rolinherit  (typeid = 16, len = 1, typmod = -1, byval = t)
         4: rolcreaterole       (typeid = 16, len = 1, typmod = -1, byval = t)
         5: rolcreatedb (typeid = 16, len = 1, typmod = -1, byval = t)
         6: rolcatupdate        (typeid = 16, len = 1, typmod = -1, byval = t)
         7: rolcanlogin (typeid = 16, len = 1, typmod = -1, byval = t)
         8: rolreplication      (typeid = 16, len = 1, typmod = -1, byval = t)
         9: rolconnlimit        (typeid = 23, len = 4, typmod = -1, byval = t)
        10: rolpassword (typeid = 25, len = -1, typmod = -1, byval = f)
        11: rolvaliduntil       (typeid = 1184, len = 8, typmod = -1, byval = t)
        ----
         1: rolname = "root"    (typeid = 19, len = 64, typmod = -1, byval = f)
         2: rolsuper = "t"      (typeid = 16, len = 1, typmod = -1, byval = t)
         3: rolinherit = "t"    (typeid = 16, len = 1, typmod = -1, byval = t)
         4: rolcreaterole = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
         5: rolcreatedb = "t"   (typeid = 16, len = 1, typmod = -1, byval = t)
         6: rolcatupdate = "t"  (typeid = 16, len = 1, typmod = -1, byval = t)
         7: rolcanlogin = "t"   (typeid = 16, len = 1, typmod = -1, byval = t)
         8: rolreplication = "t"        (typeid = 16, len = 1, typmod = -1, byval = t)
         9: rolconnlimit = "-1" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
backend>

This confirms that there is no 'postgres' role, and shows that there is a 'root' role.

Upvotes: 3

Related Questions