Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
What's the pricing for the new DBAAS? Question Question
How much is database size (space) for droplets? Question Question

Question

how can i edit pg_hba.conf in my managed database

Posted October 4, 2019 2.3k 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.

Add a comment
piouskwaw
By:
piouskwaw

Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
What's the pricing for the new DBAAS? Question Question
How much is database size (space) for droplets? Question Question

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
2 answers
bobbyiliev October 8, 2019

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

Reply Report
  • piouskwaw October 9, 2019

    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

    Reply Report
    • bobbyiliev October 9, 2019

      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

      Reply Report
      • piouskwaw October 10, 2019

        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

        Reply Report
        • bobbyiliev October 10, 2019

          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

          Reply Report
          • piouskwaw October 15, 2019

            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.

            Reply Report
LHC May 4, 2020

I had the same issue, try adding this to the end of your connection string.

Trust Server Certificate=true;

Reply Report

Related

Looking for something else?

Ask a new question Search for more help