How to securely authenticate with SCRAM in Postgres 13

by Scott Muniz | Jul 28, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Making security easy to use is crucial because hard-to-use security is likely to be neglected entirely. SCRAM with channel binding is a variation of password authentication that is almost as easy to use, but much more secure.

In basic password authentication, the connecting client simply sends the server the password. Then the server checks that it’s the right one, and allows the client to connect. Basic password authentication has several weaknesses which are addressed with SCRAM and channel binding.

In this article, I’ll show you how to set up authentication using SCRAM with channel binding. I implemented the client connection parameter  channel_binding in PostgreSQL 13, due to be released in late 2020 (PostgreSQL 13 is in beta now). SCRAM and Channel Binding have already been supported in several releases, but this new connection parameter is necessary to realize the security benefits of SCRAM and Channel Binding.

First, before diving in to the tutorial, some background on SCRAM and Channel Binding.

Disclaimer: This article is just a how-to. As with any security decision, you should perform your own analysis to determine if it’s right for your environment. No security feature is right in all cases.

The SCRAM authentication method in Postgres

SCRAM  is a secure password authentication protocol that can authenticate the client. It has several advantages over basic password authentication:

• does not reveal the user’s cleartext password to the server
• is designed to prevent replay attacks 
• enables the use of Channel Binding (see next section)
• can support multiple cryptographic hash functions
  • currently, PostgreSQL only supports SCRAM using SHA-256

For these reasons, in PostgreSQL, the scram-sha-256 password auth method is strongly recommended over md5 or password.

The first part of this tutorial can be used to set up SCRAM even if you don’t use channel binding.

Channel Binding with SCRAM

In many cases, it’s just as important for the client to authenticate the server, as it is for the server to authenticate the client; this is called mutual authentication. Without mutual authentication, the server could be a malicious imposter or you could be exposed to a MITM attack.

Channel Binding is a feature of the SCRAM protocol that allows mutual authentication over an SSL connection, even without a Certificate Authority (which is useful, since it can be difficult to configure a Certificate Authority in some environments).

It is not recommended to rely on Channel Binding if using clients earlier than version 13.

When using channel binding, you should specify channel_binding=require in the connection string (see connection parameters), which tells the client to demand channel binding before the connection succeeds. Alternatively (or additionally), you can set the PGCHANNELBINDING environment variable to require. Without one of these options set, the client may not adequately authenticate the server, undermining the purpose of channel binding.

How to know if your client supports the channel_binding parameter

First, determine the client driver that your application is using. Typically, the client driver will depend on the language you are using, and you can find it on this list of client drivers . If your client driver is listed as using libpq  (the official PostgreSQL client library), that means that it will support the channel_binding connection parameter as long as you are using at least version 13 of libpq. libpq ordinarily comes from an operating system package; for instance, on Ubuntu, look at the version of the package postgresql-client.

If your driver does not use libpq, it may still support the new connection parameter; consult your driver’s documentation for details. For instance, rust-postgres  supports the channel_binding connection parameter even though it doesn’t use libpq.

Tutorial on setting up SCRAM with channel binding in PostgreSQL 13

1. Initial Postgres Setup

• Install the Postgres 13 beta and make sure the newer versions of the postgres and psql binaries are in your PATH. If compiling from source, be sure to use the --with-openssl option.
• Run initdb -D data -k to initialize a new data directory
• pg_ctl -D data -l logfile start to start the server
• Make sure you can connect: psql "host=localhost dbname=postgres user=myuser"
• Type q at the prompt to quit
• pg_ctl -D data stop

2. Configure for SCRAM authentication and safely add a user

• Edit data/postgresql.conf and add the line password_encryption = scram-sha-256 at the bottom.
• Edit data/pg_hba.conf to set at least one authentication method to safely use for an initial superuser connection. This is needed to set up at least one user with a SCRAM password, see pg_hba.conf documentation to see the options. In my environment, allowing local connections with trust is secure enough for the initial setup:
  • local all all trust
• Start Postgres: pg_ctl -D data -l logfile start
• createuser -P myuser
  • May need to supply additional options if not using a local connection.
  • createuser has important advantages over using manual SQL commands from psql to create the user:
    • It prompts for the password, so the plain text password never ends up in the process title, the shell history, or the psql history.
    • It cryptographically hashes the password before sending it to the server, so that the server never sees the plaintext password.
• Stop Postgres: pg_ctl -D data stop

3. Configure pg_hba.conf

Edit pg_hba.conf, and make sure to create an entry that allows TCP/IP connections (one of the records that begins with host...), otherwise SSL won’t be used and Channel Binding won’t work.

Here’s the pg_hba.conf that I’m using for this demo:

host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

You may consider using hostssl instead to reject non-SSL connections. I am allowing non-SSL connections to demonstrate connection failures that can happen when channel binding is required but SSL is off.

If you only allow connections using scram-sha-256, then all users must have a SCRAM password set, or they won’t be able to connect.

4. Start the server and verify that you can connect using SCRAM

• pg_ctl -D data -l logfile start
• psql "host=localhost dbname=postgres user=myuser"
  • Will prompt for password
• pg_ctl -D data stop

5. Configure SSL and require channel binding

NOTE: generate the SSL key and certificate according to the best practices in your organization; the instructions below are just for demonstration purposes.

• Generate key and cert: openssl req -newkey rsa:2048 -nodes -keyout server.key -x509 -days 365 -out server.crt
• chmod 400 server.key
• mv server.key data/
• mv server.crt data/
• Edit data/postgresql.conf and add the line ssl = on at the bottom.

6. Connect to the server

Make sure you follow the directions to configure pg_hba.conf above.

• pg_ctl -D data -l logfile start
• psql "host=localhost dbname=postgres user=myuser sslmode=disable"
  • should prompt for password and succeed
• psql "host=localhost dbname=postgres user=myuser sslmode=require"
  • should prompt for password and succeed
• psql "host=localhost dbname=postgres user=myuser sslmode=disable channel_binding=require"
  • should prompt for password and then fail because channel binding requires SSL; but we have disabled SSL and required channel binding
• psql "host=localhost dbname=postgres user=myuser sslmode=require channel_binding=require"
  • should prompt for password and succeed
• pg_ctl -D data stop

Note that SCRAM in general does not require SSL to be used, but Channel Binding does require SSL.

7. Always Require Channel Binding

Edit your environment to set PGCHANNELBINDING=require so that all clients connecting will require channel binding.

How Channel Binding relates to a Certificate Authority

Note that we didn’t have to configure a Certificate Authority (CA) . A CA is another way for the client to authenticate the server, but it can be difficult to set up and maintain in some environments.

But if you do have a CA in your environment, you can combine it with SCRAM and Channel Binding to authenticate the server based on two separate mechanisms (which can enhance security in case one is compromised). To do so, set sslmode=verify-full or sslmode=verify-ca along with channel_binding=require in your connection parameters.

You can also use the clientcert auth option  in pg_hba.conf, to tell the server to verify the client’s certificate against the CA. But note that this auth option doesn’t verify that the CN of the client certificate matches the connecting username (to do that, you need to use the cert auth method, which means you aren’t using SCRAM or any other password authentication).

Importance of the client connection parameter

It’s important to set channel_binding=require in your connection string, otherwise the client may be fooled into not performing Channel Binding at all, and thus not authenticating the server.

The PostgreSQL authentication protocol and libpq were primarily designed around the server authenticating the client (i.e. the server doesn’t trust the client). But the client implicitly trusts the server, attempting to authenticate itself using any mechanism the server requests, and establishing the connection as soon as the server is satisfied. Methods to authenticate the server require the client to explicitly demand them—for instance, by setting sslmode=verify-full in the connection string. Channel Binding is no different: you need to set channel_binding=require.

If this step is neglected, then all a malicious server needs to do to draw in an unsuspecting client is to immediately send an AuthenticationOk message, indicating that the server is satisfied with the connecting client. The client will skip the SCRAM protocol entirely (including Channel Binding), connect to the server, and could begin issuing queries that contain sensitive data to this malicious server.

Or worse, without channel_binding=require, the server could instead send an AuthenticationCleartextPassword message. The client will respond by (you guessed it!) sending the server the cleartext password, even though the user had otherwise configured SCRAM, and thought that at least their cleartext password was safe. This not only undermines Channel Binding, it undermines the entire purpose of SCRAM.

Use SCRAM and remember to set channel_binding=require

SCRAM is a huge improvement over traditional password authentication, and SCRAM with channel binding is even better. But as we can see, PostgreSQL users need to be careful to use it correctly to get the full benefit.

Setting channel_binding=require (or at least setting the environment variable PGCHANNELBINDING to require) is a critical step to protect yourself against certain kind of attacks, and it is available with PostgreSQL client version 13.

About the Author

I am on the Postgres team at Microsoft, and I’m also a PostgreSQL committer. See Microsoft Azure Welcomes PostgreSQL Committers, which introduces me along with fellow committers Andres Freund, Thomas Munro, and David Rowley.

Known Issue – Windows Updates occasionally incorrectly show as not succeeded in Intune

by Scott Muniz | Jul 27, 2020 | Uncategorized

This article is contributed. See the original author and article here.

We were recently alerted to an issue whereby a device in the Microsoft Endpoint Manager admin console was showing “failed” for their Windows Update status.

Upon investigation, we discovered that the device was not missing any updates – everything had successfully applied. However, we did find two error codes on the device, which then were calculated by Intune as “failed”.  After the policy was re-evaluated, then the device returned to healthy.

Here’s the steps we took to replicate this experience: 

1. Create a basic Windows Update ring policy and apply to Windows 10 devices. 
2. Trigger a check for updates and refresh the device. 
3. Download Psexec.exe
4. Run psexec /sid PowerShell.exe from an elevated command prompt or elevated PowerShell window.  A new PowerShell instance will open, running in system context.
5. Run the following command in the system-context PowerShell window you created in step 4:

gwmi MDM_Update_FailedUpdates01_01 -Namespace ROOTCIMV2mdmdmmap

If nothing is returned, the device does not currently have a failed update.  

If Windows Update returns one of the two error codes as you can see on the device, then you’ll see “failed” in the console: 

HResult    : -2145082874   

InstanceID : ec67ed82-8cf6-4fa9-86bf-efdb4e7b5d00

ParentID   : ./Vendor/MSFT/Update/FailedUpdates

State      : 

HResult    : -2145082858   

InstanceID : 33e3f18f-c868-4d00-8266-01c100acf444

ParentID   : ./Vendor/MSFT/Update/FailedUpdates

State      : 

These two error codes are specific to Windows Update being too busy. It’s rare, but there are times that the service will return these codes. If you run into this, both the Intune policy refresh and then likely the Windows Update policy refresh will need to trigger to ensure policy is evaluated and updated. For more info on Intune policy refresh timelines, see:
https://docs.microsoft.com/mem/intune/configuration/device-profile-troubleshoot#how-long-does-it-take-for-devices-to-get-a-policy-profile-or-app-after-they-are-assigned

Follow Intune Support as a Feature on Twitter as @intunesuppteam for helpful articles, release info, and more!