Open haroonb opened 3 years ago
I've already commented on the pg_hba change here. It's true that it's not additive anymore - an oversight we should have mentioned in the release notes. The error messages at the end of you pasted log snippet look strange though.
The conflict message on the users can be explained like this: The operator will create a pooler user which you've specified in the connectionPooler
section. Internally the pooler role will be more like a system role and not like an app user you list in the users
part. Actually, you don't have to put pgbouncer
role there. The operator will interpret it as two different roles first, only to find out that there's a name conflict.
edit: I found out, that I can change the user
to a custom user if I change the user part in the connectionPooler
, but I doesn't accept a array, meaning multiple users. And even if I login as a admin user e.g. 'pgbouncer' I still can't connect to another db, because of the ssl error.
Hello @FxKu, thanks for the reply. I think I understand this implementation of pgbouncer a bit better now.
I'm running into the issue, that I can't use the pgbouncer user I created with the postgres-cluster to access the database through the pooler, since it is not added in the auth_file.txt
. How can I add a users to the 'auth_file.txt'.
@devops-m1:~$ kubectl exec -it acid-test-cluster-pooler-b47588474-nfqr9 -- cat /etc/pgbouncer/auth_file.txt
"pooler" "FpLIEOjwTvoUrZGpzGAMSB8d3cEmho31cxxduyxheeEvRa0fGB1V396jeGY4OsZy"
If I try to use the pgbouncer user to login, I get a error, that ssl is required.
devops-m1:~$ psql postgres://pgbouncer@10.107.138.153/pgbouncer
psql: ERROR: not allowed
ERROR: SSL required
acid-test-cluster-pooler-b47588474-nmnrsacid-test-cluster-pooler-b47588474-twpbg connection-pooler 2021-01-25 09:43:15.835 UTC [1] WARNING C-0x5605de073010: pgbouncer/pgbouncer@10.244.0.0:58320 pooler error: not allowed
connection-pooler 2021-01-25 09:43:15.836 UTC [1] WARNING C-0x5629a9f9a010: (nodb)/(nouser)@10.244.0.0:22907 pooler error: SSL required
I definetly know, that the pgbouncer user exists.
devops-m1:~$ kubectl exec -it acid-test-cluster-0 -- psql -U postgres
psql (13.1 (Ubuntu 13.1-1.pgdg18.04+1), server 12.5 (Ubuntu 12.5-1.pgdg18.04+1))
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-------------
admin | Create DB, Cannot login | {pgbouncer}
pgbouncer | Superuser, Create DB | {}
pooler | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
robot_zmon | Cannot login | {}
standby | Replication | {}
zalandos | Create DB, Cannot login | {}
postgres=#
Is there a possibility to customize the pgbouncer.ini file to suite our needs? The ssl requirement seems hardcoded at first glance.
devops-m1:~$ kubectl exec -it acid-test-cluster-pooler-b47588474-nfqr9 -- cat /etc/pgbouncer/pgbouncer.ini
# vim: set ft=dosini:
[databases]
* = host=acid-test-cluster port=5432 auth_user=pooler
[pgbouncer]
pool_mode = transaction
listen_port = 5432
listen_addr = *
auth_type = plain
auth_file = /etc/pgbouncer/auth_file.txt
admin_users = pooler
auth_query = SELECT * FROM pooler.user_lookup($1)
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
server_tls_sslmode = require
server_tls_ca_file = /etc/ssl/certs/pgbouncer.crt
server_tls_protocols = secure
client_tls_sslmode = require
client_tls_key_file = /etc/ssl/certs/pgbouncer.key
client_tls_cert_file = /etc/ssl/certs/pgbouncer.crt
log_connections = 0
log_disconnections = 0
# How many server connections to allow per user/database pair.
default_pool_size = 50
# Add more server connections to pool if below this number. Improves behavior
# when usual load comes suddenly back after period of total inactivity.
min_pool_size = 25
# How many additional connections to allow to a pool
reserve_pool_size = 25
# Maximum number of client connections allowed.
max_client_conn = 10000
# Do not allow more than this many connections per database (regardless of
# pool, i.e. user)
max_db_connections = 100
# If a client has been in "idle in transaction" state longer, it will be
# disconnected. [seconds]
idle_transaction_timeout = 600
# If login failed, because of failure from connect() or authentication that
# pooler waits this much before retrying to connect. Default is 15. [seconds]
server_login_retry = 5
# To ignore extra parameter in startup packet. By default only 'database' and
# 'user' are allowed, all others raise error. This is needed to tolerate
# overenthusiastic JDBC wanting to unconditionally set 'extra_float_digits=2'
# in startup packet.
ignore_startup_parameters = extra_float_digits,options
143 - name: create postgres cluster
142 k8s:
141 state: present
140 definition:
139 apiVersion: "acid.zalan.do/v1"
138 kind: postgresql
137 metadata:
136 name: acid-test-cluster
135 namespace: default
134 spec:
133 dockerImage: registry.opensource.zalan.do/acid/spilo-13:2.0-p2
132 teamId: "acid"
131 numberOfInstances: 3 # 2
130 users: # Application/Robot users
129 pgbouncer:
128 - superuser
127 - createdb
126 enableLogicalBackup: false
125 enableMasterLoadBalancer: false
124 enableReplicaLoadBalancer: false
123 enableConnectionPooler: true # enable/disable connection pooler deployment
122 enableReplicaConnectionPooler: false # set to enable connectionPooler for replica service
121 allowedSourceRanges: # load balancers' source ranges for both master and replica services
120 - 127.0.0.1/32
119 # databases:
118 # foo: zalando
117 # preparedDatabases:
116 # bar:
115 # defaultUsers: true
114 # extensions:
113 # pg_partman: public
112 # pgcrypto: public
111 # schemas:
110 # data: {}
109 # history:
108 # defaultRoles: true
107 # defaultUsers: false
106 postgresql:
105 version: "{{ postgres_version }}"
104 parameters: # Expert section
103 tcp_keepalives_count: "5"
102 tcp_keepalives_interval: "60"
101 tcp_keepalives_idle: "60"
100 shared_buffers: "4GB"
99 max_connections: "300"
98 log_statement: "none"
97 volume:
96 size: "{{ postgres_pvsize }}G"
95 storageClass: rook-ceph-block
94 additionalVolumes:
93 - name: empty
92 mountPath: /opt/empty
91 targetContainers:
90 - all
89 volumeSource:
88 emptyDir: {}
87 # - name: data
86 # mountPath: /home/postgres/pgdata/partitions
85 # targetContainers:
84 # - postgres
83 # volumeSource:
82 # PersistentVolumeClaim:
81 # claimName: pvc-postgresql-data-partitions
80 # readyOnly: false
79 # - name: conf
78 # mountPath: /etc/telegraf
77 # subPath: telegraf.conf
76 # targetContainers:
75 # - telegraf-sidecar
74 # volumeSource:
73 # configMap:
72 # name: my-config-map
71
70 enableShmVolume: true
69 # spiloRunAsUser: 101
68 # spiloRunAsGroup: 103
67 # spiloFSGroup: 103
66 podAnnotations:
65 backup.velero.io/backup-volumes: pgdata
64 # serviceAnnotations:
63 # annotation.key: value
62 # podPriorityClassName: "spilo-pod-priority"
61 # tolerations:
60 # - key: postgres
59 # operator: Exists
58 # effect: NoSchedule
57 resources:
56 requests:
55 cpu: 10m
54 memory: 100Mi
53 limits:
52 cpu: 5000m # 500m
51 memory: 5000Mi # 500Mi
50 patroni:
49 initdb:
48 encoding: "UTF8"
47 locale: "en_US.UTF-8"
46 data-checksums: "true"
45 pg_hba:
44 - hostssl all all 0.0.0.0/0 md5
43 - host all all 0.0.0.0/0 md5
42 - hostssl replication postgres 0.0.0.0/0 md5
41 - host replication postgres 0.0.0.0/0 md5
40 - local all all trust
39 - host all all 127.0.0.1/32 md5
38 - host all all ::1/128 md5
37 - hostssl replication standby all md5
36 - hostnossl all all all reject
35 - hostssl all all all md5
34 # slots:
33 # permanent_physical_1:
32 # type: physical
31 # permanent_logical_1:
30 # type: logical
29 # database: foo
28 # plugin: pgoutput
27 ttl: 30
26 loop_wait: &loop_wait 10
25 retry_timeout: 10
24 synchronous_mode: false
23 synchronous_mode_strict: false
22 maximum_lag_on_failover: 33554432
21
20 # restore a Postgres DB with point-in-time-recovery
19 # with a non-empty timestamp, clone from an S3 bucket using the latest backup before the timestamp
18 # with an empty/absent timestamp, clone from an existing alive cluster using pg_basebackup
17 # clone:
16 # uid: "efd12e58-5786-11e8-b5a7-06148230260c"
15 # cluster: "acid-batman"
14 # timestamp: "2017-12-19T12:40:33+01:00" # timezone required (offset relative to UTC, see RFC 3339 section 5.6)
13 # s3_wal_path: "s3://custom/path/to/bucket"
12
11 # run periodic backups with k8s cron jobs
10 # enableLogicalBackup: true
9 # logicalBackupSchedule: "30 00 * * *"
8
7 # maintenanceWindows:
6 # - 01:00-06:00 #UTC
5 # - Sat:00:00-04:00
4
3 # overwrite custom properties for connection pooler deployments
2 connectionPooler:
1 numberOfInstances: 3
180 mode: "transaction"
1 schema: "pooler"
2 user: "pooler"
3 maxDBConnections: 300 # custom
4 resources:
5 requests:
6 cpu: 300m
7 memory: 100Mi
8 limits:
9 cpu: "1"
10 memory: 100Mi
11
12 initContainers:
13 - name: date
14 image: busybox
15 command: [ "/bin/date" ]
16 # sidecars:
17 # - name: "telegraf-sidecar"
18 # image: "telegraf:latest"
19 # resources:
20 # limits:
21 # cpu: 500m
22 # memory: 500Mi
23 # requests:
24 # cpu: 100m
25 # memory: 100Mi
26 # env:
27 # - name: "USEFUL_VAR"
28 # value: "perhaps-true"
29
30 # Custom TLS certificate. Disabled unless tls.secretName has a value.
31 tls:
32 secretName: "" # should correspond to a Kubernetes Secret resource to load
33 certificateFile: "tls.crt"
34 privateKeyFile: "tls.key"
35 caFile: "" # optionally configure Postgres with a CA certificate
36 caSecretName: "" # optionally the ca.crt can come from this secret instead.
37 # file names can be also defined with absolute path, and will no longer be relative
38 # to the "/tls/" path where the secret is being mounted by default, and "/tlsca/"
39 # where the caSecret is mounted by default.
40 # When TLS is enabled, also set spiloFSGroup parameter above to the relevant value.
41 # if unknown, set it to 103 which is the usual value in the default spilo images.
42 # In Openshift, there is no need to set spiloFSGroup/spilo_fsgroup.
43
44 # Add node affinity support by allowing postgres pods to schedule only on nodes that
45 # have label: "postgres-operator:enabled" set.
46 # nodeAffinity:
47 # requiredDuringSchedulingIgnoredDuringExecution:
48 # nodeSelectorTerms:
49 # - matchExpressions:
50 # - key: postgres-operator
51 # operator: In
52 # values:
53 # - enabled
~
Thank you for the clarification and assistance.
Please, answer some short questions which should help us to understand your problem / question better?
Hi everyone, we are planning on updating our production postgres operator in the near future. I'm running a few tests on a test cluster and am running into a few issues. Currently we're using the operator from version 1.4.0.
Iis it intended, that the
pg_hba
section is not additive anymore, because I configured both statefulsets the same, but the pg_hba.conf is different.The next issue I'm experiencing is, that the pooler is not beeing deployed, even though I gave the parameters for the
schema
anduser
. The operator is giving a debug message aboutresolved a conflict of role \"pgbouncer\" between manifest role and connection pooler role to connection pooler role" cluster-name=postgres-test/acid-test-cluster pkg=cluster
, but I don't know what that is supposed to mean.