Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
what happens when you don't close the connection? –  Daniel Vérité Jun 27 '13 at 13:03
    
@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) –  vol7ron Jun 27 '13 at 15:24
    
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. –  Daniel Vérité Jun 27 '13 at 15:31
    
@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 –  vol7ron Jun 27 '13 at 18:59
add comment

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

share|improve this answer
    
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 Sep 15 '13 at 4:17
    
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 –  vol7ron Sep 15 '13 at 4:20
    
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 Sep 15 '13 at 5:01
    
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. –  Chris Travers Sep 15 '13 at 9:29
    
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. –  vol7ron Sep 15 '13 at 15:06
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.