Weird bug, can use run query in new query, but can't use when file saved

[e2ne0]

v1.4.0 vscode 1.74.0 ~ 1.74.1 windows 11 22621.900

Hello, Weird problem here. When i used the saved file to run a query it return error say ERROR: no pg_hba.conf entry for host "host", user "user", database "database", SSL off

but when i use new query and select it to run it ok And if i save the new query file it will get error too. here's my connention setting

{
  "label": "my host",
  "host": "my host",
  "user": "user",
  "port": 5432,
  "ssl": true,
  "database": "some database",
  "password": "***"
}

so pain :(

[Borvik]

While I agree it's weird... that error is coming direct from a Postgres server, and not generated by this plugin.

For each of those scenarios, what does the vscode status bar show as far as which server/database you are connected to while running each query?

[e2ne0]

@Borvik Thanks for reply so soon. I was save only one connection for this plugin. So it connected with same server and database while I run those queries. And I also tried fully reinstall this plugin and vscode, delete and add the connection again but it still doesn't work But there's no problem when I used same connection with my laptop and other Win 11 computer. I think maybe reinstall the windows can fix it 😅.

[jacksonlauder]

I am having this issue as well

[Borvik]

So... I just tested this and was not able to replicate.

Testing process: Created and saved a .pgsql file with a simple select query in it, tested it (while saved) and got results. Reloaded VSCode, file was still open, but I needed to select my server/database - once I did so, the query worked fine.

However... looking at the earlier screenshot - it looks like the file was saved with a .sql extension - that in and of itself isn't really a problem - it just means that the F5 method of running the query doesn't really work.

My guess, there is another plugin that is attempting to run the query against a different postgres server.

[jacksonlauder]

I wish it were that, but it isn't. After I took the steps that you took, I still get a no pg_hba.conf entry

[e2ne0]

Now, I am using Microsoft/postgresql plugin to dealing this problem and use this plugin for looking columns and test unsave query.

The only difference is run query shortcut.

Maybe there were some environment issues I missed.

Thanks for your help @Borvik

[Borvik]

while the error is definitely coming from postgres, what might help is screenshots of the status bar - specifically the postgres server and the selected language of the saved file:

also the following settings: and (though unlikely)

[jacksonlauder]

I have omitted the first bit as it is sensitive information.

[Borvik]

I get the need to obscure or hide sensitive information - but unfortunately this is actually a postgres issue (postgres is reporting the error) - comparing that information to rules setup in the pg_hba.conf file is what would help.

[e2ne0]

while the error is definitely coming from postgres, what might help is screenshots of the status bar - specifically the postgres server and the selected language of the saved file:

also the following settings: and (though unlikely)

Here's the screenshot of run query session that i used from a saved file. And here's the other one.

[Borvik]

So...

When a file is saved for the first time, it's internal vscode URI changes - but it does so by first "closing" the original unsaved file, and then "opening" the newly saved file - while you don't see that in the UI, that's how the event handlers fire.

This has the side effect of losing the metadata I associate with an open editor (connected server/database). Despite some extensive tests and research, that is something that is currently not solveable.

However... based on those screenshots, you have seen that and corrected the connected server/database.

For the one that failed... how did you run the query? Did you select the query, right click, and choose "Run Query"? or did you just use F5?

Also - of special note for the screenshot on the one that failed is this:

Which the other one doesn't have.

It almost looks like there are 2 languages selected (which I'm not certain vscode supports) - and I have no clue what that "Getting definition..." is - so I'm guessing those extra things are provided by other extensions.

Are you able to list the extensions that might be contributing to that? I'm both curious, and it could be relevant (depending on how you ran the query that failed).

[e2ne0]

here's the list of extensions that i used with this workspace I used right click to run the query

This is the effect of microsoft/postgresql extention I also tried to disable it and ran the query:

and select postgres manualy

But here's a good news I just found a solution that maybe can help you to figure out what happen with this If i selected the connection and not selected database it works but when i selected database it fail video

[Borvik]

So, I can't say anything about jacksonlauder's issue, but e2ne0 - I think your issue is an interesting one, that might have a simple answer without a code issue (though I think there is one, just not the one you might think).

What I find interesting about that is that when you selected the database, it fails - but if you only select a connection it works. I actually would have guessed the exact opposite.

The bug here, I think is that the extension will even attempt it, without having a database selected.

Now, on the the analysis of the issue.

I think the 2 key pieces of information - the full error message about the pg_hba.conf - and what you happened to name your server connection.

The fact that it works without a database selected, might actually be a fluke of the database side as well that might stop working at any moment.

So - first key piece of information - you are connected to "azure postgres", and therefore do not control the pg_hba.conf file.

Looking up details about Azure postgres and pg_hba issues caused me to reread the error message you are receiving - specifically the last bit... ", SSL off"

I believe most managed postgres services either require - or at least default to requiring SSL to be on. Amazon RedShift and Heroku both require it - this is my first time encountering Azure's.

For Azure you might be able to turn it off: https://stackoverflow.com/questions/62301317/azure-database-for-postgresql-server-no-pg-hba-conf-entry-for-host If not, #54 might be of assistance for enabling SSL for the connection in the extension.

Azure itself might have a bug, where it's requiring SSL when the database is specified, but not requiring it when it's left out.

[wonb168]

我也是，未保存的新窗口中可以查询，但一旦保存文件（pgsql格式）就无法再查询了

[mmdolbow]

Just wanted to say "thanks" for this thread. I encountered similar issues, and I do think some of my troubles is in having multiple extensions confusing things. (I also work with MS SQL Server db's, so I need an extension and hotkeys for that.) What worked for me was not only saving my queries as .pgsql, but also setting a default PG connection to run a query against. Then a separate hotkey for running PG queries vs others helps. :-)

[mattwg]

I ran into this problem - aws rds postgres. Getting the ERROR: no pg_hba.conf entry for host. e2neo’s workaround worked for me (do not choose the database). But then when I set the default connection and default database in the extension setting everything works fine. Go here: And set these: And everything works perfectly for that database connection and database. But if you need to change them you have to go to settings which is not so great.

[mmdolbow]

I tried the suggestion by @mattwg and that worked for me, too. But that's going to be a pain in the long run, making it hard to switch between connections (like between non-prod and prod) when testing or running queries. Which, to me, says that this bug should be looked into for resolution. At the very least, the error message of "ERROR: no pg_hba.conf entry for host "", user "", database "", no encryption" can't be correct - can it? If I can run the query successfully one way but not another, it can't be due to a postgres configuration on the host, right?

[Borvik]

ERROR: no pg_hba.conf entry is being given by the database server - I do not believe this error can be coded around within this extension.

The pg_hba.conf file can be configured to allow/disallow specific users from specific database vs all databases. Chances are some of these reported configurations are configured to NOT allow generic connections to connect, but when you configure this extension to have a default - then it uses that default and as long as the pg_hba allows the user/database combination - then it succeeds.

So - yes, it can be due to a postgres configuration.

[mmdolbow]

OK, certainly this error can arise due to the configuration on the postgres server. What I'm arguing is that I have a query that can be run with the specific user IF I designate the default connection and database for the extension, and yet that SAME query produces the error if I don't specify the defaults. Despite picking the same connection and database!

I made a GIF to demonstrate - first testing the query against the database picker, which results in the error. Then I update the settings, reopen the query, and it works fine. So this suggests to me that it's not solely a setting in the database. I'll happily eat crow if I'm wrong, I'm definitely new to this extension and it's been a long time since I used PG. Totally understand if this can't be "fixed", just want to get on the same page about how we define the situation.

[Borvik]

I think I can see what is going on.

I think Azure is still the key to what is likely going on.

When you explicitly specify connection/database for the file, and it fails - the database connection is configured to directly connect as that database, and runs into the azure side pg_hba.conf which I can do nothing about and is definitely a server setting.

When the default connection is specified and works (which to be honest I'm not sure how... from an Azure security standpoint) - the following code is run to choose the database connection:

    let connection: IConnection = null;
    for (const k in connections) {
      if (connections.hasOwnProperty(k)) {
        let connFound = (k === defaultConnection);
        if (!connFound) {
          let connName = connections[k].label || connections[k].host;
          connFound = (connName === defaultConnection);
        }

        if (connFound) {
          connection = Object.assign({}, connections[k]);
          if (connection.hasPassword || !connection.hasOwnProperty('hasPassword')) {
            connection.password = await Global.context.secrets.get(k);
          }
          break;
        }
      }
    }

    let defaultDatabase = Global.Configuration.get<string>("defaultDatabase");
    if (defaultDatabase) {
      const conn = await Database.createConnection(connection, 'postgres');

      let databases: string[] = [];
      try {
        const res = await conn.query('SELECT datname FROM pg_database WHERE datistemplate = false;');
        databases = res.rows.map<string>(database => database.datname);
      } finally {
        await conn.end();
      }

      if (databases.indexOf(defaultDatabase)) {
        connection = Database.getConnectionWithDB(connection, defaultDatabase);
      }
    }
    return connection;

What we see here, is that first connection is from the "Default Connection" setting. Next it attempts to ensure that the request default database is on the server, and if found updates the connection details.

If that validation is successful - then you would likely have been getting the same error as if you had used the database picker.

However... I believe that this validation is actually failing, why... I'm not sure - but in the event of a failure, the connection would still have been set - but the database not chosen.

Ultimately in the case of that validation failure the connection will be left using purely the settings found via the actual connection configuration found here:

Whatever configuration you happen to have there and the database it's actually connecting to and that's the one apparently allowed via the pg_hba.conf and why your query is working.

Just why I'm confused about the Azure security is that the databases are enumerated over a connection to the postgres database with a query to the pg_database table, and for a shared service like Azure or RedShift - I would have expected that to be locked down in some way.

If I were to fix the issue I do see, I would fix it to ultimately be more consistent with the extension and ui - but that would also likely stop your default connection from working as well - since the failure would be better trapped and not provide the default connection.

[mmdolbow]

Excellent, thanks @Borvik for the thorough explanation. I'm satisfied with this and respect whatever you decision you make on that front. I'm sure in the long run I'll either make sure the server side is set up accordingly or use some combination of the defaults. Right now I'm just doing R&D in a sandbox so it's good to know what is happening behind the scenes as I prepare to move into higher environments. Much appreciated!

[barhot]

The issue is that, when connection details are saved on SQL file level, the SSL option is ignored and it is OFF if a database was selected.

The solution: When you save a file and then choose only the DB server, don't choose the database after that, just press ESC.