psql: FATAL: role "postgres" does not exist

Question

Running PostgreSQL server on Void Linux. After installing ran initdb as OS user 'postgres':

[user@host]$ sudo -u postgres -i
$ initdb -D '/var/lib/postgresql/data'

Received output:

creating directory /var/lib/postgresql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... locale: Cannot set LC_MESSAGES to default locale: No such file or directory ok syncing data to disk ... ok

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: pg_ctl -D /var/lib/postgresql/data -l logfile start

I've then proceeded to create the service, grant the ownership to 'postgres' and start it:

[user@host]$ ln -s /etc/sv/postgresql /var/service
[user@host]$ sudo chown postgres: /var/service/postgresql
[user@host]$ sudo sv start postgresql
[user@host]$ ls -l /var/service/postgresql
lrwxrwxrwx 1 postgres postgres 18 nov 2 17:05 /var/service/postgresql -> /etc/sv/postgresql

Running:

[user@host]$ sudo -u postgres psql ...

will give me the following error:

psql: FATAL: role "postgres" does not exist

I've tried granting ownership of /etc/sv/postgresql to 'postgres', but no luck.

These were my sources:

https://wiki.voidlinux.eu/PostgreSQL

PostgreSQL error: Fatal: role "username" does not exist

I do not know what to do next.

EDIT: Stumbled upon psql: FATAL: role "postgres" does not exist. Cannot run psql -l because I get the same error, no matter which user I run psql as.

• Erwin Brandstetter: Tell us which version of Postgres you are trying to install and where you got the package. The error message is clear enough: role "postgres" does not exist: there is no database role named "postgres". Is it the only Postgres installation on the same machine? (So you are sure to connect to the right instance?) If so, the question is: which database role is set up by your installation. I don't know Void Linux or the XPBS package manager enough to say more. Might be better suited for superuser.SE or unix.SE.
• Jakov: The version is postgresql-9.6.10_1. Yes, it's the only installation of PostgreSQL on my system and there is only one instance of the server running. Should I move the question then? I don't know...
• Erwin Brandstetter: Not sure there are any VOID Linux or XPBS experts around here ... The key is to find the name of the Postgres role created by initdb, and then connect with the OS user of the same name to be granted access by the "trust" authentication method. Obviously not "postgres" (which would be the sane default).
• Jakov: I figured as much, but I've fallen short with finding the role. Like I mentioned in the question, I can't psql at all, so I can't list the existing roles. Is there something of relevance written in the postgresql.conf file perhaps? Something I should look for? I've greped role and got session_replication_role = 'origin'. Not much use of that, I feel.
• Erwin Brandstetter: :%s/XPBS/XBPS/g

Answer 1

Not a full answer, but consider the manual about initdb here:

-Uusername
--username=username

Selects the user name of the database superuser. This defaults to the name of the effective user running initdb. It is really not important what the superuser's name is, but one might choose to keep the customary name postgres, even if the operating system user's name is different.

Meaning ...

1. When running initdb as OS user postgres, the initial DB superuser role is postgres as well - in standard Postgres. I have heard of systems bending this rule ...

2. You should be able to force the name postgres with --username=postgres for the initial superuser - unless your package seriously messed with all this.

• Jakov: Wow, this pointed me at the right direction, all right. I knew I saw that -U flag somewhere and it was from here: stackoverflow.com/questions/50746147/… I might have misread Laurenz's answer there, but I ran initdb -U UTF8 -D /var/lib/postgresql/data and guess what the initial DB superuser role was. Anyway, I ended up almost crashing my entire system due to exhaustion. A fairly productive day. Thank you for your dedication Erwin, your help is much appreciated.
• Erwin Brandstetter: @Jakov: -U UTF8. Ouch. Was meant to be -E UTF8 I assume? Sorry for your day lost due to one sneaky scrambled letter. Then again: If you had shown your actual command in the question to begin with, we might have saved half your day ...

Answer 2

#type this:

sudo -u postgres -i

#it will prompt you to enter your password.type your password in. Voila! Ure in.

#If you wish to change to your desired username, make sure you do that from within the loggedin default postgres you just entered.

• Anthony Genovese: Wasn't the first thing the poster said was they tried sudo -u postgres -i?

Answer 3

Maybe you have used "initdb" utility to initiate your database cluster, or second database instance. You need to use like this: initdb -D "location of data directory" --username=youruser -W

--username=youruser This tells the initdb utility to set yourusername as the super user for your new db cluster. If you do not use this then there will be no Superuser for your database and you will not be able to connect to it.

You can watch related video here: https://www.youtube.com/watch?v=tdijTz8n5ok

• atraudes: This was my problem, spot on. Thank you!