Windows Unprivileged - PostgreSQL
Case study: Installing and configuring PostgreSQL for least privilege
Note that absolutely none of this is authoritative or directly based on relevant documentation. It’s mostly what I found and figured out and guessed and (in some cases) made up. Some of it may be wrong or dangerous or lead to disaster or confusion. I am not taking responsibility here for anything, not even spelling. Read and digest at your own peril!
First, download PostgreSQL from PostgreSQL. I am no PostgreSQL user but I think that is the most official source for the Windows version.
Log on as an administrator and install PostgreSQL. It proposes a default installation directory
C:\Program Files\PostgreSQL\17
which you should probably change to something more D:-ish, like
D:\Program Files\PostgreSQL\17
to separate applications from the operating system.
(This post will use the default path because the test machine only has one drive.)
The installer wants to install four items and I think you can install all of them.
Then it asks for the data directory and proposes
C:\Program Files\PostgreSQL\17\data
which again you probably should replace with something on the D drive and outside the program directory like
D:\PostgreSQL\17\data
to avoid a mix of program and data files in the same directory, which is always bad.
(I will leave the default setting here too.)
It will then ask for the password of the postgres user, the first user of the database server. Pick something really difficult to crack like secretpassword.
After everything is installed, two settings should be adjusted: the configuration of the service and the permissions of the directories.
First, let’s see how bad it is:
PS C:\Program Files\PostgreSQL\17> dir
Directory: C:\Program Files\PostgreSQL\17
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 4/20/2025 8:58 PM bin
d----- 4/20/2025 9:02 PM data
d----- 4/20/2025 8:57 PM doc
d----- 4/20/2025 8:57 PM include
d----- 4/20/2025 8:57 PM installer
d----- 4/20/2025 9:02 PM lib
d----- 4/20/2025 8:58 PM pgAdmin 4
d----- 4/20/2025 8:58 PM scripts
d----- 4/20/2025 8:58 PM share
-a---- 2/20/2025 11:00 AM 64981 commandlinetools_3rd_party_licenses.txt
-a---- 4/20/2025 9:02 PM 746 installation_summary.log
-a---- 2/20/2025 11:00 AM 75769 pgAdmin_3rd_party_licenses.txt
-a---- 2/20/2025 11:00 AM 1173 pgAdmin_license.txt
-a---- 4/20/2025 9:02 PM 339 pg_env.bat
-a---- 2/20/2025 11:00 AM 1388 server_license.txt
-a---- 2/20/2025 11:00 AM 1121 StackBuilder_3rd_party_licenses.txt
-a---- 4/20/2025 9:03 PM 258459 uninstall-postgresql.dat
-a---- 4/20/2025 9:03 PM 12268305 uninstall-postgresql.exe
PS C:\Program Files\PostgreSQL\17> Get-Service postgres*
Status Name DisplayName
------ ---- -----------
Running postgresql-x64-17 postgresql-x64-17
PS C:\Program Files\PostgreSQL\17> sc.exe qc postgresql-x64-17
[SC] QueryServiceConfig SUCCESS
SERVICE_NAME: postgresql-x64-17
TYPE : 10 WIN32_OWN_PROCESS
START_TYPE : 2 AUTO_START
ERROR_CONTROL : 1 NORMAL
BINARY_PATH_NAME : "C:\Program Files\PostgreSQL\17\bin\pg_ctl.exe" runservice -N "postgresql-x64-17" -D "C:\Program Files\PostgreSQL\17\data" -w
LOAD_ORDER_GROUP :
TAG : 0
DISPLAY_NAME : postgresql-x64-17
DEPENDENCIES : RPCSS
SERVICE_START_NAME : NT AUTHORITY\NetworkService
PS C:\Program Files\PostgreSQL\17>Well, the service is running under the NetworkService account, which isn’t too bad.
But the permissions on the directories are default permissions (which works because the service runs under the NetworkService account):
PS C:\Program Files\PostgreSQL\17> icacls .
. NT SERVICE\TrustedInstaller:(I)(F)
NT SERVICE\TrustedInstaller:(I)(CI)(IO)(F)
NT AUTHORITY\SYSTEM:(I)(F)
NT AUTHORITY\SYSTEM:(I)(OI)(CI)(IO)(F)
BUILTIN\Administrators:(I)(F)
BUILTIN\Administrators:(I)(OI)(CI)(IO)(F)
BUILTIN\Users:(I)(RX)
BUILTIN\Users:(I)(OI)(CI)(IO)(GR,GE)
CREATOR OWNER:(I)(OI)(CI)(IO)(F)
APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(RX)
APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(OI)(CI)(IO)(GR,GE)
APPLICATION PACKAGE AUTHORITY\ALL RESTRICTED APPLICATION PACKAGES:(I)(RX)
APPLICATION PACKAGE AUTHORITY\ALL RESTRICTED APPLICATION PACKAGES:(I)(OI)(CI)(IO)(GR,GE)
Successfully processed 1 files; Failed processing 0 files
PS C:\Program Files\PostgreSQL\17>Since we want to avoid using user and system accounts for services (see services), we can reconfigure the PostgreSQL service to use a service SID.
PS C:\Program Files\PostgreSQL\17> sc.exe config postgresql-x64-17 obj="NT Service\postgresql-x64-17"
[SC] ChangeServiceConfig SUCCESS
PS C:\Program Files\PostgreSQL\17>But then we have to modify the directory permissions. The service needs read (but certainly not write) access to all PostgreSQL program files and full control of all PostgreSQL data directories.
Since the service name might change in the future with new versions, we better create a group to represent it and its future versions.
PS C:\Program Files\PostgreSQL\17> New-LocalGroup PostgreSQL
Name Description
---- -----------
PostgreSQL
PS C:\Program Files\PostgreSQL\17> Add-LocalGroupMember PostgreSQL "NT Service\postgresql-x64-17"
PS C:\Program Files\PostgreSQL\17> Get-LocalGroupMember PostgreSQL
ObjectClass Name PrincipalSource
----------- ---- ---------------
Group NT SERVICE\postgresql-x64-17 Unknown
PS C:\Program Files\PostgreSQL\17> icacls . /grant "PostgreSQL:(OI)(CI)RX"
processed file: .
Successfully processed 1 files; Failed processing 0 files
PS C:\Program Files\PostgreSQL\17> icacls .
. CHAMPIGNAC\PostgreSQL:(OI)(CI)(RX)
NT SERVICE\TrustedInstaller:(I)(F)
NT SERVICE\TrustedInstaller:(I)(CI)(IO)(F)
NT AUTHORITY\SYSTEM:(I)(F)
NT AUTHORITY\SYSTEM:(I)(OI)(CI)(IO)(F)
BUILTIN\Administrators:(I)(F)
BUILTIN\Administrators:(I)(OI)(CI)(IO)(F)
BUILTIN\Users:(I)(RX)
BUILTIN\Users:(I)(OI)(CI)(IO)(GR,GE)
CREATOR OWNER:(I)(OI)(CI)(IO)(F)
APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(RX)
APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(OI)(CI)(IO)(GR,GE)
APPLICATION PACKAGE AUTHORITY\ALL RESTRICTED APPLICATION PACKAGES:(I)(RX)
APPLICATION PACKAGE AUTHORITY\ALL RESTRICTED APPLICATION PACKAGES:(I)(OI)(CI)(IO)(GR,GE)
Successfully processed 1 files; Failed processing 0 files
PS C:\Program Files\PostgreSQL\17> icacls data /grant "PostgreSQL:(OI)(CI)F"
processed file: data
Successfully processed 1 files; Failed processing 0 files
PS C:\Program Files\PostgreSQL\17> icacls data
data NT AUTHORITY\NETWORK SERVICE:(RX)
CHAMPIGNAC\Administrator:(RX)
BUILTIN\Administrators:(F)
CHAMPIGNAC\PostgreSQL:(OI)(CI)(F)
BUILTIN\Administrators:(OI)(CI)(F)
NT AUTHORITY\SYSTEM:(OI)(CI)(F)
CREATOR OWNER:(OI)(CI)(IO)(F)
NT AUTHORITY\NETWORK SERVICE:(OI)(CI)(F)
CHAMPIGNAC\Administrator:(OI)(CI)(F)
Successfully processed 1 files; Failed processing 0 files
PS C:\Program Files\PostgreSQL\17>(OI)(CI)RX means read and execute permissions which are to be inherited. (OI)(CI)F means full control which is to be inherited.
Now we should add the bin directory to the path.
PS C:\Program Files\PostgreSQL\17\bin> $psql=(Get-Item .).FullName
PS C:\Program Files\PostgreSQL\17\bin> $p=[System.Environment]::GetEnvironmentVariable("PATH","MACHINE")
PS C:\Program Files\PostgreSQL\17\bin> $p
C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files\Microsoft VS Code\bin
PS C:\Program Files\PostgreSQL\17\bin> $p+=";$psql"
PS C:\Program Files\PostgreSQL\17\bin> $p
C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files\Microsoft VS Code\bin;C:\Program Files\PostgreSQL\17\bin
PS C:\Program Files\PostgreSQL\17\bin> [System.Environment]::SetEnvironmentVariable("PATH",$p,"MACHINE")
PS C:\Program Files\PostgreSQL\17\bin> [System.Environment]::GetEnvironmentVariable("PATH","MACHINE")
C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files\Microsoft VS Code\bin;C:\Program Files\PostgreSQL\17\bin
PS C:\Program Files\PostgreSQL\17\bin>Finally we want a resource group for users that should be allowed to start and stop the PostgreSQL service.
PS C:\> New-LocalGroup RG_PostgreSQL-0x34
Name Description
---- -----------
RG_PostgreSQL-0x34
PS C:\> (Get-LocalGroup RG_PostgreSQL-0x34).SID
BinaryLength AccountDomainSid Value
------------ ---------------- -----
28 S-1-5-21-344341352-2539047333-2300305637 S-1-5-21-344341352-2539047333-2300305637-1036
PS C:\> $sddl=(sc.exe sdshow postgresql-x64-17)[1]
PS C:\> $sddl
D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
PS C:\> $sddl="D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)(A;;0x34;;;S-1-5-21-344341352-2539047333-2300305637-1036)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)"
PS C:\> sc.exe sdset postgresql-x64-17 $sddl
[SC] SetServiceObjectSecurity SUCCESS
PS C:\> sc.exe sdshow postgresql-x64-17
D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)(A;;LCRPWP;;;S-1-5-21-344341352-2539047333-2300305637-1036)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
PS C:\> Add-LocalGroupMember RG_PostgreSQL-0x34 benoit
PS C:\>User benoit can now start the PostgreSQL service and connect to it.
PS C:\> Restart-Service postgresql-x64-17
PS C:\> psql -Upostgres
Password for user postgres:
psql (17.4)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=#You can use chcp 1252 to change the console’s code page to match the Windows code page.
Next: MariaDB