-
Marcia Ramos authoredMarcia Ramos authored
stage: Enablement
group: Distribution
info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#designated-technical-writers
Database settings (FREE SELF)
GitLab supports only PostgreSQL database management system.
Thus you have two options for database servers to use with Omnibus GitLab:
- Use the packaged PostgreSQL server included with Omnibus GitLab (no configuration required, recommended).
- Use an external PostgreSQL server.
Using the PostgreSQL Database Service shipped with Omnibus GitLab
Reconfigure and PostgreSQL restarts
Omnibus normally restarts any service on reconfigure if config settings for that service were
changed in the gitlab.rb
file. PostgreSQL is unique in that some of its settings will take effect
with a reload (HUP), while others require PostgreSQL to be restarted. Because administrators
frequently want more control over exactly when PostgreSQL is restarted, Omnibus has been configured
to do a reload of PostgreSQL on reconfigure, and not a restart. This means that if you modify any
PostgreSQL setting that requires a restart, you will need to restart PostgreSQL manually after you
reconfigure.
The GitLab config template
identifies which PostgreSQL settings require a restart and which require only a reload. You can also
run a query against your database to determine if any individual setting requires a restart. Start a
database console with sudo gitlab-psql
, then replace <setting name>
in the following query
with the setting you are changing:
SELECT name,setting FROM pg_settings WHERE context = 'postmaster' AND name = '<setting name>';
If changing the setting will require a restart, the query will return the name of the setting and the current value of that setting in the running PostgreSQL instance.
Configuring SSL
Omnibus automatically enables SSL on the PostgreSQL server, but it will accept
both encrypted and unencrypted connections by default. Enforcing SSL requires
using the hostssl
configuration in pg_hba.conf
. For more details, see the
pg_hba.conf
documentation.
SSL support depends on the following files:
- The public SSL certificate for the database (
server.crt
). - The corresponding private key for the SSL certificate (
server.key
). - A root certificate bundle that validates the server's certificate (
root.crt
). By default, Omnibus GitLab uses the embedded certificate bundle in/opt/gitlab/embedded/ssl/certs/cacert.pem
. This isn't required for self-signed certificates.
A 10-year self-signed certificate and private key will be generated by Omnibus GitLab for use. If you'd prefer to use a CA-signed certificate or replace this with your own self-signed certificate, use the following steps.
Note that the location of these files can be configurable, but the private key
must be readable by the gitlab-psql
user. Omnibus manages the permissions of
the files for you, but if the paths are customized, you must ensure that the
gitlab-psql
can access the directory the files are placed in.
For more details, see the PostgreSQL documentation.
Note that server.crt
and server.key
may be different from the default SSL
certificates used to access GitLab. For example, suppose the external hostname
of your database is database.example.com
, and your external GitLab hostname
is gitlab.example.com
. You will either need a wildcard certificate for
*.example.com
or two different SSL certificates.
After you have these files, enable SSL:
-
Edit
/etc/gitlab/gitlab.rb
:postgresql['ssl_cert_file'] = '/custom/path/to/server.crt' postgresql['ssl_key_file'] = '/custom/path/to/server.key' postgresql['ssl_ca_file'] = '/custom/path/to/bundle.pem' postgresql['internal_certificate'] = "-----BEGIN CERTIFICATE----- ...base64-encoded certificate... -----END CERTIFICATE----- " postgresql['internal_key'] = "-----BEGIN RSA PRIVATE KEY----- ...base64-encoded private key... -----END RSA PRIVATE KEY----- "
Relative paths will be rooted from the PostgreSQL data directory (
/var/opt/gitlab/postgresql/data
by default). -
Reconfigure GitLab to apply the configuration changes.
-
Restart PostgreSQL for the changes to take effect:
gitlab-ctl restart postgresql
If PostgreSQL fails to start, check the logs (for example,
/var/log/gitlab/postgresql/current
) for more details.
Require SSL
-
Add the following to
/etc/gitlab/gitlab.rb
:postgresql['db_sslmode'] = 'require'
-
Reconfigure GitLab to apply the configuration changes.
-
Restart PostgreSQL for the changes to take effect:
gitlab-ctl restart postgresql
If PostgreSQL fails to start, check the logs (for example,
/var/log/gitlab/postgresql/current
) for more details.
Disabling SSL
-
Add the following to
/etc/gitlab/gitlab.rb
:postgresql['ssl'] = 'off'
-
Reconfigure GitLab to apply the configuration changes.
-
Restart PostgreSQL for the changes to take effect:
gitlab-ctl restart postgresql
If PostgreSQL fails to start, check the logs (for example,
/var/log/gitlab/postgresql/current
) for more details.
Verifying that SSL is being used
To determine whether SSL is being used by clients, you can run:
sudo gitlab-rails dbconsole --database main
In GitLab 14.1 and earlier:
sudo gitlab-rails dbconsole
At startup, you should see a banner as the following:
psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)
Type "help" for help.
To determine if clients are using SSL, issue this SQL query:
SELECT * FROM pg_stat_ssl;
For example:
gitlabhq_production=> select * from pg_stat_ssl;
pid | ssl | version | cipher | bits | compression | clientdn
------+-----+---------+------------------------+------+-------------+------------
384 | f | | | | |
386 | f | | | | |
998 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
933 | f | | | | |
1003 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1016 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1022 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1211 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1214 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1213 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1215 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1252 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f |
1280 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
382 | f | | | | |
381 | f | | | | |
383 | f | | | | |
(16 rows)
- Rows that have
t
listed under thessl
column are enabled. - Rows that have a value in the
clientdn
are using thecert
authentication method
Configure SSL client authentication
Client SSL certificates can be used to authenticate to the database server. Creating the certificates
is beyond the scope of omnibus-gitlab
. But users who have an existing SSL certificate management solution
can use this.
Configure the database server
-
Create a certificate and key for the server, the common name should equal the DNS name of the server
-
Copy the server certificate, key, and CA file to the PostgreSQL server, and ensure the permissions are correct
- The certificate should be owned by the database user (default:
gitlab-psql
) - The key file should be owned by the database user, and its permissions should be
0400
- The CA file should be owned by the database user, and its permissions should be
0400
NOTE: Don't use the filenames
server.crt
orserver.key
for these files. These filenames are reserved for the internal use ofomnibus-gitlab
. - The certificate should be owned by the database user (default:
-
Ensure the following is set in
gitlab.rb
:postgresql['ssl_cert_file'] = 'PATH_TO_CERTIFICATE' postgresql['ssl_key_file'] = 'PATH_TO_KEY_FILE' postgresql['ssl_ca_file'] = 'PATH_TO_CA_FILE' postgresql['listen_address'] = 'IP_ADDRESS' postgresql['cert_auth_addresses'] = { 'IP_ADDRESS' => { 'database' => 'gitlabhq_production', 'user' => 'gitlab' }
Set
listen_address
as the IP address of the server that the clients will use to connect to the database. Ensurecert_auth_addresses
contains a list of IP addresses, and the databases and users that are allowed to connect to the database. You can use CIDR notation when specifying the key forcert_auth_addresses
to incorporate an IP address range. -
Run
gitlab-ctl reconfigure
, and thengitlab-ctl restart postgresql
for the new settings to take effect.
Configure the Rails client
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.
-
Configure
gitlab.rb
gitlab_rails['db_host'] = 'IP_ADDRESS_OR_HOSTNAME_OF_DATABASE_SERVER' gitlab_rails['db_sslcert'] = 'PATH_TO_CERTIFICATE_FILE' gitlab_rails['db_sslkey'] = 'PATH_TO_KEY_FILE' gitlab_rails['db_rootcert'] = 'PATH_TO_CA_FILE'
-
Run
gitlab-ctl reconfigure
for the rails client to use the new settings -
Follow the steps in Verifying that SSL is being used to ensure the authentication is working.
Configure packaged PostgreSQL server to listen on TCP/IP
The packaged PostgreSQL server can be configured to listen for TCP/IP connections, with the caveat that some non-critical scripts expect UNIX sockets and may misbehave.
To configure the use of TCP/IP for the database service, make changes to both
the postgresql
and gitlab_rails
sections of gitlab.rb
.
Configure PostgreSQL block
The following settings are affected in the postgresql
block:
-
listen_address
: Controls the address on which PostgreSQL will listen. -
port
: Controls the port on which PostgreSQL will listen, and must be set iflisten_address
is. -
md5_auth_cidr_addresses
: A list of CIDR address blocks which are allowed to connect to the server, after authentication with a password. -
trust_auth_cidr_addresses
: A list of CIDR address blocks which are allowed to connect to the server, without authentication of any kind. Be very careful with this setting. It's suggested that this be limited to the loopback address of127.0.0.1/24
or even127.0.0.1/32
. -
sql_user
: Controls the expected username for MD5 authentication. This defaults togitlab
, and isn't a required setting. -
sql_user_password
: Sets the password that PostgreSQL will accept for MD5 authentication. Replacesecuresqlpassword
in the following example with an acceptable password.
postgresql['listen_address'] = '0.0.0.0'
postgresql['port'] = 5432
postgresql['md5_auth_cidr_addresses'] = %w()
postgresql['trust_auth_cidr_addresses'] = %w(127.0.0.1/24)
postgresql['sql_user'] = "gitlab"
##! SQL_USER_PASSWORD_HASH can be generated using the command `gitlab-ctl pg-password-md5 gitlab`,
##! where `gitlab` is the name of the SQL user that connects to GitLab.
postgresql['sql_user_password'] = "SQL_USER_PASSWORD_HASH"
# force ssl on all connections defined in trust_auth_cidr_addresses and md5_auth_cidr_addresses
postgresql['hostssl'] = true
Any client or GitLab service which will connect over the network will need to
provide the values of sql_user
for the username, and password provided to the
configuration when connecting to the PostgreSQL server. They must also be in the
network block provided to md5_auth_cidr_addresses
Configure GitLab Rails block
To configure the gitlab-rails
application to connect to the PostgreSQL database
over the network, several settings must be configured:
-
db_host
: Needs to be set to the IP address of the database sever. If this is on the same instance as the PostgreSQL service, this can be127.0.0.1
and will not require password authentication. -
db_port
: Sets the port on the PostgreSQL server to connect to, and must be set ifdb_host
is set. -
db_username
: Configures the username with which to connect to PostgreSQL. This defaults togitlab
. -
db_password
: Must be provided if connecting to PostgreSQL over TCP/IP, and from an instance in thepostgresql['md5_auth_cidr_addresses']
block from settings above. This is not required if you are connecting to127.0.0.1
and have configuredpostgresql['trust_auth_cidr_addresses']
to include it.
gitlab_rails['db_host'] = '127.0.0.1'
gitlab_rails['db_port'] = 5432
gitlab_rails['db_username'] = "gitlab"
gitlab_rails['db_password'] = "securesqlpassword"
Apply and restart services
After making the previous changes, an administrator should run gitlab-ctl reconfigure
.
If you experience any issues in regards to the service not listening on TCP, try
directly restarting the service with gitlab-ctl restart postgresql
.
Some included scripts of the Omnibus package (such as gitlab-psql
) expect the
connections to PostgreSQL to be handled over the UNIX socket, and may not function
properly. You can enable TCP/IP without disabling UNIX sockets.
Enabling PostgreSQL WAL (Write Ahead Log) Archiving
By default, WAL archiving of the packaged PostgreSQL isn't enabled. Consider the following when seeking to enable WAL archiving:
- The WAL level needs to be 'replica' or higher (9.6+ options are
minimal
,replica
, orlogical
) - Increasing the WAL level will increase the amount of storage consumed in regular operations
To enable WAL Archiving:
-
Edit
/etc/gitlab/gitlab.rb
:# Replication settings postgresql['sql_replication_user'] = "gitlab_replicator" postgresql['wal_level'] = "replica" ... ... # Backup/Archive settings postgresql['archive_mode'] = "on" postgresql['archive_command'] = "/your/wal/archiver/here" postgresql['archive_timeout'] = "60"
-
Reconfigure GitLab for the changes to take effect. This will result in a database restart.
Store PostgreSQL data in a different directory
By default, everything is stored under /var/opt/gitlab/postgresql
, controlled
by the postgresql['dir']
attribute.
This consists of:
- The database socket will be
/var/opt/gitlab/postgresql/.s.PGSQL.5432
. This is controlled bypostgresql['unix_socket_directory']
. - The
gitlab-psql
system user will have itsHOME
directory set to this. This is controlled bypostgresql['home']
. - The actual data will be stored in
/var/opt/gitlab/postgresql/data
.
To change the location of the PostgreSQL data