How do we integrate the postgres db with dot Net agent?

[Shubham-koli]

Hello fellow developers, I was struggling little on integrating Postgres DB with arise dot net framework. so I have my database running. I was wondering how do I configure the framework to use Postgres DB instead of default SQLite.

if you have any leads please share thank you!

[biligunb]

I think .NET agent is using default implementation of Indy SDK wallet. (which is SQLITE) to change to Postgre all you need is to give proper configuration. (wallet configuration) with postgre url, admin username, password etc... see ACA-PY for more information. There you can see better documentation

[VpavlovLumedic]

You will get an error "Unhandled exception. Hyperledger.Indy.WalletApi.UnknownWalletTypeException: The wallet type specified has not been registered." if you will just specify configs. It's not that easy.

@tmarkovski do you have any guide for postre sql integration or plans to support it in this project?

[DibbsZA]

Still no support it appears.

@acuderman does Pull #166 give support for this now? Doesn't seem to cater for a StorageCredentials model like in acapy (only a plain object)

    object storageCredentials = new {
        account = dbUser,
        password = dbPswd,
        admin_account = "",
        admin_password = ""
    };

Are we supposed to perhaps provide instead the full db url (with creds)?

[acuderman]

It is possible to use postgres storage with dotnet agent but requires slightly more work than just updating the configuration.

Steps:

1. Build postgres plugin
2. DLL import plugins init functions

internal static class PostgresPlugin
{
    [DllImport("indystrgpostgres", CharSet = CharSet.Ansi, BestFitMapping = false, ThrowOnUnmappableChar = true)]
    internal static extern void postgresstorage_init();

    [DllImport("indystrgpostgres", CharSet = CharSet.Ansi, BestFitMapping = false, ThrowOnUnmappableChar = true)]
    internal static extern int init_storagetype(string config, string credentials);
}

3. Call imported functions before agent registration

// startup.cs
public void ConfigureServices(IServiceCollection services)
{
    WalletConfiguration.WalletStorageConfiguration storageConfiguration = new WalletConfiguration.WalletStorageConfiguration
    {
        Url = "localhost:5432",
        WalletScheme = "MultiWalletSingleTableSharedPool",
    };
    object storageCredentials = new
    {
        account = "db_user",
        password = "db_password",
        admin_account = "admin_db_account",
        admin_password = "admin_password"
    };
    
    // init postgres_storage
    PostgresPlugin.postgresstorage_init();
    PostgresPlugin.init_storagetype(JsonConvert.SerializeObject(storageConfiguration),
        JsonConvert.SerializeObject(storageCredentials));

    // register agent
    services.AddAriesFramework(builder =>
    {
        builder.RegisterAgent(options =>
        {
            // ...other options
            options.WalletConfiguration = new WalletConfiguration()
            {
                Id = "wallet_id",
                StorageType = "postgres_storage",
                StorageConfiguration = storageConfiguration
            };
            options.WalletCredentials = new WalletCredentials()
            {
                Key = "key",
                StorageCredentials = storageCredentials
            };
        });
    });
}

@DibbsZA #166 Adds postgres support on the mediator agent for storing inbox records but for other agents and records postgres plugin should be already supported.

[Arsh-Sandhu]

Hi @acuderman, how can we create DLL for postgres_storage plugin. when i try to build the postgres_storage plugin project on windows, i am also facing similar error. https://github.com/hyperledger/indy-sdk/issues/2248

[acuderman]

Hi, @Arsh-Sandhu I didn't have any problems with building the plugin on the Linux system. @MaticDiba I believe you solved mentioned issue on Windows?

[Arsh-Sandhu]

yeah, on Ubuntu we are also able to build it and generate the .so file for this plugin. But we need to generate the DLL so that we can reference it in .net core cloud agent. so when we try to build in windows, we get the above error, can't find crate for 'vcpkg'

[sahil-khanna]

Hi,

The Postgres Plugin doesn't work in a Linux Docker Conatiner with the DotNet Mediator Agent. The same thing works fine on my MacOS

• I built a project on Mac OS and the output was a libindystrgpostgres.so and libindystrgpostgres.dylib files.
• I copied the .dylib into a Visual Studio Console project and was able to load and invoke the functions of the library using the DllImport function. The project works as expected on MacOS
• I copied the project (including the .so and .dylib files) into a Ubuntu-based Docker container
• When I run the project using dotnet run, I get the below error

System.DllNotFoundException: Unable to load shared library 'indystrgpostgres' or one of its dependencies. In order to help diagnose loading problems, consider setting the DYLD_PRINT_LIBRARIES environment variable: dlopen(libindystrgpostgres, 1): image not found

Any suggestions around this issue?

[DibbsZA]

@sahil-khanna We run the mediator in Linux with no issues. A difference I note from your comments is that we don't use the .dylib at all. just the .so file which is placed at the root of the project. (Remember to force it to be copied to output on build)

We use a dedicated plugin loader class

public static class PostgresPlugin
    {
        static bool Loaded = false;

        [DllImport("indystrgpostgres", CharSet = CharSet.Ansi, BestFitMapping = false, ThrowOnUnmappableChar = true)]
        internal static extern int postgresstorage_init();

        [DllImport("indystrgpostgres", CharSet = CharSet.Ansi, BestFitMapping = false, ThrowOnUnmappableChar = true)]
        internal static extern int init_storagetype(string config, string credential);

        public static void LoadPostgresPlugin(WalletStorageConfiguration walletStorageConfiguration, object walletCredentials)
        {
            if (!Loaded)
            {
                var result = postgresstorage_init();
                if (result != 0)
                {
                    throw new Exception("Error in loading postgres library");
                }

                result = init_storagetype(walletStorageConfiguration.ToJson(), walletCredentials.ToJson());
                if (result != 0)
                {
                    throw new Exception($"Error unable to configure postgres stg: { result }");
                }
            }
        }
    }

which is called from startup in

Console.WriteLine("Using Postgres Wallet.");
  object storageCredentials = new
  {
      account = Environment.GetEnvironmentVariable("PG_USER"),
      password = Environment.GetEnvironmentVariable("PG_PSWD"),
      admin_account = Environment.GetEnvironmentVariable("PG_ADMIN_USER"),
      admin_password = Environment.GetEnvironmentVariable("PG_ADMIN_PSWD")
  };

  var walletStorageConfiguration = new WalletConfiguration.WalletStorageConfiguration
  {
      Url = Environment.GetEnvironmentVariable("PG_CONNECTION"),
      WalletScheme = "MultiWalletSingleTable",
      DatabaseName = Environment.GetEnvironmentVariable("PG_DBNAME"),
      Tls = "off",
      MaxConnections = 90,
      ConnectionTimeout = 30,
  };

  var _config = new WalletConfiguration
  {
      Id = Environment.GetEnvironmentVariable("WALLET_ID"),
      StorageType = Environment.GetEnvironmentVariable("STORAGE_TYPE"),
      StorageConfiguration = walletStorageConfiguration
  };

  var _creds = new WalletCredentials
  {
      Key = Environment.GetEnvironmentVariable("WALLET_KEY"),
      StorageCredentials = storageCredentials
  };

  services.AddAriesFramework(builder =>
  {
      _ = builder.RegisterMediatorAgent<CustomMediatorAgent>(options =>
      {
          options.EndpointUri = endpointUri;
          options.WalletCredentials = _creds;
          options.WalletConfiguration = _config;
      });

      PostgresPlugin.LoadPostgresPlugin(walletStorageConfiguration, storageCredentials);
      services.AddHostedService<ForwardMessageSubscriber>();
      services.AddSingleton<Hyperledger.Aries.Agents.IAgentMiddleware, MessagesMiddleware>();

  });

[sahil-khanna]

@DibbsZA Tried the below based on your suggestion. However, getting the same error.

Use the attached Dockerfile to

• Create an image using Ubuntu
• Install .Net 3.1 and the other dependencies
• Build the Indy Postgres Plugin
• Copy the generated libraries to appropriate locations

Execute the below commands to start the container and run the .NET project

docker build -t mediator-agent-custom .
docker run -itd -p 5000:5000 --name=mediator-agent-custom mediator-agent-custom
docker exec -it mediator-agent-custom bash
dotnet run

Can you give it a shot and guide me on the changes, please?

new-mediator.zip

[sahil-khanna]

@sahil-khanna We run the mediator in Linux with no issues. A difference I note from your comments is that we don't use the .dylib at all. just the .so file which is placed at the root of the project. (Remember to force it to be copied to output on build)

We use a dedicated plugin loader class

public static class PostgresPlugin
    {
        static bool Loaded = false;

        [DllImport("indystrgpostgres", CharSet = CharSet.Ansi, BestFitMapping = false, ThrowOnUnmappableChar = true)]
        internal static extern int postgresstorage_init();

        [DllImport("indystrgpostgres", CharSet = CharSet.Ansi, BestFitMapping = false, ThrowOnUnmappableChar = true)]
        internal static extern int init_storagetype(string config, string credential);

        public static void LoadPostgresPlugin(WalletStorageConfiguration walletStorageConfiguration, object walletCredentials)
        {
            if (!Loaded)
            {
                var result = postgresstorage_init();
                if (result != 0)
                {
                    throw new Exception("Error in loading postgres library");
                }

                result = init_storagetype(walletStorageConfiguration.ToJson(), walletCredentials.ToJson());
                if (result != 0)
                {
                    throw new Exception($"Error unable to configure postgres stg: { result }");
                }
            }
        }
    }

which is called from startup in

            services.AddAriesFramework(builder =>
                        {
                            _ = builder.RegisterMediatorAgent<CustomMediatorAgent>(options =>
                            {
                                options.EndpointUri = endpointUri;
                                options.WalletCredentials = _creds;
                                options.WalletConfiguration = _config;
                            });
                            PostgresPlugin.LoadPostgresPlugin(walletStorageConfiguration, storageCredentials);
                            .....
             });

@DibbsZA , can you help with this, please?

https://github.com/hyperledger/aries-framework-dotnet/issues/107#issuecomment-994486532

[x0axz]

Hi all, is there anyone who have successfully integrated PostgreSQL with Aries Agents? If yes, could you please share detail on how to integrate it?

[DibbsZA]

Sorry for not following up on the requests from before. I have also edited my previous posts to include more complete example of the configuration setup in code. Our approach is a couple of steps:

1st I built a custom base docker image that does the necessary build and dependency installs from scratch.

indy-cli-dotnet5 .Dockerfile

FROM ubuntu:18.04
RUN apt-get update -y && apt-get install -y software-properties-common  apt-transport-https curl wget gnupg ca-certificates
RUN apt-key adv --keyserver keyserver.ubuntu.com --recv-keys CE7709D068DB5E88
RUN apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 3FA7E0328081BFF6A14DA29AA6A19B38D3D831EF
RUN add-apt-repository "deb https://repo.sovrin.org/sdk/deb bionic rc"
RUN add-apt-repository "deb https://download.mono-project.com/repo/ubuntu stable-bionic main"
RUN wget https://packages.microsoft.com/config/ubuntu/18.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
RUN dpkg -i packages-microsoft-prod.deb
RUN apt-get update -y && apt-get install -y --allow-unauthenticated  libindy indy-cli dotnet-sdk-5.0 libgdiplus
RUN curl https://sh.rustup.rs -sSf | sh -s -- -y --default-toolchain ${RUST_VER}
ENV PATH /root/.cargo/bin:$PATH
RUN cargo install cargo-deb cargo-bump
RUN cd /tmp && git clone https://github.com/hyperledger/indy-sdk.git
RUN cd ./indy-sdk/experimental/plugins/postgres_storage
RUN RUSTFLAGS=" -L ../../../libindy/target/debug/" cargo build --lib
RUN cp ./target/debug/*.so /usr/lib

We then always reference this base image in further container builds of our agent code. This is the same for Agents or Mediators .

agent or mediator .Dockerfile

FROM our_own_container_registry/registry/indy-cli-dotnet5 as base
WORKDIR /app
EXPOSE 80

COPY ./publish .
ENTRYPOINT ["dotnet", "Our_Agent.dll"]

And this is run with the necessary ENV values. For Postgres you need:

PG_ADMIN_PSWD=xxxxxxxx
PG_ADMIN_USER=xxxxxxxx
PG_CONNECTION=hostname:port_number
PG_DBNAME=database_name
PG_PSWD=xxxxxxxx
PG_USER=xxxxxxxxx
STORAGE_TYPE=postgres_storage

[x0axz]

Thank you for the thorough explanation. I tried what you said, but now I'm getting this issue. Do you have any ideas?

thread '<unnamed>' panicked at 'called `Result::unwrap()` on an `Err` value: Error(Db(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42P01"), message: "relation \"metadata\" does not exist", detail: None, hint: None, position: Some(Normal(19)), where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_relation.c"), line: Some(1381), routine: Some("parserOpenTable") }))', src/postgres_storage.rs:966:33

[x0axz]

Removing the WalletScheme = "MultiWalletSingleTable" resolved the above issue, but couldn't figured it the reason. Also, paste the libindystrgpostgres.so file in dotnet project's ./bin/Debug/netcoreapp3.1/ folder. cp libindystrgpostgres.so ./bin/Debug/netcoreapp3.1/

[x0axz]

PostgresPlugin.cs

[DllImport("libindystrgpostgres.so", CharSet = CharSet.Ansi, BestFitMapping = false, ThrowOnUnmappableChar = true)]
internal static extern int postgresstorage_init();

[DllImport("libindystrgpostgres.so", CharSet = CharSet.Ansi, BestFitMapping = false, ThrowOnUnmappableChar = true)]
internal static extern int init_storagetype(string config, string credential);