PostgreSQL: DBLink weird permission/connection error

Question

The goal is to (1) create a connection, (2) return a view of another database, and (3) close the same connection all to be stored in a view (no stored procedure or embedded password). I came up with the following code, which uses a trust account and seems to work, until filtering on a boolean field. Searching for false (field=false) works, but searching for true (field=true) or IS NOT NULL prompts for the password.

It's really odd that the data is being returned w/o needing the password, but when filtering, that is when the password is required.

---

PostgreSQL: v8.4:

• There are two databases: db_remote and db_local
• There are two accounts:
  • trusted_user (set up as trust in pg_hba.conf to both databases)
  • md5_user (set up as md5 in pg_hba.conf to both databases)
• db_remote has a view (v_sessions), which contains two fields: a text field and a boolean field, which is created with a CASE statement.

From within db_local (as md5_user):

-- Creates okay:
CREATE VIEW v_sessions AS 
SELECT * FROM (
 select '1' query_type,'' as username, false as is_logged_in 
 from dblink_connect_u('connection', 'host=development dbname=db_remote user=trusted_user') 
 union 
 select '2' query_type, username, is_logged_in 
 from dblink('connection', 'select username, is_logged_in from v_sessions') as v_session(username text, is_logged_in boolean) 
 union 
 select '3' query_type,'',false 
 from dblink_disconnect('connection')
) v_sessions 
WHERE query_type=2;
-- Calling the view with filter:
SELECT * FROM v_sessions WHERE is_logged_in;

---

What works:

• removing the AND is_logged_in
• filtering on a text field AND username = 'some value' !!
• filtering on the boolean field for a false (AND is_logged_in = false) !!

What doesn't work:

• filtering on the boolean field for a true value: AND is_logged_in IS NOT NULL, AND is_logged_in, or AND is_logged_in = true, gives the following error message:

  ERROR: password is required
  DETAIL: Non-superusers must provide a password in the connection string.

---

Filtering inside the second query works, but it doesn't help when wanting to store this in a view.

---

I suppose I'm looking for suggestions, but really trying to understand what is going on eg. why searching for false works and true does not - I first thought the connection was preemptively closing, but it's not.

• Daniel Vérité: what happens when you don't close the connection?
• vol7ron: @DanielVérité This is a lot, so thanks for reading -- the error would persist. I tried that, as well as wrapping the whole thing as another subquery, in case the filtering was going back-and-forth between the two databases. Again, the oddest part is that it does work when using false and regular text fields work -- though, those text fields aren't using a CASE statement to generate, so perhaps that is where the issue is (a CASE is used to make the boolean fields in the remote's view)
• Daniel Vérité: If you can make a reproducible test case from this and you use the latest 8.4.x, you may consider submitting it as a bug report.
• vol7ron: @DanielVérité yes I think I may need to, I'll have to check and see what happens with v9, but I assume it's the same -- I thought I was mostly explicit in how to reproduce the problem, but I can also put in the create/insert statements

Answer 1

Here is what I think is happening. I don't think it is a bug.

1. You have a view. That view is somewhat complex but it is a dynamically rewritten query.

2. The planner is pretty smart. It will ignore UNION clauses where the search criteria will not produce an output result.

3. You are not executing what you think you are. (EXPLAIN ANALYSE should show you this)

My recommendations:

1. Wrap the whole result set in a plpgsql function. Wrap that function in the view. OR

2. Use a WITH clause. I am less sure about this one. I think the with clause might result in running the whole thing consistently but not 100% sure, and it is not guaranteed in future versions. Therefore wrapping in a function is probably better.

I think what the planner is seeing is:

"Oh we don't need to execute the first and third portions of the view because they are not in the output! Let's skip and only run the second portion!" This is a feature, not a bug (and it is what allows table partitioning to be useful).

• vol7ron: I'm not sure how true any of that is, but I should run EXPLAIN ANALYZE -- I think I did, but it's been a while since I gave up on this, so it's worth trying again. Second, I don't think that explains why it works for filtering on positive, but not negative; in both cases records are returned. Third, if there is optimizing going on and ignorance of subqueries, then it would also dismiss the first one and I'd be getting another error.
• vol7ron: Wrapping in a function is what I was hoping to avoid, especially as new fields may need to be added/removed --- keeping stored procedures maintained is a more time consuming process than it should be. I'd like to see what you suggest for the WITH clause, as that's more of an aliasing mechanism and shouldn't really by anything other than readability. The reason for UNION was to ensure order of operation in the connection/disconnection
• Chris Travers: WITH in PostgreSQL creates a more stable result set than an inline view does, which is why I think it might work in this case. Again it's a sort of low-level implementation detail and may not at all be worth counting on.
• Chris Travers: Also if your issue is maintaining additional columns, it is worth noting that these are not maintained automatically with views, so you'd have to redefine your view anyway. If you want to you could RETURN SETOF RECORD and then wrap that in a view defining the output columns. It is marginally more work to maintain but not a lot.
• vol7ron: If you're creating a view that is calling a function, then you have to maintain in two places, not one. Not to mention, it's more habitual for me (and probably others) to alter tables and views then to think on altering and keeping functions maintained. But I might still give this a try. One thing I haven't looked into is setting up an FDW. I've never really had the need, so perhaps this is where it's supposed to be used.