public void BasicSsl(bool useSslStream)
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
SslMode = SslMode.Require,
TrustServerCertificate = true,
UseSslStream = useSslStream
};
using (var conn = OpenConnection(csb))
Assert.That(conn.IsSecure, Is.True);
}
public void NoSslRenegotiation()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
SslMode = SslMode.Require,
TrustServerCertificate = true
};
using (var conn = OpenConnection(csb))
{
Assert.That(conn.ExecuteScalar("SHOW ssl_renegotiation_limit"), Is.EqualTo("0"));
conn.ExecuteNonQuery("DISCARD ALL");
Assert.That(conn.ExecuteScalar("SHOW ssl_renegotiation_limit"), Is.EqualTo("0"));
}
}
public void BaseCatalogName()
{
var dbName = new NpgsqlConnectionStringBuilder(ConnectionString).Database;
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (foo INTEGER)");
using (var cmd = new NpgsqlCommand("SELECT foo,8 FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
var columns = reader.GetColumnSchema();
Assert.That(columns[0].BaseCatalogName, Is.EqualTo(dbName));
Assert.That(columns[1].BaseCatalogName, Is.EqualTo(dbName));
}
}
}
public void RejectSelfSignedCertificate(bool useSslStream)
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
SslMode = SslMode.Require,
UseSslStream = useSslStream
};
using (var conn = new NpgsqlConnection(csb))
{
// The following is necessary since a pooled connector may exist from a previous
// SSL test
NpgsqlConnection.ClearPool(conn);
// TODO: Specific exception, align with SslStream
Assert.That(() => conn.Open(), Throws.Exception);
}
}
public void MinPoolSize()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString) { MinPoolSize = 2 };
using (var conn = new NpgsqlConnection(connString))
{
connString = conn.Settings; // Shouldn't be necessary
conn.Open();
conn.Close();
}
var pool = PoolManager.Pools[connString];
Assert.That(pool.Idle, Has.Count.EqualTo(2));
// Now open 2 connections and make sure they're good
using (var conn1 = OpenConnection(connString))
using (var conn2 = OpenConnection(connString))
{
Assert.That(pool.Idle, Has.Count.Zero);
Assert.That(conn1.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
Assert.That(conn2.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
}
public void MinPoolSizeLargerThanPoolSizeLimit()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString);
Assert.That(() => csb.MinPoolSize = PoolManager.PoolSizeLimit + 1, Throws.Exception.TypeOf <ArgumentOutOfRangeException>());
}
public void FixtureSetup()
{
var connStrBuilder = new NpgsqlConnectionStringBuilder(Properties.Settings.Default.PostGis);
if (string.IsNullOrEmpty(connStrBuilder.Host) || string.IsNullOrEmpty(connStrBuilder.Database))
{
Assert.Ignore("Requires PostgreSQL connectionstring");
}
GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();
try
{
// Set up sample table
using (var conn = new NpgsqlConnection(Properties.Settings.Default.PostGis))
{
conn.Open();
// Load data
using (var shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile(), false, false, 4326))
{
shapeFile.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "DROP TABLE IF EXISTS roads_ugl";
cmd.ExecuteNonQuery();
cmd.CommandText =
"CREATE TABLE roads_ugl(id integer primary key, name character varying(100), geog geography);";
cmd.ExecuteNonQuery();
}
IEnumerable <uint> indexes = shapeFile.GetObjectIDsInView(shapeFile.GetExtents());
_insertedIds = new List <uint>(indexes.Take(100));
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO roads_ugl(id, name, geog) VALUES (@PId, @PName, ST_GeogFromWKB(@PGeom));";
var @params = cmd.Parameters;
@params.AddRange(
new[]
{
new NpgsqlParameter("PId", NpgsqlDbType.Integer),
new NpgsqlParameter("PName", NpgsqlDbType.Varchar, 100),
new NpgsqlParameter("PGeom", NpgsqlDbType.Bytea)
});
var writer = new PostGisWriter();
foreach (var idx in _insertedIds)
{
var feature = shapeFile.GetFeature(idx);
@params["PId"].NpgsqlValue = (int)idx;
@params["PName"].NpgsqlValue = feature["NAME"];
@params["PGeom"].NpgsqlValue = writer.Write(feature.Geometry);
cmd.ExecuteNonQuery();
}
}
// Verify
foreach (var pgp in GetTestProvider())
{
foreach (var idx in _insertedIds)
{
var g1 = pgp.GetGeometryByID(idx);
var g2 = shapeFile.GetGeometryByID(idx);
Assert.AreEqual(g1, g2);
}
}
}
}
}
catch (Exception ee)
{
Assert.Ignore("Failed to connect to PostgreSQL/PostGIS Server");
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDatabaseDeveloperPageExceptionFilter();
var dbProvider = Configuration["ApplicationOptions:DBProvider"].ToLower();
if (dbProvider == "sqlite")
{
services.AddDbContext <ApplicationDbContext, SqliteDbContext>(options =>
options.UseSqlite(Configuration.GetConnectionString("SQLite")));
}
else if (dbProvider == "sqlserver")
{
services.AddDbContext <ApplicationDbContext, SqlServerDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("SQLServer")));
}
else if (dbProvider == "postgresql")
{
services.AddDbContext <ApplicationDbContext, PostgreSqlDbContext>(options =>
{
// Password should be set in User Secrets in dev environment.
// See https://docs.microsoft.com/en-us/aspnet/core/security/app-secrets?view=aspnetcore-3.1
if (!string.IsNullOrWhiteSpace(Configuration.GetValue <string>("PostgresPassword")))
{
var connectionBuilder = new NpgsqlConnectionStringBuilder(Configuration.GetConnectionString("PostgreSQL"))
{
Password = Configuration["PostgresPassword"]
};
options.UseNpgsql(connectionBuilder.ConnectionString);
}
else
{
options.UseNpgsql(Configuration.GetConnectionString("PostgreSQL"));
}
});
}
services.AddIdentity <RemotelyUser, IdentityRole>(options => options.Stores.MaxLengthForKeys = 128)
.AddEntityFrameworkStores <ApplicationDbContext>()
.AddDefaultUI()
.AddDefaultTokenProviders();
var trustedOrigins = Configuration.GetSection("ApplicationOptions:TrustedCorsOrigins").Get <string[]>();
if (trustedOrigins != null)
{
services.AddCors(options =>
{
options.AddPolicy("TrustedOriginPolicy", builder => builder
.WithOrigins(trustedOrigins)
.AllowAnyHeader()
.AllowAnyMethod()
.AllowCredentials()
);
});
}
var knownProxies = Configuration.GetSection("ApplicationOptions:KnownProxies").Get <string[]>();
services.Configure <ForwardedHeadersOptions>(options =>
{
options.ForwardedHeaders = ForwardedHeaders.XForwardedFor | ForwardedHeaders.XForwardedProto;
options.ForwardLimit = null;
if (knownProxies?.Any() == true)
{
foreach (var proxy in knownProxies)
{
options.KnownProxies.Add(IPAddress.Parse(proxy));
}
}
});
services.AddMvc()
.SetCompatibilityVersion(CompatibilityVersion.Version_3_0).AddJsonOptions(options =>
{
options.JsonSerializerOptions.PropertyNamingPolicy = new PascalCasePolicy();
});
services.AddSignalR(options =>
{
options.EnableDetailedErrors = IsDev;
options.MaximumReceiveMessageSize = 500_000;
})
.AddJsonProtocol(options =>
{
options.PayloadSerializerOptions.PropertyNamingPolicy = new PascalCasePolicy();
})
.AddMessagePackProtocol();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo {
Title = "Remotely API", Version = "v1"
});
});
services.AddLogging();
services.AddScoped <IEmailSenderEx, EmailSenderEx>();
services.AddScoped <IEmailSender, EmailSender>();
services.AddScoped <DataService>();
services.AddSingleton <ApplicationConfig>();
services.AddScoped <ApiAuthorizationFilter>();
services.AddHostedService <CleanupService>();
services.AddScoped <RemoteControlFilterAttribute>();
}
/// <summary>Registers a DbContext. Takes values from connection string and/or environment variables</summary>
/// <param name="appConfig"></param>
/// <param name="migrationAssembly">Assembly name where migrations are located</param>
/// <param name="connectionVarName">Name of an environment variable that holds a connection string name</param>
/// <param name="hostVarName">Name of an environment variable that holds a db host</param>
/// <param name="portVarName">Name of an environment variable that holds a db port</param>
/// <param name="userVarName">Name of an environment variable that holds a db user name</param>
/// <param name="passwordVarName">Name of an environment variable that holds a db user password</param>
/// <param name="services"></param>
/// <exception cref="ArgumentNullException">Application configuration is required</exception>
/// <exception cref="ConfigurationException">Throws when connection string is empty or there is no password db environment variable</exception>
public static IServiceCollection AddNpgsqlContext <TContext>(
this IServiceCollection services,
IConfiguration appConfig,
string migrationAssembly = null,
string connectionVarName = DEFAULT_CONNECTION_STRING_VAR_NAME,
string hostVarName = DEFAULT_DB_HOST_VAR_NAME,
string portVarName = DEFAULT_DB_PORT_VAR_NAME,
string userVarName = DEFAULT_DB_USER_VAR_NAME,
string passwordVarName = DEFAULT_DB_PASSWORD_VAR_NAME)
where TContext : DbContext
{
services.AddDbContext <TContext>(options =>
{
if (appConfig == null)
{
throw new ArgumentNullException(nameof(appConfig));
}
// name of the connection string can be set from environment variable or default name will be used
var connectionStringName = DEFAULT_CONNECTION_STRING_NAME;
if (!string.IsNullOrWhiteSpace(connectionVarName))
{
connectionStringName = appConfig[connectionVarName];
}
if (string.IsNullOrWhiteSpace(connectionStringName))
{
if (connectionVarName != DEFAULT_CONNECTION_STRING_VAR_NAME)
{
throw new ConfigurationException($"There is no connection string name in the environment variable {connectionVarName}");
}
connectionStringName = DEFAULT_CONNECTION_STRING_NAME;
}
var connectionStringValue = appConfig.GetConnectionString(connectionStringName);
if (string.IsNullOrWhiteSpace(connectionStringValue))
{
throw new MissingConnectionStringException(connectionStringName);
}
// password can be set from environment variable or connection string
var password = "";
if (!string.IsNullOrWhiteSpace(passwordVarName))
{
password = appConfig[passwordVarName];
if (string.IsNullOrWhiteSpace(password) && passwordVarName != DEFAULT_DB_PASSWORD_VAR_NAME)
{
throw new ConfigurationException($"There is no db password in the environment variable '{passwordVarName}'");
}
}
// username can be set from environment variable or connection string
var userName = "";
if (!string.IsNullOrWhiteSpace(userVarName))
{
userName = appConfig[userVarName];
if (string.IsNullOrWhiteSpace(userName) && userVarName != DEFAULT_DB_USER_VAR_NAME)
{
throw new ConfigurationException($"There is no db user name in the environment variable '{userVarName}'");
}
}
// host can be set from environment variable or connection string
string host = null;
if (!string.IsNullOrWhiteSpace(hostVarName))
{
host = appConfig[hostVarName];
if (string.IsNullOrWhiteSpace(host) && hostVarName != DEFAULT_DB_HOST_VAR_NAME)
{
throw new ConfigurationException($"There is no db host in the environment variable '{hostVarName}'");
}
}
// port can be set from environment variable or connection string
int?port = null;
if (!string.IsNullOrWhiteSpace(portVarName))
{
var portStr = appConfig[portVarName];
if (string.IsNullOrWhiteSpace(portStr) && portVarName != DEFAULT_DB_PORT_VAR_NAME)
{
throw new ConfigurationException($"There is no db port in the environment variable '{portVarName}'");
}
try
{
if (!string.IsNullOrWhiteSpace(portStr))
{
port = int.Parse(portStr);
}
}
catch (Exception ex)
{
throw new ConfigurationException($"There is wrong value in the environment variable '{portVarName}'. Must be a positive integer", ex);
}
if (port <= 0)
{
throw new ConfigurationException($"There is wrong value in the environment variable '{portVarName}'. Must be a positive integer");
}
}
var builder = new NpgsqlConnectionStringBuilder(connectionStringValue);
if (!string.IsNullOrWhiteSpace(host))
{
builder.Host = host;
}
if (port > 0)
{
builder.Port = port.Value;
}
if (!string.IsNullOrWhiteSpace(userName))
{
builder.Username = userName;
}
if (!string.IsNullOrWhiteSpace(password))
{
builder.Password = password;
}
if (string.IsNullOrWhiteSpace(migrationAssembly))
{
options.UseNpgsql(builder.ConnectionString);
}
else
{
options.UseNpgsql(builder.ConnectionString, npgsqlOptionsAction => npgsqlOptionsAction.MigrationsAssembly(migrationAssembly));
}
});
return(services);
}
public void ConfigureServices(IServiceCollection services)
{
var connectionString = Configuration["ConnectionString"];
var databaseType = Configuration["Database"];
if (string.IsNullOrEmpty(databaseType))
{
// Use SQLite when running outside a benchmark test or if benchmarks user specified "None".
// ("None" is not passed to the web application.)
databaseType = "SQLite";
}
else if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentException("Connection string must be specified for {databaseType}.");
}
switch (databaseType.ToUpperInvariant())
{
#if !NET461
case "MYSQL":
services
.AddEntityFrameworkMySql()
.AddDbContextPool <BasicViewsContext>(options => options.UseMySql(connectionString));
break;
#endif
case "POSTGRESQL":
var settings = new NpgsqlConnectionStringBuilder(connectionString);
if (!settings.NoResetOnClose)
{
throw new ArgumentException("No Reset On Close=true must be specified for Npgsql.");
}
if (settings.Enlist)
{
throw new ArgumentException("Enlist=false must be specified for Npgsql.");
}
services
.AddEntityFrameworkNpgsql()
.AddDbContextPool <BasicViewsContext>(options => options.UseNpgsql(connectionString));
break;
case "SQLITE":
_isSQLite = true;
services
.AddEntityFrameworkSqlite()
.AddDbContextPool <BasicViewsContext>(options => options.UseSqlite("Data Source=BasicViews.db;Cache=Shared"));
break;
case "SQLSERVER":
services
.AddEntityFrameworkSqlServer()
.AddDbContextPool <BasicViewsContext>(options => options.UseSqlServer(connectionString));
break;
default:
throw new ArgumentException($"Application does not support database type {databaseType}.");
}
services.AddMvc();
}
public async Task TimeoutGettingConnectorFromExhaustedPoolAsync()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
MaxPoolSize = 1,
Timeout = 1
};
using (var conn1 = new NpgsqlConnection(connString))
{
await conn1.OpenAsync();
// Pool is exhausted
using (var conn2 = new NpgsqlConnection(connString))
Assert.That(async () => await conn2.OpenAsync(), Throws.Exception.TypeOf<NpgsqlException>());
}
// conn1 should now be back in the pool as idle
using (var conn3 = new NpgsqlConnection(connString))
conn3.Open();
}
public void DataTypeWithComposite()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(DataTypeWithComposite), // Prevent backend type caching in TypeHandlerRegistry
Pooling = false
};
using (var conn = OpenConnection(csb))
{
conn.ExecuteNonQuery("CREATE TYPE pg_temp.some_composite AS (foo int)");
conn.ReloadTypes();
conn.MapComposite<SomeComposite>();
conn.ExecuteNonQuery("CREATE TEMP TABLE data (comp pg_temp.some_composite)");
using (var cmd = new NpgsqlCommand("SELECT comp,'(4)'::some_composite FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
var columns = reader.GetColumnSchema();
Assert.That(columns[0].DataType, Is.SameAs(typeof(SomeComposite)));
Assert.That(columns[0].UdtAssemblyQualifiedName, Is.EqualTo(typeof(SomeComposite).AssemblyQualifiedName));
Assert.That(columns[1].DataType, Is.SameAs(typeof(SomeComposite)));
Assert.That(columns[1].UdtAssemblyQualifiedName, Is.EqualTo(typeof(SomeComposite).AssemblyQualifiedName));
}
}
}
public void SetUp()
{
Builder = new NpgsqlConnectionStringBuilder();
}
public override NpgsqlTypeHandler <LocalDate> Create(PostgresType postgresType, NpgsqlConnection conn)
{
var csb = new NpgsqlConnectionStringBuilder(conn.ConnectionString);
return(new DateHandler(postgresType, csb.ConvertInfinityDateTime));
}
// This method gets called by the runtime. Use this method to add services to the container.
// For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
public void ConfigureServices(IServiceCollection services)
{
var dbProvider = Configuration["ApplicationOptions:DBProvider"].ToLower();
if (dbProvider == "sqlite")
{
services.AddDbContextFactory <SqliteDbContext>(options =>
{
options.UseSqlite(Configuration.GetConnectionString("SQLite"));
});
services.AddScoped <IDbContextFactory <AppDb> >(p =>
p.GetRequiredService <IDbContextFactory <SqliteDbContext> >());
services.AddScoped <AppDb, SqliteDbContext>(p =>
p.GetRequiredService <IDbContextFactory <SqliteDbContext> >().CreateDbContext());
}
else if (dbProvider == "sqlserver")
{
services.AddDbContextFactory <SqlServerDbContext>(options =>
{
options.UseSqlServer(Configuration.GetConnectionString("SQLServer"));
});
services.AddScoped <IDbContextFactory <AppDb> >(p =>
p.GetRequiredService <IDbContextFactory <SqlServerDbContext> >());
services.AddScoped <AppDb, SqlServerDbContext>(p =>
p.GetRequiredService <IDbContextFactory <SqlServerDbContext> >().CreateDbContext());
}
else if (dbProvider == "postgresql")
{
services.AddDbContextFactory <PostgreSqlDbContext>(options =>
{
// Password should be set in User Secrets in dev environment.
// See https://docs.microsoft.com/en-us/aspnet/core/security/app-secrets?view=aspnetcore-3.1
if (!string.IsNullOrWhiteSpace(Configuration.GetValue <string>("PostgresPassword")))
{
var connectionBuilder = new NpgsqlConnectionStringBuilder(Configuration.GetConnectionString("PostgreSQL"))
{
Password = Configuration["PostgresPassword"]
};
options.UseNpgsql(connectionBuilder.ConnectionString);
}
else
{
options.UseNpgsql(Configuration.GetConnectionString("PostgreSQL"));
}
});
services.AddScoped <IDbContextFactory <AppDb> >(p =>
p.GetRequiredService <IDbContextFactory <PostgreSqlDbContext> >());
services.AddScoped <AppDb, PostgreSqlDbContext>(p =>
p.GetRequiredService <IDbContextFactory <PostgreSqlDbContext> >().CreateDbContext());
}
services.AddIdentity <RemotelyUser, IdentityRole>(options =>
{
options.Stores.MaxLengthForKeys = 128;
options.Password.RequireNonAlphanumeric = false;
})
.AddEntityFrameworkStores <AppDb>()
.AddDefaultUI()
.AddDefaultTokenProviders();
services.AddScoped <IAuthorizationHandler, TwoFactorRequiredHandler>();
services.AddAuthorization(options =>
{
options.AddPolicy(TwoFactorRequiredRequirement.PolicyName, builder =>
{
builder.Requirements.Add(new TwoFactorRequiredRequirement());
});
});
services.AddRazorPages();
services.AddServerSideBlazor();
services.AddScoped <AuthenticationStateProvider, RevalidatingIdentityAuthenticationStateProvider <RemotelyUser> >();
services.AddDatabaseDeveloperPageExceptionFilter();
var trustedOrigins = Configuration.GetSection("ApplicationOptions:TrustedCorsOrigins").Get <string[]>();
if (trustedOrigins != null)
{
services.AddCors(options =>
{
options.AddPolicy("TrustedOriginPolicy", builder => builder
.WithOrigins(trustedOrigins)
.AllowAnyHeader()
.AllowAnyMethod()
.AllowCredentials()
);
});
}
var knownProxies = Configuration.GetSection("ApplicationOptions:KnownProxies").Get <string[]>();
services.Configure <ForwardedHeadersOptions>(options =>
{
options.ForwardedHeaders = ForwardedHeaders.XForwardedFor | ForwardedHeaders.XForwardedProto;
options.ForwardLimit = null;
if (knownProxies?.Any() == true)
{
foreach (var proxy in knownProxies)
{
options.KnownProxies.Add(IPAddress.Parse(proxy));
}
}
});
services.AddSignalR(options =>
{
options.EnableDetailedErrors = IsDev;
options.MaximumReceiveMessageSize = 500_000;
})
.AddJsonProtocol(options =>
{
options.PayloadSerializerOptions.PropertyNameCaseInsensitive = true;
})
.AddMessagePackProtocol();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo {
Title = "Remotely API", Version = "v1"
});
});
services.AddHttpClient();
services.AddLogging();
services.AddScoped <IEmailSenderEx, EmailSenderEx>();
services.AddScoped <IEmailSender, EmailSender>();
services.AddTransient <IDataService, DataService>();
services.AddScoped <IApplicationConfig, ApplicationConfig>();
services.AddScoped <ApiAuthorizationFilter>();
services.AddScoped <ExpiringTokenFilter>();
services.AddHostedService <CleanupService>();
services.AddHostedService <ScriptScheduler>();
services.AddScoped <RemoteControlFilterAttribute>();
services.AddScoped <IUpgradeService, UpgradeService>();
services.AddScoped <IToastService, ToastService>();
services.AddScoped <IModalService, ModalService>();
services.AddScoped <IJsInterop, JsInterop>();
services.AddScoped <ICircuitConnection, CircuitConnection>();
services.AddScoped(x => (CircuitHandler)x.GetRequiredService <ICircuitConnection>());
services.AddSingleton <ICircuitManager, CircuitManager>();
services.AddScoped <IAuthService, AuthService>();
services.AddScoped <IClientAppState, ClientAppState>();
services.AddScoped <IExpiringTokenService, ExpiringTokenService>();
services.AddScoped <IScriptScheduleDispatcher, ScriptScheduleDispatcher>();
}
/// <summary>
/// Initializes a new instance of the <see cref="PostgreSqlMetadataCache"/> class.
/// </summary>
/// <param name="connectionBuilder">The connection builder.</param>
public PostgreSqlMetadataCache(NpgsqlConnectionStringBuilder connectionBuilder)
{
m_ConnectionBuilder = connectionBuilder;
}
public string ProcessConnectionString(string connectionString, IDictionary <string, string> environmentVariables)
{
NpgsqlConnectionStringBuilder builder;
if (connectionString != null)
{
builder = new NpgsqlConnectionStringBuilder(connectionString);
}
else
{
builder = new NpgsqlConnectionStringBuilder();
}
if (environmentVariables.ContainsKey(EnvironmentVariables.Host))
{
builder.Host = environmentVariables[EnvironmentVariables.Host];
}
if (environmentVariables.ContainsKey(EnvironmentVariables.Port))
{
var portString = environmentVariables[EnvironmentVariables.Port];
if (int.TryParse(portString, out var port))
{
builder.Port = port;
}
else
{
_logger.LogError("Failed to parse port from environment variable, provided value: {Port}",
portString);
throw new Exception(
$"Failed to parse port from environment variable, provided value: {portString}.");
}
}
if (environmentVariables.ContainsKey(EnvironmentVariables.DatabaseName))
{
builder.Database = environmentVariables[EnvironmentVariables.DatabaseName];
}
if (environmentVariables.ContainsKey(EnvironmentVariables.UserName))
{
builder.Username = environmentVariables[EnvironmentVariables.UserName];
}
if (environmentVariables.ContainsKey(EnvironmentVariables.Password))
{
builder.Password = environmentVariables[EnvironmentVariables.Password];
}
if (builder.ConnectionString.Length > 0)
{
var sensoredPasswordBuilder = new NpgsqlConnectionStringBuilder(builder.ConnectionString)
{
Password = "******"
};
_logger.LogInformation("Use connection string: \"{ConnectionString}\"",
sensoredPasswordBuilder.ConnectionString);
}
return(builder.ConnectionString);
}
public void ToStringWithoutPassword()
{
Builder.ConnectionString = "Host=host1;Username=user;Password=password";
var builderWithoutPassword = new NpgsqlConnectionStringBuilder(Builder.ToStringWithoutPassword());
Assert.That(builderWithoutPassword.Host, Is.EqualTo(Builder.Host));
Assert.That(builderWithoutPassword.Username, Is.EqualTo(Builder.Username));
Assert.That(builderWithoutPassword.Password, Is.Null);
Builder.Host = "host2";
builderWithoutPassword = new NpgsqlConnectionStringBuilder(Builder.ToStringWithoutPassword());
Assert.That(builderWithoutPassword.Host, Is.EqualTo(Builder.Host));
}
public static async Task RunPostgre()
{
var builder = new NpgsqlConnectionStringBuilder();
builder.Database = "testdb";
builder.Host = "localhost";
builder.Port = 5432;
builder.Username = "******";
builder.Password = "******";
var connectionString = builder.ToString();
Console.WriteLine(connectionString);
var db = SqlDb.Create <NpgsqlConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
});
//Number of rows affected
await db.ExecuteAsync("SELECT 'ZhangHe'");
var stringParam = "ZhangHe";
await db.ExecuteAsync("SELECT @String", new { String = stringParam });
await db.ExecuteAsync("SELECT @String", new List <(string, object)>() { ("String", stringParam) });
await db.ExecuteFormatAsync($"SELECT {stringParam}");
//Model
var exe1 = await db.ExecuteAsync <string>("SELECT 'ZhangHe'");
var exe2 = await db.ExecuteAsync <string>("SELECT @String", new { String = stringParam });
var exe3 = await db.ExecuteAsync <string>("SELECT @String", new List <(string, object)>() { ("String", stringParam) });
var exe4 = await db.ExecuteFormatAsync <string>($"SELECT {stringParam}");
(var name, var age) = await db.ExecuteAsync <string, int>("SELECT @Name;SELECT @Age", new { Name = stringParam, Age = int.MinValue });
(var names, var ages, var ids) = await db.ExecuteAsync <List <string>, List <int>, List <int> >("SELECT @Name;SELECT @Age;SELECT @Id", new { Id = 1024, Name = stringParam, Age = int.MinValue });
try { await db.ExecuteAsync("DROP TABLE \"Custom\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"CustomDetail\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"Address\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"Order\""); } catch { }
await db.ExecuteAsync("CREATE TABLE \"Custom\"(\"Id\" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),\"Name\" text,\"Age\" integer)");
await db.ExecuteAsync("CREATE TABLE \"CustomDetail\"(\"CustomId\" integer,\"NullInt\" integer,\"Bool\" boolean)");
await db.ExecuteAsync("CREATE TABLE \"Address\"(\"OrderId\" integer,\"Name\" text,\"Detail\" text,\"Mobile\" text,\"Sheng\" text,\"Shi\" text,\"Xian\" text)");
await db.ExecuteAsync("CREATE TABLE \"Order\"(\"Id\" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),\"Name\" text,\"Price\" integer,\"CreateTime\" DATE,\"CustomId\" integer,\"State\" integer,\"JsonData\" text)");
//SqlDbExtensions.RegisterTable(type => type.Name);
//SqlDbExtensions.RegisterProperty(property => property.Name);
//SqlDbExtensions.RegisterIdentity
SqlDbExtensions.RegisterDbParameter <NpgsqlConnection, JsonData>((value) => {
return(JsonWriter.ToJson <JsonData>((JsonData)value));
});
//(reader,i)=field,(reader)=model Mapper
SqlDbExtensions.RegisterDbReader <NpgsqlDataReader, JsonData>((reader, i) => {
var json = reader.GetString(i);
return(JsonReader.FromJson <JsonData>(json));
});
SqlDbExtensions.RegisterDbMethod <NpgsqlConnection>((methods) => {
foreach (var item in methods)
{
var method = item.Key;
Console.WriteLine($"{method.DeclaringType}.{method.Name}()");
}
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus1"), (expr) => {
return(new object[] { "COUNT(", expr.Arguments[1], ")+1" });//string OR Expression
});
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus2"), (expr) => {
var exprObjs = new List <object>();//string OR Expression
exprObjs.Add("COUNT(");
exprObjs.Add(expr.Arguments[0]);
exprObjs.Add(")+2");
return(exprObjs);
});
});
SqlDbExtensions.RegisterDbMember <NpgsqlConnection>((members) => {
foreach (var item in members)
{
var member = item.Key;
Console.WriteLine($"{member.DeclaringType}.{member.Name}");
}
});
await InsertAsync(db);
await SelectAsync(db);
await UpdateAsync(db);
await DeleteAsync(db);
//Transaction
//out ITransactionFactory use AsyncLocal<>
//如果不介意性能 不需要分开定义 db,dbTx
//If you don't mind performance There is no need to define separately db,dbTx
var dbTx = SqlDb.Create <NpgsqlConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
}, out var txFactory);
await TransactionAsync(dbTx, txFactory);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// services.AddCors("AllowAnyOrigin", builder => builder.AllowAnyOrigin());
services.AddCors(options =>
{
options.AddPolicy("AllowOrigin",
builder =>
{
builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader();
});
});
services.AddControllers();
string env = Environment.GetEnvironmentVariable("JWT_SECRET");
if (string.IsNullOrEmpty(env))
{
var appSettingsSection = Configuration.GetSection("AppSettings");
services.Configure <AppSettings>(appSettingsSection);
var appSettings = appSettingsSection.Get <AppSettings>();
env = appSettings.Secret;
}
var key = Encoding.ASCII.GetBytes(env);
services.AddAuthentication(options =>
{
options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
})
.AddJwtBearer(options =>
{
options.RequireHttpsMetadata = false;
options.SaveToken = true;
options.TokenValidationParameters = new TokenValidationParameters
{
ClockSkew = TimeSpan.FromMinutes(30),
ValidateIssuerSigningKey = true,
IssuerSigningKey = new SymmetricSecurityKey(key),
ValidateIssuer = false,
ValidateAudience = false
};
});
string connStr;
string databaseString = Environment.GetEnvironmentVariable("DATABASE_URL");
if (string.IsNullOrEmpty(databaseString))
{
// Use connection string from file.
connStr = Configuration.GetConnectionString("DefaultConnection1");
}
else
{
var databaseUri = new Uri(databaseString);
var userInfo = databaseUri.UserInfo.Split(':');
var builder = new NpgsqlConnectionStringBuilder
{
Host = databaseUri.Host,
Port = databaseUri.Port,
Username = userInfo[0],
Password = userInfo[1],
Database = databaseUri.LocalPath.TrimStart('/')
};
connStr = builder.ToString();
}
services.AddScoped <ITokenService, TokenService>();
services.AddDbContext <UserContext>(options => options.UseNpgsql(connStr));
services.AddDbContext <VisitorContext>(options => options.UseNpgsql(connStr));
// services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_3_0);
}
public void WithoutPersistSecurityInfo()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { Pooling = false };
using (var conn = OpenConnection(csb))
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (id INT)");
using (var cmd = new NpgsqlCommand("SELECT id FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo))
reader.GetSchemaTable();
}
}
public void ConfigureServices(IServiceCollection services)
{
var rsa = new RSACryptoServiceProvider(2048);
var key = new RsaSecurityKey(rsa.ExportParameters(true));
services.AddSingleton(new SigningCredentials(
key,
SecurityAlgorithms.RsaSha256Signature));
services.AddAuthentication().AddJwtBearer(options =>
{
options.TokenValidationParameters.IssuerSigningKey = key;
options.TokenValidationParameters.ValidAudience = "Myself";
options.TokenValidationParameters.ValidIssuer = "BasicApi";
});
var connectionString = Configuration["ConnectionString"];
var databaseType = Configuration["Database"];
if (string.IsNullOrEmpty(databaseType))
{
// Use SQLite when running outside a benchmark test or if benchmarks user specified "None".
// ("None" is not passed to the web application.)
databaseType = "SQLite";
}
else if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentException("Connection string must be specified for {databaseType}.");
}
switch (databaseType.ToUpperInvariant())
{
#if !NETFRAMEWORK
case "MYSQL":
services
.AddEntityFrameworkMySql()
.AddDbContextPool <BasicApiContext>(options => options.UseMySql(connectionString));
break;
#endif
case "POSTGRESQL":
var settings = new NpgsqlConnectionStringBuilder(connectionString);
if (!settings.NoResetOnClose)
{
throw new ArgumentException("No Reset On Close=true must be specified for Npgsql.");
}
if (settings.Enlist)
{
throw new ArgumentException("Enlist=false must be specified for Npgsql.");
}
services
.AddEntityFrameworkNpgsql()
.AddDbContextPool <BasicApiContext>(options => options.UseNpgsql(connectionString));
break;
case "SQLITE":
_isSQLite = true;
services
.AddEntityFrameworkSqlite()
.AddDbContextPool <BasicApiContext>(options => options.UseSqlite("Data Source=BasicApi.db;Cache=Shared"));
break;
case "SQLSERVER":
services
.AddEntityFrameworkSqlServer()
.AddDbContextPool <BasicApiContext>(options => options.UseSqlServer(connectionString));
break;
default:
throw new ArgumentException($"Application does not support database type {databaseType}.");
}
services.AddAuthorization(options =>
{
options.AddPolicy(
"pet-store-reader",
builder => builder
.AddAuthenticationSchemes(JwtBearerDefaults.AuthenticationScheme)
.RequireAuthenticatedUser()
.RequireClaim("scope", "pet-store-reader"));
options.AddPolicy(
"pet-store-writer",
builder => builder
.AddAuthenticationSchemes(JwtBearerDefaults.AuthenticationScheme)
.RequireAuthenticatedUser()
.RequireClaim("scope", "pet-store-writer"));
});
services
.AddMvcCore()
.AddAuthorization()
.AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver())
.AddDataAnnotations();
}
public void MinPoolSizeLargeThanPoolSizeLimit()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString);
Assert.That(() => csb.MinPoolSize = PoolManager.PoolSizeLimit + 1, Throws.Exception.TypeOf<ArgumentOutOfRangeException>());
}
protected NpgsqlConnection OpenConnection(NpgsqlConnectionStringBuilder csb)
{
return(OpenConnection(csb.ToString()));
}
private void cmdAceptar_Click(object sender, EventArgs e)
{
string connectionString = null;
switch (TypeDataBase.ToLower())
{
case "postgresql":
var builderPostgreSql = new NpgsqlConnectionStringBuilder
{
Host = connectionServerTextEdit.EditValue.ToString(),
Database = connectionDatabaseTextEdit.EditValue.ToString(),
UserName = connectionUsernameTextEdit.EditValue.ToString(),
Password = connectionPasswordTextEdit.EditValue.ToString()
};
connectionString = string.Format("Server={0};Port=5432;Database={1};User ID={2};Password={3};",
builderPostgreSql.Host, builderPostgreSql.Database, builderPostgreSql.UserName,
System.Text.Encoding.UTF8.GetString(builderPostgreSql.PasswordAsByteArray));
//Probar cadena de conexion para postgresql
try
{
var connection = new NpgsqlConnection(connectionString);
connection.Open();
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message, "Error de conexión", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
break;
case "sqlserver":
var builderSqlServer = new SqlConnectionStringBuilder
{
DataSource = connectionServerTextEdit.EditValue.ToString(),
InitialCatalog = connectionDatabaseTextEdit.EditValue.ToString(),
UserID = connectionUsernameTextEdit.EditValue.ToString(),
Password = connectionPasswordTextEdit.EditValue.ToString()
};
connectionString = string.Format("Server={0};Database={1};User ID={2};Password={3};",
builderSqlServer.DataSource, builderSqlServer.InitialCatalog, builderSqlServer.UserID,
builderSqlServer.Password);
//Probar cadena de conexion para postgresql
try
{
var connection = new SqlConnection(connectionString);
connection.Open();
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message, "Error de conexión", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
break;
}
ConnectionStringManager.SaveConnectionString("MainConnection", Cryptography.EncryptStringAes(connectionString));
ConfigurationManager.RefreshSection("connectionStrings");
IsConfig = true;
XtraMessageBox.Show("Se actualizo la información de conexion", "Configuración de conexion", MessageBoxButtons.OK,
MessageBoxIcon.Information);
Close();
}
public void ConfigureServices(IServiceCollection services)
{
services.Configure <AppSettings>(Configuration);
// We re-register the Scenarios as an instance singleton here to avoid it being created again due to the
// registration done in Program.Main
services.AddSingleton(Scenarios);
// Common DB services
services.AddSingleton <IRandom, DefaultRandom>();
var appSettings = Configuration.Get <AppSettings>();
BatchUpdateString.DatabaseServer = appSettings.Database;
Console.WriteLine($"Database: {appSettings.Database}");
Console.WriteLine($"ConnectionString: {appSettings.ConnectionString}");
switch (appSettings.Database)
{
case DatabaseServer.PostgreSql:
services.AddEntityFrameworkNpgsql();
var settings = new NpgsqlConnectionStringBuilder(appSettings.ConnectionString);
if (!settings.NoResetOnClose)
{
throw new ArgumentException("No Reset On Close=true must be specified for Npgsql");
}
if (settings.Enlist)
{
throw new ArgumentException("Enlist=false must be specified for Npgsql");
}
services.AddDbContextPool <ApplicationDbContext>(options => options.UseNpgsql(appSettings.ConnectionString));
if (Scenarios.Any("Raw") || Scenarios.Any("Dapper"))
{
services.AddSingleton <DbProviderFactory>(NpgsqlFactory.Instance);
}
break;
case DatabaseServer.SqlServer:
services.AddEntityFrameworkSqlServer();
services.AddDbContextPool <ApplicationDbContext>(options => options.UseSqlServer(appSettings.ConnectionString));
if (Scenarios.Any("Raw") || Scenarios.Any("Dapper"))
{
services.AddSingleton <DbProviderFactory>(SqlClientFactory.Instance);
}
break;
case DatabaseServer.MySql:
#if NETCOREAPP3_0 || NETCOREAPP3_1 || NETCOREAPP5_0
throw new NotSupportedException("EF/MySQL is unsupported on netcoreapp3.0 until a provider is available");
#else
services.AddEntityFrameworkMySql();
services.AddDbContextPool <ApplicationDbContext>(options => options.UseMySql(appSettings.ConnectionString));
if (Scenarios.Any("Raw") || Scenarios.Any("Dapper"))
{
services.AddSingleton <DbProviderFactory>(MySql.Data.MySqlClient.MySqlClientFactory.Instance);
}
break;
#endif
case DatabaseServer.MongoDb:
var mongoClient = new MongoClient(appSettings.ConnectionString);
var mongoDatabase = mongoClient.GetDatabase("hello_world");
services.AddSingleton(mongoClient);
services.AddSingleton(mongoDatabase);
services.AddSingleton(sp => mongoDatabase.GetCollection <Fortune>("fortune"));
services.AddSingleton(sp => mongoDatabase.GetCollection <World>("world"));
break;
}
if (Scenarios.Any("Ef"))
{
services.AddScoped <EfDb>();
}
if (Scenarios.Any("Raw"))
{
services.AddScoped <RawDb>();
}
if (Scenarios.Any("Dapper"))
{
services.AddScoped <DapperDb>();
}
if (Scenarios.Any("Mongo"))
{
services.AddScoped <MongoDb>();
}
if (Scenarios.Any("Fortunes"))
{
var settings = new TextEncoderSettings(UnicodeRanges.BasicLatin, UnicodeRanges.Katakana, UnicodeRanges.Hiragana);
settings.AllowCharacter('\u2014'); // allow EM DASH through
services.AddWebEncoders((options) =>
{
options.TextEncoderSettings = settings;
});
}
#if NETCOREAPP2_1 || NETCOREAPP2_2
if (Scenarios.Any("Mvc"))
{
var mvcBuilder = services
.AddMvcCore()
.SetCompatibilityVersion(CompatibilityVersion.Latest);
if (Scenarios.MvcJson || Scenarios.Any("MvcDbSingle") || Scenarios.Any("MvcDbMulti"))
{
mvcBuilder.AddJsonFormatters();
}
if (Scenarios.MvcViews || Scenarios.Any("MvcDbFortunes"))
{
mvcBuilder
.AddViews()
.AddRazorViewEngine();
}
}
#elif NETCOREAPP3_0 || NETCOREAPP3_1 || NETCOREAPP5_0
if (Scenarios.Any("Endpoint"))
{
services.AddRouting();
}
if (Scenarios.Any("Mvc"))
{
IMvcBuilder builder;
if (Scenarios.MvcViews || Scenarios.Any("MvcDbFortunes"))
{
builder = services.AddControllersWithViews();
}
else
{
builder = services.AddControllers();
}
if (Scenarios.Any("MvcJsonNet"))
{
builder.AddNewtonsoftJson();
}
}
#else
#error "Unsupported TFM"
#endif
if (Scenarios.Any("MemoryCache"))
{
services.AddMemoryCache();
}
if (Scenarios.Any("ResponseCaching"))
{
services.AddResponseCaching();
}
}
static void Main(string[] args)
{
var sConnStr = new NpgsqlConnectionStringBuilder
{
Host = "192.168.88.102",
Port = 5432,
Database = "outpost2",
Username = "******",
Password = "******",
}.ConnectionString;
Console.WriteLine("Тест \"Неповторяемое чтение\"");
Console.WriteLine("Использовать ISOLATION LEVEL REPEATABLE READ?");
string yes = Console.ReadLine();
bool use = yes.ToLower() == "yes" || yes.ToLower() == "да";
using (var sConn1 = new NpgsqlConnection(sConnStr))
{
using (var sConn2 = new NpgsqlConnection(sConnStr))
{
sConn1.Open();
sConn2.Open();
var sCommand = new NpgsqlCommand
{
Connection = sConn1,
CommandText = @"DROP TABLE IF EXISTS test;
CREATE TABLE test
(
id integer,
num integer
);
INSERT INTO test (id, num)
VALUES (1, 10);
SELECT num FROM test;"
};
Console.WriteLine("Что храниться в таблице: " + sCommand.ExecuteScalar());
if (use)
{
sCommand = new NpgsqlCommand
{
Connection = sConn1,
CommandText = @"BEGIN ISOLATION LEVEL REPEATABLE READ ;
SELECT num FROM test;"
}
}
;
else
{
sCommand = new NpgsqlCommand
{
Connection = sConn1,
CommandText = @"BEGIN;
SELECT num FROM test;"
}
};
Console.WriteLine($"Первая транзакция считала (транзакция не завершилась): {sCommand.ExecuteScalar()}");
var sCommand2 = new NpgsqlCommand
{
Connection = sConn2,
CommandText = @"UPDATE test
SET num = num + 200
WHERE id = 1
RETURNING num;"
};
Console.WriteLine($"Вторая транзакция обновила поле, новое значение: {sCommand2.ExecuteScalar()}");
sCommand = new NpgsqlCommand
{
Connection = sConn1,
CommandText = @"SELECT num FROM test;
COMMIT;"
};
Console.WriteLine($"Первая транзакция завершилась, результат: {sCommand.ExecuteScalar()}");
}
}
Console.WriteLine("Для выхода нажмите enter...");
Console.ReadLine();
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.Configure <CookiePolicyOptions>(options =>
{
// This lambda determines whether user consent for non-essential cookies is needed for a given request.
options.CheckConsentNeeded = context => true;
//options.MinimumSameSitePolicy = SameSiteMode.None;
options.MinimumSameSitePolicy = (SameSiteMode)(-1);
options.OnAppendCookie = cookieContext =>
CheckSameSite(cookieContext.Context, cookieContext.CookieOptions);
options.OnDeleteCookie = cookieContext =>
CheckSameSite(cookieContext.Context, cookieContext.CookieOptions);
});
services.ConfigureApplicationCookie(opts => opts.LoginPath = "/Account/Login");
BindAndRegisterConfigurationSettings(Configuration, services);
DIServicesConfiguration(services);
services.AddIdentity <AppUser, IdentityRole>(opts =>
{
opts.User.RequireUniqueEmail = true;
//opts.User.AllowedUserNameCharacters = "abcdefghijklmnopqrstuvwxyz";
opts.Password.RequiredLength = 6;
opts.Password.RequireNonAlphanumeric = false;
opts.Password.RequireLowercase = false;
opts.Password.RequireUppercase = false;
opts.Password.RequireDigit = false;
}).AddEntityFrameworkStores <ExpenseTrackerDbContext>()
.AddDefaultTokenProviders();
if (Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") == "Production")
{
var dbPassword = Environment.GetEnvironmentVariable("DATABASE_PASSWORD");
var dbHost = Environment.GetEnvironmentVariable("DATABASE_HOST");
var dbUsername = Environment.GetEnvironmentVariable("DATABASE_USERNAME");
var dbName = Environment.GetEnvironmentVariable("DATABASE_NAME");
var gClientId = Environment.GetEnvironmentVariable("GOOGLE_CLIENTID");
var gClientSecret = Environment.GetEnvironmentVariable("GOOGLE_CLIENTSECRET");
var fClientId = Environment.GetEnvironmentVariable("FACEBOOK_CLIENTID");
var fClientSecret = Environment.GetEnvironmentVariable("FACEBOOK_CLIENTSECRET");
int.TryParse(Configuration["PostgreSql:Port"], out int port);
var builder = new NpgsqlConnectionStringBuilder()
{
Host = dbHost,
Password = dbPassword,
Username = dbUsername,
Database = dbName,
Port = port
};
services.AddDbContext <ExpenseTrackerDbContext>(options => options.UseNpgsql(builder.ConnectionString));
services.AddHangfire(x => x.UsePostgreSqlStorage(builder.ConnectionString));
services.AddAuthentication()
.AddGoogle(googleOptions =>
{
googleOptions.ClientId = gClientId;
googleOptions.ClientSecret = gClientSecret;
//googleOptions.CallbackPath = "/Account/ExternalLoginCallback";
})
.AddFacebook(facebookOptions =>
{
facebookOptions.AppId = fClientId;
facebookOptions.AppSecret = fClientSecret;
});
}
else
{
var connectionString = Configuration["PostgreSql:ConnectionString"];
var dbPassword = Configuration["PostgreSql:DbPassword"];
var builder = new NpgsqlConnectionStringBuilder(connectionString)
{
Password = dbPassword
};
services.AddDbContext <ExpenseTrackerDbContext>(options => options.UseNpgsql(builder.ConnectionString));
services.AddHangfire(x => x.UsePostgreSqlStorage(builder.ConnectionString));
services.AddAuthentication()
.AddGoogle(googleOptions =>
{
googleOptions.ClientId = Configuration["OAUTH:providers:0:clientId"];
googleOptions.ClientSecret = Configuration["OAUTH:providers:0:clientSecret"];
//googleOptions.NonceCookie.SameSite = (SameSiteMode)(-1);
googleOptions.CorrelationCookie.SameSite = (SameSiteMode)(-1);
googleOptions.Events = new Microsoft.AspNetCore.Authentication.OAuth.OAuthEvents
{
//OnRemoteFailure = (context) =>
//{
// context.Response.Redirect(context.Properties.GetString("returnUrl"));
// context.HandleResponse();
// return Task.CompletedTask;
//},
OnRedirectToAuthorizationEndpoint = redirectContext =>
{
if (Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") != "Development")
{
//Force scheme of redirect URI (THE IMPORTANT PART)
redirectContext.RedirectUri = redirectContext.RedirectUri.Replace("http://", "https://", StringComparison.OrdinalIgnoreCase);
}
return(Task.FromResult(0));
}
};
//googleOptions.CallbackPath = "/Account/ExternalLoginCallback";
})
.AddFacebook(facebookOptions =>
{
facebookOptions.AppId = Configuration["OAUTH:providers:1:clientId"];
facebookOptions.AppSecret = Configuration["OAUTH:providers:1:clientSecret"];
});
}
services.AddHttpContextAccessor();
// Automatically perform database migration
services.BuildServiceProvider().GetService <ExpenseTrackerDbContext>().Database.Migrate();
services.Configure <ForwardedHeadersOptions>(options =>
{
options.ForwardedHeaders =
ForwardedHeaders.XForwardedFor | ForwardedHeaders.XForwardedProto;
});
services.AddMvc(options =>
{
var policy = new AuthorizationPolicyBuilder()
.RequireAuthenticatedUser().Build();
options.Filters.Add(new AuthorizeFilter(policy));
//options.Filters.Add(new AutoValidateAntiforgeryTokenAttribute());
})
.SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}
public PostgresEventStoreSettings(NpgsqlConnectionStringBuilder connectionStringBuilder, string applicationName)
: this(connectionStringBuilder.ConnectionString, connectionStringBuilder.ConnectionString, applicationName)
{
}
public void CompositeTypeResolutionWithGlobalMapping()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(CompositeTypeResolutionWithGlobalMapping), // Prevent backend type caching in TypeHandlerRegistry
Pooling = false
};
using (var conn = OpenConnection(csb))
{
conn.ExecuteNonQuery("CREATE TYPE pg_temp.composite1 AS (x int, some_text text)");
NpgsqlConnection.GlobalTypeMapper.MapComposite <SomeComposite>("composite1");
try
{
conn.ReloadTypes();
// Resolve type by DataTypeName
using (var cmd = new NpgsqlCommand("SELECT @p", conn))
{
cmd.Parameters.Add(new NpgsqlParameter
{
ParameterName = "p",
DataTypeName = "composite1",
Value = DBNull.Value
});
using (var reader = cmd.ExecuteReader())
{
reader.Read();
Assert.That(reader.GetDataTypeName(0), Does.StartWith("pg_temp").And.EndWith(".composite1"));
Assert.That(reader.IsDBNull(0), Is.True);
}
}
// Resolve type by ClrType (type inference)
conn.ReloadTypes();
using (var cmd = new NpgsqlCommand("SELECT @p", conn))
{
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p", Value = new SomeComposite {
X = 8, SomeText = "foo"
}
});
using (var reader = cmd.ExecuteReader())
{
reader.Read();
Assert.That(reader.GetDataTypeName(0), Does.StartWith("pg_temp").And.EndWith(".composite1"));
}
}
// Resolve type by OID (read)
conn.ReloadTypes();
using (var cmd = new NpgsqlCommand("SELECT ROW(1, 'foo')::COMPOSITE1", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
Assert.That(reader.GetDataTypeName(0), Does.StartWith("pg_temp").And.EndWith(".composite1"));
}
}
finally
{
NpgsqlConnection.GlobalTypeMapper.UnmapComposite <SomeComposite>("composite1");
}
}
}
// This method gets called by the runtime. Use this method to add services to the container.
// For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
public void ConfigureServices(IServiceCollection services)
{
//services.AddMvc();
services.AddControllersWithViews();
services.AddMemoryCache();
//Configure Redis distributed cache
var redisConnectionString = Configuration["RedisConnectionString"];
services.AddStackExchangeRedisCache(options => {
options.Configuration = redisConnectionString;
options.InstanceName = "";
});
services.AddTransient <IDataGenerator, BogusDataGenerator>();
services.AddHttpClient <ILocationService, LocationService>();
//Configure CosmosDB
var cosmosConfig = new CosmosDbConfiguration();
Configuration.Bind("CosmosConfig", cosmosConfig);
services.AddSingleton(cosmosConfig);
try
{
services.AddHttpClient <ICosmosDbService, CosmosDbService>();
services.AddSingleton <ICosmosDbService>(
InitializeCosmosClientInstanceAsync(cosmosConfig)
.GetAwaiter().GetResult());
}
catch (Exception ex)
{
LogException(ex, "Could not start CosmosDB services");
}
//Configure PostgresSQL
services.AddTransient <IPostgresSqlRepository, PostgresSqlRepository>();
services.AddTransient <IPostgresSqlService, PostgresSqlService>();
//Configure SQL Server
var sqlConnectionString = Configuration["SqlConnectionString"];
services.AddDbContext <LocationDbContext>(options =>
options.UseSqlServer(sqlConnectionString, builder => builder
.UseNetTopologySuite()
.EnableRetryOnFailure()),
ServiceLifetime.Transient);
//Configure PostgresSql
var pgConnectionString = Configuration["PostgreSql:ConnectionString"];
var pgPassword = Configuration["PostgreSql:DbPassword"];
var pgBuilder = new NpgsqlConnectionStringBuilder(pgConnectionString)
{
Password = pgPassword
};
services.AddDbContext <StudentDbContext>(options =>
options.UseNpgsql(pgBuilder.ConnectionString));
//Configure Azure Search
//AzureSearchConfiguration
var azureConfig = new AzureSearchConfiguration();
Configuration.Bind("AzureSearchConfiguration", azureConfig);
services.AddSingleton(azureConfig);
//var azureConfig = Configuration["AzureSearchConfiguration"];
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
// services.AddHttpsRedirection(options =>
// {
// options.HttpsPort = 443;
// });
services.AddSingleton <IJwtFactory, JwtFactory>();
//Http client for google maps requests
services.AddHttpClient();
// In production, the Angular files will be served from this directory
services.AddSpaStaticFiles(configuration =>
{
configuration.RootPath = "ClientApp/dist";
});
string dbUrl = Environment.GetEnvironmentVariable("DATABASE_URL");
string connectionString = null;
if (dbUrl != null)
{
var databaseUri = new Uri(dbUrl);
var userInfo = databaseUri.UserInfo.Split(':');
var builder = new NpgsqlConnectionStringBuilder
{
Host = databaseUri.Host,
Port = databaseUri.Port,
Username = userInfo[0],
Password = userInfo[1],
Database = databaseUri.LocalPath.TrimStart('/')
};
connectionString = builder.ToString();
}
else
{
connectionString = Configuration.GetConnectionString("DefaultConnection");
}
services.AddDbContext <ApplicationDbContext>(options =>
options.UseNpgsql(connectionString));
services.AddDefaultIdentity <User>(options =>
{
// Password settings
options.Password.RequireDigit = false;
options.Password.RequiredLength = 3;
options.Password.RequireNonAlphanumeric = false;
options.Password.RequireUppercase = false;
options.Password.RequireLowercase = false;
}).AddRoles <IdentityRole>().AddEntityFrameworkStores <ApplicationDbContext>();
services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
.AddJwtBearer(options =>
{
options.TokenValidationParameters = new TokenValidationParameters
{
ValidateIssuer = true,
ValidateAudience = true,
ValidateLifetime = true,
ValidateIssuerSigningKey = true,
ValidIssuer = Configuration["Jwt:Issuer"],
ValidAudience = Configuration["Jwt:Issuer"],
IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(Configuration["Jwt:Key"]))
};
options.Events = new JwtBearerEvents
{
// Adding header if token expired
OnAuthenticationFailed = context =>
{
if (context.Exception.GetType() == typeof(SecurityTokenExpiredException))
{
context.Response.Headers.Add("Token-Expired", "true");
}
return(Task.CompletedTask);
}
};
});
}
public async Task Range_resolution()
{
if (IsMultiplexing)
{
Assert.Ignore("Multiplexing, ReloadTypes");
}
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(Range_resolution), // Prevent backend type caching in TypeHandlerRegistry
Pooling = false
};
using var conn = await OpenConnectionAsync(csb);
// Resolve type by NpgsqlDbType
using (var cmd = new NpgsqlCommand("SELECT @p", conn))
{
cmd.Parameters.AddWithValue("p", NpgsqlDbType.Range | NpgsqlDbType.Integer, DBNull.Value);
using (var reader = await cmd.ExecuteReaderAsync())
{
reader.Read();
Assert.That(reader.GetDataTypeName(0), Is.EqualTo("int4range"));
}
}
// Resolve type by ClrType (type inference)
conn.ReloadTypes();
using (var cmd = new NpgsqlCommand("SELECT @p", conn))
{
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p", Value = new NpgsqlRange <int>(3, 5)
});
using (var reader = await cmd.ExecuteReaderAsync())
{
reader.Read();
Assert.That(reader.GetDataTypeName(0), Is.EqualTo("int4range"));
}
}
// Resolve type by DataTypeName
conn.ReloadTypes();
using (var cmd = new NpgsqlCommand("SELECT @p", conn))
{
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p", DataTypeName = "int4range", Value = DBNull.Value
});
using (var reader = await cmd.ExecuteReaderAsync())
{
reader.Read();
Assert.That(reader.GetDataTypeName(0), Is.EqualTo("int4range"));
}
}
// Resolve type by OID (read)
conn.ReloadTypes();
using (var cmd = new NpgsqlCommand("SELECT int4range(3, 5)", conn))
using (var reader = await cmd.ExecuteReaderAsync())
{
reader.Read();
Assert.That(reader.GetDataTypeName(0), Is.EqualTo("int4range"));
Assert.That(reader.GetFieldValue <NpgsqlRange <int> >(0), Is.EqualTo(new NpgsqlRange <int>(3, true, 5, false)));
}
}
NpgsqlConnection OpenConnectionAndUnprepare(NpgsqlConnectionStringBuilder csb) => OpenConnectionAndUnprepare(csb.ToString());
internal static string GetNpgsqlDatabaseName(string connectionString)
{
var pgBuilder = new NpgsqlConnectionStringBuilder(connectionString);
return(pgBuilder.Database);
}
protected NpgsqlConnection OpenConnection(NpgsqlConnectionStringBuilder csb)
=> OpenConnection(csb.ToString());
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.Configure <CookiePolicyOptions>(options =>
{
// This lambda determines whether user consent for non-essential cookies is needed for a given request.
options.CheckConsentNeeded = context => true;
options.MinimumSameSitePolicy = SameSiteMode.None;
});
var dbProvider = Configuration["ApplicationOptions:DBProvider"].ToLower();
if (dbProvider == "sqlite")
{
services.AddDbContext <ApplicationDbContext>(options =>
options.UseSqlite(
Configuration.GetConnectionString("SQLite")));
}
else if (dbProvider == "sqlserver")
{
services.AddDbContext <ApplicationDbContext>(options =>
options.UseSqlServer(
Configuration.GetConnectionString("SQLServer")));
}
else if (dbProvider == "postgresql")
{
// Password should be set in User Secrets in dev environment.
// See https://docs.microsoft.com/en-us/aspnet/core/security/app-secrets?view=aspnetcore-3.1
if (!string.IsNullOrWhiteSpace(Configuration.GetValue <string>("PostgresPassword")))
{
var connectionBuilder =
new NpgsqlConnectionStringBuilder(Configuration.GetConnectionString("PostgreSQL"));
connectionBuilder.Password = Configuration["PostgresPassword"];
services.AddDbContext <ApplicationDbContext>(options =>
options.UseNpgsql(connectionBuilder.ConnectionString));
}
else
{
services.AddDbContext <ApplicationDbContext>(options =>
options.UseNpgsql(Configuration.GetConnectionString("PostgreSQL")));
}
}
services.AddIdentity <RemotelyUser, IdentityRole>(options => options.Stores.MaxLengthForKeys = 128)
.AddEntityFrameworkStores <ApplicationDbContext>()
.AddDefaultUI()
.AddDefaultTokenProviders();
var remoteControlAuthentication = Configuration
.GetSection("ApplicationOptions:RemoteControlRequiresAuthentication").Get <bool>();
services.AddAuthorization(options =>
{
options.AddPolicy("RemoteControlPolicy", policy =>
{
if (remoteControlAuthentication)
{
policy.RequireAuthenticatedUser();
}
else
{
policy.RequireAssertion(context => true);
}
policy.Build();
});
});
services.ConfigureApplicationCookie(cookieOptions =>
{
cookieOptions.Cookie.SameSite = SameSiteMode.None;
});
var trustedOrigins = Configuration.GetSection("ApplicationOptions:TrustedCorsOrigins").Get <string[]>();
if (trustedOrigins != null)
{
services.AddCors(options =>
{
options.AddPolicy("TrustedOriginPolicy", builder => builder
.WithOrigins(trustedOrigins)
.AllowAnyHeader()
.AllowAnyMethod()
.AllowCredentials()
);
});
}
var knownProxies = Configuration.GetSection("ApplicationOptions:KnownProxies").Get <string[]>();
if (knownProxies != null)
{
services.Configure <ForwardedHeadersOptions>(options =>
{
foreach (var proxy in knownProxies)
{
options.KnownProxies.Add(IPAddress.Parse(proxy));
options.ForwardLimit = 2;
}
});
}
services.AddMvc()
.SetCompatibilityVersion(CompatibilityVersion.Version_3_0).AddJsonOptions(options =>
{
options.JsonSerializerOptions.PropertyNamingPolicy = new PascalCasePolicy();
});
services.AddSignalR(options =>
{
options.EnableDetailedErrors = IsDev;
options.MaximumReceiveMessageSize = 20000000;
})
.AddJsonProtocol(options =>
{
options.PayloadSerializerOptions.PropertyNamingPolicy = new PascalCasePolicy();
})
.AddMessagePackProtocol();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo {
Title = "Remotely API", Version = "v1"
});
});
services.AddLogging();
services.AddScoped <IEmailSenderEx, EmailSenderEx>();
services.AddScoped <IEmailSender, EmailSender>();
services.AddScoped <DataService>();
services.AddScoped <RemoteControlSessionRecorder>();
services.AddSingleton <ApplicationConfig>();
services.AddSingleton <RandomGenerator>();
}
public void PruneIdleConnectors()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(PruneIdleConnectors),
ConnectionIdleLifetime = 1,
ConnectionPruningInterval = 1
};
using (var conn1 = OpenConnection(connString))
using (var conn2 = OpenConnection(connString))
connString = conn1.Settings; // Shouldn't be necessary
// We now have 2 connections in the pool, MinPoolSize is 1
Thread.Sleep(500);
Assert.That(PoolManager.Pools[connString].Idle, Has.Count.EqualTo(2));
Thread.Sleep(1500);
Assert.That(PoolManager.Pools[connString].Idle, Has.Count.EqualTo(1));
}
public void SetUp()
{
Builder = new NpgsqlConnectionStringBuilder();
}
public async Task CancelOpenAsync()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString) {
ApplicationName = nameof(CancelOpenAsync),
MaxPoolSize = 1,
};
using (var conn1 = new NpgsqlConnection(connString))
{
await conn1.OpenAsync();
// Pool is exhausted
using (var conn2 = new NpgsqlConnection(connString))
{
var cts = new CancellationTokenSource(1000);
Assert.That(async () => await conn2.OpenAsync(cts.Token), Throws.Exception.TypeOf<TaskCanceledException>());
}
// The cancelled open attempt should have left a cancelled task completion source
// in the pool's wait queue. Make sure that's so
using (var conn2 = new NpgsqlConnection(connString))
{
new Timer(o => conn1.Close(), null, 1000, Timeout.Infinite);
await conn2.OpenAsync();
}
}
}
public void ConfigureServices(IServiceCollection services)
{
// Set up and configure Entity Framework
var connStringBuilder = new NpgsqlConnectionStringBuilder(
Configuration.GetConnectionString("Booking")
);
connStringBuilder.Password = Configuration["BOOKING_PASSWORD"];
services
.AddEntityFrameworkNpgsql()
.AddDbContext <BookingContext>(
options => options.UseNpgsql(connStringBuilder.ConnectionString)
);
// Set up and configure Identity
services.AddIdentity <IdentityUser <Guid>, IdentityRole <Guid> >()
.AddEntityFrameworkStores <BookingContext, Guid>();
services.Configure <IdentityOptions>(options =>
{
options.Password.RequiredLength = 8;
options.User.RequireUniqueEmail = true;
var appCookie = options.Cookies.ApplicationCookie;
appCookie.CookieName = Configuration["Identity:CookieName"];
appCookie.LoginPath = "/account/login";
appCookie.ReturnUrlParameter = "returnurl";
});
// Set up and configure Localization
services.AddSingleton <IStringLocalizerFactory, StringLocalizerFactory>();
services.Configure <StringLocalizerFactoryOptions>(options =>
Configuration.GetSection("Localization:Factory").Bind(options)
);
services.AddLocalization();
services.Configure <RequestLocalizationOptions>(options =>
{
var defaultCulture = Configuration["Localization:DefaultCulture"];
var supportedCulturesConfig = Configuration.GetSection("Localization:SupportedCultures");
var supportedCultures = supportedCulturesConfig.GetChildren()
.Select(child => new CultureInfo(child.ToString()))
.ToArray();
options.DefaultRequestCulture = new RequestCulture(
culture: defaultCulture, uiCulture: defaultCulture
);
options.RequestCultureProviders = new List <IRequestCultureProvider>()
{
new CookieRequestCultureProvider()
{
CookieName = Configuration["Localization:CookieName"]
},
new AcceptLanguageHeaderRequestCultureProvider()
};
options.SupportedCultures = supportedCultures;
options.SupportedUICultures = supportedCultures;
});
// Set up and configure MVC
services.AddAntiforgery();
services.AddRouting(options => options.LowercaseUrls = true);
services.AddMvc(options => options.Filters.Add(typeof(GlobalExceptionFilter)))
.AddFluentValidation(options => options.RegisterValidatorsFromAssemblyContaining <Startup>());
// Set up custom dependencies for injection
services.AddScoped <IPasswordHasher, PasswordHasher>();
services.AddScoped <IPasswordHasher <IdentityUser <Guid> >, PasswordHasher>();
services.Configure <Common.Mvc.Options.PasswordHasherOptions>(options =>
Configuration.GetSection("Identity:Hasher").Bind(options)
);
services.AddScoped <ErrorResponseFactory>();
services.AddSingleton <IOpenIdConnectServerProvider, AuthorizationProvider>();
}
public void ClearWithNoPool()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString) {
ApplicationName = nameof(ClearWithNoPool)
};
using (var conn = new NpgsqlConnection(connString))
NpgsqlConnection.ClearPool(conn);
}
public void IntegratedSecurityWithoutUsername()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
IntegratedSecurity = true,
Username = null,
Password = null,
};
using (var conn = new NpgsqlConnection(csb))
{
try
{
conn.Open();
}
catch (Exception e)
{
if (TestUtil.IsOnBuildServer)
throw;
Console.WriteLine(e);
Assert.Ignore("Integrated security (GSS/SSPI) doesn't seem to be set up");
}
}
}
public NpgsqlCommunication(string dbName, string userId, string server, string port, string password)
{
ConnectionBuilder = CreateConnectionStringBuilder(server, port, dbName, userId, password);
Ds = new DataSet(dbName);
}
public NpgsqlCommunication(ConnectionStringSettings connectionString)
{
ConnectionBuilder = new NpgsqlConnectionStringBuilder(connectionString.ConnectionString);
Ds = new DataSet(connectionString.Name);
}
public NpgsqlCommunication(string connectionString, string dbName)
{
ConnectionBuilder = new NpgsqlConnectionStringBuilder(connectionString);
Ds = new DataSet(dbName);
}
public void IntegratedSecurityWithUsername()
{
var username = Environment.GetEnvironmentVariable("USERNAME") ??
Environment.GetEnvironmentVariable("USER");
if (username == null)
throw new Exception("Could find username");
var csb = new NpgsqlConnectionStringBuilder(ConnectionString) {
IntegratedSecurity = true,
Username = username,
Password = null,
};
using (var conn = new NpgsqlConnection(csb))
{
try
{
conn.Open();
}
catch (Exception e)
{
if (TestUtil.IsOnBuildServer)
throw;
Console.WriteLine(e);
Assert.Ignore("Integrated security (GSS/SSPI) doesn't seem to be set up");
}
}
}
public async Task GetConnectorFromExhaustedPoolAsync()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
MaxPoolSize = 1,
Timeout = 0
};
using (var conn1 = new NpgsqlConnection(connString))
{
await conn1.OpenAsync();
// Pool is exhausted
using (var conn2 = new NpgsqlConnection(connString))
{
new Timer(o => conn1.Close(), null, 1000, Timeout.Infinite);
await conn2.OpenAsync();
}
}
}