Thread: Error: for dblink view

Objective :

1. Create new user (Ex. piam_card ) in postgres database

2. Create view (Ex. attendance ) using dblink functionality. The view will be created in Database 2 & view will have the inner join with database 1 tables

3. The newly created user (piam_Card) must have readonly permission to created view (attendance)

Please provide the steps to achieve above functionality.

===============================================================================================================

View Defination:

CREATE OR REPLACE VIEW view_piam_cardswipes AS

SELECT DISTINCT events.alarmdiscription AS doorno,

replace(to_char(events.controllerdate::time without time zone::interval, 'HH24:MI:SS'::text), ':'::text, ''::text)

AS occurrencetime,

    to_char(events.controllerdate::date::timestamp with time zone, 'ddmmyyyy'::text) AS occurrencedate,

    resset.employeeid AS ecno,

    events.idalarm AS "inout",

    events.eventid AS recid,

    events.cardnumber AS event_cardnum,

   events.issuelevel,

    events.eventname,

    resset.status

   FROM events

JOIN dblink('port=5432 dbname=PIAMDB', 'SELECT   employeeid, cardnumber as map_cardnum, status

  FROM piamapp.mapping_cardholder_card'::text) resset(employeeid character varying, map_cardnum bigint, status integer)

ON events.cardnumber::bigint = resset.map_cardnum 

WHERE events.idalarm = 1 OR events.idalarm = 2;

ALTER TABLE view_piam_cardswipes

 OWNER TO postgres;

GRANT ALL ON TABLE view_piam_cardswipes TO postgres;

GRANT SELECT ON TABLE view_piam_cardswipes TO piam_card;

GRANT SELECT ON TABLE view_piam_cardswipes TO dblink_regression_test;

COMMENT ON VIEW view_piam_cardswipes

  IS 'This view return the card swipe entries ';

===============================================================================================================

I have referred below url , but still not able to succeed.

http://1command.com/man/?query=dblink_connect&sektion=3&manpath=FreeBSD+9.0-RELEASE+and+Ports

Errors:

I am getting different errors:

1. Below error after using above url setting.

ERROR:  password is required

DETAIL:  Non-superuser cannot connect if the server does not request a password.

HINT:  Target server's authentication method must be changed.

2.

ERROR: password is required

SQL state: 2F003

Detail: Non-superusers must provide a password in the connection string.