knowledge is power

0%

PostgreSQL GSSAPI Authentication with Kerberos part-2: PostgreSQL Configuration

Posted on Edited on In , , , ,

Featured image

1. Overview

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. postgresql.conf, pg_hba.conf and 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.

1
$ ./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.

1
2
3
4
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. 

1
2
3
4
5
6
7
8
9
10
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.

1
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 fall-through or 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. hostgssenc and hostnogssenc for the tests. For now, just put below two lines started with hostgssenc after IPv4 local connections.

1
2
3
4
# 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.

  • hostgssenc is used to match a TCP connection made with GSSAPI encryption.
  • postgres is the database name.
  • david and postgres are the users allowed to connect to the database.
  • 192.168.0.0/24 is the network for this particular setup.
  • gss include_realm=0 means the authentication method gss is used with the option include_realm=0 which 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_indent.conf is used to map the users. The mapping can be used when user want to use email like user name e.g. postgres@highgo.ca to log in the database. For example, postgres@highgo.ca could be mapped to just postgres. According to PostgreSQL document, if hostgssenc is used, then only three authentication options are allowed: gss, reject, and trust. When 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 1, if 0 is 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$ \1 is 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.
    For example,
    1
    hostgssenc  postgres  postgres  192.168.0.0/24  gss include_realm=1 map=mymap krb_realm=HIGHGO.CA
    Where, 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 HIGHGO.CA only.

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 gss with include_realm=0 only, then restart Postgres server. 

1
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. 

1
2
$ klist
klist: No credentials cache found (filename: /tmp/krb5cc_1001)

No credentials cached yet, then run kinit postgres to initial the user authentication.

1
2
$ kinit postgres
Password for postgres@HIGHGO.CA:

Now, check the cached credentials again,

1
2
3
4
5
6
7
$ 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 postgres

1
2
3
4
5
6
$ 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,

1
2
3
4
5
6
7
8
9
10
11
$ 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.
GSSAPI Postgres
From the above wireshark capture,

  • message 1: AS_REQ is the initial user authentication request triggered by kinit.
  • message 2: KRB Error is the reply from Authentication Server to ask password for user postgres
  • message 3: AS_REQ is the user authentication request encrypted using postgres‘s password
  • message 4: AS_REP is the encrypted reply from Authentication Server. Upon now, kini is done, and user postgres‘s credential has been cached.
  • message 5: TGS_REQ is the request from psql to 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 postgres.

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
$ 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

GSSAPI Client
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

    1
    2
    $ 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

    1
    2
    $ 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

    1
    2
    3
    4
    $ 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"

    9. Summary

    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.