Is it possible to use TPQConnection without username or password?
lazarus / 16-Databases

----------------------------------------------------------------------
rgh · 2024-01-23 11:05

I have postgresql running on a linux host, with a database called
a2log.

On that host is a system user called a2logger. Postgresql is
configured, via pg_hba.conf, such that the linux user called a2logger
can connect to the a2log database as database role a2logger without
requiring a password.

Code: [Select]$ cat pg_hba.conf | grep a2logger
local   a2log           a2logger                                peer

For example, from the command line, I can run psql as user a2logger
and connect to database a2log:
Code: [Select]$ sudo -u a2logger psql a2log
psql (12.17 (Ubuntu 12.17-0ubuntu0.20.04.1))
Type "help" for help.

a2log=>

I'm wanting to write a program that runs on that same host as user
a2logger and connects to the database. Since its to run as a2logger,
it should connect without needing username or password. Here is my
attempt:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {$MODE
OBJFPC}{$H+} //  use long strings as default Program
no_username_connect; uses  sysutils, pqconnection, sqldb;   function
CreateConnection: TSQLConnection;begin  result :=
TPQConnection.Create(nil);  result.Hostname := '127.0.0.1'; 
result.Params.Add('port=5432');              result.DatabaseName :=
'a2log';  //result.UserName := 'usermame';  //result.Password :=
'password';end; var     AConnection : TSQLConnection; begin  //verify
program is running as expected user  writeln('Running as ' +
GetEnvironmentVariable('USER'));  AConnection := CreateConnection; 
AConnection.Open;  if Aconnection.Connected then   
writeln('Successful connect!')  else    writeln('If the connection
failed,  exception should be raised');  AConnection.Close; 
AConnection.Free;end. 
However this doesn't work.

Code: [Select]$ sudo -u a2logger ./no_username_connect
Running as a2logger
An unhandled exception occurred at $000000000043F0D0:
EDatabaseError: Connection to database failed (PostgreSQL:
fe_sendauth: no password supplied
)
  $000000000043F0D0
  $000000000043F198
  $000000000041F748

$

Is it possible to use TPQConnection without providing a username or
password?

----------------------------------------------------------------------
MarkMLl · 2024-01-23 11:35

I suspect that psql is picking up $USER and using that, plus a blank
password, for authentication. Arguably, it's not the client-side
library's prerogative to make that assumption: even if it might appear
reasonable it could result in some very difficult bugs.

Try putting the name into your code explicitly, and if necessary try
putting a blank password into your code in case writing to the
control's password property is actually initialising something via a
writer procedure.

Other than that I can't really suggest anything other than making sure
that you can step into the RTL/FCL code and laying into it with a
debugger (which really isn't difficult, the Lazarus IDE helps a great
deal).

MarkMLl

----------------------------------------------------------------------
Zvoni · 2024-01-23 11:37

in your conf, is that "peer"-entry the auth-method?
acc. to documentation, there is no auth-method-named "peer"
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
Guess: Instead of "peer" you need "trust"
and config must be reloaded for the PSQL-Server (or restart it
completely)

Another Approach: Instead of using the Properties of the Connection-
Object, try to set the Connection-String directly.

At a guess: in the Background the Connection concats a Connection-
String from the Properties, and that would result in something like
"...
DRIVER=SomePSQLDriver;SERVER=SomeIP;Port=1234;USERNAME=;PASSWORD=;..."

----------------------------------------------------------------------
MarkMLl · 2024-01-23 12:01

Quote from: Zvoni on January 23, 2024, 12:37:06 pmacc. to
documentation, there is no auth-method-named "peer"

There certainly used to be:

Code: [Select]# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

Apart from that I agree with your point about if necessary using a
configuration string.

MarkMLl

----------------------------------------------------------------------
Zvoni · 2024-01-23 15:18

Hmmm.... OK
acc. to https://www.postgresql.org/docs/current/auth-methods.html
there still IS the "peer" auth-method

BUT
QuotePeer authentication, which relies on operating system facilities
to identify the process at the other end of a local connection. This
is not supported for remote connections.
Is it possible, that the Server-Value of "127.0.0.1" get's translated
as "remote" (as stupid as it may sound)?
Maybe if TS tried "localhost" instead of "127.0.0.1"

----------------------------------------------------------------------
MarkMLl · 2024-01-23 16:52

Quote from: Zvoni on January 23, 2024, 04:18:50 pmIs it possible, that
the Server-Value of "127.0.0.1" get's translated as "remote" (as
stupid as it may sound)?
Maybe if TS tried "localhost" instead of "127.0.0.1"

I've added a couple more lines to my example: the version of file I'm
looking at /implies/ that "remote" is anything other than unix-domain
sockets.

MarkMLl

----------------------------------------------------------------------
rgh · 2024-01-23 19:23

Before I start investigating the suggestions to manually construct a
ConnectionString, I thought I'd look at what it is that's currently
being created.

Could anyone give me the correct syntax for doing that? Or direct me
to an example? My current code has an error at line 26.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {$MODE
OBJFPC}{$H+} //  use long strings as default Program
no_username_connect; uses  sysutils, pqconnection, sqldb;   function
CreateConnection: TPQConnection;begin  result :=
TPQConnection.Create(nil);  result.Hostname := '127.0.0.1'; 
result.Params.Add('port=5432');              result.DatabaseName :=
'a2log';  //result.UserName := 'a2logger';  //result.Password :=
'password';end; var     AConnection : TPQConnection; begin  //verify
program is running as expected user  writeln('Running as ' +
GetEnvironmentVariable('USER'));  AConnection := CreateConnection; 
writeln(AConnection.GetConnectionString());  AConnection.Open;  if
Aconnection.Connected then    writeln('Successful connect!')  else   
writeln('If the connection failed,  exception should be raised'); 
AConnection.Close;  AConnection.Free;end. 
Code: [Select]$ fpc no_username_connect.pas
Free Pascal Compiler version 3.0.4+dfsg-23 [2019/11/25] for x86_64
Copyright (c) 1993-2017 by Florian Klaempfl and others
Target OS: Linux for x86-64
Compiling no_username_connect.pas
no_username_connect.pas(26,23) Error: identifier idents no member
"GetConnectionString"
no_username_connect.pas(35) Fatal: There were 1 errors compiling
module, stopping
Fatal: Compilation aborted
Error: /usr/bin/ppcx64 returned an error exitcode

----------------------------------------------------------------------
Zvoni · 2024-01-23 20:14

GetConnectionString is a function of TPQConnection, not TSQLConnection

And the DB-Name argument is missing