PGConnection.getNotifications() over an ssl=true connection does not seem to work...

[mat-chartier]

Hi,

It seems that retrieving notifications using PGConnection.getNotifications() over a connection having ssl=true does not work.
Does this ring a bell to anyone ?

This works:

Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432?ssl=false";
conn = Connection.getConnection(url, "user", "pwd");
Statement stmt = conn.createStatement();
stmt.execute("LISTEN some_channel");
pgConn = conn.unwrap(org.postgresql.PGConnection.class);
PGNotification[] notifications = pgConn.getNotifications();

This does not (getNotifications always returns null):

Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432?ssl=true";
conn = Connection.getConnection(url, "user", "pwd");
Statement stmt = conn.createStatement();
stmt.execute("LISTEN some_channel");
pgConn = conn.unwrap(org.postgresql.PGConnection.class);
PGNotification[] notifications = pgConn.getNotifications();

Note that for testing I send NOTIFY statements directly from a psql client.

Thanks.

[davecramer]

hi @mat-chartier I'm going to presume this works fine with non-ssl connections ? If so then yes, this does make some sense. We can't read ahead with ssl connections. I will spend some time on this today though.

Thanks for the report.

not sure what you are using notifications for but they are usually not my first choice. I'd prefer to use logical replication to get notifications of changes.

Dave

[mat-chartier]

Hi @davecramer

Yes it does work fine with non-ssl connections.

I'm using LISTEN / NOTIFY (with name and payload) as a message queue shared across several instances of application servers, specifically tomcat servers. We would use a "real" message broadcasting system, but I saw this mechanism offered by postgres as a shortcut for a proof of concept in a prototype dealing with asynchronous messaging.

Matthieu.

[davecramer]

Thanks for the information. As for your use case you should be aware that NOTIFY will not survive a restart; whereas logical replication will.

[mat-chartier]

In your first reply you stated that notifications would not go through ssl connections. Is this a definitive answer ? No problem with that, just so we know if we need to look at alternatives.

[davecramer]

In your first reply you stated that notifications would not go through ssl connections. Is this a definitive answer ? No problem with that, just so we know if we need to look at alternatives.

It's not that they won't go through. Currently we have an issue reading them. I would suggest you find another solution as I don't see a short term solution.

[davecramer]

FYI, it's no longer necessary to use Class.forName

[davecramer]

@mat-chartier So I checked and this code below works fine. I was wrong

public class TestNotifications {
    private static final String url = "jdbc:postgresql://localhost/postgres?ssl=verify-ca";

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(url, "davec", "password")) {
            Statement stmt = conn.createStatement();
            try (ResultSet rs = stmt.executeQuery("select ssl_is_used()")){
                if (rs.next()){
                    System.out.println("SSL is: " + rs.getBoolean(1));
                }
            }
            stmt.execute("LISTEN some_channel");
            PGConnection pgConn = conn.unwrap(org.postgresql.PGConnection.class);
            PGNotification[] notifications = pgConn.getNotifications();
            System.out.println (notifications.toString());
        }catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

You have to put the getNotifications in a thread though. I put a breakpoint in and then sent the notification and read it after.

Dave

[mat-chartier]

Are you sure about ssl=verify-ca ? According to the documentation https://jdbc.postgresql.org/documentation/ssl/ it should be ssl=true&sslmode=verify-ca shouldn't it ?

I will try again though, without the Class.forName as you mentioned and as per your example.

I should also add that we are by default using ssl=true to establish connections and for any other statements such as selects, updates, etc... everything works fine.

[davecramer]

you can use ssl or verify-ca that part is not relevant to the problem. I installed sslinfo to make sure it was an ssl connection.

The relevant part is that you need to have a thread running to read the notification.

[mat-chartier]

Yes, this is pretty clear from the doc too (https://jdbc.postgresql.org/documentation/server-prepare/#example92receiving-notifications) and I did that but to no avail on my setup... only ssl=false seems to work in my case.

[davecramer]

Ok, I haven't actually tried a thread, so I'll try that in a bit

[mat-chartier]

My deepest apologies, I was using an outdated driver. With the latest one, I have successfully been able to read notifications over an ssl connection.
Funny thing though ?ssl=false does not open a non-ssl connection, to achieve this I have to set sslmode to disable.

Again, I'm sorry for the confusion.

[davecramer]

good to know, thanks for letting me know!

you can just not use ssl at all if you don't want an ssl connection