vscode-postgres: Weird bug, can use run query in new query, but can't use when file saved

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

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.

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.

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.