|
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. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com |
