how can i edit pg_hba.conf in my managed database

October 4, 2019 275 views
Databases

i want to log into my managed database cluster to edit pg_hba.conf using ssh
but any time i try i get access denied.

1 Answer

Hello,

With the Managed Database solutions you don’t have SSH access to the servers and you can’t edit the configuration files, as that way you are risking of breaking the managed cluster. This is all done by the DigitalOcean engineers.

What are the exact changes that you need to make?

Regards,
Bobby

  • OK, I am have created an asp.net core 2.1 app so after creating the managed database I want to connect to it but when i use the connection string that is provided i get an error. after some research i was told to edit pg_hba.conf file and add my ip address to the content of the file.
    So Please,

    1. How can I connect to my database in the appsettings file of my asp.net core 2.1 project.
    2. How do i migrate to the database.

    Thank you

    • Hello,

      Can you share your current connection string and the error that you are getting? Please make sure to remove any sensitive information!

      Regarding the migration, first you need to export your database from your current SQL server and then import it to your managed DB cluster, here’s how to do that:

      https://www.digitalocean.com/docs/databases/postgresql/how-to/import-databases/

      Regards,
      Bobby

      • Connection String:

        User ID=id;Password=Password;Host=database-db-do-user-6-0.db.ondigitalocean.com;Port=22;Database=database-db;Integrated Security=True;Pooling=True;

        Error:

        no pg_hba.conf entry for host “my-ip”, user “my-username”, database “my-database”, SSL off

        • Hello @piouskwaw

          You need to make sure that your Port is set to your managed database port. And also make sure to add your droplet’s IP to the trusted source for your managed database as well.

          Let me know how it goes.
          Regards,
          Bobby

          • Hello Sir,

            After publishing the app if i enter the server Ip in other to open the app in a browser I get this error:

            Service Unavailable
            The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later.

            Apache/version (Ubuntu) Server at Server_IP Port 80

            if i check the application logs i get this error too:

            Application startup exception: System.AggregateException: One or more errors occurred. (Connection refused) —> System.Net.Sockets.SocketException: Connection refused
            at System.Net.Sockets.Socket.BeginConnectEx(EndPoint remoteEP, Boolean flowContext, AsyncCallback callback, Object state)
            at System.Net.Sockets.Socket.UnsafeBeginConnect(EndPoint remoteEP, AsyncCallback callback, Object state, Boolean flowContext)
            at System.Net.Sockets.Socket.BeginConnect(EndPoint remoteEP, AsyncCallback callback, Object state)
            at System.Threading.Tasks.TaskFactory1.FromAsyncImpl[TArg1](Func4 beginMethod, Func2 endFunction, Action1 endAction, TArg1 arg1, Object state, TaskCreationOptions creationOptions)
            at Npgsql.NpgsqlConnector.ConnectAsync(NpgsqlTimeout timeout, CancellationToken cancellationToken)
            at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
            at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
            at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
            at Npgsql.NpgsqlConnection.<>cDisplayClass32_0.<<Open>gOpenLong|0>d.MoveNext()
            — End of stack trace from previous location where exception was thrown —
            at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
            at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
            at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func
            4 operation, Func4 verifySucceeded, CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable
            1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
            at System.Linq.AsyncEnumerable.FirstOrDefault[TSource](IAsyncEnumerable1 source, CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.TaskResultAsyncEnumerable
            1.Enumerator.MoveNext(CancellationToken cancellationToken)
            at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator2.MoveNextCore(CancellationToken cancellationToken)
            at System.Linq.AsyncEnumerable.AsyncIterator
            1.MoveNext(CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteSingletonAsyncQuery[TResult](QueryContext queryContext, Func
            2 compiledQuery, IDiagnosticsLogger1 logger, Type contextType)
            at Microsoft.AspNetCore.Identity.UserManager
            1.FindByNameAsync(String userName)
            at App
            name.Data.ApplicationDbContext.CreateAdminAccount(IServiceProvider serviceProvider, IConfiguration configuration) in Data\ApplicationDbContext.cs:line 94
            — End of inner exception stack trace —
            at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
            at Presscode.Startup.Configure(IApplicationBuilder app)
            — End of stack trace from previous location where exception was thrown —
            at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)
            at Microsoft.AspNetCore.Hosting.Internal.AutoRequestServicesStartupFilter.<>cDisplayClass0_0.<Configure>b0(IApplicationBuilder builder)
            at Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication()
            —> (Inner Exception #0) System.Net.Sockets.SocketException (111): Connection refused
            at System.Net.Sockets.Socket.BeginConnectEx(EndPoint remoteEP, Boolean flowContext, AsyncCallback callback, Object state)
            at System.Net.Sockets.Socket.UnsafeBeginConnect(EndPoint remoteEP, AsyncCallback callback, Object state, Boolean flowContext)
            at System.Net.Sockets.Socket.BeginConnect(EndPoint remoteEP, AsyncCallback callback, Object state)
            at System.Threading.Tasks.TaskFactory1.FromAsyncImpl[TArg1](Func4 beginMethod, Func2 endFunction, Action1 endAction, TArg1 arg1, Object state, TaskCreationOptions creationOptions)
            at Npgsql.NpgsqlConnector.ConnectAsync(NpgsqlTimeout timeout, CancellationToken cancellationToken)
            at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
            at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
            at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
            at Npgsql.NpgsqlConnection.<>cDisplayClass32_0.<<Open>gOpenLong|0>d.MoveNext()
            — End of stack trace from previous location where exception was thrown —
            at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
            at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
            at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func
            4 operation, Func4 verifySucceeded, CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable
            1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
            at System.Linq.AsyncEnumerable.FirstOrDefault[TSource](IAsyncEnumerable1 source, CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.TaskResultAsyncEnumerable
            1.Enumerator.MoveNext(CancellationToken cancellationToken)
            at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator2.MoveNextCore(CancellationToken cancellationToken)
            at System.Linq.AsyncEnumerable.AsyncIterator
            1.MoveNext(CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
            at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteSingletonAsyncQuery[TResult](QueryContext queryContext, Func
            2 compiledQuery, IDiagnosticsLogger1 logger, Type contextType)
            at Microsoft.AspNetCore.Identity.UserManager
            1.FindByNameAsync(String userName)
            at App
            Name.Data.ApplicationDbContext.CreateAdminAccount(IServiceProvider serviceProvider, IConfiguration configuration) in Data\ApplicationDbContext.cs:line 94<—

            From this Line:

            Microsoft.AspNetCore.Identity.UserManager`1.FindByNameAsync(String userName)
            at App_name.Data.ApplicationDbContext.CreateAdminAccount(IServiceProvider serviceProvider, IConfiguration configuration) in Data\ApplicationDbContext.cs:line

            When i lunch the app locally i check if no user exist then CreateAdminAccount creates an admin user.

            Am sure the app cannot make any migrations before the app lunches in other to create the admin user.

            please i want to know how to apply the migration to the database.

            Thank you.

Have another answer? Share your knowledge.