PostgreSQL GSSAPI Authentication with Kerberos part-3: the status of authentication, encryption and user principal

Enterprise PostgreSQL Solutions

1 comment

PostgreSQL GSSAPI Authentication with Kerberos part-3: the status of authentication, encryption and user principal

1. Overview

In previous two blogs, we explained how to setup Kerberos, and how to configure PostgreSQL to support GSSAPI user authentication. This blog will be focusing on how to check GSSAPI authentication, encryption and user principal information when given different connection options.

2. pg_stat_gssapi view

According to the official PostgreSQL document, “PostgreSQL supports GSSAPI for use as either an encrypted, authenticated layer, or for authentication only.“ To check the authentication, encryption and user principal, we need to use pg_stat_gssapi view, which is a dynamic statistics views containing one row per backend and showing the information about GSSAPI authentication and encryption used on this connection.

Before start the test below, make sure the PostgreSQL server and the psql client has the option --with-gssap enabled during build time.

3. Authentication and Encryption status

  • Scenario 1:

Both authentication and encryption are enabled when the host-based authentication is configured with hostgssenc and gss in pg_hba.conf
Set below user authentication rule to pg_hba.conf and disable all other rules.

hostgssenc  postgres  postgres  192.168.0.102/32  gss include_realm=0 krb_realm=HIGHGO.CA

Initiate the user postgres credential cache using kinit, and then connect to PostgreSQL server with user postgres

postgres@pg:~$ psql -d postgres -h pg.highgo.ca -U postgres
psql (12.2)
GSSAPI-encrypted connection
Type "help" for help.

postgres=# SELECT pid, gss_authenticated, encrypted, principal from pg_stat_gssapi where pid = pg_backend_pid();
 pid  | gss_authenticated | encrypted |     principal      
------+-------------------+-----------+--------------------
 2274 | t                 | t         | postgres@HIGHGO.CA
(1 row)

postgres=#

From the result, we can see this connection is encrypted and the user is authenticated with principal postgres@HIGHGO.CA.

  • Scenario 2:

The encryption will be disabled, but user authentication is still enabled when the host-based authentication is configured with hostnogssenc and gss in pg_hba.conf
Set below user authentication rule to pg_hba.conf and disable all other rules.

hostnogssenc  postgres  postgres  192.168.0.102/32  gss include_realm=0 krb_realm=HIGHGO.CA
postgres@pg:~$ psql -d postgres -h pg.highgo.ca -U postgres
psql (12.2)
Type "help" for help.

postgres=# SELECT pid, gss_authenticated, encrypted, principal from pg_stat_gssapi where pid = pg_backend_pid();
 pid  | gss_authenticated | encrypted |     principal      
------+-------------------+-----------+--------------------
 2291 | t                 | f         | postgres@HIGHGO.CA
(1 row)

postgres=#

The result tells no encryption, but user has been authenticated using principal postgres@HIGHGO.CA

  • Scenario 3:

Both encryption and authentication are all enabled when the host-based authentication is configured with host and gss in pg_hba.conf.
Set below user authentication rule to pg_hba.conf and disable all other rules.

host  postgres  postgres  192.168.0.102/32  gss include_realm=0 krb_realm=HIGHGO.CA
postgres@pg:~$ psql -d postgres -h pg.highgo.ca -U postgres
psql (12.2)
GSSAPI-encrypted connection
Type "help" for help.

postgres=# SELECT pid, gss_authenticated, encrypted, principal from pg_stat_gssapi where pid = pg_backend_pid();
 pid  | gss_authenticated | encrypted |     principal      
------+-------------------+-----------+--------------------
 2309 | t                 | t         | postgres@HIGHGO.CA
(1 row)

postgres=#

This result is the same as the first one, meaning, host is equivalent to hostgssenc when gss is specified.

  • Scenario 4:

The authentication will be disabled, but encryption is still on when the host-based authentication is configured with host and trust in pg_hba.conf.
Set below user authentication rule to pg_hba.conf and disable all other rules.

postgres@pg:~$ psql -d postgres -h pg.highgo.ca -U postgres
psql (12.2)
GSSAPI-encrypted connection
Type "help" for help.

postgres=# SELECT pid, gss_authenticated, encrypted, principal from pg_stat_gssapi where pid = pg_backend_pid();
 pid  | gss_authenticated | encrypted | principal 
------+-------------------+-----------+-----------
 2322 | f                 | t         | 
(1 row)

postgres=#
host  postgres  postgres  192.168.0.102/32  trust

This result tells that the encryption will be always on when --with-gssapi is enabled during build time, unless hostnogssenc is specified in the host-based authentication file.

  • Scenario 5:

Both authentication and encryption will be disabled when the host-based authentication is configured with host and trust in pg_hba.conf, and the client psql requests a non-gssenc mode connection, i.e. gssencmode=disable.
Set below user authentication rule to pg_hba.conf and disable all other rules.

host  postgres  postgres  192.168.0.102/32  trust
postgres@pg:~$ psql -h pg.highgo.ca -U postgres -d "dbname=postgres gssencmode=disable"
psql (12.2)
Type "help" for help.

postgres=# SELECT pid, gss_authenticated, encrypted, principal from pg_stat_gssapi where pid = pg_backend_pid();
 pid  | gss_authenticated | encrypted | principal 
------+-------------------+-----------+-----------
 2328 | f                 | f         | 
(1 row)

postgres=#

You can also achieve the same result by setting the environment PGGSSENCMODE=disable from the client side. For example,

PGGSSENCMODE=disable psql -h pg.highgo.ca -U postgres -d postgres

4. Summary

In this blog, we discussed how to check authentication, encryption and user principal in 5 different scenarios. As you can see once --with-gssapi is enabled in PostgreSQL, the encryption will always be turned on unless you specify hostnogssenc in the host-based authentication file, or manually disable gssenc mode from a client side. Knowing the difference might help you when working the security related environment setup using GSSAPI.

One Response

  1. David Zhang brian ryberg says:

    Thanks. This is cool information!

    I wish you’d be able to explain the same in part2 & 3 as instructive for MariaDB, because Kerberos/gssapi for those users is a pain in (my) butt 😀

Leave a Reply

Your email address will not be published. Required fields are marked *