SQLParseException when opening CrateDB connection in C#

Integrations
1

I get this exception when I open the C# connection to my database:

Npgsql.PostgresException: ‘XX000: SQLParseException: line 2:51: mismatched input ‘READ’ >expecting {, ‘;’}’
This exception was originally thrown at this call stack:
[External Code]
CrateDBClient.Program.SaveLocation(CrateDBClient.Location) in Program.cs
CrateDBClient.Program.Main(string) in Program.cs

The code I execute:

// Host -> sudo nano /etc/hostname ; and check if pingable on other device
using (var conn = new NpgsqlConnection("Host=ark1123;Username=edgeingest;Password=p@ssw0rd"))
{
  conn.Open(); <- fails on this row
  // Insert some data

It fails when I open the connection in C# (using the latest crate nuget package).

<PackageReference Include="Npgsql.CrateDB" Version="1.2.3" />

I have a Linux machine which runs the Crate container and port 5432 and 4200 are opened. (hostname is ark1123).

The table is created with:

CREATE TABLE iss (timestamp TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP,position GEO_POINT)
CREATE USER edgeingest WITH (password = 'p@ssw0rd');
GRANT ALL to edgeingest;

On the machine running the C# code, I can reach the admin portal.

What is happening?

Sander

2

I found out I was missing the line

NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory());

Once added, I was able to run queries.

I noticed that the connectionstring also works without providing the password.

What is the common way to secure access to tables?

1 Like
3

The default configuration (crate.yml) coming with docker images uses a simplified version of the standard configuration file (https://github.com/crate/crate/blob/master/app/src/main/dist/config/crate.yml).

HBA (Host Based Authentication) is disabled by default

By default, the boolean setting auth.host_based.enabled is false and therefore host based authentication is disabled. In this instance, the CrateDB cluster allows any unauthenticated connections.

With the standard configuration you wouldn’t be able to connect to crateDB running in a Docker container unless you would start a terminal session within the container (localhost).

After setting up an inital user, you should adapt the crate.yml and activate HBA. with something like

auth.host_based.enabled: true

auth:
  host_based:
    config:
      0:
        user: crate
        address: _local_
        method: trust
      99:
        method: password
`
``

then you can only connect using a user/pwd combination from outside the docker container.