This seems non obvious to me, purely because one has to create the cursor before ever executing any queries [1]. It therefore think it feels like a "sub-connection", or something the database is aware of at any rate.
It would be much clearer to me that the cursor is an iterator if it was actually returned as the result of executing a query.
> one has to create the cursor before ever executing any queries
You are right, that is a bit weird, although only a very slight annoyance IMO. As I said in reply to the sibling comment, I had forgotten this as I mostly use SQLite from Python and it doesn't actually require this: you can call .execute() on the connection object and it returns the cursor, which does seem a lot cleaner.
It would be much clearer to me that the cursor is an iterator if it was actually returned as the result of executing a query.
[1] https://www.psycopg.org/docs/usage.html