I have gone through many SO question answer my problem was not solved..
This is the method I tried:
user@user-Latitude-XT3:~$ sudo su postgres
No passwd entry for user 'postgres'
user@user-Latitude-XT3:~$
and many others.
I keep on getting fatal error user does not exist
any working solution?
please Note before marking duplicate, tried all similar but wasn’t solved yet.
Nakx
1,4401 gold badge23 silver badges32 bronze badges
asked Sep 10, 2017 at 15:27
0
This is how I normally login to postgres:
sudo -i
sudo -i -u postgres
psql
Eugen Konkov
21.6k14 gold badges104 silver badges153 bronze badges
answered Sep 10, 2017 at 16:29
Here how i fixed,
-
vi /etc/postgresql/9.3/main/pg_hba.conf
-
local all postgres peer
here change peer to trust
-
restart,
sudo service postgresql restart
-
now try,
psql -U postgres
answered Sep 10, 2017 at 17:52
1
If you don’t specify the user, psql will try to login with the current system logged in user. Try to add the user option (-U
) to the psql command:
psql -U <existing role with permission>
answered Jul 8, 2022 at 18:11
Maicon MauricioMaicon Mauricio
1,9471 gold badge13 silver badges29 bronze badges
I’m setting up my PostgreSQL 9.1 in windows.
I can’t do anything with PostgreSQL: can’t createdb, can’t createuser; all operations return the error message
Fatal: role root does not exist
root is my account name, which I created while installing Postgresql
But I am able to connect using:
username : postgres
How can I connect to postgres using role root
?
There is a solution mentioned for linux platforms using su command here but not able to figure out solution for windows7
Thanks in Advance
asked Feb 2, 2015 at 11:26
1
If you want to login to Postgres using the username root
you need to first create such a user.
You first need to login as the Postgres super user. This is typically postgres
(and is specified during installation):
psql -U postgres <user-name>
Then you can create roles and databases:
psql (9.4.0)
Type "help" for help.
postgres=# create user root with password 'verysecret';
CREATE ROLE
postgres=# q
c:
c:>psql -U root postgres
psql (9.4.0)
Type "help" for help.
postgres=>
Logged in as the superuser you can also grant the root
user the necessary privileges.
All parameters for psql
are documented in the manual.
Creating users and databases is also documented in the manual:
- connecting to the database
- create user
- create database
bathyscapher
1,4131 gold badge11 silver badges18 bronze badges
answered Feb 2, 2015 at 11:35
5
In some cases, when you install postgres the initial DB is not created.
You need to execute initdb.
answered Aug 28, 2018 at 20:19
NatachaNatacha
1,12415 silver badges23 bronze badges
Same issue appeared while restoring DB/table on postgres docker container .
-
. When you connect to Postgres DB(psql shell) from inside the docker container, the default user would be a «root» (unless you specify
psql -U "some-user-name"
)
[Manjunath-MacBook-Air:$ sudo docker exec -it a2ff6075344e bash
bash-5.0# psql -U postgres postgres < testdb_pg_dump
-
So, the issue gets resolved, by logging to psql shell with appropriate username
Here , -U postgres
specifies that user connecting to DB is «postgres»
answered Aug 6, 2020 at 3:46
Manju NManju N
8389 silver badges13 bronze badges
причины проблемы
Postgresql не может использовать пользователя root для запуска службы, поэтому вы должны переключиться на другого пользователя, чтобы запустить обычную службу, но иногда нам нужно использовать пользователя root для использования postgresql
Решение
метод первый
Не используйте postgresql как пользователь root без необходимости
Метод второй
Вручную создайте корневую роль, здесь я использую имя пользователя postgres для запуска службы postgresql
su postgres
# Создать пользователя root
postgres=#create user root with password 'password';
CREATE ROLE
# Предоставить права доступа к базе данных пользователю root
postgres=# GRANT ALL PRIVILEGES ON DATABASE mydatabase to root;
GRANT
# Измените пользователя на суперпользователя (см. Фактические потребности)
postgres=# ALTER ROLE root WITH SUPERUSER;
postgres=# q
Вы также можете напрямую создать root как суперпользователь, вход в систему означает разрешение входа в систему
CREATE ROLE root superuser PASSWORD 'password' login;
I started a postgres docker container using
sudo docker run --name some-postgres -d postgres
and then attempted to connect to it using
sudo docker run -it --rm --link some-postgres:postgres postgres psql -h postgres
which gives the error psql: FATAL: role "root" does not exist
These commands are exactly the same as the ones listed on the docker hub page Only run as root and without -e POSTGRES_PASSWORD=mysecretpassword
Have I done something wrong here? What could be causing this?
asked Jun 19, 2018 at 13:56
$ docker run --rm --name some-postgres -d postgres
cb2ddbb0f4f715077ebc1bfc2dc7151e5a6d07cd374c28be1db6d6ad77b9b16a
$ docker run -it --rm --link some-postgres:postgres postgres psql -h postgres -U postgres
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.
postgres=#
You forgot the -U postgres
answered Jun 25, 2018 at 22:28
You must log in to answer this question.
Not the answer you’re looking for? Browse other questions tagged
.
Not the answer you’re looking for? Browse other questions tagged
.
In this document I am going to discuss about some of the most frequent errors faced by users while connecting to PostgreSQL. This is document is to help the beginners who started working on PostgreSQL. PostgreSQL has a process called postmaster which acts as listener process, by default the process listens on 5432.
There are different tools available to connect to PostgreSQL, here I am going to use the psql command line tool but there is no difference in error messages.
ERROR – 1
[root@localhost ~]# psql
psql: error: FATAL: role “root” does not exist
By default “psql” command line tool use the operating system user as the database user and localhost the hostname if the user name is not passed as argument, here I logged in as the root user and tried to login to PostgreSQL without username which caused the error.
Solution
give the username when logging into the database, by default the psql command line take the username as the database name, so no need to pass the database as I am trying to connect to PostgreSQL.
psql -U postgres -h localhost
ERROR – 2
[root@localhost ~]# psql -U postgres
psql: error: FATAL: Peer authentication failed for user “postgres”
Solution
By default PostgreSQL installation using yum repository configure the pg_hba.conf with peer, ident authentication. So change the peer and ident authentication methods to md5 and reload the configuration.
[root@localhost data]# psql -U postgres
Password for user postgres:
psql (12.5)
Type "help" for help.
postgres=#
ERROR – 3
[root@localhost data]# psql -h 192.168.159.151 -U postgres -d postgres
psql: error: could not connect to server: Connection refused
Is the server running on host “192.168.159.151” and accepting
TCP/IP connections on port 5432?
This is the common error users get when they connect to PostgreSQL. The error represents there is no listener for host and port combination.
Solution
Most common issue is listener_address parameter is set to ‘localhost’ or port set to a non default value.
postgres=# show listen_addresses ;
listen_addresses
------------------
localhost
(1 row)
postgres=# show port;
port
------
5432
(1 row)
Here the issue is listener_address is localhost, so changed it to ‘*’ where it listens on all ip’s in my host. I have only one ip configured, if your server has multiple ip’s then set the ip address on which the PostgreSQL connections should happen.
Configure the listener_address or change the port and restart the PostgreSQL cluster.
postgres=# alter system set listen_addresses to '*';
ALTER SYSTEM
postgres=# q
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-12-20 07:12:20.317 PST [3051] LOG: starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-12-20 07:12:20.318 PST [3051] LOG: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 07:12:20.318 PST [3051] LOG: listening on IPv6 address "::", port 5432
2020-12-20 07:12:20.319 PST [3051] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-12-20 07:12:20.320 PST [3051] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 07:12:20.332 PST [3051] LOG: redirecting log output to logging collector process
2020-12-20 07:12:20.332 PST [3051] HINT: Future log output will appear in directory "log".
done
server started
ERROR – 4
-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: no pg_hba.conf entry for host “192.168.159.151”, user “postgres”, database “postgres”, SSL off
This is another common error which users will face due to missing entry for the user host address (ip address) in pg_hba.conf file. host based authentication file presents in data directory which has a list of ip addresses or host addresses allowed to connect to PostgreSQL.
Solution
Make an entry like below for the user host or subnet in pg_hba.conf file. Any ip address with 192.168.X.X will be able to connect to the host.
host all all 192.168.0.0/16 md5
ERROR – 5
-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: sorry, too many clients already
-bash-4.2$ psql -h 192.168.159.151 -U viswamitra -d postgres
psql: error: FATAL: remaining connection slots are reserved for non-replication superuser connections
-bash-4.2$ psql -U viswamitra -d postgres -h 192.168.159.151
psql: error: FATAL: too many connections for role “viswamitra”
This is also a common error users face after started using the database in application. This errors will come after the connection limit is reached to the maximum configured value.
Connection limit can be set at different levels
- cluster – which is applicable for all users and databases
- user – which is applicable for the user
- database – which is applicable for the given database
Solution
Login to the database as a super user, if there are any idle connections from longer time close them and check the application connection pool settings to close the idle connections after some time interval.
postgres=# select pid,datname,usename,application_name,state,now()-state_change as idle_duration from pg_stat_activity where state = 'idle';
pid | datname | usename | application_name | state | idle_duration
------+----------+------------+------------------+-------+-----------------
3656 | alex | postgres | psql | idle | 00:14:06.647055
3652 | alex | postgres | psql | idle | 00:14:11.718486
3623 | postgres | viswamitra | psql | idle | 00:15:47.530912
(3 rows)
postgres=# select pg_terminate_backend(3656);
pg_terminate_backend
----------------------
t
(1 row)
If there are no idle sessions to cancel and the error is first and second one in the list increase the “max_connections” in postgresql.conf file. This requires a reboot of the PostgreSQL cluster to make it effect.
postgres=# alter system set max_connections to '1000';
ALTER SYSTEM
postgres=#
/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart
For third error (5.3) , the connection limit is configured at the user level, it can be changed using the alter user statement.
postgres=# alter user viswamitra connection limit 100;
ALTER ROLE