<NME> pg_hba.conf.erb
<BEF> # This file is managed by gitlab-ctl. Manual changes will be
# erased! To change the contents below, edit /etc/gitlab/gitlab.rb
# and run `sudo gitlab-ctl reconfigure`.

# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the
# PostgreSQL documentation for a complete description
# of this file.  A short synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTION]
# host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
# hostssl    DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
# hostnossl  DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain socket,
# "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an
# SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", a database name, or
# a comma-separated list thereof.
#
# USER can be "all", a user name, a group name prefixed with "+", or
# a comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names from
# a separate file.
#
# CIDR-ADDRESS specifies the set of hosts the record matches.
# It is made up of an IP address and a CIDR mask that is an integer
# (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies
# the number of significant bits in the mask.  Alternatively, you can write
# an IP address and netmask in separate columns to specify the set of hosts.
#
# METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi",
# "krb5", "ident", "pam" or "ldap".  Note that "password" sends passwords
# in clear text; "md5" is preferred since it sends encrypted passwords.
#
# OPTION is the ident map or the name of the PAM service, depending on METHOD.
#
# Database and user names containing spaces, commas, quotes and other special
# characters must be quoted. Quoting one of the keywords "all", "sameuser" or
# "samerole" makes the name lose its special character, and just match a
# database or username with that name.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can use
# "pg_ctl reload" to do that.

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records. In that case you will also need to make PostgreSQL listen
# on a non-local interface via the listen_addresses configuration parameter,
# or via the -i or -h command line switches.
#


# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
<% @custom_pg_hba_entries.each do |name, entries| %>
# <%= name %>
<% entries.each do |entry| %>
<%= entry['type'] %> <%= entry['database'] %> <%= entry['user'] %> <%= entry['cidr'] %> <%= entry['method'] %> <%= entry['option'] %>
<% end %>
<% end %>

# "local" is for Unix domain socket connections only
local   all         all                               peer map=gitlab

<% @trust_auth_cidr_addresses.each do |cidr| %>
host<% if @hostssl %>ssl<% end %>    all         all         <%= cidr %>           trust
  <% if @sql_replication_user %>
host<% if @hostssl %>ssl<% end %>    replication <%= @sql_replication_user %> <%= cidr %>     trust
  <% end %>
<% end %>

<% @md5_auth_cidr_addresses.each do |cidr| %>
host<% if @hostssl %>ssl<% end %>    all         all         <%= cidr %>           md5
  <% if @sql_replication_user %>
host<% if @hostssl %>ssl<% end %>    replication <%= @sql_replication_user %> <%= cidr %>     md5
  <% end %>
<% end %>


<MSG> Merge branch 'postgresql-cert-hba' into 'master'

Add support for PostgreSQL certificate authentication

See merge request gitlab-org/omnibus-gitlab!4618
<DFF> @@ -89,3 +89,6 @@ host<% if @hostssl %>ssl<% end %>    replication <%= @sql_replication_user %> <%
   <% end %>
 <% end %>
 
+<% @cert_auth_addresses.each do |addr, data| %>
+hostssl <%= data['database'] %> <%= data['user'] %> <%= addr %> cert
+<% end %>

{'commit': 'diff --git a/changelogs/unreleased/postgresql-cert-hba.yml b/changelogs/unreleased/postgresql-cert-hba.yml\nnew file mode 100644\nindex 000000000..ca01e0f1d\n--- /dev/null\n+++ b/changelogs/unreleased/postgresql-cert-hba.yml\n@@ -0,0 +1,5 @@\n+---\n+title: Add support for cert authentication with PostgreSQL\n+merge_request: 4618\n+author:\n+type: changed\ndiff --git a/doc/settings/database.md b/doc/settings/database.md\nindex 423f62139..2936a3fd8 100644\n--- a/doc/settings/database.md\n+++ b/doc/settings/database.md\n@@ -168,7 +168,59 @@ gitlabhq_production=> SELECT * FROM pg_stat_ssl;\n (19 rows)\n ```\n \n-Rows that have `t` listed under the `ssl` column are enabled.\n+1. Rows that have `t` listed under the `ssl` column are enabled.\n+1. Rows that have a value in the `clientdn` are using the `cert` authentication method\n+\n+#### Configure SSL client authentication\n+\n+Client SSL certificates can be used to authenticate to the database server. Creating the certificates\n+is beyond the scope of `omnibus-gitlab`. But users who have an existing SSL certificate management solution\n+can use this.\n+\n+##### Configure the database server\n+\n+1. Create a certificate and key for the server, the common name should equal the DNS name of the server\n+1. Copy the server certificate, key, and CA file to the PostgreSQL server, and ensure the permissions are correct\n+   1. The certificate should be owned by the database user (default: `gitlab-psql`)\n+   1. The key file should be owned by the database user, and its permissions should be `0400`\n+   1. The CA file should be owned by the database user, and its permissions should be `0400`\n+\n+   NOTE: **Note:**\n+   Do not use the filenames `server.crt` or `server.key` for these files. These are reserved for internal use of `omnibus-gitlab`.\n+\n+1. Ensure the following is set in `gitlab.rb`.\n+\n+   ```ruby\n+   postgresql[\'ssl_cert_file\'] = \'PATH_TO_CERTIFICATE\'\n+   postgresql[\'ssl_key_file\'] = \'PATH_TO_KEY_FILE\'\n+   postgresql[\'ssl_ca_file\'] = \'PATH_TO_CA_FILE\'\n+   postgresql[\'listen_address\'] = \'IP_ADDRESS\'\n+   postgresql[\'cert_auth_addresses\'] = {\n+   \'IP_ADDRESS\' => {\n+     \'database\' => \'gitlabhq_production\',\n+     \'user\' => \'gitlab\'\n+   }\n+   ```\n+\n+   `listen_address` should be set to an IP address of the server that the clients will use to connect to the database.\n+   `cert_auth_addresses` should contain a list of IP addresses, and the databases and users that are allowed to connect to the database.\n+1. Run `gitlab-ctl reconfigure` then `gitlab-ctl restart postgresql` in order for the new settings to take effect\n+\n+#### Configure the Rails client\n+\n+In order for the rails client to connect to the server, you will need a certficate and key with the `commonName` set to `gitlab`, that is signed by a certificate authority trusted in the CA file specified in `ssl_ca_file` on the database server.\n+\n+1. Configure `gitlab.rb`\n+\n+   ```ruby\n+   gitlab_rails[\'db_host\'] = \'IP_ADDRESS_OR_HOSTNAME_OF_DATABASE_SERVER\'\n+   gitlab_rails[\'db_sslcert\'] = \'PATH_TO_CERTIFICATE_FILE\'\n+   gitlab_rails[\'db_sslkey\'] = \'PATH_TO_KEY_FILE\'\n+   gitlab_rails[\'db_rootcert\'] = \'PATH_TO_CA_FILE\'\n+   ```\n+\n+1. Run `gitlab-ctl reconfigure` for the rails client to use the new settings\n+1. Follow the steps in [Verifying that SSL is being used](#verifying-that-ssl-is-being-used) to ensure the authentication is working.\n \n ### Configure packaged PostgreSQL server to listen on TCP/IP\n \ndiff --git a/files/gitlab-config-template/gitlab.rb.template b/files/gitlab-config-template/gitlab.rb.template\nindex 90a997544..40c99d6a1 100644\n--- a/files/gitlab-config-template/gitlab.rb.template\n+++ b/files/gitlab-config-template/gitlab.rb.template\n@@ -1048,6 +1048,12 @@ gitlab_rails[\'object_store\'][\'objects\'][\'pages\'][\'bucket\'] = nil\n # postgresql[\'ssl_key_file\'] = \'server.key\'\n # postgresql[\'ssl_ca_file\'] = \'/opt/gitlab/embedded/ssl/certs/cacert.pem\'\n # postgresql[\'ssl_crl_file\'] = nil\n+# postgresql[\'cert_auth_addresses\'] = {\n+#   \'ADDRESS\' => {\n+#     database: \'gitlabhq_production\',\n+#     user: \'gitlab\'\n+#   }\n+# }\n \n ### Replication settings\n ###! Note, some replication settings do not require a full restart. They are documented below.\ndiff --git a/files/gitlab-cookbooks/postgresql/attributes/default.rb b/files/gitlab-cookbooks/postgresql/attributes/default.rb\nindex b7fb1f2eb..0ef3541b1 100644\n--- a/files/gitlab-cookbooks/postgresql/attributes/default.rb\n+++ b/files/gitlab-cookbooks/postgresql/attributes/default.rb\n@@ -34,6 +34,7 @@ default[\'postgresql\'][\'ssl_cert_file\'] = \'server.crt\'\n default[\'postgresql\'][\'ssl_key_file\'] = \'server.key\'\n default[\'postgresql\'][\'ssl_ca_file\'] = "#{node[\'package\'][\'install-dir\']}/embedded/ssl/certs/cacert.pem"\n default[\'postgresql\'][\'ssl_crl_file\'] = nil\n+default[\'postgresql\'][\'cert_auth_addresses\'] = {}\n \n default[\'postgresql\'][\'shmmax\'] = /x86_64/.match?(node[\'kernel\'][\'machine\']) ? 17179869184 : 4294967295\n default[\'postgresql\'][\'shmall\'] = /x86_64/.match?(node[\'kernel\'][\'machine\']) ? 4194304 : 1048575\ndiff --git a/files/gitlab-cookbooks/postgresql/templates/default/pg_hba.conf.erb b/files/gitlab-cookbooks/postgresql/templates/default/pg_hba.conf.erb\nindex 698130a3b..bbd764572 100644\n--- a/files/gitlab-cookbooks/postgresql/templates/default/pg_hba.conf.erb\n+++ b/files/gitlab-cookbooks/postgresql/templates/default/pg_hba.conf.erb\n@@ -89,3 +89,6 @@ host<% if @hostssl %>ssl<% end %>    replication <%= @sql_replication_user %> <%\n   <% end %>\n <% end %>\n \n+<% @cert_auth_addresses.each do |addr, data| %>\n+hostssl <%= data[\'database\'] %> <%= data[\'user\'] %> <%= addr %> cert\n+<% end %>\ndiff --git a/spec/chef/recipes/postgresql_spec.rb b/spec/chef/recipes/postgresql_spec.rb\nindex df68ce9df..001016f5a 100644\n--- a/spec/chef/recipes/postgresql_spec.rb\n+++ b/spec/chef/recipes/postgresql_spec.rb\n@@ -832,6 +832,33 @@ RSpec.describe \'postgresql 9.6\' do\n       expect(postgresql_config).to notify(\'execute[reload postgresql]\').to(:run).immediately\n       expect(postgresql_config).to notify(\'execute[start postgresql]\').to(:run).immediately\n     end\n+\n+    context \'cert authentication\' do\n+      it \'is disabled by default\' do\n+        expect(chef_run).to render_file(pg_hba_conf).with_content { |content|\n+          expect(content).to_not match(/cert$/)\n+        }\n+      end\n+\n+      it \'can be enabled\' do\n+        stub_gitlab_rb(\n+          postgresql: {\n+            cert_auth_addresses: {\n+              \'1.2.3.4/32\' => {\n+                database: \'fakedatabase\',\n+                user: \'fakeuser\'\n+              },\n+              \'fakehostname\' => {\n+                database: \'anotherfakedatabase\',\n+                user: \'anotherfakeuser\'\n+              },\n+            }\n+          }\n+        )\n+        expect(chef_run).to render_file(pg_hba_conf).with_content(\'hostssl fakedatabase fakeuser 1.2.3.4/32 cert\')\n+        expect(chef_run).to render_file(pg_hba_conf).with_content(\'hostssl anotherfakedatabase anotherfakeuser fakehostname cert\')\n+      end\n+    end\n   end\n \n   it \'creates sysctl files\' do\n', 'commit_subject': "Merge branch 'postgresql-cert-hba' into 'master'", 'repo_name': 'gitlabhq/omnibus-gitlab', 'committer': {'name': 'Balasankar "Balu" C', 'email': '250a398eb3bfb9862062144cc742cc8f9bdd8d78@gitlab.com', 'time_sec': '1602853171', 'tz_offset': '0', 'date': {'seconds': '1602853171'}}, 'commit_tree': '4f6d24f06c09a97478f54a682ff9a5d6ebf23005', 'commit_message': "Merge branch 'postgresql-cert-hba' into 'master'\n\nAdd support for PostgreSQL certificate authentication\n\nSee merge request gitlab-org/omnibus-gitlab!4618", 'language_name': 'Ruby', 'license': 'apache-2.0', 'hunks': "@@ -89,3 +89,6 @@ host<% if @hostssl %>ssl<% end %>    replication <%= @sql_replication_user %> <%\n   <% end %>\n <% end %>\n \n+<% @cert_auth_addresses.each do |addr, data| %>\n+hostssl <%= data['database'] %> <%= data['user'] %> <%= addr %> cert\n+<% end %>\n", 'addition_count': 3, 'deletion_count': 0, 'src_file': 'a/files/gitlab-cookbooks/postgresql/templates/default/pg_hba.conf.erb', 'tgt_file': 'b/files/gitlab-cookbooks/postgresql/templates/default/pg_hba.conf.erb', 'file_extension': '.erb', 'before_file': '# This file is managed by gitlab-ctl. Manual changes will be\n# erased! To change the contents below, edit /etc/gitlab/gitlab.rb\n# and run `sudo gitlab-ctl reconfigure`.\n\n# PostgreSQL Client Authentication Configuration File\n# ===================================================\n#\n# Refer to the "Client Authentication" section in the\n# PostgreSQL documentation for a complete description\n# of this file.  A short synopsis follows.\n#\n# This file controls: which hosts are allowed to connect, how clients\n# are authenticated, which PostgreSQL user names they can use, which\n# databases they can access.  Records take one of these forms:\n#\n# local      DATABASE  USER  METHOD  [OPTION]\n# host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]\n# hostssl    DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]\n# hostnossl  DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]\n#\n# (The uppercase items must be replaced by actual values.)\n#\n# The first field is the connection type: "local" is a Unix-domain socket,\n# "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an\n# SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.\n#\n# DATABASE can be "all", "sameuser", "samerole", a database name, or\n# a comma-separated list thereof.\n#\n# USER can be "all", a user name, a group name prefixed with "+", or\n# a comma-separated list thereof.  In both the DATABASE and USER fields\n# you can also write a file name prefixed with "@" to include names from\n# a separate file.\n#\n# CIDR-ADDRESS specifies the set of hosts the record matches.\n# It is made up of an IP address and a CIDR mask that is an integer\n# (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies\n# the number of significant bits in the mask.  Alternatively, you can write\n# an IP address and netmask in separate columns to specify the set of hosts.\n#\n# METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi",\n# "krb5", "ident", "pam" or "ldap".  Note that "password" sends passwords\n# in clear text; "md5" is preferred since it sends encrypted passwords.\n#\n# OPTION is the ident map or the name of the PAM service, depending on METHOD.\n#\n# Database and user names containing spaces, commas, quotes and other special\n# characters must be quoted. Quoting one of the keywords "all", "sameuser" or\n# "samerole" makes the name lose its special character, and just match a\n# database or username with that name.\n#\n# This file is read on server startup and when the postmaster receives\n# a SIGHUP signal.  If you edit the file on a running system, you have\n# to SIGHUP the postmaster for the changes to take effect.  You can use\n# "pg_ctl reload" to do that.\n\n# Put your actual configuration here\n# ----------------------------------\n#\n# If you want to allow non-local connections, you need to add more\n# "host" records. In that case you will also need to make PostgreSQL listen\n# on a non-local interface via the listen_addresses configuration parameter,\n# or via the -i or -h command line switches.\n#\n\n\n# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD\n<% @custom_pg_hba_entries.each do |name, entries| %>\n# <%= name %>\n<% entries.each do |entry| %>\n<%= entry[\'type\'] %> <%= entry[\'database\'] %> <%= entry[\'user\'] %> <%= entry[\'cidr\'] %> <%= entry[\'method\'] %> <%= entry[\'option\'] %>\n<% end %>\n<% end %>\n\n# "local" is for Unix domain socket connections only\nlocal   all         all                               peer map=gitlab\n\n<% @trust_auth_cidr_addresses.each do |cidr| %>\nhost<% if @hostssl %>ssl<% end %>    all         all         <%= cidr %>           trust\n  <% if @sql_replication_user %>\nhost<% if @hostssl %>ssl<% end %>    replication <%= @sql_replication_user %> <%= cidr %>     trust\n  <% end %>\n<% end %>\n\n<% @md5_auth_cidr_addresses.each do |cidr| %>\nhost<% if @hostssl %>ssl<% end %>    all         all         <%= cidr %>           md5\n  <% if @sql_replication_user %>\nhost<% if @hostssl %>ssl<% end %>    replication <%= @sql_replication_user %> <%= cidr %>     md5\n  <% end %>\n<% end %>\n\n', 'message': "Merge branch 'postgresql-cert-hba' into 'master'\n\nAdd support for PostgreSQL certificate authentication\n\nSee merge request gitlab-org/omnibus-gitlab!4618", 'author_name': 'Balasankar "Balu" C', 'lang': 'conf'}