PostgreSQL: DBLink weird permission/connection error

dblinkpostgresql

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.

Best Answer

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

Related Question