1

I have a bot that runs on my Windows machine and I recently bought an Ubuntu virtual server. After a series of like infinite errors, I just reset the server completely and retrying from scratch. You can look here to see another post of some of my previous errors: no pg_hba.conf entry for host / Connect call failed / Invalid data directory for cluster 12 main - Postgresql Ubuntu

So now when I try to start my bot, it gives me this error:

Traceback (most recent call last):
  File "bot.py", line 950, in <module>
    bot.loop.run_until_complete(create_db_pool())
  File "/usr/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "bot.py", line 160, in create_db_pool
    bot.pg_con = await asyncpg.create_pool(database='xxx', user='postgres', password='???')
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/pool.py", line 398, in _async__init__
    await self._initialize()
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/pool.py", line 426, in _initialize
    await first_ch.connect()
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/pool.py", line 125, in connect
    self._con = await self._pool._get_new_connection()
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/pool.py", line 468, in _get_new_connection
    con = await connection.connect(
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1718, in connect
    return await connect_utils._connect(
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/connect_utils.py", line 663, in _connect
    con = await _connect_addr(
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/connect_utils.py", line 642, in _connect_addr
    await asyncio.wait_for(connected, timeout=timeout)
  File "/usr/lib/python3.8/asyncio/tasks.py", line 483, in wait_for
    return fut.result()
asyncpg.exceptions.InvalidAuthorizationSpecificationError: Peer authentication failed for user "postgres"

The database, user, and password are all correct though. It's working even right now as the bot is running on my Windows machine, which is why I think this is an Ubuntu specific issue. My listen addresses set to"

listen_addresses = '*'

pg_hba.conf on local windows server:

# TYPE  DATABASE        USER            ADDRESS            METHOD

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5
# IPv6 local connections:
host    all             all             ::0/                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             0.0.0.0/0            md5
host    replication     all             ::0/                 md5

and my pg_hba.conf on Ubuntu server set to:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

If I change the first line from peer to md5, I get:

asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "postgres"

and changing to trust says that there is no database xxx which is the database on my local Windows machine. Typing ps ax | grep postgres shows:

   9929 ?        Ss     0:02 /usr/lib/postgresql/12/bin/postgres -D /var/lib/pos           tgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
   9931 ?        Ss     0:00 postgres: 12/main: checkpointer
   9932 ?        Ss     0:01 postgres: 12/main: background writer
   9933 ?        Ss     0:01 postgres: 12/main: walwriter
   9934 ?        Ss     0:01 postgres: 12/main: autovacuum launcher
   9935 ?        Ss     0:01 postgres: 12/main: stats collector
   9936 ?        Ss     0:00 postgres: 12/main: logical replication launcher
  21821 pts/0    S+     0:00 grep --color=auto postgres

I've been stuck on this for a really long time and I have no idea what to do. I really hope someone knows the issue and I hope I've given enough information. Thank you.

I think its trying to connect to a database on the Ubuntu server instead of the database on my Windows machine. How do I make it target the Windows machine?

Break
  • 332
  • 3
  • 19
  • This is going to need more information. Where is the Postgres running on the Windows machine, Ubuntu VM or both? Is the Ubuntu VM running on the Windows machine, if not where? – Adrian Klaver Aug 10 '20 at 17:17
  • Postgres is on my Windows machine and the Ubuntu VM is from Vultr.com but I'm connecting to it with PuTTy – Break Aug 10 '20 at 17:20
  • So where is the program that throwing the error above running? Also is Postgres installed on the Ubuntu VM and running? – Adrian Klaver Aug 10 '20 at 17:28
  • A database with the pg_hba file you show would not be throwing that error message. To get that message, your pg_hba must be specifying either "ident" or "peer" as the method. So you must have more than one pg_hba file, and are not using the one you think. On the other hand, at least your server is running now. – jjanes Aug 10 '20 at 18:24
  • Postgres is install on unbuntu and I believe it's running. `bot.py` is at `/root/bot/bot.py` I just also typed `sudo find / -type f -name pg_hba.conf` and got 2 results: ```/var/lib/postgresql/12/main/pg_hba.conf``` ```/etc/postgresql/12/main/pg_hba.conf``` and one of them was actually different, so I thought this was finally the mistake. But I changed them both to the conf above (and also removed one, then the other, then put both back) and it's still giving the same error. I don't know what's going on. – Break Aug 10 '20 at 19:11
  • Actually now that I've restarted, it's giving the ```OSError: Multiple exceptions: [Errno 111] Connect call failed ('::1', 5432, 0, 0), [Errno 111] Connect call failed ('127.0.0.1', 5432)``` error again. So I guess the config file is not correct like that? – Break Aug 10 '20 at 19:14
  • It is not clear to me which Postgres server you are trying to connect to and from where? Are you trying to reach the Windows Pg server from the Ubuntu VM, the Ubuntu Pg server from Windows, or the Ubuntu Pg from in the Ubuntu VM? How did you install Postgres on Ubuntu? If from the Pg repos there should not be ```pg_hba.conf``` in ```/var/lib/postgresql/12/main```. What does ```ps ax | grep postgres``` on the Ubuntu VM show? Please add to you question along with above comment answers so folks don't have to work through the comments. – Adrian Klaver Aug 10 '20 at 19:37
  • I updated the post to show result of that command and also the current config that it's using. I don't know what to change it to without breaking it cause it seems to break whenever I change it to anything. My friend helped me set up the postgres database but I'm pretty sure it's just running on my Windows machine and I'm trying to connect to it from the Ubuntu server – Break Aug 11 '20 at 17:50

1 Answers1

3

client.pg_con = await asyncpg.create_pool(database=database, user=user, password=password, host="127.0.0.1")

Add host="127.0.0.1" as well, this solved my problem.

Yalchin Mammadli
  • 444
  • 4
  • 11