Re: [pgadmin-support] pgAdmin Firewall, SSH problem
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dave Lazar > Sent: 13 January 2005 02:24 > To: pgadmin-support@postgresql.org > Subject: [pgadmin-support] pgAdmin Firewall, SSH problem > > Hi, > > I have a couple of postgresql servers running and they work great. I > develop with pgAdmin using SSH and pageant, putty and tunneling. I > have one problem server > though and I am looking for help. > > One server is firewalled, and I configured the firewall (Smoothwall) > to port forward port 5432. My postgresql server private IP at > 192.168.100.18 works fine connecting via localhost. > > My pg_hba.conf contains the following setup: > > local all all md5 > hostall all 127.0.0.1 > 255.255.255.255 md5 > hostall all 192.168.100.18 > 255.255.255.255 md5 > hostall all 66.66.66.66 255.255.255.255 > md5 > hostall all 77.77.77.77 255.255.255.255 > md5 > > Where 66.66.66.66 is my development machine public IP and 77.77.77.77 > is my firewall public IP and hence the route to the postgresql > program. > > I setup my putty connection tunnel on my development machine to > forward 35432 to the > public IP of the postgresql server, 77.77.77.77:5432, which is my > firewall, which should port forward packets to the IP of postgresql. > > In pgadmin I connect to localhost on port 35432 and all is well, > when I do not have a firewall in the chain... > > In my case with the firewall, even with port forwarding setup for port > 5432, pgAdmin times out after 5 minutes or so complaining about SSL > handshake not working... > > I am not using SSL, although I do have that option with server.crt and > server.key files available. > > What might be causing pgAdmin these connection problems... I > am not sure > if the firewall is the culprit or if I screwed up. All my other boxes > w/o firewalls work fine this way Hi Dave, I'm not entirely clear on your setup - I assume it's something like: Dev Box -> Firewall -> PG Server You then tunnel a connection to the firewall, which is then forwarded to the PostgreSQL port on the server. I assume then, that the firewall is the endpoint of the tunnel? Is there any reason that you don't just forward a port on the firewall to the PG server, change the 77.77.77.77 line to a hostssl, and enable SSL in pgAdmin, thus eliminating the need to use pagent etc? Regards, Dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
[pgadmin-support] adding column with sequence
Hey there i am using pgadmin3 V 1.2.0 I have a question about your tool. id like to add a column to a table with a sequence, but adding a sequence in the column window is always grayed out, what am i doing wrong ? thanks for you help. Greets Ben -- *~*~* *~*~* We are connecting the world ! *~*~* *~*~* SolNet - Internet Solution Provider Bechburgstrasse 29, 4528 Zuchwil, Switzerland Phone: +41 32 686 82 82, Fax: +41 32 685 96 13 WWW: http://www.solnet.ch - E-Mail:[EMAIL PROTECTED] *~*~* *~*~* *~*~* *~*~* *~*~* *~*~* *~*~* *~*~* *~*~*~* pgpqlWu6jpv9j.pgp Description: PGP signature
Re: [pgadmin-support] pgadmin Bogus Varno: 3
Please stay on the list! [EMAIL PROTECTED] wrote: Andreas, Here are the results of my tests Test 1 --- This query run interactively gives ERROR: bogus varno: 3 SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0) WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oid ORDER BY relname Test 2 --- This query with the field pg_get_viewdef(c.oid, true) AS definition removed run interactively works and returns a list of 55 view OIDs which is correct in my case. SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0) WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oid ORDER BY relname Test 3 - This query which runs the function using one of the OID's as an example returns the definition without error select pg_get_viewdef(17391, true) AS definition Test 4 -- Altering the original query to return only one (the same) OID fails with Error Bogus Varno: 3, as in, SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0) WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oid ORDER BY relname offset 0 limit 1 Check this query again without OFFSET 0 LIMIT 1. Use WHERE c.OID = 17391 instead. I suspect that this will work, and you'll have to try which oid is the offending one (binary search using WHERE c.OID BETWEEN ). Test 5 -- Hardwiring pg_get_viewdef(c.oid, true) works SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(17391, true) AS definition FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0) WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oid ORDER BY relname offset 0 limit 1 Other Points -- Changing pg_get_viewdef(c.oid, true) to pg_get_viewdef(c.oid, false) has no effect. Other databases that I have transferred to Postgres 8 don't exhibit this problem. I have checked the operation of all the views in the Postgres 8 database that exhibits the problem and they all seem to work fine so I don't believe that the problem is a corrupted view or some such. The main obvious feature of the database exhibiting the problem is that it is much bigger and more complex than any other database I have ported from Postgres 7 to Postgres 8 but I guess this may be irelevent. I hope I have covered all useful points Thanks for the support Laurie :. CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgadmin-support] pgadmin Bogus Varno: 3
[EMAIL PROTECTED] wrote >> SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) >> AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS >> definition >> FROM pg_class c >> LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and >> des.objsubid=0) >> WHERE ((c.relhasrules AND (EXISTS ( >>SELECT r.rulename FROM pg_rewrite r >> WHERE ((r.ev_class = c.oid) >> AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind >> = 'v'::char)) >>AND relnamespace = 2200::oid >> ORDER BY relname >> offset 0 limit 1 > >Check this query again without OFFSET 0 LIMIT 1. >Use WHERE c.OID = 17391 instead. >I suspect that this will work, and you'll have to try which oid is the >offending one (binary search using WHERE c.OID BETWEEN ). This did identify one view that caused the problem. When this view is dropped pgadmin works fine. The view definition (as generated by Pgadmin 1.2.0 from Postgres 7.4.2) is CREATE OR REPLACE VIEW full_product_view AS SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_, ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text FROM rspper WHERE rspper.rspperid = slimprdmgrrspperid) AS _prd_slimprdmgrrspperid_d, prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_, actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS _slimprdnmgnnmeid_gennme_catnmeclssid_ FROM prd JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid; This SQL creates the view ok on both Postgres 7 and Postgres 8 in that it does not return an error and the resulting view seems to operate correctly on both versions of Postgres. Pgadmin 1.2.0 works fine with the Postgres 7 but fails with Postgres 8.0.0.rc4. The cause appears to be the call to pg_get_viewdef(c.oid, false) which works correctly on Postgres 7 but generates the error Bogus Varno: 3 in Postgres 8. AFAICT the function is objecting to the scalar select. Is this one for a pgsql-bugs or pgadmin-support? Thanks again for the help Laurie :. CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgadmin-support] pgadmin Bogus Varno: 3
[EMAIL PROTECTED] wrote: > AFAICT the function is objecting to the scalar select. The original query definition causing the Pgadmin problem was: CREATE OR REPLACE VIEW full_product_view AS SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_, ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text FROM rspper WHERE rspper.rspperid = slimprdmgrrspperid) AS _prd_slimprdmgrrspperid_d, prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_, actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS _slimprdnmgnnmeid_gennme_catnmeclssid_ FROM prd JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid; Rewriting the query to include the table qualification on the scalar select cures the problem with pg_get_viewdef(c.oid, true). CREATE OR REPLACE VIEW full_product_view AS SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_, ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text FROM rspper WHERE rspper.rspperid = prd.slimprdmgrrspperid) AS _prd_slimprdmgrrspperid_d, prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_, actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS _slimprdnmgnnmeid_gennme_catnmeclssid_ FROM prd JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid; I don't know if this behaviour is expected. Regards Laurie :. CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [pgadmin-support] pgadmin Bogus Varno: 3
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0) WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class = c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oid ORDER BY relname offset 0 limit 1 Check this query again without OFFSET 0 LIMIT 1. Use WHERE c.OID = 17391 instead. I suspect that this will work, and you'll have to try which oid is the offending one (binary search using WHERE c.OID BETWEEN ). This did identify one view that caused the problem. When this view is dropped pgadmin works fine. The view definition (as generated by Pgadmin 1.2.0 from Postgres 7.4.2) is CREATE OR REPLACE VIEW full_product_view AS SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_, ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text FROM rspper WHERE rspper.rspperid = slimprdmgrrspperid) AS _prd_slimprdmgrrspperid_d, prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_, actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS _slimprdnmgnnmeid_gennme_catnmeclssid_ FROM prd JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid; This SQL creates the view ok on both Postgres 7 and Postgres 8 in that it does not return an error and the resulting view seems to operate correctly on both versions of Postgres. Pgadmin 1.2.0 works fine with the Postgres 7 but fails with Postgres 8.0.0.rc4. The cause appears to be the call to pg_get_viewdef(c.oid, false) which works correctly on Postgres 7 but generates the error Bogus Varno: 3 in Postgres 8. AFAICT the function is objecting to the scalar select. Is this one for a pgsql-bugs or pgadmin-support? This is one for pgsql-bugs. Apparently you can pinpoint the problem, please post a summary of it immediately to pgsql-bugs so it gets reviewed; maybe it can be fixed for pgsql8.0-gold. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[pgadmin-support] Bugs in PgadminIII 1.3.0 beta (7.1.05)
Maybe I'm telling old stories... if so, it's just the case. I enountered couriosities while working with DOMAINS When defining an aditional CHECK-constraint, the generated SQL looks like that: > -- Domain: msisdn_type -- DROP DOMAIN msisdn_type; CREATE DOMAIN msisdn_type AS varchar(32) CHECK CHECK ((VALUE)::text ~ '^\\d+'::text); ALTER DOMAIN msisdn_type OWNER TO robert; < notice the twice CHECK. -- USing the Grant-Assistand on Functions privileges are mixed up. Selecting one or more funcs and adding a privilege "ALL" results in a "GRANT EXECUTE" while EXECUTE results in a "REVOKE ALL" needless to say that the middle checkbox is empty ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[pgadmin-support] pgadmin3 cores on FreeBSD 5.3
Hi folks, I'm just wondering if I'm just special and I'm the only one that is having pgadmin3 core dump when it starts on. I'm running pgadmin3-1.2.0 under FreeBSD 5.3-stable. Any ideas? suggestions? thanks -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[pgadmin-support] pgadmin tunnel problem
Hi, I have a couple of postgresql servers running and they work great with putty and ssh tunneling combined with pgadmin. I have one problem server though and I am looking for help. My public IP is firewalled, and I port forward port 5432. My server IP at 192.168.100.18 works fine with code residing on the server itself, connecting via localhost. My pg_hba.conf contains the following setup: local all all md5 hostall all 127.0.0.1 255.255.255.255 md5 hostall all 192.168.100.18 255.255.255.255 md5 hostall all 66.66.66.66 255.255.255.255 md5 hostall all 77.77.77.77 255.255.255.255 md5 Where 66.66.66.66 is my local public IP and 77.77.77.77 is my public IP for the firewall and hence the server. Usually I setup my putty connection to tunnel a port like 35432 to the public IP of the postgresql server, say 77.77.77.77:5432 And in pgadmin I connect to localhost on port 35432 and all is well, when I do not have a firewall in the chain... In my case with the firewall, even with port forwarding setup for port 5432, pgAdmin times out after 5 minutes or so complaining about SSL handshake not working... I have installed a server.crt and server.key file in the pgsql/data directory and the server seems to work fine with ssl_enabled in the postgresql.conf file, and tcp enabled. What might be causing pgAdmin these connection problems... I am not sure if the firewall is the culprit or if I screwed up something with SSL... Any insight most appreciated... Dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[pgadmin-support] Error Message: ERROR: pg_aclcheck: class "tmp_041206_131204" not found
-- System Information -- Platform: Version: 5.2 Build: 3790 -- Application Information -- Name: pgAdmin II Version: 1.6.0 Descripton: Name: pgSchema Version: 1.6.0 Descripton: PostgreSQL Schema Objects v1.6.0 -- Database Information -- Version: 7.2.1 Descripton: PostgreSQL 7.2.1 on i686-pc-winnt-msdev, compiled by msdev 6.0 -- Driver Information -- Name: PostgreSQL Version: 7.3.100 Descripton: PostgreSQL 7.2.1 on i686-pc-winnt-msdev, compiled by msdev 6.0 -- Error Information -- Description: ERROR: pg_aclcheck: class "tmp_041206_131204" not found Number: -2147467259 Routine: pgAdmin II:frmMain.mnuPopupViewData_Click Insert your comment: please reply L
Re: [pgadmin-support] pgadmin3 cores on FreeBSD 5.3
Dear Dan. Thank you for the report. - Original Message - From: "Dan Langille" <[EMAIL PROTECTED]> > Hi folks, > > I'm just wondering if I'm just special and I'm the only one that is > having pgadmin3 core dump when it starts on. > > I'm running pgadmin3-1.2.0 under FreeBSD 5.3-stable. > > Any ideas? suggestions? At present, various problems are being watched with FreeBSD5.3:-( wxWidgets of current-cvs(version 2.5.3) won't be built. It has a pthread problem further though it can be built narrowly by this. -- --- src/regex/regex.h.orig Thu Dec 2 12:18:24 2004 +++ src/regex/regex.h Fri Jan 14 09:46:59 2005 @@ -292,7 +292,10 @@ #defineREG_ATOI101 /* convert error-code name to number */ #defineREG_ITOA102 /* convert error-code number to name */ +/* FreeBSD5.3 need patches. */ +#undef re_comp +#undef re_exec +#define re_comp wxre_comp +#define re_exec wxre_exec /* * the prototypes, as possibly munched by regfwd -- Therefore I used wxWidgets-pgAdmin3-20040907-2(Version 2.5.2), and tried it. It couldn't be used under the condition which wasn't good. I recognize that must put these problems in order. However, I have contact recently with Mr. Max Khon, He is ports the maintainer of FreeBSD. He is doing a countermeasure vigorously including these problems. I spend the time when it is limited.:-( While it is near, it hopes that it is solved. Of course, I work, too. regards, Hiroshi Saito ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
