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.
- 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
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;
- removing the
- filtering on a text field
AND username = 'some value'!!
- filtering on the boolean field for a
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.