Allow LocalSystem to access PostgreSQL database

Question

I'm trying to configure my PostgreSql database ("mydb") in order to give login rights to the windows "LocalSystem" account.

I've created a user in my database named LocalSystem

SQL> CREATE USER LocalSystem;

Then, I've tried using SSPI config in pg_hba.conf, but maybe I've misconfigured the configuration line:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    mydb            LocalSystem     127.0.0.1/32            sspi

When I run a command using psql, I'm asked to provide a password...which i didn't expect..

EDIT: I've also tried to map my domain account to a postgres user, using

pg_ident.conf
#MAPNAME       SYSTEM-USERNAME         PG-USERNAME
 postgresname  domain\username         postgresname

pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host  mydb            postgresname    127.0.0.1/32            sspi

Does someone know what should I do?

Thanks

What exactly is your problem? What is the error message you get? Note that `LocalSystem` is the same as `localsystem` in SQL. If you want a case-sensitive name, you need to quote it: `"LocalSystem"` when creating the user. — , Oct 19 '17 at 17:02
When i run a command using psql, I'm asked to provide a password. But LocalSystem doesn't have a password, right? This is a Windows account. — MaxD, Oct 19 '17 at 19:18
I've also tried to map my domain account to a postgres user and use sspi authentication.. — MaxD, Oct 19 '17 at 19:30
It asks for the password of the **Postgres** account, **not** the Windows account. you can set the Postgres password through `ALTER ROLE` or in psql using the `\password` command — , Oct 19 '17 at 19:55
Oh, i didn't understood the SSPI authentication that way. I thought the goal of SSPI was to provide a way to "sync" authentication processes between Windows and Postgres so that Postgres password wouldn't be necessary if the user is actually logged-in in windows. Thanks for your help! :D — MaxD, Oct 20 '17 at 19:42

score 0 · Answer 1 · answered Oct 20 '17 at 19:43

0

It asks for the password of the Postgres account, not the Windows account. you can set the Postgres password through ALTER ROLE or in psql using the \password command

answered Oct 20 '17 at 19:43

MaxD

51
6

score 0 · Answer 2 · answered Nov 18 '20 at 22:28

You need to add new mapping line to pg_ident.conf:

anyMappingName "SYSTEM@NT AUTHORITY" postgresUserName
Add this mapping to all active lines in pg_hba.conf:

host mydb all 127.0.0.1/32 sspi map=anyMappingName
Then you should connect to postgres db as postgresUserName user. I mean you should define Username in your connection string, and also define IntegratedSecurity as TRUE.

For example, postgresUserName can be "LocalSystem" user, that you created in postgres db earlier, or superuser "postgres".

Allow LocalSystem to access PostgreSQL database

2 Answers2