Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxTw4-00072t-A0 for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Aug 2019 10:21:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hxTw1-0008JZ-Cq for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Aug 2019 10:21:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxTw0-0008JI-Ui for pgsql-hackers@lists.postgresql.org; Tue, 13 Aug 2019 10:21:45 +0000 Received: from chandler.sharp.fm ([80.168.143.3]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1hxTvx-0004Vm-DA for pgsql-hackers@postgresql.org; Tue, 13 Aug 2019 10:21:43 +0000 Received: from [10.253.32.213] (unknown [5.56.169.80]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) (Authenticated sender: minfrin@sharp.fm) by chandler.sharp.fm (Postfix) with ESMTPSA id A0E36E8587; Tue, 13 Aug 2019 11:21:39 +0100 (BST) DKIM-Filter: OpenDKIM Filter v2.11.0 chandler.sharp.fm A0E36E8587 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sharp.fm; s=default; t=1565691699; bh=IAwsZbJmCAqJYkbNL4whn9W8cfsGNIsPrHbiH8t0Lro=; h=From:Subject:Date:In-Reply-To:Cc:To:References:From; b=p4+u7SDAdKnR2YBKFiL/AZ5WEav44Oa9HzCo8JoSWv+A9gUHVxoCN5XkZl+kW6A2Z XbOkiX/PP8OpFuJco7maF9wqi/N8iHMjxPiJctHGV7g+Ie1J06Uv4MjK1htxUlE3AB 9VUxoHUybwG+VG7gky6dBG3KxUIOz3Ix00u3fZ2k= From: Graham Leggett Message-Id: <191719E9-6D3B-4E43-82F3-E7CC7871F173@sharp.fm> Content-Type: multipart/signed; boundary="Apple-Mail=_7D5BC362-2B35-4F07-A599-BFB8B1419CFB"; protocol="application/pkcs7-signature"; micalg=sha-256 Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Re: Feature: Use DNS SRV records for connecting Date: Tue, 13 Aug 2019 12:21:37 +0200 In-Reply-To: Cc: PostgreSQL mailing lists To: Feike Steenbergen References: X-Mailer: Apple Mail (2.3445.104.11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_7D5BC362-2B35-4F07-A599-BFB8B1419CFB Content-Type: multipart/alternative; boundary="Apple-Mail=_D271C698-653B-4DA0-B8A9-8E128B1AEFA6" --Apple-Mail=_D271C698-653B-4DA0-B8A9-8E128B1AEFA6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On 13 Aug 2019, at 11:50, Feike Steenbergen = wrote: > I'd like to get some feedback on whether or not implementing a DNS SRV = feature > for connecting to PostgreSQL would be desirable/useful. A big +1. We currently use SRV records to tell postgresql what kind of server it = is. This way all of our postgresql servers have an identical = configuration, they just tailor themselves on startup as appropriate: _postgresql-master._tcp.sql.example.com. The above record in our case declares who the master is. If the = postgresql startup says =E2=80=9Chey, that=E2=80=99s me=E2=80=9D it = configures itself as a master. If the postgresql startup says =E2=80=9Chey= , that=E2=80=99s not me=E2=80=9D it configures itself as a slave of the = master. We also use TXT records to define the databases we want (with protection = against DNS security issues, we never remove a database based on a TXT = record, but signed DNS records will help here). _postgresql.sql.example.com TXT "v=3DPGSQL1;d=3Dmydb;u=3Dmyuser" We use a series of systemd =E2=80=9Cdaemons=E2=80=9D that are configured = to run before and after postgresql to do the actual configuration on = bootup, but it would be great if postgresql could just do this out the = box. Regards, Graham =E2=80=94 --Apple-Mail=_D271C698-653B-4DA0-B8A9-8E128B1AEFA6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 On = 13 Aug 2019, at 11:50, Feike Steenbergen <feikesteenbergen@gmail.com> wrote:

I'd like to get = some feedback on whether or not implementing a DNS SRV feature
for connecting to PostgreSQL = would be desirable/useful.
A big +1.

We = currently use SRV records to tell postgresql what kind of server it is. = This way all of our postgresql servers have an identical configuration, = they just tailor themselves on startup as appropriate:

_postgresql-master._tcp.sql.example.com.

The above record in our case declares who the = master is. If the postgresql startup says =E2=80=9Chey, that=E2=80=99s = me=E2=80=9D it configures itself as a master. If the postgresql startup = says =E2=80=9Chey, that=E2=80=99s not me=E2=80=9D it configures itself = as a slave of the master.

We also = use TXT records to define the databases we want (with protection against = DNS security issues, we never remove a database based on a TXT record, = but signed DNS records will help here).

_postgresql.sql.example.com TXT "v=3DPGSQL1;d=3Dmydb;u=3Dmyuser"

We use a series of = systemd =E2=80=9Cdaemons=E2=80=9D that are configured to run before and = after postgresql to do the actual configuration on bootup, but it would = be great if postgresql could just do this out the box.

Regards,
Graham
=E2=80=94

= --Apple-Mail=_D271C698-653B-4DA0-B8A9-8E128B1AEFA6-- --Apple-Mail=_7D5BC362-2B35-4F07-A599-BFB8B1419CFB Content-Disposition: attachment; filename=smime.p7s Content-Type: application/pkcs7-signature; name=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExDzANBglghkgBZQMEAgEFADCABgkqhkiG9w0BBwEAAKCCCZIw ggRaMIIDQqADAgECAg5HwxAAGJ3AQRyfPlRoQTANBgkqhkiG9w0BAQUFADBXMQswCQYDVQQGEwJC RTEZMBcGA1UEChMQR2xvYmFsU2lnbiBudi1zYTEQMA4GA1UECxMHUm9vdCBDQTEbMBkGA1UEAxMS R2xvYmFsU2lnbiBSb290IENBMB4XDTE2MDMxNjAwMDAwMFoXDTI0MDMxNjAwMDAwMFowVDELMAkG A1UEBhMCQkUxGTAXBgNVBAoTEEdsb2JhbFNpZ24gbnYtc2ExKjAoBgNVBAMTIUdsb2JhbFNpZ24g UGVyc29uYWxTaWduIDIgQ0EgLSBHMzCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKSP dFvrWfQ03SWpWG9CwOisShmwDfAmLfGc9ZXXfdLL1asfIljqZSi29tNKG2rMEP3picdEM4hGNejA ANbbI40WVztQQ7T6uo6xMpB23zXtgZI5wTkaVD5nuBgLrmiDVj4o45b48I+lpF1g5ao7DfbW7Zup XvXXBbxfwIDknIl2ld+0G6V70nLpZVJMLzV4Tq4g+t2zOUqDNujrjD9slJX5lCn+jfBFWs4t42HF XKnYVObGQVIZg7rJKqSBmi7tE0J2UzFjT66Pei//PNOWOgREVuGQXBAuLMjoderYT6lZsNegkVUO je1+Ike3PsRqGCJR7BW6ni30Ze7ZUTs81IkCAwEAAaOCASUwggEhMA4GA1UdDwEB/wQEAwIBBjAS BgNVHRMBAf8ECDAGAQH/AgEAMB0GA1UdDgQWBBRkN5W3gUTjWp14LLIDUTmdvQHJUTAfBgNVHSME GDAWgBRge2YaRQ2XyolQL30EzTSo//z9SzA9BggrBgEFBQcBAQQxMC8wLQYIKwYBBQUHMAGGIWh0 dHA6Ly9vY3NwLmdsb2JhbHNpZ24uY29tL3Jvb3RyMTAzBgNVHR8ELDAqMCigJqAkhiJodHRwOi8v Y3JsLmdsb2JhbHNpZ24uY29tL3Jvb3QuY3JsMEcGA1UdIARAMD4wPAYEVR0gADA0MDIGCCsGAQUF BwIBFiZodHRwczovL3d3dy5nbG9iYWxzaWduLmNvbS9yZXBvc2l0b3J5LzANBgkqhkiG9w0BAQUF AAOCAQEAyu7QSwY+EGtu8r64Ofc1+G/ZsUsrpU3ZViR+NdxZE3LCxFUaE2pzqAYqlXw1e3Bq+F09 YM4su2pzUrSE+uPx402T2HK2xAAZgshpV0d/Ybf8zI6h9ty15tqOmJOOuLfXjfgaAgX0cLKpgu3B b8sRUMJucH8EGGtBFezm8BslBXSwC5kUrHL4cZV3vpe/2I3WKHW8RkO93e/i6KPnxaW6FGJsyGT1 wOPPVlisADjNJ+hqQgL7hXxYUBy1B/v+qKHHkkEJa1+Jedo5H6qpmfefwFEkdGFJY+lFvOud4kLK VPmCzvrMTiKlIvgEhmefHgXh5yJ/kUPQUUntu/8QJI/+LTCCBTAwggQYoAMCAQICDGQOau5+udWO eEju0jANBgkqhkiG9w0BAQUFADBUMQswCQYDVQQGEwJCRTEZMBcGA1UEChMQR2xvYmFsU2lnbiBu di1zYTEqMCgGA1UEAxMhR2xvYmFsU2lnbiBQZXJzb25hbFNpZ24gMiBDQSAtIEczMB4XDTE2MTAw MjIyMDM1NVoXDTE5MTIyNzIxMTIyNlowgYsxCzAJBgNVBAYTAkdCMQ8wDQYDVQQIEwZMb25kb24x DzANBgNVBAcTBkxvbmRvbjEgMB4GA1UEChMXUGVwcGVycG90IE1lZGlhIExpbWl0ZWQxFzAVBgNV BAMTDkdyYWhhbSBMZWdnZXR0MR8wHQYJKoZIhvcNAQkBFhBtaW5mcmluQHNoYXJwLmZtMIIBIjAN BgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvz1BnGpS6Rj8OzA23A5MLZI4cNrNntEiPrRZhh3L +A0jnoQASx9qemYsHn85EVqWKu01OrtsKBoHC49AD8oGSviKnfKmTi2DAkRpLRL3dJWd1zpcopsd Uco/VICfR88jJMEgjTKq7kHnm9ghVfHfTCXwyhfZCUoC5f2086JOFlvCU/t81WAPMne0j1ek2wav qr+iSFJpDr0OYDAY4jjVPslgbNeijzTVzvmETRX6CeQVovKPszytqwbhfyS+1af8uIyzKoofhdM6 IHb3KnnjXYtDiuEXtb/+/vykIen9ORxWZrbVMdXFtHLZyPWziVvWlcEsjFf7qJvUDo21fkQxSQID AQABo4IByDCCAcQwDgYDVR0PAQH/BAQDAgWgMIGWBggrBgEFBQcBAQSBiTCBhjBJBggrBgEFBQcw AoY9aHR0cDovL3NlY3VyZS5nbG9iYWxzaWduLmNvbS9jYWNlcnQvZ3NwZXJzb25hbHNpZ24yZzNv Y3NwLmNydDA5BggrBgEFBQcwAYYtaHR0cDovL29jc3AyLmdsb2JhbHNpZ24uY29tL2dzcGVyc29u YWxzaWduMmczME0GA1UdIARGMEQwQgYKKwYBBAGgMgEoCjA0MDIGCCsGAQUFBwIBFiZodHRwczov L3d3dy5nbG9iYWxzaWduLmNvbS9yZXBvc2l0b3J5LzAJBgNVHRMEAjAAMEMGA1UdHwQ8MDowOKA2 oDSGMmh0dHA6Ly9jcmwuZ2xvYmFsc2lnbi5jb20vZ3MvZ3NwZXJzb25hbHNpZ24yZzMuY3JsMBsG A1UdEQQUMBKBEG1pbmZyaW5Ac2hhcnAuZm0wHQYDVR0lBBYwFAYIKwYBBQUHAwIGCCsGAQUFBwME MB0GA1UdDgQWBBSYfvlbW+sB1vS/ul5Fi0wzbJ/juTAfBgNVHSMEGDAWgBRkN5W3gUTjWp14LLID UTmdvQHJUTANBgkqhkiG9w0BAQUFAAOCAQEAmb7wgcASHY12fJy0N5xZirdawv0I/XsuGn0cQ+r1 CePuoYeROBuuNrsn5lHqUMznz0C9vHOrHfAbG7sqMjc3KQ7sUMjQBtBiL7Xad4PAmqbFyjQmdkL6 SBNbjYA3UIUuro+CMOgVaVd3JJbE2c8FCzFepqWFzBuJA0LSsV0I+0Acq2yRJPkw7qGmJ+QAIs+w EMLIghsqnuj51TFm4eHCnQLzf0s7Zry+H+/nZhHMMlh8iGmWMhtXLUGMYxZcnR1GgxPESQ6E/0rE GVjVaq98o9o4CxaRbBxs8lv7nmmSAOX7NOPEi2X++QFRThPPdjIUOk0O6A+6GoLW9Du4BjP0BTGC AugwggLkAgEBMGQwVDELMAkGA1UEBhMCQkUxGTAXBgNVBAoTEEdsb2JhbFNpZ24gbnYtc2ExKjAo BgNVBAMTIUdsb2JhbFNpZ24gUGVyc29uYWxTaWduIDIgQ0EgLSBHMwIMZA5q7n651Y54SO7SMA0G CWCGSAFlAwQCAQUAoIIBVTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEP Fw0xOTA4MTMxMDIxMzdaMC8GCSqGSIb3DQEJBDEiBCAgbK2UMs1Xhuo8TBfnnaVBWnW6oPDs9/t4 UyTaB7lU9TBzBgkrBgEEAYI3EAQxZjBkMFQxCzAJBgNVBAYTAkJFMRkwFwYDVQQKExBHbG9iYWxT aWduIG52LXNhMSowKAYDVQQDEyFHbG9iYWxTaWduIFBlcnNvbmFsU2lnbiAyIENBIC0gRzMCDGQO au5+udWOeEju0jB1BgsqhkiG9w0BCRACCzFmoGQwVDELMAkGA1UEBhMCQkUxGTAXBgNVBAoTEEds b2JhbFNpZ24gbnYtc2ExKjAoBgNVBAMTIUdsb2JhbFNpZ24gUGVyc29uYWxTaWduIDIgQ0EgLSBH MwIMZA5q7n651Y54SO7SMA0GCSqGSIb3DQEBAQUABIIBAHkm1bWSOaPtR1QnctG3dcUFzrSjHYAi K30EnuqnWT3wgaAB6xUTmckTrGVQYUR5pAGA8V8MPrxfAeU/9imtq1dlLvOxDU3WgSPUKjGBthWE ROWZoGggapxwZIHIp6ocSY1bgd5sdQ4L5muYjLXOYE5PWr8/U928O5vhlbwubC6nnRQmxWdJSEis 1K93N3LUowZMlID3/z34WRS5/h8AmXDuBPz1dBwt2dsrMMsAhv1+axrmc1ccB0ZVsbKEciyjh7G5 qyFyD5Rb+3lLEhH9s3mMjmX9iqOH9g1HF5FwSSBWDZ/XmeQ6m10/y9+M2+uqe/Csu2Bhff8zhG7n soDCUJgAAAAAAAA= --Apple-Mail=_7D5BC362-2B35-4F07-A599-BFB8B1419CFB--