Setting Up Integrated SSPI Authentication for PostgreSQL
To use the Tech Suite Installer, PostgreSQL should be configured to use SSPI authentication as with the SQL Server version of Admin App. SSPI is a Windows technology for secure authentication with single sign-on.
The setup steps are as follows:
Step 1. Identify Administrative Account
We will need to identify the administrative account in use on the Windows Server so we can create that same account in Postgres for installation. In steps ahead, we will configure Postgres to use SSPI mode, also known as Integrated Security, in which connections to the database use this administrative user's identity (in this case exampleadmin). In this example, we will use exampleadmin as the administrative and Postgres account; please record the actual administrative account in your environment.
To determine your administrative account name we suggest running the following commands in a PowerShell prompt. In the next steps, be sure to use the output from these commands exactly as it appears in the prompt; all configuration past this point is case-sensitive.
Step 2. Log in to pgAdmin 4
Launch pgAdmin, which was installed with Postgres. You will be prompted for a master password, and can use the same password used during Postgres installation for the default user 'postgres'.
Step 3. Create Administrative Account in Postgres
Expand "Servers" on the left hand side. Under the expanded "Servers" node, drill down into "PostgreSQL 11" and then right-click on "Login / Group Roles" and select "Create". Under "General", key in the Windows username you are logged in as (in this case 'exampleadmin'), and under "Privileges" checked all to Yes.
Step 4. Open File Explorer to C:\Program Files\PostgreSQL\11\data
At this point, 'exampleadmin' exists, but we need to configure postgres to make use of it in SSPI mode. We'll be modifying two files in C:\Program Files\PostgreSQL\11\data.
Step 5. Edit pg_ident.conf
Edit "pg_ident.conf" and establish 5 records at the bottom of the file (see below). The first record describes our example Windows admin, exampleadmin, and makes mention of this machine's own name, example-vm, so naturally this first line will vary to match your environment's Windows user and machine name. The second record establishes a mapping between the example Windows admin and the default postgres user to ensure commands using the default user are authenticated. The last 3 lines establish similar recognition which will be used by Ed-Fi software after installation, so it's useful to get them keyed in here now while we're establishing exampleadmin.
SYSTEM-USERNAME: This value can be user@domain or user@machinename. If the value has space (e.g.: apppoolname@IIS APPPOOL), then the value should be enclosed with double quotes (e.g.: “apppoolname@IIS APPPOOL”).
PG-USERNAME: Postgresql user (user) should be created on postgresql server if it is not available already (Step 3). Postgresql username is case sensitive, so username on the server and PG-USERNAME specified in this file must be identical.
Step 6. Modify pg_hba.conf
Edit "pg_hba.conf" modifying the appropriate existing line from md5 to sspi, mentioning the user map that we established in pg_ident.conf (see below). Below, the relevant line is the "IPv6" definition where we mention both sspi and mymap. Please adjust to the active connections in use on this server.
Step 7. Reload Postgres Configuration
Postgres may not reload configuration from the pg_hba.conf or pg_ident.config files after changes above are made. To ensure the Ed-Fi installation doesn't encounter any errors due to stale configuration, we will manually reload the configuration using the Reload Configuration application added to the start menu during Postgres installation. Search for "Reload Configuration" in the start menu and launch as an administrator.
If successful, a cmd prompt will appear with the text "server signaled". You can now close this cmd prompt.
If the Reload Configuration application was not installed during Postgres installation, or if you prefer to use the command line, pg_ctl.exe provided in the bin folder where Postgres is installed will preform the same operation. In a default installation this bin folder is located at C:\Program Files\PostgreSQL\11\bin. Run the following command from the bin directory to reload configuration. This command will not restart the PostgreSQL server.
Alternatively, pgAdmin may be used to reload configuration by exploring the Tools menu option and choosing "Reload Configuration" from it.
Step 8. Confirm SSPI
Finally, we'll confirm this setup by going back into pgAdmin and connecting as our Windows user, exampleadmin.
- Shut down and restart pgAdmin (available in the system tray). You'll be prompted for the master password again.
- On the left hand side, expand "Servers". pgAdmin will mistakenly prompt for the password for the 'postgres' account, along with the error "SSPI authentication failed for user 'postgres'". This is expected, and we can Cancel out of that login prompt.
- Then, back under "Servers", right-click on "PostgreSQL 11", select "Properties", then the "Connection" tab. In the Username text box, change postgres to your Windows user, in this case exampleadmin.
- Save, and then expand the "PostgreSQL 11" item on the left hand side.
You will be correctly prompted for the password for this Windows user, in this case exampleadmin. After entering the password for that Windows account, the "PostgreSQL 11" item will expand, demonstrating that connectivity for SSPI mode has been established successfully.
When we uninstall Ed-Fi application(s), the special logins created for the Ed-Fi application(s) under "Login / Group Roles" in pgAdmin, stay on the system unless explicitly
deleted or removed. In order to remove a login, right-click the login/role ("adminapp" login is selected in the screenshot below) under "Login / Group Roles" in pgAdmin and select
"Delete/Drop" from the options. You will be prompted to confirm the drop/removal of the login/role.
- No labels