error ... (auth) read error: Resource temporarily unavailable

[timlok]

Hello!

Problem: There is no access to the public schema of another user's database. When connecting to a postgresql server without odyssey, there is access.

Users are postgres (superuser) and zabbix. The zabbix user is the owner of the zabbix database. When connecting via odyssey by postgres, you cannot read the public schema of the zabbix database.

Connect to postgresql server without odyssey:

[root@hl-pg-conpool01 ~]# psql -h 10.51.21.68 -p 5432 -U postgres -d zabbix -c "\d" | head -n 6
Password for user postgres:
                    List of relations
 Schema |            Name            |   Type   | Owner
--------+----------------------------+----------+--------
 public | acknowledges               | table    | zabbix
 public | actions                    | table    | zabbix
 public | alerts                     | table    | zabbix

Connect to postgresql server via odyssey

[root@hl-pg-conpool01 ~]# psql -h 127.0.0.1 -p 5000 -U postgres -d zabbix -c "\d" | head -n 6
Password for user postgres:
Did not find any relations.
[root@hl-pg-conpool01 ~]# psql -h 127.0.0.1 -p 5000 -U zabbix -d zabbix -c "\d" | head -n 6
Password for user zabbix: 
                    List of relations
 Schema |            Name            |   Type   | Owner  
--------+----------------------------+----------+--------
 public | acknowledges               | table    | zabbix
 public | actions                    | table    | zabbix
 public | alerts                     | table    | zabbix

At the same time, with any attempt to log in by any user in the logs, the following messages:

Dec 02 13:33:12 hl-pg-conpool01 odyssey[2487]: 2487 02 Dec 13:33:12.094 error [c8b88b95d7a0f none] [user - zabbix, db - zabbix] (auth) read error: Resource temporarily unavailable
Dec 02 13:35:50 hl-pg-conpool01 odyssey[2487]: 2487 02 Dec 13:35:50.207 error [ce4c708129718 none] [user - postgres, db - zabbix] (auth) read error: Resource temporarily unavailable
Dec 02 13:36:55 hl-pg-conpool01 odyssey[2487]: 2487 02 Dec 13:36:55.181 error [cf75fc65a5160 none] [user - zabbix, db - zabbix] (auth) read error: Resource temporarily unavailable

I use: Centos 7.6 odyssey rpm package compiled with pgsql-11.6 (PGDG) odyssey version:

[root@hl-pg-conpool01 odyssey]# git log -n 1
commit 95f174003117018073ab16d4c57478d19d6cab5b
Author: Dima Starkov <dvstark@yandex.ru>
Date:   Sun Nov 24 21:00:00 2019 +0500

    Add integration tests for SCRAM Authentication (#78)

    * Add integration tests for SCRAM Authentication

    * Made tests independent of the postgresql version

configuration file:

###
### SERVICE
###

#daemonize no
#priority -10
# pid_file "/var/run/odyssey.pid"

unix_socket_dir "/tmp"
unix_socket_mode "0644"

###
### LOGGING
###

#log_file "/var/log/odyssey.log"

log_format "%p %t %l [%i %s] [user - %u, db - %d] (%c) %m\n"
log_config yes
#log_debug yes
#log_session yes
#log_query yes
#log_stats yes
log_debug no
log_session no
log_query no
log_stats no

#stats_interval 60
stats_interval 300

###
### PERFORMANCE
###

#workers 1
#resolvers 1
readahead 8192
cache_coroutine 210

nodelay yes
keepalive 7200

###
### GLOBAL LIMITS
###

#client_max 2000
client_max_routing 32

###
### LISTEN
###

listen {
    tls "disable"
    host "*"
#    port 6432
    port 5000
}

###
### ROUTING
###

storage "local" {
    type "local"
    #tls "disable"
}

database "console" {
    user "odyssey" {
        #authentication "none"
        authentication "md5"
        password "odyssey"
        pool "session"
        storage "local"
    }
}

storage "postgres_server" {
    type "remote"
    tls "disable"
    host "10.51.21.68"
    port 5432
}

database "postgres" {
    user "postgres" {
        authentication "md5"
        password "postgres_password"
        storage "postgres_server"
        storage_user "postgres"
        storage_password "postgres_password"
        pool "session"
        client_max 4
        pool_size 0
        pool_timeout 0
        pool_ttl 60
        pool_cancel yes
        pool_discard yes
        pool_rollback yes
        client_fwd_error yes
        log_debug no
    }
}

database "zabbix" {
    user "postgres" {
        authentication "md5"
        password "postgres_password"
        storage "postgres_server"
        storage_user "postgres"
        storage_db "postgres"
        storage_password "postgres_password"
        #pool "transaction"
        pool "session"
        client_max 10
        pool_size 0
        pool_timeout 0
        pool_ttl 60
        pool_cancel yes
        #pool_discard no
        pool_discard yes
        pool_rollback yes
        client_fwd_error yes
        log_debug no
    }
    user "zabbix" {
        authentication "md5"
        password "zabbix_password"
        storage "postgres_server"
        storage_user "zabbix"
        storage_password "zabbix_password"
        #pool "transaction"
        pool "session"
        client_max 2000
        pool_size 187
        pool_timeout 0
        pool_ttl 60
        pool_cancel yes
        #pool_discard no
        pool_discard yes
        pool_rollback yes
        client_fwd_error yes
        log_debug no
    }
}

[x4m]

Hi! Thanks for feedback and sorry for so late reply!

I think what happens is:

1. psql first will try SSL, unsuccessfully
2. then connect without SSL

point 1 causes these log lines. We could make logging better here...

[timlok]

Such symptoms are not only with psql, but good. But what to do with access to the public scheme?

[x4m]

what to do with access to the public scheme?

I think the problem is in search_path parameter of different users.

[adampielak]

I have same situation with configuration 1:1 from @timlok but, after working two days Dec 29 02:15:55 localhost odyssey[27182]: 27182 29 Dec 02:15:55.497 info [c050eb45e8f48 s49f42f1204f9] [user - zabbix, db - zabbix] (main) client disconnected (read/write error, addr 10.39.64.21:51024): Resource temporarily unavailable Dec 29 02:15:55 localhost odyssey[27182]: 27182 29 Dec 02:15:55.498 info [c0327073e4ba9 s10d0695d7387] [user - zabbix, db - zabbix] (main) client disconnected (read/write error, addr 10.39.64.21:51064): Resource temporarily unavailable Dec 29 02:15:55 localhost odyssey[27182]: 27182 29 Dec 02:15:55.498 info [cee71f03bd96a s9ecefe6bab75] [user - zabbix, db - zabbix] (main) client disconnected (read/write error, addr 10.39.64.21:51094): Resource temporarily unavailable 3xDB Centos7 PostgreSQL 11 2xPooler Centos8 Odyssey git: 3974c0d

I still analyze, because now after restart odyssey, +/- 1 minute i have same problem on logs

[x4m]

@adampielak do you observe any other effects besides log messages?

[timlok]

Everything is clear with logs and this is not a problem. But the lack of access to the public scheme is a problem.