Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Craig Ringer]

On 03/18/2013 01:07 PM, Craig Ringer wrote:
> System wide installation of the root may allow OpenSSL to discover it
> and use it for verification back to the root without having to trust it
> to sign clients. I'll do some more checking to see if this is possible
> with how Pg uses OpenSSL but I'm inclined to doubt it.
It looks like we aren't reading the system-wide certs or looking them up
when certs aren't resolved in the files Pg explicitly passes to OpenSSL,
so a system-wide install doesn't look like it'll work.

I've had a look at how Apache handles this and it took me a while to
work out what's going on. Apache uses SSLCACertificateFile (concatenated
certs) / SSLCACertificatePath (hashed dir) to look up trusted
certificate signers. It According to the docs it doesn't appear to make
any provision there for trusting intermediate certificates but not their
parents as signers of client certificates, but it looks like support is
there, the docs just don't explain it well.

Apache has SSLCADNRequestFile / SSLCADNRequestPath which are described
as controlling the acceptable certificate names sent to clients in a
client cert request. The docs don't make it clear if Apache will trust
only client certs with these certs in their chains or whether this only
controls the list of certificate DNs presented to the client rather than
what's accepted in response. The code suggests that they control trust
not just the cert list presented.

In Apache's modules/ssl/ssl_engine_init.c it calls
SSL_CTX_load_verify_locations on the SSLCACertificateFile and
SSLCACertificatePath.

It then  calls ssl_init_FindCAList with the
SSLCADNRequestFile/SSLCADNRequestPath if they're specified in the
configuration, otherwise it calls it with the
SSLCACertificateFile/SSLCACertificatePath . That calls
ssl_init_PushCAList on all of the certs it finds in the File and Path
variants. For each cert file that calls SSL_load_client_CA_file and for
each cert within each file pushes the cert onto a STACK_OF(X509_NAME) if
a cert with the same DN isn't already in the stack. It passes the stack
to OpenSSL's SSL_CTX_set_client_CA_list .

So what Apache does appears to boil down to:

SSL_CTX_load_verify_locations(ca_file,ca_path);
if (ca_dn_file || ca_dn_path) {
SSL_CTX_set_client_CA_list( ... STACK_OF unique certs on ca_dn_file
and ca_dn_path ... );
} else {
SSL_CTX_set_client_CA_list( ... STACK_OF unique certs on ca_file and
ca_path );
}

This appears to match Ian's description of having a validation-only cert
list and a separate list of certs used to verify clients. I'd like to
follow Apache's model:

in postgresql.conf, if ssl_ca_file is set then pass it to
SSL_CTX_load_verify_locations . If the proposed new parameter
ssl_ca_valid_client_signers_file is set then pass the certs in that to
SSL_CTX_set_client_CA_list ; otherwise pass the certs in ssl_ca_file to
SSL_CTX_set_client_CA_list and thus retain the current behaviour.
Hopefully we can avoid the ugly read-and-deduplicate stuff Apache has to
do because we currently only support a certfile anyway, we don't read
certdirs, so I'll look for helper functions that wrap
SSL_CTX_set_client_CA_list.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Craig Ringer]

On 03/18/2013 02:27 PM, Ian Pilcher wrote:
> On 03/18/2013 12:07 AM, Craig Ringer wrote:
>> So this problem is verified.

> * Trusted certificates - What currently goes in the (unfortunately
>   named) root.crt file.

Well, a little unfortunate. It contains roots of *client authentication*
trust, which is fair enough, they just aren't necessarily self-signed
certificates that are roots of *certificate validity* trust (root CA certs).

This list is set by SSL_CTX_set_client_CA_list . The examples section of
its man page contains:

  Scan all certificates in CAfile and list them as acceptable CAs:

   SSL_CTX_set_client_CA_list(ctx,SSL_load_client_CA_file(CAfile));

> * Validation-only certificates - CA certificates that are used only to
>   complete the chain from a trusted certificate to a self-signed root.
>   I haven't been able to come up with a particularly good name for a
>   file containing this type of certificate(s) -- validate.crt?

We should probably take advantage of the fact that 9.2 made these
filenames configurable to deprecate root.crt and choose two descriptive
filenames, something like trusted_cert_roots.crt and
trusted_client_cert_signers.crt .

> This is conceptually simple, and I've been fiddling with it for the last
> week or so.  Unfortunately, the OpenSSL documentation has made this far
> more challenging that it should be.  Simple things like reading multiple
> certificates from a file, checking whether an X509_STORE contains a
> particular certificate, etc. are all proving to be unexpectedly
> difficult.  (I never thought that I'd miss the Java SSL API!)

Apache's sources are useful there. When working with OpenSSL sometimes
the sanest option is to find something you know already does it right,
work out how, *understand why it works* and then apply that approach to
your code. Blindly copying their approach is stupid and guaranteed to
lead to security holes, but others' code remains some of the best
documentation for OpenSSL if used for hints rather than blindly copied.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] C++Builder table exist

[Charl Roux]

Thanks. Case-folding was my problem.
Is there any way of getting PostgreSQL to work according to the SQL standard 
(The folding of
unquoted names to lower case in PostgreSQL is incompatible with the SQL
standard, which says that unquoted names should be folded to
upper case.), so there is no need for me to add quotes to all names? 

> To: pgsql-general@postgresql.org
> From: ja...@xnet.co.nz
> Subject: Re: [GENERAL] C++Builder table exist
> Date: Sat, 16 Mar 2013 06:16:33 +
> 
> On 2013-03-13, Charl Roux  wrote:
> > --_51d77859-0e03-4afa-bde6-853bee9c0a11_
> > Content-Type: text/plain; charset="iso-8859-1"
> > Content-Transfer-Encoding: quoted-printable
> 
> appologies for the formatting, gmane did something to your email that
> SLRN didn't like.
> 
> > void __fastcall TfrmMain::FormCreate(TObject *Sender)
> > {
> >   int errorCode;
> >   TStringList *tableList = new TStringList;
> >   frmDataModule->eyeConnection->GetTableNames(tableList);
> >
> >   // create queryBackup table if does not exist
> >   AnsiString SQL = "CREATE TABLE queryBackup( queryName VARCHAR(30) PRIMARY 
> > KEY ,query VARCHAR(1))";
> >   if( tableList->IndexOf("queryBackup") < 0 )
> > errorCode = frmDataModule->eyeConnection->ExecuteDirect(SQL);
> > }
> 
> > ERROR: relation "querybackup" already exists.
> 
> The problem is you're checking for queryBackup and then creating
> querybackup when it doesn't exist. (note: capitalisation)
> 
> http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html
> 
> This case-folding is one of the few places where postgres deliberately
> breaks the sql standard (AIUI standard wants case folded upwards).
> 
> -- 
> ⚂⚃ 100% natural
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
  

Re: [GENERAL] C++Builder6 enum

[Charl Roux]

Thanks. Case-folding was my problem.

Is there any way of getting PostgreSQL to work according to the SQL standard 
(The folding of
unquoted names to lower case in PostgreSQL is incompatible with the SQL
standard, which says that unquoted names should be folded to
upper case.), so there is no need for me to add quotes to all names? 

> Date: Fri, 15 Mar 2013 07:05:11 -0700
> From: adrian.kla...@gmail.com
> To: charl.r...@hotmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] C++Builder6 enum
> 
> On 03/15/2013 04:06 AM, Charl Roux wrote:
> > Hi,
> >
> > I am using C++Builder6 on WinXPSP3, with Devart's dbExpress driver. I
> > declared a enumeration type called 'professionEnum', which is used by my
> > variable 'profession'. I get an error: 'Cannot access field 'rootcause'
> > as type Text', if I try and enter a value into the enum field.
> 
> Not sure where 'professionEnum' comes into play, it not shown anywhere 
> below. I will assume you mean 'rootCauseEnum'.
> >
> > 1. I create the table as follows:
> > CREATE TABLE IF NOT EXISTS
> > status(
> > statusCode BIGINT PRIMARY KEY,
> > description VARCHAR(50),
> > level SMALLINT,
> > rootCause rootCauseEnum,
> > material VARCHAR(100),
> > actionTaken VARCHAR(50)
> > )
> >
> > 2. I add an enum type 'rootCauseEnum' as follows:
> > AnsiString SQL;
> >
> >SQL = "CREATE TYPE ";
> >SQL = SQL + edtEnumerationType->Text;// My entry: rootCauseEnum
> >SQL = SQL + " AS ENUM ( ";
> >SQL = SQL + edtEnumerationList->Text; // My entry:
> > 'none','unknown','elec','oil'
> >SQL = SQL + " )";
> >
> >int errorCode = frmDataModule->eyeConnection->ExecuteDirect(SQL);
> >
> > 3. I add a record to the status table from the command prompt which
> > works fine as follows:
> > eye=# INSERT INTO status VALUES( 12, 'Running', 0, 'none', 'Not
> > applicable', 'NA');
> >
> > 4.I add a record to the status table from C++Builder as follows:
> > I have an edit box for every field, as I enter the following all is fine:
> > statusCode=13
> > description=Stopped
> > level=0
> > As I enter any of the following into the rootCause field:
> > rootCause=none
> > rootCause=1
> > I get the following error:
> > Cannot access field 'rootcause' as type Text.
> 
> Are you sure this is not a case sensitivity problem on the field 
> rootCause. In your table definition it is rootCause, in the error 
> message it is rootcause. Postgres will fold down unless the field name 
> is quoted. If the the original table definition quoted the field names 
> then they retain their case and need to be quoted when used. For more 
> detail see:
> 
> http://www.postgresql.org/docs/9.2/static/sql-syntax-lexical.html
> 4.1.1. Identifiers and Key Words
> 
> 
> > Therefore before I apply the updates to the table, i get the error.
> >
> > The field 'rootCause' is indicated as (Memo) in run-time.
> >
> > This worked fine when I used MySQL. With MySQL I can enter
> > rootCause=none
> > or
> > rootCause=1
> >
> > Thanks.
> >
> > Charl
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@gmail.com
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
  

Re: [GENERAL] C++Builder table exist

[Albe Laurenz]

Charl Roux wrote:
> Is there any way of getting PostgreSQL to work according to the SQL standard 
> (The folding of unquoted
> names to lower case in PostgreSQL is incompatible with the SQL standard, 
> which says that unquoted
> names should be folded to upper case.), so there is no need for me to add 
> quotes to all names?

No, PostgreSQL will always ford to lowercase.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Addled index

[Greg Jaskiewicz]

On 17 Mar 2013, at 04:30, Tom Lane  wrote:

> Oleg Alexeev  writes:
>> * it is varchar columns, 256 and 32 symbols length
>> * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8
>> * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms,
>> commit_delay = 100, commit_siblings = 10, checkpoint_timeout = 20min,
>> checkpoint_completion_target = 0.7
>> * postgres 9.2.3 installed via yum repository for version 9.2
>> * 64 bit Centos 6, installed and updated from yum repository
> 
> fsync off?  Have you had any power failures or other system crashes?
> ext4 is *way* more prone than ext3 was to corrupt data when fsync is
> disabled, because it caches and reorders writes much more aggressively.
> 
>> Database located on software md raid 1 based on two SSD disks array. Ext4
>> filesystem. Database is master node.
> 
> Meh.  I quote from the RHEL6 documentation (Storage Administration
> Guide, Chapter 20: Solid-State Disk Deployment Guidelines):
> 
>> Red Hat also warns that software RAID levels 1, 4, 5, and 6 are not
>> recommended for use on SSDs.
> 
> https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/html/Storage_Administration_Guide/newmds-ssdtuning.html
> 
> The part of the docs I'm looking at only asserts that performance is
> bad, but considering that it's a deprecated combination, it may well be
> that there are data-loss bugs in there.  I'd certainly suggest making
> sure you are on a *recent* kernel.  If that doesn't help, reconsider
> your filesystem choices.
> 
Yeah, I don't think I'd consider using software raid for SSDs any time a good 
idea

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Stephen Frost]

Craig, all,

* Craig Ringer (cr...@2ndquadrant.com) wrote:
> PROBLEM VERIFIED

Let me just say "ugh".  I've long wondered why we have things set up in
such a way that the whole chain has to be in one file, but it didn't
occur to me that it'd actually end up causing this issue.  In some ways,
I really wonder about this being OpenSSL's fault as much as ours, but I
doubt they'd see it that way. :)

> What we need to happen instead is for root.crt to contain only the
> trusted certificates and have a *separate* file or directory for
> intermediate certificates that OpenSSL can look up to get the
> intermediates it needs to validate client certs, like
> `ssl_ca_chain_file` or `ssl_ca_chain_path` if we want to support
> OpenSSL's hashed certificate directories.

Makes sense to me.  I'm not particular about the names, but isn't this
set of CAs generally considered intermediary?  Eg: 'trusted', '
intermediate', etc?

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] C++Builder table exist

[Adrian Klaver]

On 03/18/2013 03:25 AM, Charl Roux wrote:

Thanks. Case-folding was my problem.
Is there any way of getting PostgreSQL to work according to the SQL
standard (The folding of unquoted names to lower case in PostgreSQL is
incompatible with the SQL standard, which says that unquoted names
should be folded to upper case.), so there is no need for me to add
quotes to all names?



As far I know the only difference would be whether the table name you 
get in the error comes back as lower case or upper case. In your 
situation where you are using a quoted mixed case both folding styles 
would lead to an error. In that aspect the SQL standard and Postgres 
follow the same rule, if the name is quoted on creation the case it was 
quoted in must be maintained on subsequent use. If you do not want to 
quote identifiers on use then do not quote then on creation:



test=> create TABLE case_test("MixedCaseQuoted" varchar, 
MixedCaseUnQuoted varchar);

CREATE TABLE
test=> \d+ case_test
 Table "utility.case_test"
  Column   |   Type| Modifiers | Storage  | 
Description

---+---+---+--+-
 MixedCaseQuoted   | character varying |   | extended |
 mixedcaseunquoted | character varying |   | extended |
Has OIDs: no

test=> SELECT mixedcasequoted from case_test ;
ERROR:  column "mixedcasequoted" does not exist
LINE 1: SELECT mixedcasequoted from case_test ;
   ^
test=> SELECT mixedcaseunquoted from case_test ;
 mixedcaseunquoted
---
(0 rows)

test=> SELECT "MixedCaseQuoted" from case_test ;
 MixedCaseQuoted 



- 



(0 rows) 






test=> SELECT MixedCaseUnQuoted from case_test ;
 mixedcaseunquoted 



--- 



(0 rows)


--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High RAM usage on postgres

[Merlin Moncure]

On Thu, Mar 14, 2013 at 1:55 PM, prashantmalik  wrote:
> Hello,
>
> We are facing very HIGH memory utilization on postgreSQL server and need
> help.
>
> Total RAM : 32GB
> Total CPU : 16cores
>
> ---
> *Table Size:*
> SELECT relname, pg_size_pretty(relpages::bigint * 8 * 1024) as size,
> relkind,reltuples::bigint as rows, relpages, relfilenode FROM pg_class ORDER
> BY relpages DESC;
>   relname   |size| relkind |
> rows   | relpages | relfilenode
> ++-+-+--+-
>  customer   | 1863 MB| r   |
> 8307040 |   238507 |  189335
>
>
> *Query :* "SELECT * FROM customer"
> ---
> top
>
> top - 00:14:38 up 44 days, 12:06,  2 users,  load average: 3.57, 1.34, 0.69
> Tasks: 243 total,   3 running, 240 sleeping,   0 stopped,   0 zombie
> Cpu(s):  6.5%us,  0.6%sy,  0.0%ni, 92.5%id,  0.4%wa,  0.0%hi,  0.0%si,
> 0.0%st
> Mem:  32949816k total, 31333260k used,  1616556k free,   526988k buffers
> Swap:  4192956k total,  1989136k used,  2203820k free,  9182092k cached
>
>   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
> 12671 root  25   0 19.8g  19g 1612 R 100.1 62.6   4:31.78 psql
> 32546 postgres  15   0 6694m 157m 156m S  0.0  0.5   0:02.91 postmaster
>
>
>
> ---
> postgresql.conf
> shared_buffers = 6400MB# min 128kB
> # (change requires restart)
> temp_buffers = 286720# min 800kB
>
> work_mem = 320MB# min 64kB
> maintenance_work_mem = 960MB
>
> checkpoint_segments = 32# in logfile segments, min 1, 16MB each
> checkpoint_timeout = 1h# range 30s-1h
> checkpoint_completion_target = 0.9# checkpoint target duration, 0.0 -
> 1.0
> checkpoint_warning = 10min# 0 disables
>
> effective_cache_size = 16000MB
>
> Hello,
>
> We are facing very HIGH memory utilization on postgreSQL server and need
> help.
>
> Total RAM : 32GB
> Total CPU : 16cores
>
> ---
> *Table Size:*
> SELECT relname, pg_size_pretty(relpages::bigint * 8 * 1024) as size,
> relkind,reltuples::bigint as rows, relpages, relfilenode FROM pg_class ORDER
> BY relpages DESC;
>   relname   |size| relkind |
> rows   | relpages | relfilenode
> ++-+-+--+-
>  customer   | 1863 MB| r   |
> 8307040 |   238507 |  189335
>
>
> *Query :* "SELECT * FROM customer"
> ---
> top
>
> top - 00:14:38 up 44 days, 12:06,  2 users,  load average: 3.57, 1.34, 0.69
> Tasks: 243 total,   3 running, 240 sleeping,   0 stopped,   0 zombie
> Cpu(s):  6.5%us,  0.6%sy,  0.0%ni, 92.5%id,  0.4%wa,  0.0%hi,  0.0%si,
> 0.0%st
> Mem:  32949816k total, 31333260k used,  1616556k free,   526988k buffers
> Swap:  4192956k total,  1989136k used,  2203820k free,  9182092k cached
>
>   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
> 12671 root  25   0 19.8g  19g 1612 R 100.1 62.6   4:31.78 psql
> 32546 postgres  15   0 6694m 157m 156m S  0.0  0.5   0:02.91 postmaster
>
>
>
> ---
> More over, the query is throwing all the data on the screen at once without
> any keyboard interrupt for this table.
> For all other tables, output is shown in parts when keys are pressed from
> keyboard.
>
> Is a query from another server with less memory(16GB) is made to this
> postgres, oomkiller kills the postgres thread due to out of memory.

what are you intending to do with the 19gb+ data you are querying out?

problem is psql buffering whole result set in memory before outputting
result.   note this is core problem with libpq client library until
very recently.  there are several easy workarounds:

*) use cursor
*) don't select entire table, page it out using index (I can suggest
some methods )
*) if you are outputting to file, consider using COPY

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
T

Re: [GENERAL] High RAM usage on postgres

[Daniel Verite]

Merlin Moncure wrote:

> problem is psql buffering whole result set in memory before outputting
> result.   note this is core problem with libpq client library until
> very recently.  there are several easy workarounds:
> 
> *) use cursor
> *) don't select entire table, page it out using index (I can suggest
> some methods )
> *) if you are outputting to file, consider using COPY

Also there's psql's FETCH_COUNT that is specifically meant to avoid the
buffering problem.

>From the 9.1 manpage:

   FETCH_COUNT
   If this variable is set to an integer value > 0, the results
of
   SELECT queries are fetched and displayed in groups of that
many
   rows, rather than the default behavior of collecting the
entire
   result set before display. Therefore only a limited amount of
   memory is used, regardless of the size of the result set.
   Settings of 100 to 1000 are commonly used when enabling this
   feature. Keep in mind that when using this feature, a query
   might fail after having already displayed some rows.


Best regards,
-- 
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Concurrent updates

[Steve Erickson]

I have a table that I want to use as a queue with all functionality (Insert,
update, delete) embodied in a stored procedure.  Inserts and deletes are no
problem.  An external program would call the stored procedure to get one or
more emails to work on, selecting on "state='N'", then updating the row so
"state='P'".  My problem is having multiple threads calling the stored
procedure simultaneously and getting the same row(s).  Selecting FOR UPDATE
won't work as, if thread #1 gets 3 rows and thread #2 starts before thread
#1 completes (Commits), thread #2 will select the same 3 rows as thread #1
except, since thread #1 will update the state (changing the state to 'P') so
that those rows no longer meet thread #2 criteria, and thread #2 will
receive zero rows.  The table looks like:

 

CREATE TABLE dss.stage_email

(

  emailid bigserial NOT NULL,

  email_path text,

  state character(1) DEFAULT 'N'::bpchar, -- N = New Email, P=Processing,
D=Deleting

  fetch_date timestamp without time zone DEFAULT now(),

  ingest_date timestamp without time zone

)

 

Steve Erickson

Senior Developer

266 East 7th Street, Floor 4

Saint Paul, MN 55101

651.925.3237 office

612.242.1343 cell

 

NOTICE:  This email, including any attachments, is covered by the Electronic
Communications Privacy Act, is confidential and may be legally privileged.
If you are not the intended recipient, you are hereby notified that any
retention, dissemination, distribution or copying of this communication is
strictly prohibited.  If you have received this communication in error,
please immediately notify our office by return email or at our telephone
number (651) 925-3200.  Then delete and destroy all copies of this email and
any attachments.

 

Thank you.

 

[GENERAL] When did this behavior change (and where else might it bite me)?

[Jeff Amiel]

In prepping for an upgrade to 9.2.3, I stumbled across this:

CREATE TABLE foo
(
  myint integer,
  string1 text,
  string2 text
)
WITH (
  OIDS=FALSE
);

insert into foo values (12345,'Y','N');

 select * from foo f where f.myint = 12345 or f.name='Y'

In 9.2.3, this returns:
ERROR:  column f.name does not exist
LINE 1:  select * from foo f where myint = 12345 or f.name='Y'

in 8.4.6 ,this returns no error (and gives me the row from the table)

It looks like the parser is short-circuiting in 8.4.6 before stumbling upon the 
invalid column name - EXCEPT when the column name is NOT a reserved word  
(although according to 
http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html, 
'name' is not a reserved word).

Example - in 8.4.6, this WILL return an error:
 select * from foo f where f.myint = 12345 or f.poopy='Y'
ERROR:  column f.poopy does not exist
LINE 2:   select * from foo f where f.myint = 12345 or f.poopy='Y'
   ^

NOTE:  The problem (assuming the problem is in 8.4.6) only manifests itself 
when I use table aliases .
 select * from foo f where myint = 12345 or name='Y' 

gives an error I would expect:
ERROR:  column "name" does not exist
LINE 2:    select * from foo f where myint = 12345 or name='Y'
  ^


Any insight into what change (I poured through the release notes and couldn't 
find anything) may have 'fixed'  this behavior so that I might better head 
these off before my conversion?

(yes, my example was contrived - and I did have an bug where the wrong column 
name was used)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] When did this behavior change (and where else might it bite me)?

[Steve Atkins]

On Mar 18, 2013, at 9:49 AM, Jeff Amiel  wrote:

> In prepping for an upgrade to 9.2.3, I stumbled across this:
> 
> CREATE TABLE foo
> (
>   myint integer,
>   string1 text,
>   string2 text
> )
> WITH (
>   OIDS=FALSE
> );
> 
> insert into foo values (12345,'Y','N');
> 
>  select * from foo f where f.myint = 12345 or f.name='Y'
> 
> In 9.2.3, this returns:
> ERROR:  column f.name does not exist
> LINE 1:  select * from foo f where myint = 12345 or f.name='Y'
> 
> in 8.4.6 ,this returns no error (and gives me the row from the table)

That's (unintentionally) an attribute style data type cast - bar.name is the 
same as name(bar), and tries to cast bar to type "name" (an internal-use string 
type) 

Try "select foo from foo", "select name(foo::text) from foo" and "select 
name(foo) from foo" to see what's going on.

That was tightened up in 9.1, I think:

Casting

Disallow function-style and attribute-style data type casts for composite 
types (Tom Lane)

For example, disallow composite_value.text and text(composite_value). 
Unintentional uses of this syntax have frequently resulted in bug reports; 
although it was not a bug, it seems better to go back to rejecting such 
expressions. The CASTand :: syntaxes are still available for use when a cast of 
an entire composite value is actually intended.

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] When did this behavior change (and where else might it bite me)?

[Tom Lane]

Jeff Amiel  writes:
> select * from foo f where f.myint = 12345 or f.name='Y'

> In 9.2.3, this returns:
> ERROR:  column f.name does not exist
> LINE 1:  select * from foo f where myint = 12345 or f.name='Y'

> in 8.4.6 ,this returns no error (and gives me the row from the table)

That changed in this 9.1 patch:

commit 543d22fc7423747afd59fe7214f2ddf6259efc62
Author: Tom Lane 
Date:   Sun Nov 7 13:03:19 2010 -0500

Prevent invoking I/O conversion casts via functional/attribute notation.

PG 8.4 added a built-in feature for casting pretty much any data type to
string types (text, varchar, etc).  We allowed this to work in any of the
historically-allowed syntaxes: CAST(x AS text), x::text, text(x), or
x.text.  However, multiple complaints have shown that it's too easy to
invoke such casts unintentionally in the latter two styles, particularly
field selection.  To cure the problem with the narrowest possible change
of behavior, disallow use of I/O conversion casts from composite types to
string types via functional/attribute syntax.  The new functionality is
still available via cast syntax.

In passing, document the equivalence of functional and attribute syntax
in a more visible place.

It's not that "name" is a reserved word or not, it's that it's the name
of a datatype that's considered to be of string category; so you can
cast just about anything to a name.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] regexp_replace failing on 9.0.4

[Rob Sargent]

On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] 
test machine and my 9.1.2[3] dev box all is fine


This is may statement

update cms.segment_data s
set text = regexp_replace(s.text,
 '(^.*)ns/acres/pathology/dx/1.5(.*$)',
E'\\1ns/acres/pathology/dx/1.6\\2')
from
  dm.payload_segment p,
  support.fix_pathdx_namespace f
where s.id  = p.segment_data_id
and p.item_id = f.item_id
and p.content_version = f.maxversion
;

"UPDATE 76" reported, but zero changes persist



When I just select the regexp like so

select legacy_id,
regexp_replace( substring(s.text, 1, 150) ,
'(^.*)ns/acres/pathology/dx/1.5(.*$)',
E'\\1ns/acres/pathology/dx/1.6\\2')
from
  dm.payload_segment p,
  support.fix_pathdx_namespace f,
  cms.segment_data s
where s.id  = p.segment_data_id
  and p.item_id = f.item_id
  and p.content_version = f.maxversion
;

I get the corrected data (chiefly the "1.6")

Can anyone see where I've gone off track?



[1]PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc 
(SUSE Linux) 4.5.0 20100604 [gcc-4_5-branch revision 160292], 64-bit


[2]PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc 
(SUSE Linux) 4.5.0 20100604 [gcc-4_5-branch revision 160292], 64-bit


[3]PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc 
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace failing on 9.0.4

[Tom Lane]

Rob Sargent  writes:
> On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] 
> test machine and my 9.1.2[3] dev box all is fine

AFAICS from the commit logs, there were no changes affecting the regex
code between 9.0.3 and 9.0.4.  I'm suspicious that your data is
different on the different servers.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Addled index

[Oleg Alexeev]

On 17 March 2013 08:30, Tom Lane  wrote:

> Oleg Alexeev  writes:
> > * it is varchar columns, 256 and 32 symbols length
> > * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8
> > * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms,
> > commit_delay = 100, commit_siblings = 10, checkpoint_timeout = 20min,
> > checkpoint_completion_target = 0.7
> > * postgres 9.2.3 installed via yum repository for version 9.2
> > * 64 bit Centos 6, installed and updated from yum repository
>
> fsync off?  Have you had any power failures or other system crashes?
> ext4 is *way* more prone than ext3 was to corrupt data when fsync is
> disabled, because it caches and reorders writes much more aggressively.
>
> > Database located on software md raid 1 based on two SSD disks array. Ext4
> > filesystem. Database is master node.
>
> Meh.  I quote from the RHEL6 documentation (Storage Administration
> Guide, Chapter 20: Solid-State Disk Deployment Guidelines):
>
> > Red Hat also warns that software RAID levels 1, 4, 5, and 6 are not
> > recommended for use on SSDs.
>
>
> https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/html/Storage_Administration_Guide/newmds-ssdtuning.html
>
> The part of the docs I'm looking at only asserts that performance is
> bad, but considering that it's a deprecated combination, it may well be
> that there are data-loss bugs in there.  I'd certainly suggest making
> sure you are on a *recent* kernel.  If that doesn't help, reconsider
> your filesystem choices.
>
> (Disclaimer: I work for Red Hat, but not in the filesystem group,
> so I don't necessarily know what I'm talking about.  But I have the
> feeling you have chosen a configuration that's pretty bleeding-edge
> for RHEL6.)
>
> regards, tom lane
>


I think fsync=off was really bad idea.



-- 
Oleg V Alexeev
E:oalex...@gmail.com

Re: [GENERAL] regexp_replace failing on 9.0.4

[Rob Sargent]

On 03/18/2013 01:19 PM, Tom Lane wrote:

Rob Sargent  writes:

On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2]
test machine and my 9.1.2[3] dev box all is fine


AFAICS from the commit logs, there were no changes affecting the regex
code between 9.0.3 and 9.0.4.  I'm suspicious that your data is
different on the different servers.

regards, tom lane

Good to hear, thought I might have glossed over the telling release note 
- my usual mo


I restored my dev db from prod just for this run. test and prod are out 
of sync by a couple of weeks.


What I had to do ultimately was as follows,

create table support.duplicate_pathdx_namespace
as select item_id, legacy_id, name, locked_by, maxversion,
   regexp_replace(substring(content,1,150),
  '(^.*)ns/acres/pathology/dx/1.5(.*$)',
   E'\\1ns/acres/pathology/dx/1.6\\2')
  || substring(content, 151) as content
from support.fix_pathdx_namespace;

and the update finally stuck, then copied that reconstructed textblock 
over to the target production table in an regexp-less update.  Doing the 
reg_exp_replace on the whole text blog as part of create temp table did 
not work.


I'll double check my dev server from same pre-run dump.

Weird.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace failing on 9.0.4

[Tom Lane]

Rob Sargent  writes:
> On 03/18/2013 01:19 PM, Tom Lane wrote:
>> Rob Sargent  writes:
>>> On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2]
>>> test machine and my 9.1.2[3] dev box all is fine

>> AFAICS from the commit logs, there were no changes affecting the regex
>> code between 9.0.3 and 9.0.4.  I'm suspicious that your data is
>> different on the different servers.

> Good to hear, thought I might have glossed over the telling release note 
> - my usual mo

Maybe we're barking up the wrong tree by suspecting the regex itself.
Perhaps the updates were suppressed by a trigger, or the transaction
rolled back instead of committing, or some such?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Ian Pilcher]

On 03/18/2013 02:01 AM, Craig Ringer wrote:
> This appears to match Ian's description of having a validation-only cert
> list and a separate list of certs used to verify clients. I'd like to
> follow Apache's model:

Ready for some more good news?

It's possible that I'm missing something, but Apache (mod_ssl) appears
to exhibit the exact same behavior.

Tested on Fedora 18, with the following packages:

  httpd-2.4.3-15.fc18.x86_64
  mod_ssl-2.4.3-15.fc18.x86_64
  openssl-1.0.1e-3.fc18.x86_64

I have set the following in /etc/httpd/conf.d/ssl.conf:

  Listen 3 https
  
  ServerName postgres.example.com
  SSLCertificateFile /etc/pki/tls/certs/postgres.crt
  SSLCertificateKeyFile /etc/pki/tls/private/postgres.key
  SSLCACertificateFile /etc/pki/tls/certs/client-ca.chain
  SSLCADNRequestFile /etc/pki/tls/certs/client-ca.crt
  SSLVerifyClient require
  SSLVerifyDepth  10

Notes:

  * The port is set to 3, because that's hard-coded into the
(attached) test client.
  * I am using the certificates that I previously sent.
  * ServerName is set to postgres.example.com to match its certificate.
  * postgres.crt contains its entire chain (postgres.crt + server-ca.crt
+ root-ca.crt), so that I don't have to specify a
SSLCertificateChainFile.
  * client-ca.chain is client-ca.crt + root-ca.crt.  As with PostgreSQL,
I found that I have to provide the root CA certificate in order for
any client to connect.

With this configuration, the test client is able to connect with the
"good" client certificate, but it is also able to connect with the "bad"
client certificate when it presents a certificate chain that includes
the server CA certificate.

-- 

Ian Pilcher arequip...@gmail.com
Sometimes there's nothing left to do but crash and burn...or die trying.

#include 
#include 
#include 

#include 
#include 
#include 

#include 
#include 

int main(int argc, char *argv[])
{
SSL_CTX *ctx;
SSL *ssl;
struct sockaddr_in server_addr;
int sock_fd;

if (argc != 3) {
	fprintf(stderr, "USAGE: %s  \n", argv[0]);
	exit(__LINE__);
}

SSL_load_error_strings();
SSL_library_init();

if ((ctx = SSL_CTX_new(SSLv23_client_method())) == NULL) {
	ERR_print_errors_fp(stderr);
	exit(__LINE__);
}

if (SSL_CTX_use_certificate_chain_file(ctx, argv[1]) != 1) {
	ERR_print_errors_fp(stderr);
	exit(__LINE__);
}

if (SSL_CTX_use_PrivateKey_file(ctx, argv[2], SSL_FILETYPE_PEM) != 1) {
	ERR_print_errors_fp(stderr);
	exit(__LINE__);
}

if (SSL_CTX_check_private_key(ctx) != 1) {
	ERR_print_errors_fp(stderr);
	exit(__LINE__);
}

memset(&server_addr, 0, sizeof server_addr);
server_addr.sin_family = AF_INET;
server_addr.sin_port = htons(3);
server_addr.sin_addr.s_addr = htonl(INADDR_LOOPBACK);

if ((sock_fd = socket(AF_INET, SOCK_STREAM, 0)) == -1) {
	perror("socket");
	exit(__LINE__);
}

if (connect(sock_fd, (struct sockaddr *)&server_addr,
		sizeof server_addr) == -1) {
	perror("connect");
	exit(__LINE__);
}

puts("Connected.  Starting SSL handshake.");

if ((ssl = SSL_new(ctx)) == NULL) {
	ERR_print_errors_fp(stderr);
	exit(__LINE__);
}

if (SSL_set_fd(ssl, sock_fd) == 0) {
	ERR_print_errors_fp(stderr);
	exit(__LINE__);
}

if (SSL_connect(ssl) != 1) {
	ERR_print_errors_fp(stderr);
	exit(__LINE__);
}

puts("SSL handshake successful.  Shutting down.");

return 0;
}

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace failing on 9.0.4

[Rob Sargent]

On 03/18/2013 02:40 PM, Tom Lane wrote:

Rob Sargent  writes:

On 03/18/2013 01:19 PM, Tom Lane wrote:

Rob Sargent  writes:

On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2]
test machine and my 9.1.2[3] dev box all is fine



AFAICS from the commit logs, there were no changes affecting the regex
code between 9.0.3 and 9.0.4.  I'm suspicious that your data is
different on the different servers.



Good to hear, thought I might have glossed over the telling release note
- my usual mo


Maybe we're barking up the wrong tree by suspecting the regex itself.
Perhaps the updates were suppressed by a trigger, or the transaction
rolled back instead of committing, or some such?

regards, tom lane


The work was all rolled into a function:
  o find the chapters;
  o copy the necessary data (mainly the text blob) into a back-out 
table

  o "lock" the chapters (protect them from exposure to the client app)
  o perform the regexp_replace as the update to prod. table

The function was exec'd in a tx and committed, leaving the back-out 
table and the programmatic locks in place, but the update itself had 
been a no-op and continued to be with ad hoc update statements, until I 
hit the final goofy answer ( rg_replace(string, start) || substring(end) )


Have not yet had a chance to re-create on dev.  Test worked like a charm.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Roadmap for Postgres on AIX

[Wasim Arif]

Hi guys,

What is the road map for Postgres on the AIX platform? I understand that
the pg build farm contains an AIX 5.3 server; are there any plans to
upgrade to 6.1 and 7.1?
Our servers run on AIX and we are evaluating using Postgres as the RDBMS.
Part of my evaluation is understanding where the pg team is headed with
AIX, and if it is considered a supported platform for
the foreseeable future.

Any pointers would be appreciated.

Thanks,
Wasim.

Re: [GENERAL] DB design advice: lots of small tables?

[Kevin Grittner]

Shaun Thomas  wrote:
> On 03/15/2013 08:36 AM, Kevin Grittner wrote:
>
>> I occasionally hear someone maintaining that having a meaningless
>> sequential ID column as the primary key of each table is required
>> by the relational model.
>
> You know, I've heard you mention this a couple times, and I still don't
> understand why you maintain such a position. Artificial the linkages may be, 
> but
> unless I'm missing something, the level of abstraction is often necessary to
> avoid excessive storage requirements and bulky foreign keys.
>
> For me, it comes down to two things:
>
> 1. Copy a "natural" arbitrary-length text string possibly millions of
> times, or use a 32/64-bit integer lookup value.
> 2. Use multiple natural columns in a primary key, necessitating copying all
> columns to child tables for foreign keys, or use one surrogate key for both.

When I was working for the Wisconsin Courts the Circuit Court
databases had some tables which were maintained by a central Court
Operations group and some tables holding data generated by each
county.  The primary key of each county maintained table included a
county number (smallint).  Most county-maintained tables were
related to court cases, and included a court case number as part of
the primary key (varchar(14)).  Beyond that, other fields were
added to each table, and where there was a natural heirarchy to the
data the "child" normally had the PK of its parent and something to
make it unique within that set.  Often this was a unique number
that started at 1 for each parent.  Some of these tables, when
replicated to the central state-wide site, had hundreds of millions
of rows.

You would probably be surprised how many queries were able to
optimize much better than they could have with a single-column ID
in each table.  The plans often surprised me, and when I would
force the planner to take the plan which seemed obvious to me, 90%
of the time the planner had me beat.  The other times often
suggested new indexes or adjustments to cost factors which
benefited the whole workload.

> Yes, it complicates the schema.

That's not the point.

> But until someone comes up with a database storage method that automatically 
> deduplicates stored data, I
> can never advocate using arbitrary strings as natural keys, no matter how 
> unique
> and meaningful they are.

If micro-managing disk space usage it the primary concern,
single-column synthetic keys are likely to win in many
circumstances.  I have no doubt that there are cases where it can
benefit performance.  I can guarantee you that many of the queries
we ran at Wisconsin Courts would have been much slower with such
synthetic keys because they limit the available plans and force
some unnatural "navigation".  I know because I saw cases where
people forced the type of navigation the planner would need to do
if synthetic keys were used, and the result was performance orders
of magnitude worse.

> Maybe I just say that because I was burned by just such a column in a previous
> engagement. It was 64-characters of arbitrary text, and was used as a lookup
> value for dozens of tables. Had it been mapped to a "meaningless"
> surrogate key, several tables would have been halved (or more) in size.
> Don't even get me started on indexing that horrible monstrosity in every
> table it lived in.

That is getting pretty extreme.

> Small tables? Yeah, whatever. I don't care. But anything that has the
> potential to be duplicated millions of times? You better bet that's going to
> be a serial identifier.

Like I said, Wis Cts. has a table that has hundreds of millions of
rows and the primary key is a smallint, a varchar(14), and another
smallint.  You can poke around in it at this site by drilling down
on the "Court Record Events" button for a case:

http://wcca.wicourts.gov/

> Now, you've been doing this longer than I have, in a wider array of
> contexts, so you clearly have some perspective I don't. From where I'm
> sitting though, I don't get the barely suppressed rage. ;)

Oh, every time a programmer who had been working in Access or MySQL
was hired, I had to have this discussion all over again.  You're
ripping the scabs off the old wounds from those battles.  :-)  I usually
had to pull out plans from complex (and fast!) queries to make my
point.

The other thing is that it brings back memories from the early '80s
of working in a database product called TOTAL which required
explicit navigation over links.  Relational technology was such a
breath of fresh air compared to that, I dread a pendulum swing back
to that.

That's been my experience, anyway.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Roadmap for Postgres on AIX

[John R Pierce]

On 3/18/2013 3:39 PM, Wasim Arif wrote:


What is the road map for Postgres on the AIX platform? I understand 
that the pg build farm contains an AIX 5.3 server; are there any plans 
to upgrade to 6.1 and 7.1?
Our servers run on AIX and we are evaluating using Postgres as the 
RDBMS. Part of my evaluation is understanding where the pg team is 
headed with AIX, and if it is considered a supported platform for 
the foreseeable future.


I have been building postgres for my companies inhouse use on AIX 6.1 
without any issues.   I am using IBM XLC to compile it, and I'm building 
it with plpgsql and plperl support, but not any of the others (we don't 
use them).   I had to compile my own zlib and readline libraries to go 
with it, which I static link to simplify deployment.because I link 
it with readline, my builds are GPL tainted and can't be distributed 
as-is, anyways, my employers policies would not allow me to do that.


I use a few tools from the IBM AIX Linux Toolkit for convenience, 
notably gtar, gmake, and wget.   XLC is installed in /usr/vac


my build script looks something like...

|cd $HOME/src|
|wget http:||///zlib-1.2.5.tar.gz|
|gtar xzf zlib-||1.2||.||5||.tar.gz|
|cd zlib-||1.2||.||5|
|export OBJECT_MODE=||64|
|CC=/usr/vac/bin/xlc CFLAGS=||"-q64 -mcpu=pwr6 -O2"| 
|AR=||"/usr/bin/ar"| |./configure --prefix=$HOME --||64| |--||static|

|gmake clean|
|gmake|
|gmake install|

|cd $HOME/src|
|wget http:||///readline-6.1.tar.gz|
|gtar xzf readline-||6.1||.tar.gz|
|cd readline-||6.1|
|export OBJECT_MODE=||64|
|CC=/usr/vac/bin/xlc CFLAGS=||"-q64"| |AR=||"/usr/bin/ar"| |./configure 
--prefix=$HOME --||64| |--||static|

|gmake clean|
|gmake|
|gmake install

|
|cd ~/src|
|wget http:||///postgresql-9.1.6.tar.gz|
|gtar xzf postgresql-||9.1||.||6||.tar.gz|
|cd postgresql-||9.1||.||6|

|export OBJECT_MODE=||64|
|CC=/usr/vacpp/bin/xlc CFLAGS=||"-q64 -I$HOME/src/include -O2 
-qarch=pwr5 -qtune=balanced"| |\|

|||LDFLAGS=||"-L$HOME/src/lib"| |AR=||"/usr/bin/ar"| |\|
|||./configure --prefix=/opt/pgsql91|
|gmake clean|
|gmake|
|cd contrib|
|gmake|
|cd ..|
|gmake check|
|su -c "gmake install"|

|cd /opt|
|gtar cvzf /tmp/postgresql-||9.1||.||6||-AIX-||1.0||.tar.gz pgsql91|

this tarball is what we use on our production servers, we run postgres 
from /opt/pgsql91/...




--
john r pierce  37N 122W
somewhere on the middle of the left coast

[GENERAL] Surge 2013 CFP Open

[Katherine Jeschke]

The Surge 2013 CFP is open. For details or to submit a paper, please visit
http://surge.omniti.com/2013

-- 
Katherine Jeschke
Director of Marketing and Creative Services
OmniTI Computer Consulting, Inc.
11830 West Market Place, Suite F
Fulton, MD 20759
O: 240-646-0770, 222
F: 301-497-2001
C: 443/643-6140
omniti.com
Surge 2013 

The information contained in this electronic message and any attached
documents is privileged, confidential, and protected from disclosure.  If
you are not the intended recipient, note that any review, disclosure,
copying, distribution, or use of the contents of this electronic message or
any attached documents is prohibited. If you have received this
communication in error, please destroy it and notify us immediately by
telephone (1-443-325-1360) or by electronic mail (i...@omniti.com). Thank
you.

[GENERAL] .backup file documentation

[Christophe Pettus]

Are is the contents of the .backup file (generated by pg_stop_backup()) 
documented anywhere?  (Some of it is self-explanatory, of course).  If not, is 
there a quick summary of what START WAL LOCATION, STOP WAL LOCATION, and 
CHECKPOINT LOCATION are?

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enforcing Parameterised Nested Loop Join Order for Foreign Table Joins

[Adam Zegelin]

Tom,

Thank you for your prompt reply. Your advice has pointed me in the right 
direction.

I now have the wrapper identifying columns that are inputs to the web service, 
and thus parameterisable. The ec_classes, left_join_clauses and 
right_join_clauses trees are scanned for Var exprs that match these attributes. 
If they are present, the relid is added to the required list of outer rels for 
the path -- this is done as an extension to the logic I posted previously.

In all cases this seems to work, except one. A join between 3 tables. The 
foreign table has 2 parameterised columns, each given a restriction based on 
one of the other two tables:

adam=# explain select * from l1, l2, foreign1 where foreign1.a = l1.a and 
foreign1.b = l2.a;
 QUERY PLAN


 Merge Join  (cost=5000704.96..5001278.44 rows=37822 width=168)
   Merge Cond: (l2.a = foreign1.b)
   ->  Sort  (cost=85.43..88.50 rows=1230 width=36)
 Sort Key: l2.a
 ->  Seq Scan on l2  (cost=0.00..22.30 rows=1230 width=36)
   ->  Sort  (cost=5000619.54..5000634.91 rows=6150 width=132)
 Sort Key: foreign1.b
 ->  Merge Join  (cost=5000135.26..5000232.51 rows=6150 width=132)
   Merge Cond: (foreign1.a = l1.a)
   ->  Sort  (cost=549.83..552.33 rows=1000 width=96)
 Sort Key: foreign1.a
 ->  Foreign Scan on foreign1  
(cost=500.00..500.00 rows=1000 width=96)
   ->  Sort  (cost=85.43..88.50 rows=1230 width=36)
 Sort Key: l1.a
 ->  Seq Scan on l1  (cost=0.00..22.30 rows=1230 
width=36)

My path generation logic seems to work:

baserel->cheapest_parameterized_paths = (
   {FOREIGNPATH
   :pathtype 120
   :parent_relids (b 3)
   :required_outer (b 1 2)
   :rows 500
   :startup_cost 0.00
   :total_cost 0.00
   :pathkeys <>
   :fdw_private <>
   }
   {FOREIGNPATH
   :pathtype 120
   :parent_relids (b 3)
   :required_outer (b)
   :rows 1000
   :startup_cost 500.00
   :total_cost 500.00
   :pathkeys <>
   :fdw_private <>
   }
)

Yet the planner picks the non-parameterised path:

ForeignPath* best_path = {FOREIGNPATH
   :pathtype 120
   :parent_relids (b 3)
   :required_outer (b)
   :rows 1000
   :startup_cost 500.00
   :total_cost 500.00
   :pathkeys <>
   :fdw_private <>
   }

I’ve tried adjusting planner tuneables to disable all join types except nested 
loop, and setting `join_collapse_limit` to 1 with no desirable outcome.

Yet, adding a restriction clause between the other two tables forces them to be 
scanned first:

adam=# explain select * from l1, l2, foreign1 where foreign1.a = l1.a and 
foreign1.b = l2.a and l1.b > l2.b;
   QUERY PLAN
-
 Nested Loop  (cost=0.00..2544241.17 rows=12608 width=168)
   ->  Nested Loop  (cost=0.00..22741.17 rows=504300 width=72)
 Join Filter: (l1.b > l2.b)
 ->  Seq Scan on l1  (cost=0.00..22.30 rows=1230 width=36)
 ->  Materialize  (cost=0.00..28.45 rows=1230 width=36)
   ->  Seq Scan on l2  (cost=0.00..22.30 rows=1230 width=36)
   ->  Foreign Scan on foreign1  (cost=0.00..0.00 rows=500 width=96)
 Filter: ((a = l1.a) AND (b = l2.a))


ForeignPath* best_path = {FOREIGNPATH
   :pathtype 120
   :parent_relids (b 3)
   :required_outer (b 1 2)
   :rows 500
   :startup_cost 0.00
   :total_cost 0.00
   :pathkeys <>
   :fdw_private <>
   }


On 18/03/2013, at 4:09 PM, Tom Lane  wrote:

> Adam Zegelin  writes:
>> Some service endpoints have no concept of unqualified queries. In the 
>> example above, a ‘sequence scan’ of Bing is a not possible.
> 
> In that case, you shouldn't be generating such a path.  But keep in mind
> that this may lead to failure to produce any plan at all for some
> queries.  If the foreign data source is really so broken that it can't
> do that, then you have little choice ... but you shouldn't be thinking
> of that as anything but a broken design decision on their part.

I tried adding a condition that would prevent the non-parameterised path from 
being generated if the service only supported parameterised scans. Postgres 
refuses to generate a plan: "ERROR:  could not devise a query plan for the 
given query". I did a bit of digging and this error is generated by 
pathnode.c:set_cheapest . As there is no non-parameterised 
`cheapest_total_path` the error is raised (line 253).

For now, I just add an expensive non-pramerterised path and let the FDW throw 
an error if no qual is found involving the required columns.

Regards,
Adam
  

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Roadmap for Postgres on AIX

[Tom Lane]

Wasim Arif  writes:
> What is the road map for Postgres on the AIX platform? I understand that
> the pg build farm contains an AIX 5.3 server; are there any plans to
> upgrade to 6.1 and 7.1?

The reason there's an AIX 5.3 buildfarm member is that someone cares
enough about preserving portability to that platform to provide a
buildfarm member.  If you're worried about other AIX releases, I suggest
you do likewise.  It's not a huge burden.  Basic info about it is here:
http://buildfarm.postgresql.org/cgi-bin/register-form.pl

By and large, our approach to AIX is the same as to any other platform:
we'll support it as long as users of the platform provide testing and
any necessary portability fixes.  That burden might fall more directly
on you, as one of a relatively small number of users, than it would on
somebody using say Linux or BSD.  But as long as you are willing to do
some of the work you need not fear that we're planning to abandon you.

(FWIW, I believe there was a more-recent AIX version in the buildfarm
till recently.  Don't know why its owner stopped running it.)

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Enforcing Parameterised Nested Loop Join Order for Foreign Table Joins

[Tom Lane]

Adam Zegelin  writes:
> My path generation logic seems to work:

> baserel->cheapest_parameterized_paths = (
>{FOREIGNPATH
>:pathtype 120
>:parent_relids (b 3)
>:required_outer (b 1 2)
>:rows 500
>:startup_cost 0.00
>:total_cost 0.00
>:pathkeys <>
>:fdw_private <>
>}
>{FOREIGNPATH
>:pathtype 120
>:parent_relids (b 3)
>:required_outer (b)
>:rows 1000
>:startup_cost 500.00
>:total_cost 500.00
>:pathkeys <>
>:fdw_private <>
>}
> )

I think you missed my point: you should not be insisting on a maximal
set of required outer rels.

In this particular case, it won't generate a cross-product join of l1
and l2 because there's a heuristic that says that's unlikely to be a
good idea.  But in related cases, there could be join order restrictions
that *require* us not to do the joins in that order; so even if you
could talk us out of applying that heuristic, this code is still subject
to undesirable failures.  You really need to provide three paths using
the three possible combinations of outer rels.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Analyzing the types of prepared statements

[John DeSoi]

On Mar 16, 2013, at 8:30 AM, Jason Dusek  wrote:

> However, it is not clear to me at this juncture how to get the
> return type for a statement, given its text. Preparing and
> looking it up in pg_prepared_statements will retrieve the
> argument types but not the return type. Wrapping the query in a
> SQL stored procedure which returns record doesn't cause the
> server to infer and store a more specific type.

You might want to take a look at the extended query protocol here:

http://www.postgresql.org/docs/current/interactive/protocol-flow.html

If you send a "parse" (P) messaged followed by "describe" (D), you can get a 
row description for the return type without executing anything.

John DeSoi, Ph.D.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] .backup file documentation

[Ray Stell]

On Mar 18, 2013, at 7:36 PM, Christophe Pettus wrote:

> Are is the contents of the .backup file (generated by pg_stop_backup()) 
> documented anywhere?  (Some of it is self-explanatory, of course).  If not, 
> is there a quick summary of what START WAL LOCATION, STOP WAL LOCATION, and 
> CHECKPOINT LOCATION are?

http://www.postgresql.org/docs/9.2/static/functions-admin.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace failing on 9.0.4

[Rob Sargentg]

Maybe we're barking up the wrong tree by suspecting the regex itself.
Perhaps the updates were suppressed by a trigger, or the transaction
rolled back instead of committing, or some such?

regards, tom lane
Barking mad, more like it.  I had rolled  back the execution of my 
function in my dev env. and running again produces the same result as 
experienced in production system.


Why the update of the text type field view regexp_replace is failing 
still confuses me, but that's pretty much my natural state.  The size of 
the text field ranges from 7.5k to 24k char.



Here is the update (again)
  update cms.segment_data s
  set text = regexp_replace(f.content, 
'(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2')

  from
dm.payload_segment p,
support.fix_pathdx_namespace f
  where s.id  = p.segment_data_id
  and p.item_id = f.item_id
  and p.content_version = f.maxversion
  ;



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Craig Ringer]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/18/2013 08:55 PM, Stephen Frost wrote:
> Makes sense to me. I'm not particular about the names, but isn't this
> set of CAs generally considered intermediary? Eg: 'trusted', '
> intermediate', etc?
They are intermediary, but we're dealing with the case where trust and
authorization are not the same thing. Trust stems from the trusted root
in the SSL CA model, but that's a chain of trust for *identity*
(authentication), not *authorization*.

Bob J. Criminal might well have a client certificate from a trusted
authority proving that he's who he says he is (he's authenticated) but
we sure as hell don't want to authorize his access to anything.

That's where the intermediate certs come in. We might say "Only users
with certificates issued by our corporate HR team are authorized to
connect to our servers". This is a root of trust, but this time it's a
root of trust to *authorize*, not just to authenticate.

The usual SSL terminology doesn't consider this, because it's a simple
back and white trust model where authenticated = authorized.

I guess that suggests we should be calling this something like
'ssl_authorized_client_roots'.

- -- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.13 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJRR/dqAAoJELBXNkqjr+S2TV4H/3f9Hnf9JhSuGhWblh2adgTJ
Rkdx/9RbByJDMJP0s0c8C1sXaWZGJmKmLhJoes4IIvOVW85SVUa9WoT+UBJPdx9P
esUNsSLFokLqom3TxNRZOHaloyZ+OZafSUnKCwMOIvD0hIehrS3Wcg70QMSj06tX
h22BVhA8bzO1Wdg9UdD98jcuWdEbLgWzVtvIXjICcMJ1azgiF1VY4zwUUbBJBfLG
UIA7+2TtVaXQuge6qWgId0RTKKrb6cLHXCSQ/rigy0mRH9m/G5jKmqENvLAnafI4
4lSBPyDzNj2fBfP9YgIiAe/EGjnJMWQfBBghQI3QrK2kjOZXtzZoOb4XEjfn3FI=
=u+2j
-END PGP SIGNATURE-



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Stephen Frost]

Craig,

* Craig Ringer (cr...@2ndquadrant.com) wrote:
> They are intermediary, but we're dealing with the case where trust and
> authorization are not the same thing. Trust stems from the trusted root
> in the SSL CA model, but that's a chain of trust for *identity*
> (authentication), not *authorization*.

Oh, I see.

> The usual SSL terminology doesn't consider this, because it's a simple
> back and white trust model where authenticated = authorized.

That's not entirely accurate on a couple of levels.  First, basic SSL is
only concerned with authentication, authorization has historically been
left up to the application.  The more typical approach with the
situation you're describing is to have an organization-level root CA
which you only issue certs against.  If you're using a public CA as your
root, then you need to make sure you know how to ensure only the right
people have access, typically be storing in the mapping table the unique
ID issued by the CA for your users.  It's very rare, from what I've
seen, for public CAs to issue intermediate CAs to organizations to
generate their own certs off of, so I'm a bit confused about how we got
to this point.

What I *have* seen is cross-root-cert trusts (known as the Federal
Bridge in the US government), but that's quite a different thing as you
have multiple self-signed root CAs involved and need to know how to
properly traverse between them based on the trusts which have been
built.

Regarding cross-CAS authorization, there are extended attributes which
are listed in the certificates that applications are expected to look
at when considering authorization for the client.  It's been taken
further than that however, where inter-CA trusts have been defined
with actual mappings between these extended attributes, including
'null' mappings (indicating that CA 'A' doesn't trust attribute 'q'
from CA 'B').

> I guess that suggests we should be calling this something like
> 'ssl_authorized_client_roots'.

I'm no longer convinced that this really makes sense and I'm a bit
worried about the simple authentication issue which I thought was at the
heart of this concern.  Is there anything there that you see as being an
issue with what we're doing currently..?

I do think we want to figure out a way to improve our mapping table to
be able to use more than just the CN, since that can be repeated in
multiple certs issued from a root CA, particularly when there are
intermediary CAs.  One option might be to provide a way to map against a
specific issuing CA, or to a CA in the chain, but there's a lot of risk
to that due to CA churn (in large setups, you're going to have lots of
users who have certs issued from a bunch of different CAs, and those
user certs will roll to new CAs as new badges are issued, for
example..).  It can get to be a real nightmare to try and keep up with
all of the changes at that level.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Craig Ringer]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/19/2013 01:46 PM, Stephen Frost wrote:
> If you're using a public CA as your
> root, then you need to make sure you know how to ensure only the right
> people have access, typically be storing in the mapping table the unique
> ID issued by the CA for your users.
Yep, in most applications I've seen you usually store a list of
authorized SubjectDNs or you just use your own self-signed root and
issue certs from it.

I'm pretty sure I've seen tools match on part of the DN, like the
organisation field, but since I can't remember *where* I'm not sure
that's all that useful.
> It's very rare, from what I've
> seen, for public CAs to issue intermediate CAs to organizations to
> generate their own certs off of, so I'm a bit confused about how we got
> to this point.
I don't know about "very rare" but it's certainly not common outside
very large orgs. I tried to find a CA that'd let me issue intermediate
client certs for 2ndQuadrant but found nobody that'd do it for
certificate volumes less than several thousand new certs a month. I'd
been using intermediate CAs based on my own self-signed CA root quite
heavily in infrastructure elsewhere I was rather surprised that the same
sort of thing wasn't easily available for public CAs.

I get the impression it's fairly common in internal infrastructure,
especially with the cert management tools offered by Microsoft Active
Directory servers, but have no strong information to substantiate this.
Nor do I know whether we need to support this mode of operation.

BTW, This discussion has made me realise that I know less about SSL/TLS
and X.509 certificate extensions than I'd like to when dealing with this
topic. In particular, I don't know whether a CA can issue an
intermediate CA with extensions that restrict it to validly signing only
host certificates for hosts under a particular domain or
user-identifying client certs with CNs under a particular organisation -
and whether, if such extensions exist, applications actually check them
when verifying the certificate trust chain.


> What I *have* seen is cross-root-cert trusts (known as the Federal
> Bridge in the US government), but that's quite a different thing as you
> have multiple self-signed root CAs involved and need to know how to
> properly traverse between them based on the trusts which have been
> built.
Ugh, that's not something I've ever had the ... privilege ... to deal
with before.
>
> I'm no longer convinced that this really makes sense and I'm a bit
> worried about the simple authentication issue which I thought was at the
> heart of this concern. Is there anything there that you see as being an
> issue with what we're doing currently..?
Only for using intermediate certs as authorization roots, and it may be
reasonable to say "we don't support that, use an authorized DN list". Or
come up with a better solution like checking attributes of the SubjectDN
for authorization purposes after validating the signature chain to prove
authenticity.
> I do think we want to figure out a way to improve our mapping table to
> be able to use more than just the CN, since that can be repeated in
> multiple certs issued from a root CA, particularly when there are
> intermediary CAs. One option might be to provide a way to map against a
> specific issuing CA, or to a CA in the chain, but there's a lot of risk
> to that due to CA churn (in large setups, you're going to have lots of
> users who have certs issued from a bunch of different CAs, and those
> user certs will roll to new CAs as new badges are issued, for
> example..). It can get to be a real nightmare to try and keep up with
> all of the changes at that level.
Certificate fingerprint? Easily obtained via most client UIs and via
openssl x509 -in cert.crt -fingerprint, eg:

SHA1 Fingerprint=DA:03:9B:FB:81:69:AB:48:64:3D:35:B4:90:56:CF:F1:24:FE:89:B0

However, if I was managing a group large enough to want cert auth I'd
want to be able to specify something like:

SubjectDNMatches: C=*, ST=*, L=*, O=MyCompany, CN=*

... in which case there'd no longer be a need to restrict trust to
intermediate CAs, you'd just trust the root and restrict the authorized
SubjectDNs. If you don't trust your own root CA not to issue certs in
your company's name to 3rd parties you shouldn't be using it. (Whether
it's actually sane to trust a CA is another argument).

- -- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.13 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJRSAJZAAoJELBXNkqjr+S2JrcIALZebfEW4FbfFI6WOs6qDutr
tz486SlnPV+cf29ex242evSUgNQTz38uFKMIs9EIRfe7sVKz3whn0MARmQY9dKph
CusbXNqcPIBbZIZM1hObaKOnMvNnGk5sxnRh4iKjzcMjqCULG5LVX7bXAXn3PcjA
u3lYlNWONWdmz708QOCgvpui4wEv5+bVuik/CnRdPu+BWAcndJHUMuxZMxkUC/rs
4OjLlEg6BPiXRgIKTFBNsa0vvCyVBUd5ri0RCtxUr5T/L/ORWdM+Ic0nqCEPTqyI
EOtDKu