Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Having issues with the postgres db #48

Closed
havedill opened this issue Jan 23, 2020 · 4 comments
Closed

Having issues with the postgres db #48

havedill opened this issue Jan 23, 2020 · 4 comments

Comments

@havedill
Copy link
Contributor

havedill commented Jan 23, 2020 •
edited

I seem to be struggling to send the activation email due to issues with the postgres db

Maillog

Jan 23 23:45:06 ip-172-31-59-45 postfix/trivial-rewrite[5669]: warning: connect to pgsql server localhost: could not connect to server: Connection refused??Is the server running on host "localhost" (127.0.0.1) and accepting??TCP/IP connections on port 5432??

root@ip-172-31-59-45:~# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                      NAMES
348d9e88da2c        simplelogin/app     "yacron -c /code/cro…"   4 days ago          Up 4 minutes                                   sl-cron
7933ea24f373        simplelogin/app     "python email_handle…"   4 days ago          Up 4 minutes        0.0.0.0:20381->20381/tcp   sl-email
58869fcb6dfd        simplelogin/app     "gunicorn wsgi:app -…"   4 days ago          Up 4 minutes        0.0.0.0:7777->7777/tcp     sl-app
fc73436f558a        postgres            "docker-entrypoint.s…"   4 days ago          Up 2 minutes        5432/tcp                   sl-db

root@ip-172-31-59-45:~# docker exec -it sl-db psql -U emailalias simplelogin
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

simplelogin=# SELECT domain FROM custom_domain WHERE domain='%s' AND verified=true UNION SELECT '%s' WHERE '%s' = 'XXXXXXX.info' LIMIT 1;
 domain
--------
(0 rows)

More postfix logs


root@ip-172-31-59-45:~# Jan 24 00:02:52 ip-172-31-59-45 postfix/smtpd[7722]: connect from unknown[1.1.1.4]
Jan 24 00:02:52 ip-172-31-59-45 postfix/trivial-rewrite[7151]: warning: connect to pgsql server localhost: could not connect to server: Connection refused??Is the server running on host "localhost" (127.0.0.1) and accepting??TCP/IP connections on port 5432??
Jan 24 00:02:52 ip-172-31-59-45 postfix/trivial-rewrite[7151]: warning: relay_domains: pgsql:/etc/postfix/pgsql-relay-domains.cf: table lookup problem
Jan 24 00:02:52 ip-172-31-59-45 postfix/trivial-rewrite[7151]: warning: relay_domains lookup failure
Jan 24 00:02:52 ip-172-31-59-45 postfix/trivial-rewrite[7151]: warning: relay_domains: pgsql:/etc/postfix/pgsql-relay-domains.cf: table lookup problem
Jan 24 00:02:52 ip-172-31-59-45 postfix/trivial-rewrite[7151]: warning: relay_domains lookup failure
Jan 24 00:02:52 ip-172-31-59-45 postfix/smtpd[7722]: NOQUEUE: reject: RCPT from unknown[1.1.1.4]: 451 4.3.0 <hXXXX@gmail.com>: Temporary lookup failure; from=<support@XXXXX.info> to=<XXXXXXvXX@gmail.com> proto=ESMTP helo=<[1.1.1.4]>
Jan 24 00:02:52 ip-172-31-59-45 postfix/smtpd[7722]: lost connection after RSET from unknown[1.1.1.4]
Jan 24 00:02:52 ip-172-31-59-45 postfix/smtpd[7722]: disconnect from unknown[1.1.1.4] ehlo=1 mail=1 rcpt=0/1 rset=1 commands=3/4
@havedill
Copy link
Contributor Author

havedill commented Jan 24, 2020 •
edited

So i deleted my postgres db and ran teh following (in my attempt to open port 5432)

root@ip-172-31-59-45:~# docker run -d \
>     --name sl-db \
>     -e POSTGRES_PASSWORD=XXXXXXXXXXXXXXXXXXXXX\
>     -e POSTGRES_USER=XXXXXXXXXXXXXX\
>     -e POSTGRES_DB=simplelogin \
>     -p 5432:5432 \
>     --network="sl-network" \
>     postgres

And heres the logs now. Seems like it connects but the queries are wrong?

root@ip-172-31-59-45:~# echo "test" | mailx -s "test" XXXXXXXX@gmail.com
root@ip-172-31-59-45:~# Jan 24 00:07:15 ip-172-31-59-45 postfix/pickup[6861]: 8549141C97: uid=0 from=<root@ip-172-31-59-45.ec2.internal>
Jan 24 00:07:15 ip-172-31-59-45 postfix/trivial-rewrite[8912]: warning: pgsql query failed: fatal error from host localhost: ERROR:  relation "custom_domain" does not exist?LINE 1: SELECT 'smtp:127.0.0.1:20381' FROM custom_domain WHERE domai...?                                           ^?
Jan 24 00:07:15 ip-172-31-59-45 postfix/trivial-rewrite[8912]: warning: pgsql:/etc/postfix/pgsql-transport-maps.cf lookup error for "*"
Jan 24 00:07:15 ip-172-31-59-45 postfix/trivial-rewrite[8912]: warning: pgsql:/etc/postfix/pgsql-transport-maps.cf lookup error for "*"
Jan 24 00:07:15 ip-172-31-59-45 postfix/cleanup[8911]: 8549141C97: message-id=<20200124000715.8549141C97@app.XXXXXX.info>
Jan 24 00:07:15 ip-172-31-59-45 postfix/qmgr[6862]: 8549141C97: from=<root@ip-172-31-59-45.ec2.internal>, size=349, nrcpt=1 (queue active)
Jan 24 00:07:15 ip-172-31-59-45 postfix/trivial-rewrite[8912]: warning: pgsql query failed: fatal error from host localhost: ERROR:  relation "custom_domain" does not exist?LINE 1: SELECT domain FROM custom_domain WHERE domain='gmail.com' AN...?                           ^?
Jan 24 00:07:15 ip-172-31-59-45 postfix/trivial-rewrite[8912]: warning: relay_domains: pgsql:/etc/postfix/pgsql-relay-domains.cf: table lookup problem
Jan 24 00:07:15 ip-172-31-59-45 postfix/trivial-rewrite[8912]: warning: relay_domains lookup failure
Jan 24 00:07:15 ip-172-31-59-45 postfix/error[8915]: 8549141C97: to=<XXXXXX@gmail.com>, relay=none, delay=0.08, delays=0.05/0.02/0/0.01, dsn=4.3.0, status=deferred (address resolver failure)
Jan 24 00:07:15 ip-172-31-59-45 postfix/error[8915]: warning: pgsql query failed: fatal error from host localhost: ERROR:  relation "custom_domain" does not exist?LINE 1: SELECT domain FROM custom_domain WHERE domain='gmail.com' AN...?                           ^?
Jan 24 00:07:15 ip-172-31-59-45 postfix/error[8915]: warning: fast_flush_domains: pgsql:/etc/postfix/pgsql-relay-domains.cf: table lookup problem
Jan 24 00:07:15 ip-172-31-59-45 postfix/error[8915]: warning: 8549141C97: flush service failure

@havedill
Copy link
Contributor Author

havedill commented Jan 24, 2020 •
edited

OK so it seems either i forgot, or needed to rerun the following

docker run --rm \
    --name sl-migration \
    -v $(pwd)/dkim.key:/dkim.key \
    -v $(pwd)/dkim.pub.key:/dkim.pub.key \
    -v $(pwd)/simplelogin.env:/code/.env \
    --network="sl-network" \
    simplelogin/app:1.0.0 flask db upgrade

Now my issue is im just having issues hitting gmails smtp servers..

Jan 24 00:30:17 ip-172-31-59-45 postfix/smtpd[9959]: connect from unknown[1.1.1.4]
Jan 24 00:30:17 ip-172-31-59-45 postfix/smtpd[9959]: 4DAC647D8C: client=unknown[1.1.1.4]
Jan 24 00:30:17 ip-172-31-59-45 postfix/cleanup[9944]: 4DAC647D8C: message-id=<157982581730.9.14289193416466083985@58869fcb6dfd>
Jan 24 00:30:17 ip-172-31-59-45 postfix/qmgr[6862]: 4DAC647D8C: from=<support@#XXXXXXXX.info>, size=14498, nrcpt=1 (queue active)
Jan 24 00:30:17 ip-172-31-59-45 postfix/smtpd[9959]: disconnect from unknown[1.1.1.4] ehlo=1 mail=1 rcpt=1 data=1 commands=4
Jan 24 00:30:47 ip-172-31-59-45 postfix/smtp[9946]: connect to gmail-smtp-in.l.google.com[173.194.66.26]:25: Connection timed out
Jan 24 00:30:47 ip-172-31-59-45 postfix/smtp[9946]: connect to gmail-smtp-in.l.google.com[2607:f8b0:400d:c03::1b]:25: Network is unreachable
Jan 24 00:30:47 ip-172-31-59-45 postfix/smtp[9946]: connect to alt1.gmail-smtp-in.l.google.com[2800:3f0:4003:c00::1b]:25: Network is unreachable
Jan 24 00:31:17 ip-172-31-59-45 postfix/smtp[9946]: connect to alt1.gmail-smtp-in.l.google.com[64.233.186.27]:25: Connection timed out

root@ip-172-31-59-45:~# ping google.com
PING google.com (172.217.5.238) 56(84) bytes of data.
64 bytes from iad30s07-in-f238.1e100.net (172.217.5.238): icmp_seq=1 ttl=48 time=0.738 ms

@havedill
Copy link
Contributor Author

root@ip-172-31-59-45:~# ping alt1.gmail-smtp-in.l.google.com PING alt1.gmail-smtp-in.l.google.com (64.233.186.27) 56(84) bytes of data. 64 bytes from cb-in-f27.1e100.net (64.233.186.27): icmp_seq=1 ttl=37 time=122 ms

@havedill
Copy link
Contributor Author

Ok changed

inet_protocols = ipv4

and now im getting the emails, although to my spam folder :(

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@havedill