PostgreSQL: DBLink weird permission/connection error

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:

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

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

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

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

My recommendations:

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

-
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).

StackExchange Database Administrators Q#45303, answer score: 2