elsa-workflows / elsa-workflows/elsa-core

Elsa 3.3 Secret Management - SQL Server DataType does not support secret expiry greater than 24 hours

Open
#6,356 1 comment 0 reactions 0 assignees View on GitHub
triaged
Dominant language
C#
Stars
7.9k
Forks
1.5k
Avg merge
15h 22m
Merged PRs (30d)
114

Description

## Description
I've been trying out the new secret management within Elsa but I've hit an issue that seems to be specific to the SQL Server implementation. By default, Entity Framework maps the `TimeSpan` type to `time(7)`, but this type only supports 24 hours, so the options from Elsa Studio can't be used.

## Steps to Reproduce
Create a new project and add the required dependencies for SQL Server secret persistence, for example:
```
dotnet add package Elsa
dotnet add package Elsa.EntityFrameworkCore
dotnet add package Elsa.EntityFrameworkCore.SqlServer
dotnet add package Elsa.Secrets.Api
dotnet add package Elsa.Secrets.Persistence.EntityFrameworkCore.SqlServer
dotnet add package Elsa.Workflows.Api
dotnet add package Elsa.Workflows.Core
dotnet add package Elsa.Workflows.Management
```

Enable the secrets management and API
``` csharp
using Elsa.EntityFrameworkCore.Extensions;
using Elsa.EntityFrameworkCore.Modules.Runtime;
using Elsa.Extensions;
using Elsa.Secrets.Extensions;
using Elsa.Secrets.Persistence;

var builder = WebApplication.CreateBuilder(args);

var connectionString = "Server=(localdb)\\MSSQLLocalDB;Initial Catalog=elsa-secrets;";

builder.Services.AddDataProtection();
builder.Services.AddElsa(elsa =>
elsa.UseWorkflowsApi()
.UseSecrets()
.UseSecretsManagement(management => management.UseEntityFrameworkCore(a => a.UseSqlServer(connectionString)))
.UseSecretsApi()
.UseIdentity(identity =>
{
identity.TokenOptions = options => options.SigningKey = "sufficiently-large-secret-signing-key";
identity.UseAdminUserProvider();
})
.UseDefaultAuthentication(auth => auth.UseAdminApiKey())
.AddSwagger()
);

var app = builder.Build();
app.UseWorkflowsApi();
app.UseSwaggerUI();
app.Run();
```

Run the project and attempt to create a new secret, passing in an `expiresIn` of a day or greater
```
curl --location 'http://localhost:5000/elsa/api/secrets' \
--header 'Authorization: ApiKey 00000000-0000-0000-0000-000000000000' \
--header 'Content-Type: application/json' \
--data '{"name":"Example Expiry","scope":"General","value":"Example Value","description":"Test to demonstrate expiry","expiresIn":"7.00:00:00"}'
```

## Expected Behavior
Secret is created within the `[Elsa].[Secrets]` table with `[ExpiresIn]` and `[ExpiresAt]` both populated

## Actual Behavior
The operation fails with the error `Value '1.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999`

## Log Output
```
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
Executing endpoint 'HTTP: POST /elsa/api/secrets'
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (0ms) [Parameters=[@p0='?' (Size = 450), @p1='?' (DbType = DateTimeOffset), @p2='?' (Size = 4000), @p3='?' (Size = 4000), @p4='?' (DbType = DateTimeOffset), @p5='?' (DbType = Time), @p6='?' (DbType = Boolean), @p7='?' (DbType = DateTimeOffset), @p8='?' (Size = 450), @p9='?' (Size = 4000), @p10='?' (Size = 450), @p11='?' (Size = 4000), @p12='?' (DbType = Int32), @p13='?' (Size = 450), @p14='?' (DbType = DateTimeOffset), @p15='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [Elsa].[Secrets] ([Id], [CreatedAt], [Description], [EncryptedValue], [ExpiresAt], [ExpiresIn], [IsLatest], [LastAccessedAt], [Name], [Owner], [Scope], [SecretId], [Status], [TenantId], [UpdatedAt], [Version])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15);
fail: Microsoft.EntityFrameworkCore.Update[10000]
An exception occurred in the database while saving changes for context type 'Elsa.Secrets.Persistence.EntityFrameworkCore.SecretsDbContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> System.OverflowException: SqlDbType.Time overflow. Value '1.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__211_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> System.OverflowException: SqlDbType.Time overflow. Value '1.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__211_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.