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?

8
  • 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? Commented Aug 10, 2020 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 Commented Aug 10, 2020 at 17:20
  • So where is the program that throwing the error above running? Also is Postgres installed on the Ubuntu VM and running? Commented Aug 10, 2020 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. Commented Aug 10, 2020 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. Commented Aug 10, 2020 at 19:11

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.