Environment: PostgreSQL 9.2 on Windows
-----------------------------------------
We have 2 databases: XXX and XXX_stats. Tables in XXX database are created by XXX user. Tables in the XXX_stats database are created by XXX_stats user.
We want to create a view in the XXX_stats database as XXX_stats user pointing to the XXX database table by using dblink.


SOLUTION 1:
------------

CREATE OR REPLACE VIEW deployment
AS SELECT a.pk1
FROM dblink('dbname=XXX host=localhost user=XXX password=postgres'::text, 'SELECT pk1 FROM DEPLOYMENT'::text) as a(pk1 bigint);

And the pg_hba has to have either one of these lines:

host all all 0.0.0.0/0 md5
host all all 127.0.0.1/32 md5

PROBLEM 1:
---------
Problem is that the password is visible in the pgadmin when you click on the view “deployment”.

If we leave “trust” instead md5 in the pg_hba we will get this error:

ERROR: password is required
SQL state: 2F003
Detail: Non-superuser cannot connect if the server does not request a password.
Hint: Target server's authentication method must be changed.



SOLUTION 2:
------------

Update pg_hba.conf, trusting local connections:

# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust


As superuser on XXX database run:
GRANT USAGE ON SCHEMA public TO "XXX_stats";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "XXX_stats";

As superuser on XXX_stats
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO "XXX_stats";

As XXX_stats user on XXX_stats database:

-- Create a persistent, named connection. The user needs to be specified to avoid the 'MACHINENAME$' role error.
SELECT dblink_connect_u('XXX_dblink', 'dbname=XXX user=XXX_stats' );

-- SELECT from the link:
CREATE OR REPLACE VIEW deployment
AS
SELECT a.pk1 FROM dblink('XXX_dblink', 'SELECT pk1 FROM DEPLOYMENT'::text) AS a(pk1 BIGINT)


PROBLEM 2:
-------------
This dblink_connect_u is a connection for a session and we should be able to connect to this table in the XXX database from all sessions connecting as XXX_stats users to the XXX_stats database from our application.