How do I solve this problem to use psql? | psql: error: FATAL: role "postgres" does not exist

Question

I'm having trouble using PostgreSQL. I have recently installed this version (13+223.pgdg20.04+1) of postgresql package in ubuntu 20.04.

I'm trying to run psql command, but I get the following error:

psql: error: FATAL: role "my_username" does not exist

I have tried to create a new user with createuser me, but I get the following error:

createuser: error: could not connect to database template1: FATAL: role "my_username" does not exist

I have tried also forcing the postgres user with createuser me --username=postgres, but I get the following error:

createuser: error: could not connect to database template1: FATAL: Peer authentication failed for user "postgres"

How do I solve these problems to use PostgreSQL locally on my computer without these problems?

PD: I have reinstalled postgres and now I'm getting a different error while doing psql:

psql: error: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Valdes

Answer 1

I'm not sure why I had a bad installation, but I have completely uninstalled postgres following this post:

https://kb.objectrocket.com/postgresql/how-to-completely-uninstall-postgresql-757

after that I have restarted my computer and installed posgres again following the proper instructions in:

https://www.postgresql.org/download/linux/ubuntu/

and now it looks like it works without problems

Valdes

Answer 2

first check user postgres exists:

$ id postgres

Then:

$ su - postgres

Password:

$ psql psql (15.1 (Debian 15.1-1.pgdg110+1)) Type "help" for help.

If, Password for user postgres is no known then change it: $ su - postgres

Password: su: Authentication failure

$ sudo passwd postgres

New password:

Retype new password:

passwd: password updated successfully

Finally again:

$ su - postgres

Password:

$ psql

psql (15.1 (Debian 15.1-1.pgdg110+1)) Type "help" for help.

Harrier

Answer 3

You need to provide username in the psql command using -U option.

psql -U postgres

Postgresql comes with a predefined superuser role called postgres. If you want to create more roles, you first have to connect as this initial role.

Dalenedalenna

It didn't worked, but I have reinstalled postgre (I had 2 versions and I have uninstalled both and installed the last version) and now the command "psql" shows a different error: psql: error: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? – Valdes

Answer 4

Peer authentication means (there are advanced possibilities, but those are not going to be used by default, while the simple method is the default for apt-installed PostgreSQL) that you have to be the OS user 'postgres' to connect as the database user 'postgres'. So you would do:

sudo -u postgres createuser me

You don't need to specify --username=postgres, since that is the default behavior anyway once you use sudo -u postgres

Alternatively, you could change your pg_hba.conf to use a different authentication method other than peer, if you want to.

Obie

Answer 5

If someone has faced the same issue recently, this is the way I solved it:

When Postgres is installed then a 'postgres' superuser is created as default. This supersuser has all the permissions, included creating new roles and users.

• First, login into psql as sudo:

  sudo -u postgres psql

• If login to psql command line is Ok then you will be able to create new users and roles. To create a new role you can use the CREATE ROLE command as follows:

CREATE ROLE <role_name> LOGIN PASSWORD <'password'>;

the LOGIN attribute is optional, but if declared then you have to set a PASSWORD

• To confirm that the new user has been added correctly, type the \d command into psql to see the list with all the current users and their privileges.

psql \du

• If the new user/role was created correctly, you will be able to login using the new role and not the default postgres supersuser.

psql -U <new_role>

You can find more info about creating new roles in the PostgreSQL Official Documentation

Unwish

Answer 6

As your regular user, (as opposed to as postgres or other user), start the server with the following command:

sudo service postgresql start

The response will be:

 * Starting PostgreSQL 14 database server                  [ OK ]

Then, to get into the psql command line:

sudo -u postgres psql

If successful, you will get a message and prompt like this:

psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1))
Type "help" for help.

postgres=#

Notecase