Zeoslib Portal

Posted: **18.01.2024, 17:43**

(I use google translate for english, sorry

)

how can I connect to a local postgresql server via a unix socket in zeoslib, but when the DBMS is on a non-standard port (for example, 5433) ?

debian 10 (astralinux 1.7 "Smolensk"

), lazarus 3.0.0, zeoslib r8073, postgresql 11, all default settings except the port number (port = 5433), peer authentication, unix domain socket file:

Code: Select all

ls -a /var/run/postgresql/.s*
/var/run/postgresql/.s.PGSQL.5433  /var/run/postgresql/.s.PGSQL.5433.lock

Through the standard program (from postgresql-client package) I connect as “psql -p 5433” and everything works, peer authentication passes.

but when I try to do the same through zeoslib:

Code: Select all

var
  zconn1: TZConnection;
begin
  zconn1 := TZConnection.Create(nil);
  zconn1.Protocol := 'postgresql';
  zconn1.Port := 5433;
  zconn1.Connect;

I see an error:

error: connection to server on socket
"/var/run/postgresql/.s.PGSQL.5432"
failed: No such file or directory
Is the server running locally and accepting connections on that socket ?

Why is it trying to connect to the unix socket " .s.PGSQL.5432" if I explicitly specified port=5433 in ZConnection ?

documentation for postgresql:

host
Name of host to connect to. If a host name begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default behavior when host is not specified, or is empty, is to connect to a Unix-domain socket in /tmp (or whatever socket directory was specified when PostgreSQL was built).

ok, let's try to explicitly specify the path to the directory in host:

Code: Select all

var
  zconn1: TZConnection;
begin
  zconn1 := TZConnection.Create(nil);
  zconn1.Protocol := 'postgresql';
  zconn1.Port := 5433;
  zconn1.Host := '/var/run/postgresql/';
  zconn1.Connect;

error: connection to server on socket
"/var/run/postgresql/.s.PGSQL.5432"
failed: No such file or directory
Is the server running locally and accepting connections on that socket ?

Code: 7 Message: Connect to "var/run/postgresql/:5433" as user ""

It seems that the host in ZConnection is NOT the same as the host in libpq - i.e. ZConnection always tries to connect via tcp/ip and does not take into account that the host property may begin with a slash and this means using unix sockets.

but if the "host" property in zconnection is empty, it ignores the "port" property and always connects to 5432, but connects via a unix socket (I changed the postgresql port to the default 5432 and tried it - for the default connection via unix socket and peer, authentication works: no need to configure nothing at all except specifying the 'postgresql' protocol in zconnection).

how to force ZConnection to use a unix socket, but correctly pass the port number (the way "psql -p 5433" is done - it connects via a unix socket and correctly adds the port number as an extension to the file in '/var/run/postgresql/.s.PGSQL.') ?

P.S. if we use the standard TPQConnection from lazarus - everything is fine in it, there the parameters specified in the "Params" property are passed to libpq and we can specify it like this:

Code: Select all

var
  PQConnection1: TPQConnection;
begin
  PQConnection1 := TPQConnection.Create(nil);
  PQConnection1.LoginPrompt := false;
  PQConnection1.KeepConnection := false;
  PQConnection1.Params.Text := 'port=5433';
  PQConnection1.Connected := true;

and everything connects correctly (only the port was specified, the host was not specified and it uses a unix socket) both through a unix socket and to port 5433.

Can someone add the same property to ZConnection (protocol='postgresql'), in which we can specify any parameters and they would be passed without changes to libpq in order to already use any functionality (properties) specified on libpq parameter key words ?

Posted: **17.02.2024, 19:12**

(delete this message)

Posted: **17.02.2024, 20:46**

as I understand it, the problem is that the current sources are not designed to use a port number without specifying a host: if only a port without a host is specified, then an incorrect URL is generated, in which the port is simply not added (/src/dbc/ZDbcIntfs.pas, lines 5001-5006):

Code: Select all

   if HostName <> '' then
   begin
     Result := Result + HostName;
     if Port <> 0 then
       Result := Result + ':' + ZFastCode.IntToStr(Port);
   end;

for now I replaced this code with:

Code: Select all

   if HostName <> '' then
     Result := Result + HostName;
   if Port <> 0 then
     Result := Result + ':' + ZFastCode.IntToStr(Port);

and the port is added to the URL regardless of the host. Parsing this URL is successful - at line 5075:

Code: Select all

BreakString(AValue, ':', AHostName, APort);

The port is successfully obtained, even if the host is not specified there.

Well, then all the code also works correctly.

If you agree, make this change to the source code.

Well, maybe also add the following changes: in the ZDbcPostgreSql.pas file, in lines 787:

Code: Select all

  If IsIpAddr(HostName) then
    AddParamToResult('hostaddr', HostName)
  else
    AddParamToResult('host', HostName);

and 793:

Code: Select all

  AddParamToResult('dbname', Database);

check and add the host and dbname parameters to the connection string only if they are not empty (if they are empty, and they can be empty, then there is no need to add them to the connection string)

Posted: **17.02.2024, 20:56**

By the way, add both user and password at the same time, in line 794 of the ZDbcPostgreSql.pas file:

Code: Select all

   if user <> '' then begin
     AddParamToResult('user', User);
     AddParamToResult('password', Password);
   end;

is also a little incorrect: user authentication can be done via kerberos (by the way, it wouldn’t hurt to add the ability to specify the principal name if kerberos is used but the principal is not default. Although rarely does anyone create a custom one in the domain) or also using built-in OS methods, and an already authenticated user can map (using the postgresql config) to the desired role (and it is the name of this role that is specified as user), accordingly, there may be a situation when only the role is specified, but there is no password. Therefore, it is better to rewrite the code above as:

Code: Select all

   if user <> '' then begin
     AddParamToResult('user', User);
     if Password <> '' then AddParamToResult('password', Password);
   end;

Posted: **17.02.2024, 21:07**

about how correct it is to specify only the port without the host in the URL - I looked at 34.1.1.2. Connection URIs, it says:

34.1.1.2. Connection URIs
The general form for a connection URI is:

postgresql://[userspec@][hostspec][/dbname][?paramspec]

where userspec is:

user[:password]

and hostspec is:

[host][:port][,...]

and paramspec is:

name=value[&...]

As I understand it, this means that in hostspec we can only specify [:port] without [host], i.e. everything is correct

Posted: **20.02.2024, 09:06**

Hello iteh,

I am sorry, I couldn't look at the problem more early. I am tight on time these days, so cannot do much debugging of problems. Thank you very much for debugging this. I added your change for the handling of ports in TZURL.GetURL.
I also added your suggestions to only fill in host name and database if they are not empty.

I am hesitant to do the same with the password. I do know that SSPI authentication works even if we keep the addition of empty passwords. I am not sure though what happens if someone wants to authenticate using an empty password when something like md5 authentiacation gets used? To be more precise:
-> Is it possible to create a user with an empty password?
-> Assuming, this is possible, what happens if we don't add the password='' part to the connection string in that case?

Since SSPI works right now with an empty password set, I assume that GSSAPI and Kerberos will work too.

iteh wrote: ↑17.02.2024, 21:07 about how correct it is to specify only the port without the host in the URL - I looked at 34.1.1.2. Connection URIs, it says:

When it comes to URI-handling in TZUrl the relevant documentation isn't PostgreSQLs URI handling but JDBCs URI handling.

Your changes should show up in SVN and Github soon

Thank you for your work.

Jan

Posted: **20.02.2024, 12:45**

marsupilami, thanks for making the changes

about an empty password (the ability to set an empty password and set it in the connection string) - I think, it needs to be filtered, because, according to CREATE ROLE,

If no password is specified, the password will be set to null and password authentication will always fail for that user. A null password can optionally be written explicitly as PASSWORD NULL.

Note
Specifying an empty string will also set the password to null, but that was not the case before PostgreSQL version 10. In earlier versions, an empty string could be used, or not, depending on the authentication method and the exact version, and libpq would refuse to use it in any case. To avoid the ambiguity, specifying an empty string should be avoided.

as I understand it, even if we try to pass an empty password to the libpq library, the library itself must reject it.

But, just in case, let's filter empty passwords already at the zeoslib level ?

Posted: **20.02.2024, 14:40**

and another question, according to LIBPQ-CONNSTRING-URIS:

The host part is interpreted as described for the parameter host. In particular, a Unix-domain socket connection is chosen if the host part is either empty or looks like an absolute path name, otherwise a TCP/IP connection is initiated. Note, however, that the slash is a reserved character in the hierarchical part of the URI. So, to specify a non-standard Unix-domain socket directory, either omit the host part of the URI and specify the host as a named parameter, or percent-encode the path in the host part of the URI:

postgresql:///dbname?host=/var/lib/postgresql
postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

and also LIBPQ-PARAMKEYWORDS:

If a host name looks like an absolute path name, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. (On Unix, an absolute path name begins with a slash. On Windows, paths starting with drive letters are also recognized.) If the host name starts with @, it is taken as a Unix-domain socket in the abstract namespace (currently supported on Linux and Windows)

I understand that we won’t check all the options now (for example, I don’t currently have a deployed copy under Windows with SSPI to check - only Linux), but if you don’t mind, we can do something like this, ZDbcIntfs.pas:

this code:

Code: Select all

function TZURL.GetURL: string;
var
  Params: string;
begin
...
  // HostName/Port
  if HostName <> '' then
    Result := Result + HostName;
...
  // Join the params
  Params := '';

replace with:

Code: Select all

function TZURL.GetURL: string;
var
  Params: string;
  HostAsParam: boolean;
begin
...
  // HostName/Port
  HostAsParam := false;
  if HostName <> '' then
  begin
    if (HostName[1] in ['@','/']) or // Unix-domain socket directory or abstract namespace
       (pos(':\',HostName) = 2) then // windows path with drive letters
      HostAsParam := true
    else
      Result := Result + HostName;
  end;
 ...
  // Join the params
  if HostAsParam then
    Params := 'host=' + HostName + '?'
  else
    Params := '';

and in TZURL.SetURL - this code:

Code: Select all

// AHostName, APort
    BreakString(AValue, ':', AHostName, APort);

replace with:

Code: Select all

// AHostName, APort
    BreakString(AValue, ':', AHostName, APort);
    if pos('host=', AProperties) = 1 then
    begin
      AHostName := copy(AProperties,1,pos('?',AProperties));
      delete(AProperties,1,length(AHostName));
      delete(AHostName,pos('?',AHostName),Length(AHostName));
      delete(AHostName,1,5);
    end;

if I'm not mistaken anywhere (yes, I'm a little "clumsily" passing the host parameter as "host=...", but that's all I could come up with so that there would be no unnecessary problems with the situation when the port number is not specified, but we need pass both the host in the form of a directory in Linux and the name of the database and so that it is parsed correctly via "/". Alternatively, always pass to GetURL/SetURL all parts, host and port, etc., even if they are empty - so that there is a fixed structure with known delimiters) this will allow us to specify the host both as a unix directory and as a windows path and as a regular host and not specify the host at all.

Well, just (is it necessary?) to correct the message when there is an error connecting to the database - so that it is more informative.

It is also possible (is it necessary?) to add ifdef conditions so that the corresponding parts of the code are done only for windows/linux.

well, or don’t touch anything else for now - perhaps I’m the only one who tried to connect through zeoslib by specifying a local directory

Posted: **21.02.2024, 08:20**

iteh wrote: ↑20.02.2024, 12:45 But, just in case, let's filter empty passwords already at the zeoslib level ?

I applied that change.

When it comes to using directory names instead of host names, things get harder to an extent. The problem most possibly about getting the encoding and decoding of dbc layer URIs done correctly. I do acknowledge that there is a problem because we cannot use directory names as the host name there. But then I am not sure if this really is the way to go.
I think it might make more sense to specify the directory as a separate parameter in the propertiers of TZConnection. Something like this:

Code: Select all

Conn := TZConnection.Create;
Conn.Protocol = 'potgres';
Conn.Database = 'zeostets';
Conn.HostName := '';
Conn.Properties.Add('serversocket=/path/to/dir');
//more settings to come

The reason for this is that there are databases where we have file names as database names. If we allow to have path names as host names and don't specify ports, we cannot get that parsed correctly. I think the specification for a DBC layer URi is more or less like this:

zdbc:<protocol>://[servername][:<portnumber>]/[databasename]

If we use that specification and assume that firebird will add a possibility to talk using unix domain sockets, things will get messy on Linux:
-> Socket name: /tmp/firebird.socket
-> Database name: /srv/firebird/somedatabase.fdb

zdbc:firebird:///tmp/firebird.socket/srv/firebird/somedatabase.fdb

I cannot see any algorithm parse that correctly.
But I can see the following being parsed correctly:

zdbc:firebird:///srv/firebird/somedatabase.fdb?serversocket=/tmp/firebird.socket

So - I think if we want to support connectiong to unix domain sockets in non-standard locations, we should simply add another parameter that will be checked if the host name is empty.

Posted: **21.02.2024, 08:27**

Addendum: I just checked what the PostgreSQL JDBC driver does. They - more or less - go the same route. The unix domain socket name is part of a separate parameter:
https://jdbc.postgresql.org/documentation/use/#unix-sockets

So - I think this really is the way to go. Add another parameter to be able to specify a unix domain socket.

Posted: **21.02.2024, 09:25**

(delete this message)

Posted: **21.02.2024, 12:25**

marsupilami wrote: ↑21.02.2024, 08:20 databases where we have file names as database names
...
So - I think if we want to support connectiong to unix domain sockets in non-standard locations, we should simply add another parameter that will be checked if the host name is empty.

yes, you are right, for such an ambiguous situation, it is better to pass it through separate parameters, not as a common encoded string (which is problematic to parse).

I probably missed something in the code (ZPropertiesEditor.pas has TZProperty - should I somehow describe here the keywords that I added to the connection string ?), but the following code seems to me to be the simplest solution (I added a check to the function that the string is not empty, immediately inside TryAddParamViaKeyword - to make the main code look simpler, because we don’t seem to have parameters with empty values; also added prioritization of values from ZConnection.Properties for some parameters - yes, for the password such prioritization is already done in TZURL.DoOnPropertiesChange - but we can still use this code with the TryAddParamViaKeyword function, because the Info.Values['password'] value will be empty and the function will work correctly; and also added some keywords from LIBPQ-PARAMKEYWORDS as an example):

ZDbcPostgreSql.pas , TZPostgreSQLConnection.BuildConnectStr

after procedure AddParamToResult add this function:

Code: Select all

  function TryAddParamViaKeyword(const AKeyWord: RawByteString; const AAltValue: String = ''): boolean;
  var
    AValue: string;
  begin
    AValue := Info.Values[AKeyWord];
    Result := (AValue <> '') or (AAltValue <> '');
    if not Result then
      exit;
    if AValue <> '' then
      AddParamToResult(AKeyWord, AValue)
    else
      AddParamToResult(AKeyWord, AAltValue);
  end;

and this code:

Code: Select all

  if HostName <> '' then begin
    If IsIpAddr(HostName) then
      AddParamToResult('hostaddr', HostName)
    else
      AddParamToResult('host', HostName);
  end;

  AddParamToResult('port', ZFastCode.IntToStr(Port));
  if database <> '' then
    AddParamToResult('dbname', Database);
  if user <> '' then begin
    AddParamToResult('user', User);
    AddParamToResult('password', Password);
  end;

  If Info.Values[ConnProps_SSLMode] <> ''
    // the client (>= 7.3) sets the ssl mode for this connection
    // (possible values are: require, prefer, allow, disable)
  then AddParamToResult(ConnProps_SSLMode, Info.Values[ConnProps_SSLMode])
  else if Info.Values[ConnProps_RequireSSL] <> ''
    // the client (< 7.3) sets the ssl encription for this connection
    // (possible values are: 0,1)
  then AddParamToResult(ConnProps_RequireSSL, Info.Values[ConnProps_RequireSSL]);

  if Info.Values[ConnProps_SSLPassword] <> '' then AddParamToResult(ConnProps_SSLPassword, Info.Values[ConnProps_SSLPassword]);
  if Info.Values[ConnProps_SSLCompression] <> '' then AddParamToResult(ConnProps_SSLCompression, Info.Values[ConnProps_SSLCompression]);
  if Info.Values[ConnProps_SSLCert] <> '' then AddParamToResult(ConnProps_SSLCert, Info.Values[ConnProps_SSLCert]);
  if Info.Values[ConnProps_SSLKey] <> '' then AddParamToResult(ConnProps_SSLKey, Info.Values[ConnProps_SSLKey]);
  if Info.Values[ConnProps_SSLRootcert] <> '' then AddParamToResult(ConnProps_SSLRootcert, Info.Values[ConnProps_SSLRootcert]);
  if Info.Values[ConnProps_SSLCrl] <> '' then AddParamToResult(ConnProps_SSLCrl, Info.Values[ConnProps_SSLCrl]);
  { tcp keepalives by Luca Olivetti }
  if Info.Values[ConnProps_keepalives] <> '' then AddParamToResult(ConnProps_keepalives,Info.Values[ConnProps_keepalives]);
  if Info.Values[ConnProps_keepalives_idle] <> '' then AddParamToResult(ConnProps_keepalives_idle,Info.Values[ConnProps_keepalives_idle]);
  if Info.Values[ConnProps_keepalives_interval] <> '' then AddParamToResult(ConnProps_keepalives_interval,Info.Values[ConnProps_keepalives_interval]);
  if Info.Values[ConnProps_keepalives_count] <> '' then AddParamToResult(ConnProps_keepalives_count,Info.Values[ConnProps_keepalives_count]);

  { Sets a connection timeout. }
  ConnectTimeout := StrToIntDef(Info.Values[ConnProps_Timeout], -1);
  if ConnectTimeout >= 0 then
    AddParamToResult('connect_timeout', ZFastCode.IntToStr(ConnectTimeout));

  { Sets the application name }
  if Info.Values[ConnProps_ApplicationName] <> '' then
    AddParamToResult(ConnProps_ApplicationName, Info.Values[ConnProps_ApplicationName]);

replace with (yes, there is a lot there - I rewrote some of the code for better readability.. and maybe this code needs to correct the function name and my “English” comments

):

Code: Select all

  // parameters, specified via ZConnection.Properties (Info.Values[...], has priority:
  if not (TryAddParamViaKeyword('host') or TryAddParamViaKeyword('hostaddr')) then // libpq allows us to specify both "host" and "hostaddr" at the same time if the client wants it
    if HostName <> '' then begin
      If IsIpAddr(HostName) then
        AddParamToResult('hostaddr', HostName)
      else
        AddParamToResult('host', HostName);
    end;

  TryAddParamViaKeyword('port', ZFastCode.IntToStr(Port));
  TryAddParamViaKeyword('dbname', Database);
  if TryAddParamViaKeyword('user', User) then
    TryAddParamViaKeyword('password', Password);

  // the client (>= 7.3) sets the ssl mode for this connection
  // (possible values are: require, prefer, allow, disable)
  If not TryAddParamViaKeyword(ConnProps_SSLMode) then
  // the client (< 7.3) sets the ssl encription for this connection
  // (possible values are: 0,1)
    TryAddParamViaKeyword(ConnProps_RequireSSL);

  TryAddParamViaKeyword(ConnProps_SSLPassword);
  TryAddParamViaKeyword(ConnProps_SSLCompression);
  TryAddParamViaKeyword(ConnProps_SSLCert);
  TryAddParamViaKeyword(ConnProps_SSLKey);
  TryAddParamViaKeyword(ConnProps_SSLRootcert);
  TryAddParamViaKeyword(ConnProps_SSLCrl);
  { tcp keepalives by Luca Olivetti }
  TryAddParamViaKeyword(ConnProps_keepalives);
  TryAddParamViaKeyword(ConnProps_keepalives_idle);
  TryAddParamViaKeyword(ConnProps_keepalives_interval);
  TryAddParamViaKeyword(ConnProps_keepalives_count);

  { Sets a connection timeout. }
  ConnectTimeout := StrToIntDef(Info.Values[ConnProps_Timeout], -1);
  if ConnectTimeout >= 0 then
    AddParamToResult('connect_timeout', ZFastCode.IntToStr(ConnectTimeout));

  { Sets the application name }
  TryAddParamViaKeyword(ConnProps_ApplicationName);

  { Sets the custom parameters from https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS }
  TryAddParamViaKeyword('krbsrvname');
  TryAddParamViaKeyword('service');

(yes, in this case, if someone assigns for example , windows path in ZConnection.Hostname, then the TZURL.SetURL source code will incorrectly parse the string "c:\abc" as "c" as the host and "\abc" as the port - but this can be added to the documentation that ZConnection.Hostname can be either empty or IP or a name resolved to IP, and any other options - through ZConnection.Properties)

I thought about adding the code "adding, to the connection string, all parameters not explicitly specified earlier in the TZPostgreSQLConnection.BuildConnectStr", but Info.Values contains a lot of other values placed and checked in other modules - and I abandoned this idea.

And one more question: is it possible to add the contents of the BuildConnectStr to the connection error message ?
this will help programmers when debugging - so that they can immediately see what parameters were passed to libpq and, if something is missing, they think about how to pass what they need to ZConnection.

Posted: **27.03.2024, 11:36**

Hello iteh,

I am sorry for the delay. I am moving currently. I will check this during the easter holidays.

With best regards,

Jan

Zeoslib Portal

connecting via unix socket to a non-standard port

connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port

Re: connecting via unix socket to a non-standard port