In previous blog, we have setup Kerberos, added all required principals and verified each principal. This blog will explain all the necessary configuration, i.e.
pg_ident.conf, in PostgreSQL for user authentication using GSSAPI with Kerberos.
2. Build PostgreSQL with GSSAPI
The official PostgreSQL release for Ubuntu has GSSAPI enabled for user authentication with Kerberos, however if you want to build it from source code, you can simply enable it by giving the option
--with-gssapi in configure process like below.
$ ./configure --with-gssapi --prefix=/home/postgres/pgapp
There is another option
--with-krb-srvnam, PostgreSQL uses the default name of the Kerberos service principal. According to Postgres official document, “There’s usually no reason to change this unless you have a Windows environment, in which case it must be set to upper case POSTGRES“. In this blog, we keep it as default, and no extra configuration required for it. After enabled
--with-gssapi option, you can build, install, initialize database, change configuration and start database service as normal. The commands used in this blog are list below.
make && make install export PATH=/home/postgres/pgapp/bin:$PATH export PGDATA=/home/postgres/pgdata/data initdb -D $PGDATA
3. Keytab file
If you have followed the previous blog “part-1: how to setup Kerberos on Ubuntu”, then you should already have the keytab file. Now, copy the keytab file to Service Server (Postgres Server) and put it to a folder with appropriate permissions to the user owning the Postgres process. For example, user
postgres and folder
/home/postgres/pgdata/data in this blog. The instance principal extracted from KDC server can be verified using ktutil on Service Server.
postgres@pg:~$ ktutil ktutil: list slot KVNO Principal ---- ---- --------------------------------------------------------------------- ktutil: read_kt postgres.keytab ktutil: list slot KVNO Principal ---- ---- --------------------------------------------------------------------- 1 1 postgres/pg.highgo.ca@HIGHGO.CA ktutil:
postgres/pg.highgo.ca@HIGHGO.CA is the principal we defined in previous blog in the format of
primary/instance@REALM. In this example,
postgres is the service,
/pg.highgo.ca is the instance using the hostname.
4. PostgreSQL Configuration
Once keytab file has been set properly, we can configure the keytab file location in postgresql.conf like below.
krb_server_keyfile = '/home/postgres/pgdata/data/postgres.keytab'
Other than the keytab, we also need Postgres Server to allow connection from the network by change the listen_addresses.
listen_addresses = '*'
This is the minimum changes in postgresql.conf required for GSSAPI user authentication with Kerberos.
5. PostgreSQL Client Authentication
pg_hba.conf is the file used to control clients authentication in PostgreSQL, where
hba stands for host-based authentication. A default
pg_hba.conf file is installed when the data directory is initialized by initdb. For details please refer to the rules defined for this file, which provides a summary of the contents of the client authentication configuration file. The basic rule is that “The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no
backup: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.” In this blog, we will be focusing on GSSAPI releated users authentication using two key words, i.e.
hostnogssenc for the tests. For now, just put below two lines started with
hostgssenc after IPv4 local connections.
# IPv4 local connections: host all all 127.0.0.1/32 trust hostgssenc postgres david 192.168.0.0/24 gss include_realm=0 hostgssenc postgres postgres 192.168.0.0/24 gss include_realm=0
Here is how we define the user authentication for using GSSAPI according to PostgreSQL document.
hostgssencis used to match a TCP connection made with GSSAPI encryption.
postgresis the database name.
postgresare the users allowed to connect to the database.
192.168.0.0/24is the network for this particular setup.
gss include_realm=0means the authentication method
gssis used with the option
include_realm=0which indeicates the realm name from the authenticated user principal will be stripped off before being passed through the user name mapping.
6. PostgreSQL User Name Maps
pg_ident.conf is used to map the users. The mapping can be used when user want to use email like user name e.g.
firstname.lastname@example.org to log in the database. For example,
email@example.com could be mapped to just
postgres. According to PostgreSQL document, if
hostgssenc is used, then only three authentication options are allowed:
gss is selected as the authentication option, then you can use below three option to specify the
gss in further details.
- include_realm: default set to
0is specified as above example, then realm name from the authenticated user principal is stripped off.
- map: is the mapping between system and database user names. The mapping supports regular expression when system user started with
/. For example,
mymap /^(.*)@mydomain\.com$ \1is to remove the domain part for users from system user names.
- krb_realm: is used to match user principal names. If this parameter is set, only users of that realm will be accepted.
hostgssenc postgres postgres 192.168.0.0/24 gss include_realm=1 map=mymap krb_realm=HIGHGO.CA
gss include_realm=1 map=mymap krb_realm=HIGHGO.CA is saying, match a TCP connection with GSS encryption enabled and map system user using
mymap defined in
pg_ident.conf for user from reamlm
7. User Authentication
Now, we have one database admin user
postgres setup on PostgreSQL server, let’s restrict this
postgres user has to connect to Postgre server using GSSAPI user authentication with Kerberos.
7.1. database admin user local authentication
Disable all other user authentication
in pg_hba.conf, set user authentication to allow
include_realm=0 only, then restart Postgres server.
hostgssenc postgres postgres 192.168.0.0/24 gss include_realm=0
From Postgres server terminal, run
kdestroy -A to clean all cached credentials, then run
klist to check.
$ klist klist: No credentials cache found (filename: /tmp/krb5cc_1001)
No credentials cached yet, then run
kinit postgres to initial the user authentication.
$ kinit postgres Password for postgres@HIGHGO.CA:
Now, check the cached credentials again,
$ klist Ticket cache: FILE:/tmp/krb5cc_1001 Default principal: postgres@HIGHGO.CA Valid starting Expires Service principal 2020-03-15 14:17:07 2020-03-16 00:17:07 krbtgt/HIGHGO.CA@HIGHGO.CA renew until 2020-03-16 14:17:04
After the credentials has been cached, let’s try to log in use database admin user
$ psql -d postgres -h pg.highgo.ca -U postgres psql (12.2) GSSAPI-encrypted connection Type "help" for help. postgres=# \q
As the message
GSSAPI-encrypted connection above indicates the connection is encrypted. Let’s check the cached credentials again,
$ klist Ticket cache: FILE:/tmp/krb5cc_1001 Default principal: postgres@HIGHGO.CA Valid starting Expires Service principal 2020-03-15 14:17:07 2020-03-16 00:17:07 krbtgt/HIGHGO.CA@HIGHGO.CA renew until 2020-03-16 14:17:04 2020-03-15 14:21:21 2020-03-16 00:17:07 postgres/pg.highgo.ca@ renew until 2020-03-16 14:17:04 2020-03-15 14:21:21 2020-03-16 00:17:07 postgres/pg.highgo.ca@HIGHGO.CA renew until 2020-03-16 14:17:04
We can see the credentials for service principal
postgres/pg.highgo.ca has been cached.
From the above wireshark capture,
- message 1: AS_REQ is the initial user authentication request triggered by
- message 2: KRB Error is the reply from Authentication Server to ask password for user
- message 3: AS_REQ is the user authentication request encrypted using
- message 4: AS_REP is the encrypted reply from Authentication Server. Upon now,
kiniis done, and user
postgres‘s credential has been cached.
- message 5: TGS_REQ is the request from
psqlto Ticket Granting Server (TGS) for a service ticket.
- message 6: TGS_REP is the reply from Ticket Granting Server which contains a service session key generated by TGS and encrypted using a temporary session key generated by AS for user
Within the service principal expire time, any new log in from the same machine with user
postgres will not be required to provide the password. If you use wireshark to monitor the traffic again, you won’t see any
KRB5 message between KDC and Postgres Server.
7.2. database admin user remote authentication
Now, let’s run the same test from the Client machine to observer the authentication messages.
$ kdestroy -A $ klist klist: No credentials cache found (filename: /tmp/krb5cc_1000) $ klist Ticket cache: FILE:/tmp/krb5cc_1000 Default principal: postgres@HIGHGO.CA Valid starting Expires Service principal 2020-03-15 14:57:53 2020-03-16 00:57:53 krbtgt/HIGHGO.CA@HIGHGO.CA renew until 2020-03-16 14:57:50 $ psql -d postgres -U postgres -h pg.highgo.ca psql (12.2) GSSAPI-encrypted connection Type "help" for help. postgres=# \q $ klist Ticket cache: FILE:/tmp/krb5cc_1000 Default principal: postgres@HIGHGO.CA Valid starting Expires Service principal 2020-03-15 14:57:53 2020-03-16 00:57:53 krbtgt/HIGHGO.CA@HIGHGO.CA renew until 2020-03-16 14:57:50 2020-03-15 14:58:00 2020-03-16 00:57:53 postgres/pg.highgo.ca@ renew until 2020-03-16 14:57:50 2020-03-15 14:58:00 2020-03-16 00:57:53 postgres/pg.highgo.ca@HIGHGO.CA renew until 2020-03-16 14:57:50
The result is almost the same as authenticate user
postgres on PostgreSQL server, but if you look at the wireshark, you will find the PostgreSQL Server, i.e.
192.168.0.102 didn’t involve in the entire user authentication process. In other words, PostgreSQL Server only rely on the keytab file extracted from KDC server.
If you want to use a different user to log into database, then you need create the user on PostgreSQL server as database user, and create the principal for this user on KDC server. Create corresponding user authentication in
pg_hba.conf file and restart PostgreSQL server. Then you should be able to use the new user to log in database from either Client machine or PostgreSQL server machine.
8. Common errors
Some common errors may happen during the PostgreSQL user authentication with Kerberos. Here is a short list.
- Try to connect before the user credentials has been cached
$ psql -d postgres -h pg.highgo.ca -U postgres psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host "192.168.0.103", user "postgres", database "postgres"
- User doesn’t exist in KDC
$ psql -d postgres -h pg.highgo.ca -U jack psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host "192.168.0.103", user "jack", database "postgres"
- Service principal is expired
$ psql -d postgres -h pg.highgo.ca -U postgres psql: error: could not connect to server: could not initiate GSSAPI security context: Unspecified GSS failure. Minor code may provide more information could not initiate GSSAPI security context: Ticket expired FATAL: no pg_hba.conf entry for host "192.168.0.103", user "postgres", database "postgres"
In this blog, we explained how to enable GSSAPI from source code and the keytab file, discussed those three key configuration files, i.e. “PostgreSQL Configuration”, “PostgreSQL Client Authentication” and “PostgreSQL Users Mapping”, and we also demonstrated user authentication from different hosts with Kerberos. In next blog, we will discuss how to check authentication and encryption status for different connection requests.