Unable to access psql terminal

[blackyzero]

Hello,

I am getting following error when trying to access psql terminal. This happens on all nodes Is it cause of using different default username in vars/main.yml file

patroni_superuser_username: "patroni_usr"

root@pgsql01:~# sudo -u postgres psql
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist
root@pgsql01:~#

root@pgsql01:~# ls -alh /var/run/postgresql/.s.PGSQL.5432
srwxrwxrwx 1 postgres postgres 0 Apr 25 14:26 /var/run/postgresql/.s.PGSQL.5432
root@pgsql01:~#

Thank you.

[vitabaks]

Hi @blackyzero

Please connect to the database and show the list of users

psql -U patroni_usr -d postgres

\du

[vitabaks]

patroni_superuser_username it name for the superuser, set during initialization (initdb)

https://patroni.readthedocs.io/en/latest/SETTINGS.html#postgresql

I guess patroni doesn't create a postgres user by default

[blackyzero]

Thanks for quick respond. It gives me following

root@pgnode01:/etc/postgresql/14/main# psql -U patroni_usr -d postgres
psql (14.2 (Debian 14.2-1.pgdg110+1))
Type "help" for help.

postgres=# \du
                                          List of roles
       Role name        |                         Attributes                         | Member of
------------------------+------------------------------------------------------------+-----------
 patroni_replicator_usr | Replication                                                | {}
 patroni_usr            | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

How could i correct it?

[vitabaks]

Specify the user in vars/main.yml

postgresql_users:
  - {name: "postgres", password: "postgres-pass", flags: "SUPERUSER"}

Execute playbook ansible-playbook deploy_pgcluster.yml --tags postgresql_users

[blackyzero]

I get following

TASK [Include OS-specific variables] ********************************************************
ok: [192.168.169.151]
ok: [192.168.169.152]

TASK [postgresql-users : Make sure the PostgreSQL users are present] ************************
failed: [192.168.169.151] (item=postgres) => {"ansible_loop_var": "item", "changed": false, "item": {"flags": "SUPERUSER", "name": "postgres", "password": "p0stgres_pss!"}, "msg": "unable to connect to database: connection to server on socket \"/var/run/postgresql/.s.PGSQL.5432\" failed: FATAL:  role \"postgres\" does not exist\n"}
...ignoring

PLAY RECAP **********************************************************************************
192.168.169.151            : ok=9    changed=0    unreachable=0    failed=0    skipped=6    rescued=0    ignored=1
192.168.169.152            : ok=8    changed=0    unreachable=0    failed=0    skipped=7    rescued=0    ignored=0
192.168.169.153            : ok=2    changed=0    unreachable=0    failed=0    skipped=3    rescued=0    ignored=0

[vitabaks]

ok I'll fix it

[vitabaks]

Fixed 83a2f3ec2de5e5cdca80a6ca1729fa375e47bb59

please download the playbook again and test it

[blackyzero]

i am still getting following error with new deployment.

# sudo -u postgres psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

[vitabaks]

error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"

we no longer use socket connection for this task https://github.com/vitabaks/postgresql_cluster/blob/master/roles/postgresql-users/tasks/main.yml#L11

TCP only - 127.0.0.1

please download the playbook again

[vitabaks]

Did you specify the user in the variables ?

postgresql_users:
  - {name: "postgres", password: "postgres-pass", flags: "SUPERUSER"}

[blackyzero]

yes, i already added this line before running deployment.

[vitabaks]

ok i will try to reproduce and fix it a bit later

[blackyzero]

thanks @vitabaks FYI, i also retry the deployment, and also getting same.

TASK [patroni : Disable "postgresql@14-main" service] ***************************************
ok: [192.168.169.152]
ok: [192.168.169.151]

TASK [patroni : Add PATRONICTL_CONFIG_FILE environment variable into /etc/environment] ******
ok: [192.168.169.152]
ok: [192.168.169.151]

TASK [postgresql-users : Make sure the PostgreSQL users are present] ************************
failed: [192.168.169.151] (item=postgres) => {"ansible_loop_var": "item", "changed": false, "item": {"flags": "SUPERUSER", "name": "postgres", "password": "p0stgres_p@sS121!"}, "msg": "unable to connect to database: connection to server on socket \"/var/run/postgresql/.s.PGSQL.5432\" failed: FATAL:  role \"postgres\" does not exist\n"}
...ignoring

TASK [pgbouncer/userlist : Get users and password md5 from pg_shadow] ***********************
ok: [192.168.169.151]

TASK [pgbouncer/userlist : Generate /etc/pgbouncer/userlist.txt] ****************************
changed: [192.168.169.152]
changed: [192.168.169.151]

RUNNING HANDLER [pgbouncer/userlist : Reload pgbouncer service] *****************************
changed: [192.168.169.152]
changed: [192.168.169.151]

TASK [deploy-finish : Check postgresql cluster health] **************************************
ok: [192.168.169.151]

TASK [deploy-finish : PostgreSQL Cluster health] ********************************************
ok: [192.168.169.151] => {
    "patronictl_result.stdout_lines": [
        "+---------+-----------------+--------------+---------+----+-----------+",
        "| Member  | Host            | Role         | State   | TL | Lag in MB |",
        "+ Cluster: postgres-cluster (7090936482763241882) ---+----+-----------+",
        "| pgsql01 | 192.168.169.151 | Leader       | running |  1 |           |",
        "| pgsql03 | 192.168.169.152 | Sync Standby | running |  1 |         0 |",
        "+---------+-----------------+--------------+---------+----+-----------+"
    ]
}

TASK [deploy-finish : Get postgresql database list] *****************************************
ok: [192.168.169.151]

TASK [deploy-finish : PostgreSQL list of databases] *****************************************
ok: [192.168.169.151] => {
    "dbs_result.stdout_lines": [
        "   name   |    owner    | encoding |   collate   |    ctype    |  size   | tablespace ",
        "----------+-------------+----------+-------------+-------------+---------+------------",
        " postgres | patroni_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 8553 kB | pg_default",
        "(1 row)"
    ]
}

TASK [deploy-finish : Create list of nodes] *************************************************
ok: [192.168.169.151]

TASK [deploy-finish : PostgreSQL Cluster connection info] ***********************************
ok: [192.168.169.151] => {
    "msg": [
        "+------------------------------------------------+",
        "address 192.168.169.151,192.168.169.152",
        "port 6432 (pgbouncer)",
        "+------------------------------------------------+"
    ]
}

PLAY RECAP **********************************************************************************
192.168.169.151            : ok=105  changed=7    unreachable=0    failed=0    skipped=348  rescued=0    ignored=1
192.168.169.152            : ok=93   changed=6    unreachable=0    failed=0    skipped=336  rescued=0    ignored=0
192.168.169.153            : ok=26   changed=1    unreachable=0    failed=0    skipped=113  rescued=0    ignored=0
localhost                  : ok=0    changed=0    unreachable=0    failed=0    skipped=1    rescued=0    ignored=0

root@ansible:/opt/services/ansible/postgresql_cluster#

root@pgsql01:~# sudo -u postgres psql
could not change directory to "/root": Permission denied
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

[blackyzero]

moved https://github.com/vitabaks/postgresql_cluster/issues/169

[vitabaks]

create separate issues for unrelated issues. This does not apply to "Unable to access sql terminal"

[blackyzero]

You're right. i have just created another issue regarding this. Thanks

[vitabaks]

Hello @blackyzero !

Fixed: 0752de87b26462edc5d19a09437880b0705abbff

TASK [postgresql-users : Make sure the PostgreSQL users are present] ********************************************************************************************************************************************
changed: [172.31.1.188] => (item=postgres)
changed: [172.31.1.188] => (item=mydb-user)

ubuntu@pgnode01:~/postgresql_cluster$ sudo -u postgres psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1+b1))
Type "help" for help.

postgres=# \du
                                        List of roles
     Role name      |                         Attributes                         | Member of 
--------------------+------------------------------------------------------------+-----------
 mydb-user          |                                                            | {}
 patroni_replicator | Replication                                                | {}
 patroni_usr        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres           | Superuser                                                  | {}

[blackyzero]

great ! thanks @vitabaks