[BUGS] BUG #4932: Upgrade 8.2.13 -> 8.4.0 - Kerberos option missing
The following bug has been logged online: Bug reference: 4932 Logged by: Peter Much Email address: p...@citylink.dinoex.sub.org PostgreSQL version: 8.4.0 Operating system: FreeBSD 7.2 Description:Upgrade 8.2.13 -> 8.4.0 - Kerberos option missing Details: In chapter 19.3.5 of the manual an option "krb_server_hostname" is mentioned. This option was present in 8.2 but is no longer present in 8.4.0 So at least we have a documentation bug here. I was using this option. According to my notices, the problem is that (since about 7.4) psql (or the client lib) uses the network-interface-name to build the K5 principal name, while postgres (the server) uses the local hostname. So this works fine as long as hostname == interface-name; and otherwise one should set the hostname to the interface-name in postgresql.conf with the beforementioned option. I found another solution in absence of that option: I can rename the principal in the keytab file with K5 tools and so change this name to the hostname. Without trying to dig deeper, I am thinking what would happen if the server listens on more than one interface. Wouldnt we need more than one principal then? And how would we configure these on the server side if only one name is used? But the essential point seems to me the following: section 19.3.5 of the manual reads "hostname is the fully qualified host name of the server machine." But _there_is_no_such_thing_ as a "fully qualified hostname"! There are only _fully_qualified_interface-names_, and any host can have *many* of these. The hostname itself is nothing else than an arbitrary label for the machine, and it should never be used by networking software. rgds, PMc -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4932: Upgrade 8.2.13 -> 8.4.0 - Kerberos option missing
On Wed, Jul 22, 2009 at 11:42, Peter Much wrote: > > The following bug has been logged online: > > Bug reference: 4932 > Logged by: Peter Much > Email address: ...@citylink.dinoex.sub.org > PostgreSQL version: 8.4.0 > Operating system: FreeBSD 7.2 > Description: Upgrade 8.2.13 -> 8.4.0 - Kerberos option missing > Details: > > In chapter 19.3.5 of the manual an option "krb_server_hostname" is > mentioned. > This option was present in 8.2 but is no longer present in 8.4.0 It is present, only it has now been moved to pg_hba.conf. It is no longer in postgresql.conf. My guess is that you tried it configured the same way as in previous versions, where there was a global parameter in postgresql.conf? > So at least we have a documentation bug here. That page lists settings for pg_hba.conf, so I believe it is correct. However, suggestions for improvements are always welcome :-) > But the essential point seems to me the following: section 19.3.5 of the > manual reads "hostname is the fully qualified host name of the server > machine." > > But _there_is_no_such_thing_ as a "fully qualified hostname"! > There are only _fully_qualified_interface-names_, and any host can have > *many* of these. The hostname itself is nothing else than an arbitrary label > for the machine, and it should never be used by networking software. In a very large part of the cases, the fully qualified hostname will be the same as the fully qualified interface name for the only interface that's configured. Anyway, the whole reason for moving the krb_server_hostname parameter into pg_hba.conf is to make it *more* flexible to configure situations like this. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4933: ts_rewrite() causes segfault when query with more than one node becomes empty
The following bug has been logged online:
Bug reference: 4933
Logged by: Aaron Marcuse-Kubitza
Email address: aaro...@blackducksoftware.com
PostgreSQL version: 8.3.7
Operating system: Mac OS X 10.5.7
Description:ts_rewrite() causes segfault when query with more than
one node becomes empty
Details:
Running
SELECT ts_rewrite('''example'' & ''example'''::tsquery,
'''example'''::tsquery, ''::tsquery)
causes the error "LOG: server process (PID ...) was terminated by signal
11: Segmentation fault". Any combination of &, |, and () using only
'example' nodes causes the same problem.
However, running
SELECT ts_rewrite('''example'''::tsquery, '''example'''::tsquery,
''::tsquery)
works as expected.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4932: Upgrade 8.2.13 -> 8.4.0 - Kerberos option missing
Dear Magnus! On Wed, Jul 22, 2009 at 11:52:32AM +0200, Magnus Hagander wrote: ! On Wed, Jul 22, 2009 at 11:42, Peter Much wrote: ! > In chapter 19.3.5 of the manual an option "krb_server_hostname" is ! > mentioned. ! > This option was present in 8.2 but is no longer present in 8.4.0 ! ! It is present, only it has now been moved to pg_hba.conf. It is no ! longer in postgresql.conf. My guess is that you tried it configured ! the same way as in previous versions, where there was a global ! parameter in postgresql.conf? Nearly. I merged my old and new config, noticed the option was gone, tried it nevertheless and got an error, tried again without it and obviousely logins did not work. I confess that I did not carefully study new HBA features - but even if I had, I am not quite sure if I would have gotten that point at once. Now understanding it, I bow in respect - this is indeed a fine improvement! ! > But _there_is_no_such_thing_ as a "fully qualified hostname"! ! In a very large part of the cases, the fully qualified hostname will ! be the same as the fully qualified interface name for the only ! interface that's configured. Alright, frankly and just out of band of the topic, let me say one thing: I am installing systems for the big commercial vendors for more than a decade now, and this matter was an ongoing annoyance all of the time. While at first glance it may be considered just a matter of convenience, the real trouble starts as soon as one does high-availability solutions; these will definitely break on such an assumption, and we end up with patching the hostname on takeover: so having no functional mailer, unintellegible logfiles, not knowing for sure on which hardware we admins are logged in, and similar ugliness more. Here I am talking about the commercial middleware vendors, who are really stubborn in this matter - in the OpenSource the situation is already a thousand times better! ! Anyway, the whole reason for moving the krb_server_hostname parameter ! into pg_hba.conf is to make it *more* flexible to configure situations ! like this. Indeed, I agree with You, and I am very happy. :) rgds, PMc -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4932: Upgrade 8.2.13 -> 8.4.0 - Kerberos option missing
On Wed, Jul 22, 2009 at 17:29, Peter Much wrote: > On Wed, Jul 22, 2009 at 11:52:32AM +0200, Magnus Hagander wrote: > ! On Wed, Jul 22, 2009 at 11:42, Peter Much > wrote: > > Now understanding it, I bow in respect - this is indeed a fine > improvement! Thanks :-) > ! > But _there_is_no_such_thing_ as a "fully qualified hostname"! > > ! In a very large part of the cases, the fully qualified hostname will > ! be the same as the fully qualified interface name for the only > ! interface that's configured. > > Alright, frankly and just out of band of the topic, let me say > one thing: I am installing systems for the big commercial vendors > for more than a decade now, and this matter was an ongoing annoyance > all of the time. > While at first glance it may be considered just a matter of > convenience, the real trouble starts as soon as one does > high-availability solutions; these will definitely break on such > an assumption, and we end up with patching the hostname on takeover: > so having no functional mailer, unintellegible logfiles, not knowing > for sure on which hardware we admins are logged in, and similar > ugliness more. > Here I am talking about the commercial middleware vendors, who > are really stubborn in this matter - in the OpenSource the situation > is already a thousand times better! If you have any suggestions for improvements on either the documentation on the feature itself from someone who's deploying them "for real customers", that's always interesting. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4934: regression in IN with joins in subselect
The following bug has been logged online: Bug reference: 4934 Logged by: Benjamin Reed Email address: ran...@opennms.org PostgreSQL version: 8.4.0 Operating system: Mac OS X 10.5 Description:regression in IN with joins in subselect Details: I've hit a regression vs. PostgreSQL 8.2 and 8.3 (and probably others) related to subselects. This query: ---(snip!)--- SELECT DISTINCT ipInterface.ipAddr FROM ipInterface JOIN node ON (ipInterface.nodeID = node.nodeID) JOIN ifServices ON (ipInterface.id = ifServices.ipInterfaceId) JOIN service ON (ifServices.serviceID = service.serviceID) WHERE ( node.nodeID IN ( SELECT category_node.nodeID FROM category_node, categories WHERE categories.categoryID = category_node.categoryID AND categories.categoryName = 'IMP_mid' ) ) AND ( node.nodeID IN ( SELECT category_node.nodeID FROM category_node, categories WHERE categories.categoryID = category_node.categoryID AND categories.categoryName = 'DEV_AC' ) ) AND ( node.nodeID IN ( SELECT category_node.nodeID FROM category_node, categories WHERE categories.categoryID = category_node.categoryID AND categories.categoryName = 'OPS_Online' ) ) AND (node.nodeId = 1) AND (ipInterface.ipAddr = '192.168.1.1') AND (service.serviceName = 'ICMP') LIMIT 1; ---(snip!)--- ...passes in PostgreSQL 8.2 and 8.3 (which I have handy to test), but fail in 8.4.0, as well as current origin/REL8_4_STABLE in git. I reported it in IRC, and the original hope was that it was related to bug #4906, but since testing latest git, it appears that is not the case. The query plan in 8.3 is this: ---(snip!)--- Limit (cost=4.27..76.68 rows=1 width=50) -> Unique (cost=4.27..76.68 rows=1 width=50) -> Nested Loop IN Join (cost=4.27..76.68 rows=1 width=50) -> Nested Loop IN Join (cost=4.27..60.12 rows=1 width=54) -> Nested Loop IN Join (cost=4.27..43.56 rows=1 width=54) -> Nested Loop (cost=4.27..26.99 rows=1 width=54) -> Nested Loop (cost=4.27..18.72 rows=1 width=54) -> Nested Loop (cost=4.27..17.90 rows=2 width=58) -> Index Scan using ipinterface_nodeid_ipaddr_ismanaged_idx on ipinterface (cost=0.00..8.27 rows=1 width=58) Index Cond: ((1 = nodeid) AND ((ipaddr)::text = '192.168.1.1'::text)) -> Bitmap Heap Scan on ifservices (cost=4.27..9.60 rows=2 width=8) Recheck Cond: (ipinterface.id = ifservices.ipinterfaceid) -> Bitmap Index Scan on ifservicves_ipinterfaceid_idx (cost=0.00..4.27 rows=2 width=0) Index Cond: (ipinterface.id = ifservices.ipinterfaceid) -> Index Scan using pk_serviceid on service (cost=0.00..0.40 rows=1 width=4) Index Cond: (ifservices.serviceid = service.serviceid) Filter: ((servicename)::text = 'ICMP'::text) -> Index Scan using node_id_type_idx on node (cost=0.00..8.27 rows=1 width=4) Index Cond: (nodeid = 1) -> Nested Loop (cost=0.00..16.55 rows=1 width=4) -> Index Scan using category_idx on categories (cost=0.00..8.27 rows=1 width=4) Index Cond: ((categoryname)::text = 'IMP_mid'::text) -> Index Scan using catenode_unique_idx on category_node (cost=0.00..8.27 rows=1 width=8) Index Cond: ((public.categories.categoryid = public.category_node.categoryid) AND (1 = public.category_node.nodeid)) -> Nested Loop (cost=0.00..16.55 rows=1 width=4) -> Index Scan using category_idx on categories (cost=0.00..8.27 rows=1 width=4) Index Cond: ((categoryname)::text = 'OPS_Online'::text) -> Index Scan using catenode_unique_idx on category_node (cost=0.00..8.27 rows=1 width=8) Index Cond: ((public.categories.categoryid = public.category_node.categoryid) AND (1 = public.category_node.nodeid)) -> Nested Loop (cost=0.00..16.55 rows=1 wi
Re: [BUGS] BUG #4934: regression in IN with joins in subselect
"Benjamin Reed" writes: > I've hit a regression vs. PostgreSQL 8.2 and 8.3 (and probably others) > related to subselects. This query: It's not going to be possible to examine this with just the query. You need to provide a self-contained test case. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4934: regression in IN with joins in subselect
On 7/22/09 4:39 PM, Tom Lane wrote: > "Benjamin Reed" writes: >> I've hit a regression vs. PostgreSQL 8.2 and 8.3 (and probably others) >> related to subselects. This query: > > It's not going to be possible to examine this with just the query. > You need to provide a self-contained test case. Attached is a test case, including the query that causes the error at the end. On 8.3, it returns 1 row (192.168.1.1), on 8.4 including the git 8.4 branch, it returns none. -- Benjamin Reed The OpenNMS Group http://www.opennms.org/ drop table if exists node cascade; drop table if exists categories cascade; drop table if exists category_node cascade; drop table if exists ipinterface cascade; drop table if exists ifservices cascade; drop table if exists service cascade; create table node ( nodeId serial not null, nodeLabel varchar, constraint pk_nodeId primary key (nodeId) ); insert into node values (1, 'node1'); insert into node values (2, 'node2'); insert into node values (3, 'node3'); insert into node values (4, 'node4'); create table ipInterface ( id serial not null, nodeId integer not null, ipAddr varchar(16) not null, constraint ipinterface_pkey PRIMARY KEY (id), constraint fk_nodeId1 foreign key (nodeId) references node ON DELETE CASCADE ); insert into ipInterface values (1, 1, '192.168.1.1'); insert into ipInterface values (2, 1, '192.168.1.2'); insert into ipInterface values (3, 1, '192.168.1.3'); insert into ipInterface values (4, 2, '192.168.2.1'); insert into ipInterface values (5, 2, '192.168.2.2'); insert into ipInterface values (6, 2, '192.168.2.3'); insert into ipInterface values (7, 3, '192.168.3.1'); insert into ipInterface values (8, 3, '192.168.3.2'); insert into ipInterface values (9, 3, '192.168.3.3'); insert into ipInterface values (10, 4, '192.168.4.1'); insert into ipInterface values (11, 4, '192.168.4.2'); insert into ipInterface values (12, 4, '192.168.4.3'); create table categories ( categoryId serial not null, categoryNamevarchar(64) not null, constraint category_pkey primary key (categoryId) ); create unique index category_idx ON categories(categoryName); insert into categories values (1, 'DEV_AC'); insert into categories values (2, 'IMP_mid'); insert into categories values (3, 'OPS_Online'); insert into categories values (4, 'some category'); create table category_node ( categoryId integer, nodeId integer, constraint categoryid_fkey1 foreign key (categoryId) references categories (categoryId) ON DELETE CASCADE, constraint nodeid_fkey1 foreign key (nodeId) references node ON DELETE CASCADE ); insert into category_node values (1, 1); insert into category_node values (2, 1); insert into category_node values (3, 1); insert into category_node values (2, 2); insert into category_node values (3, 3); insert into category_node values (1, 4); create table service ( serviceID serial not null, serviceName varchar(32) not null, constraint pk_serviceID primary key (serviceID) ); insert into service values (1, 'ICMP'); insert into service values (2, 'SNMP'); insert into service values (3, 'HTTP'); create table ifServices ( id serial not null, nodeID integer not null, ipAddr varchar(16) not null, serviceID integer not null, ipInterfaceId integer not null, constraint ifservices_pkey PRIMARY KEY (id), constraint ipinterface_fkey FOREIGN KEY (ipInterfaceId) REFERENCES ipInterface (id) ON DELETE CASCADE, constraint fk_nodeID3 foreign key (nodeID) references node ON DELETE CASCADE, constraint fk_serviceID1 foreign key (serviceID) references service ON DELETE CASCADE ); create unique index ifservices_nodeid_ipaddr_svc_unique on ifservices(nodeID, ipAddr, serviceId); create index ifservices_nodeid_idx on ifservices(nodeID); create index ifservices_serviceid_idx on ifservices(serviceID); create index ifservices_nodeid_serviceid_idx on ifservices(nodeID, serviceID); create index ifservicves_ipInterfaceId_idx on ifservices(ipInterfaceId); insert into ifservices values (1, 1, '192.168.1.1', 1, 1); insert into ifservices values (2, 1, '192.168.1.1', 2, 1); insert into ifservices values (3, 1, '192.168.1.2', 1, 2); insert into ifservices values (4, 1, '192.168.1.2', 3, 2); insert into ifservices values (5, 1, '192.168.1.3', 1, 3); insert into ifservices values (6, 2, '192.168.2.1', 1, 4); insert into ifservices values (7, 2, '192.168.2.1', 2, 4); insert into ifservices values (8, 2, '192.168.2.2', 1, 5); insert into ifservices values (9, 2, '192.168.2.2', 3, 5); insert into ifservices values (10, 2, '192.168.2.3', 1, 6); insert into ifservices values (11, 3, '192.168.3.1', 1, 7); insert into
Re: [BUGS] BUG #4934: regression in IN with joins in subselect
Benjamin Reed writes: > Attached is a test case, including the query that causes the error at > the end. On 8.3, it returns 1 row (192.168.1.1), on 8.4 including the > git 8.4 branch, it returns none. Thanks, it seems like the problem is that it's applying *both* its methods for implementing an IN join: it's unique-ifying the sub-select output via a HashAggregate, and then using a Semi Join anyway when that gets joined to the "node" table. And the Semi Join has indeterminate output for some of the other output columns. (The join order it's choosing seems a bit odd too, but with so few rows in the tables it may be that all the join orders seem to have the same cost.) I think this is probably a small fix, but running out of energy for tonight ... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
