Re: [GENERAL] time penalties on triggers?

[Kenneth Downs]

Jan Theodore Galkowski wrote:

Does anyone know, or can anyone point to information about how much
triggers penalize inserts in PG tables?  I'm getting a report that it is
substantial, and before I investigate more.  The triggers in question
look like:
  


I use triggers everywhere.  About a year ago I did some very simple 
tests, 1000's of inserts to tables, then 1000s of inserts to the same 
tables with all triggers disabled.


What I found was that the largest factor in performance was the size of 
the row being inserted.  This makes sense since Postgres uses MVCC.


I also found it very hard to pin down the penalty of the trigger, but 
came up with rough figures of 30-50% overhead.  The complexity of the 
trigger did not matter.


This all made sense based on the fact that writing to disk takes 
infinitely more time than processing trigger code, so the penalty seems 
to come into play as overhead in firing the trigger. 






--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] a provocative question?

[Kenneth Downs]

Tom Lane wrote:

"TJ O'Donnell" <[EMAIL PROTECTED]> writes:
  

I ran across this quote on Wikipedia at
http://en.wikipedia.org/wiki/Eudora_%28e-mail_client%29
"Text files are also much safer than databases, in that should disk 
corruption occur, most of the mail is likely to be unaffected, and any 
that is damaged can usually be recovered."



Should probably insert as well the standard disclaimer about Wikipedia.  
Great source of info, but that particular sentence has not been 
corrected yet by the 
forces-that-dictate-everything-ends-up-correct-sooner-or-later to point 
out the design trade-offs between simple systems like files (or paper 
for that matter) vs more complex but safer systems such as databases.


And no, I wont write it :)



This is mostly FUD.  You can get data out of a damaged database, too.
(I'd also point out that modern filesystems are nearly as complicated
as databases --- try getting your "simple" text files back if the
filesystem metadata is fried.)

In the end there is no substitute for a good backup policy...

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
  



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010

Re: [GENERAL] Writing most code in Stored Procedures

[Kenneth Downs]

Guy Rouillier wrote:

Steve Manes wrote:
I'm fairly hardcore about keeping as much business logic as I can in 
the database.  In fact, I only do SELECTs from the application, and 
usually via Views.  All inserts, updates and deletes are via procs.

...
And, yes, it's faster.  Particularly if business logic decisions have 
to be made in context with a transaction.


I have a thread I started ages ago over on the PERFORM list that I'm 
sadly just now being able to provide some insight on.  I'll be 
replying on that thread in more detail, but the short of it turns out 
to be that at least in this one application, using stored procs for 
inserts is slowing down the app considerably.  The app does high 
volume inserts and updates, about 16 million rows a day.  By switching 
from stored procs to inline inserts, elapsed time dropped from 2595 
seconds to 991 seconds for a test run.


So the moral of the story is that, as anyone who has worked 
professionally for a number of years knows, no magic bullets exist. 
General guidelines can be helpful, but each scenario must be 
individually investigated.




I would suggest separating the strategy of server-side biz rules from 
the implementation method of stored procedures.  You can also implement 
biz rules as triggers and stick with inline inserts, updates and 
deletes, which is what we do in my shop.


That being said, the enforcement of the biz rules has to be done 
somewhere, and no matter where it is done it will involve disk reads and 
validations.  It always *seemed* to me that putting the code on a table 
trigger involves the least possible overhead for doing these things. It 
provides complete real-time enforcement within the transaction with no 
network overhead, and has the simplest possible interface: plain old SQL.


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] MVCC cons

[Kenneth Downs]

Tom Lane wrote:

Kenneth Downs <[EMAIL PROTECTED]> writes:
  
Speaking as an end-user, I can give only one I've ever seen, which is 
performance.  Because of MVCC, Postgres's write performance (insert and 
update) appears on my systems to be almost exactly linear to row size.  
Inserting 1000 rows into a table with row size 100 characters takes 
twice as long as inserting 1000 rows into a table with row size 50 
characters.



Not sure why you'd think that's specific to MVCC.  It sounds like purely
an issue of disk write bandwidth.

regards, tom lane
  


I did not see this in MS SQL Server.

--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010

Re: [GENERAL] MVCC cons

[Kenneth Downs]

RPK wrote:

I want to know whether MVCC has cons also. Is it heavy on resources? How
PGSQL MVCC relates with SQL Server 2005 new Snapshot Isolation.
  


Speaking as an end-user, I can give only one I've ever seen, which is 
performance.  Because of MVCC, Postgres's write performance (insert and 
update) appears on my systems to be almost exactly linear to row size.  
Inserting 1000 rows into a table with row size 100 characters takes 
twice as long as inserting 1000 rows into a table with row size 50 
characters.


This tends to be more of an issue for me because my system materializes 
derived information in columns, so my tables are more fat to begin with, 
and so this hits me harder.


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Interesting abilities of substring

[Kenneth Downs]

Tom Lane wrote:

Kenneth Downs <[EMAIL PROTECTED]> writes:
  
Basically, it "knows what you mean" when you do substrings on dates and 
numbers, doing an implicit cast for you.



Implicit casts to text are evil, and are mostly going to be gone in 8.3.
So try not to rely on this behavior ...
  


Based on general principle, or on specific bad things like unexpected or 
ill-defined results?



regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
  



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010

Re: [GENERAL] Interesting abilities of substring

[Kenneth Downs]

Sure, we use a user interface widget called "Ajax Dynamic List" from 
www.dhtmlgoodies.com.  This replaces the HTML SELECT element.


When a user is sitting on a foreign-key field, such as a PATIENT or 
CUSTOMER field, the user can just start typing letters or numbers.  An 
AJAX call is made to the back end which searches all columns in a 
pre-defined list.  If the list is first_name,last_name,dob, and they 
type "ken" they will get everybody whose first name or last name begins 
with 'ken', and the date is ignored.  If they type '1991' they will get 
everybody born in 1991, and of course '1991-10' returns everybody born 
in October 1991.


So it turns out that both string concatenation and substring do some 
nifty implicit typing.  This means making the generalized lookup 
requires you only to know the list of columns, without also needing to 
know their types.


SELECT patient as _value
 ,patient
   || ' - ' || namlst
   || ' - ' || nam1st
   || ' - ' || phone
   || ' - ' || ssn
   || ' - ' || dob as _display   FROM patients WHERE (   
SUBSTRING(LOWER(patient) FROM 1 FOR 2)='ke'

  OR SUBSTRING(LOWER(namlst) FROM 1 FOR 2)='ke'
  OR SUBSTRING(LOWER(nam1st) FROM 1 FOR 2)='ke'
  OR SUBSTRING(LOWER(phone) FROM 1 FOR 2)='ke'
  OR SUBSTRING(LOWER(ssn) FROM 1 FOR 2)='ke'
  OR SUBSTRING(LOWER(dob) FROM 1 FOR 2)='ke') ORDER BY patient 
Limit 20


Eventually I will be driven to optimize that, but on tables < 10,000 
rows it is still well within the threshhold of the human attention span 
of < .5 second or so.


Naz Gassiep wrote:
This may be useful to me and others, care to paste an example of what 
you mean?

Thanks,
- Naz.

Kenneth Downs wrote:
Here is something cool that I did not realize postgres's substring() 
could do.


Basically, it "knows what you mean" when you do substrings on dates 
and numbers, doing an implicit cast for you. This is really nice if 
you happen to be writing a generalized search system, as it makes the 
code significantly simpler...


That's all, now back to our regularly scheduled mailing list.




--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Interesting abilities of substring

[Kenneth Downs]

Here is something cool that I did not realize postgres's substring() 
could do.


Basically, it "knows what you mean" when you do substrings on dates and 
numbers, doing an implicit cast for you. This is really nice if you 
happen to be writing a generalized search system, as it makes the code 
significantly simpler...


That's all, now back to our regularly scheduled mailing list.

--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Linux distro

[Kenneth Downs]

Paolo, I started with linux 6 years ago after being a confirmed 
microsoftie my entire career, this is the experience I can offer:


Ubuntu: What Windows wants to be, what the Mac is w/o the  and with 
more control.  I just replaced a hard drive in a dell machine.  A 
generic windows CD (the customer did not have the specific recovery CD 
for that box) could not install drivers for the network, the video, or 
the sound.  Ubuntu did all of them.  Ubuntu however is a desktop OS.  
Great graphics, great package management.  However, it is still Linux 
and you still have to do some Googling here and there to find a HOWTO.  
Perhaps the most annoying problem is lack of support for widescreen 
monitors, you have to type tech data into an X config to get that 
working.  Also, IMHO stay away from 7.04, I've tried it on two machines 
and had troubles on both, to the extent of wiping one and starting over 
at 6.10.  Stay with 6.10.


Suse: going for the same space as Ubuntu.  I tried it first, 6 years 
ago.  It was ok at the time but can't tell you about the modern stuff.  
I don't trust Novell to get it right though, just a personal feeling.


Fedora I don't use but as I understand there is a large body of people 
with a lot of cultural knowledge about how it works.  So when you go 
Fedora you join the club as it were and do things their way.  I tried 
Red Hat back when it was Red Hat some 9 years ago and again 6 years ago 
and I always found myself stuck on some detail that I could not find an 
answer to. 

Now if you want a hardcore distro to learn everything about linux, go 
with gentoo.  There are no binary packages (at least not that I use or 
can easily find), but you end up knowing *everything* about how Linux 
works.  Very active community.  I used this as a desktop for 3 years as 
a sort of long-term boot camp.  It did make me very comfortable with all 
things linux.


Conclusion: I use gentoo on my servers and Ubuntu on my desktops.  
Except for the virus ^H^H^H^^H gaming machine that I have for the kids 
running XP.


[EMAIL PROTECTED] wrote:

Hello,

I bought a Dell server and I am going to use it for installing PostgrSQL
8.2.4. I always used Windows so far and I would like now to install a
Linux distribution on the new server. Any suggestion on which distribution
? Fedora, Ubuntu server, Suse or others?

Thanks in advance,
Paolo Saudin



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match
  



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Retrieve the record ID

[Kenneth Downs]

We have a system that sends back information by using the RAISE NOTICE.  
I've taken two lines out of it and put them here to illustrate the 
idea.  You may have to play with it to get it right:


NotifyList = 'The OID is:  ' || CAST(new.oid as varchar(10)) || ';';
RAISE NOTICE '%',NotifyList;

Luca Ciciriello wrote:

Hi all.
I'm new to this list and, first of all, I'm a new user of PostgreSQL.
The version I'm using is 8.2.3 and I've the necessity to retrieve, 
using an

application, the ID of a modified (INSERT, UPDATE, DELETE) record of a
triggered table. I wasn't able to find out a way to obtain the 
required ID.

The only information available outside of the DBMS and usable by my app
(subscribed for a significant event) are the table name and the server
process id.
The application is running on Windows XP and the server is installed on
Windows Server 2003.
Any one knows a way to notify my app with the ID of the modified record?
Any idea is appreciated.

Thanks in advance.

Luca

_
Watch all 9 Live Earth concerts live on MSN.  http://liveearth.uk.msn.com


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

      http://www.postgresql.org/docs/faq



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] How to prevent modifications in a tree of rows, based on a condition?

[Kenneth Downs]

Philippe Lang wrote:

Hi,

I have a database which can be simplified in the following way, with
three tables:

An "order" has multiple "order lines", and an "order line" has multiple
"line steps".

I'd like to prevent any kind of modification (insert, update, delete) in
a order (and its lines, and steps) if all the steps in the lines of the
order are "checked". If that condition is not true, a modification is
accepted.
  


We do this with BEFORE triggers.  If the "flag_closed" column = 'Y', the 
modification is prevented.


In the future we are also going to put in an exception for some 
privileged group, such as the "purge" group perhaps, that will allow 
that group to still make deletes, but only deletes.



A brute force solution is to write 3 x 3 triggers, and code that
properly, but I suspect there something more elegant to do with
Postgresql?

Does anyone have an idea maybe? The rule system? Thanks for your ideas.

Philippe Lang

---

CREATE TABLE public.orders
(
  idint4 PRIMARY KEY,
  value int4
) WITHOUT OIDS;

CREATE TABLE public.order_lines
(
  idint4 PRIMARY KEY,
  value int4,
  order_id  int4 REFERENCES public.orders
) WITHOUT OIDS;

CREATE TABLE public.line_steps
(
  idint4 PRIMARY KEY,
  value int4,
  checked   bool,
  order_line_id int4 REFERENCES public.order_lines
) WITHOUT OIDS;

-- Insert values
INSERT INTO orders VALUES (1, 1);

INSERT INTO order_lines VALUES (1, 1, 1);

INSERT INTO line_steps VALUES (1, 1, true, 1);
INSERT INTO line_steps VALUES (2, 2, true, 1);
INSERT INTO line_steps VALUES (3, 3, true, 1);

INSERT INTO order_lines VALUES (2, 2, 1);

INSERT INTO line_steps VALUES (4, 1, true, 2);
INSERT INTO line_steps VALUES (5, 2, true, 2);
INSERT INTO line_steps VALUES (6, 3, true, 2);

INSERT INTO order_lines VALUES (3, 3, 1);

INSERT INTO line_steps VALUES (7, 1, true, 3);
INSERT INTO line_steps VALUES (8, 2, true, 3);
INSERT INTO line_steps VALUES (9, 3, true, 3);

-- Modifications that should be forbidden
UPDATE orders SET value = 123 WHERE id = 1;
UPDATE order_lines SET value = 123 WHERE id = 1;
UPDATE line_steps SET value = 123 WHERE id = 1;

-- We show final data
SELECT 
o.id AS order_id,
o.value AS order_value, 
ol.id AS order_line_id,
ol.value AS order_line_value, 
ls.id AS line_steps_id,

ls.value AS line_step_value,
ls.checked AS check

FROM orders AS o

INNER JOIN order_lines AS ol
ON o.id = ol.order_id

INNER JOIN line_steps AS ls
ON ol.id = ls.order_line_id

ORDER BY o.id, ol.id, ls.id;

---(end of broadcast)---
TIP 1: 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
  



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PGSQL development tools. Any advice?

[Kenneth Downs]

in the shameless self-promotion department, I'm rather fond of my own 
tool, Andromeda. 


http://www.andromeda-project.org

From your post, I think that you will be impressed with our dependency 
tracking, change management, and overall build abilities.  It was born 
on Linux, I'm typing this reply in Ubuntu.  It is gnome/kde agnostic.


What you may not like is that, because it builds all components out of a 
data dictionary, it actually has no facility for managing the creation 
of stored procedures.  It assumes that everything important is in the 
spec, and it uses the spec to build tables and triggers. 

View management is the big missing piece, I simply have not gotten to it 
yet, so I have queries in client code.  I've recently begun to ponder 
the form of views, but can't promise anything.  I am still looking for 
that first contributor though (hint hint).


Bottom line, it was designed to handle systems of hundreds of tables 
based on my experience as system architect of similar systems.  To get 
to my goals I threw away some things that people may be surprised to 
find missing, but the strategy played out and I got to where you want to 
go, though perhaps not by the route you might expect.




Vincenzo Romano wrote:

Hi all.
I'd like to use a development tool for my PGSQL tables, indices and,
most important thing, views and functions.
Writing, fixing and maintaining functions and views is getting more
and more complicated as they are growing in number and complexity.

The tool that more closely mathces my ideas is Druid
(http://druid.sourceforge.net/)
It's quite good with table and indices, but cannot handle
properly the dependencies for views and functions.

I've seen other tools aimed to database management, but
they lack real "development" features.

Is there advice for a better tool to be run under Linux/KDE?

MTIA!

  



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] SQL Manager 2007 for PostgreSQL released

[Kenneth Downs]

Nikolay Samokhvalov wrote:

On 22 May 2007 17:47:46 -0700, EMS Database Management Solutions
(SQLManager.net) <[EMAIL PROTECTED]> wrote:

We, here at EMS Database Management Solutions, are pleased to announce
SQL Manager 2007 for PostgreSQL - the new major version of the
powerful PostgreSQL administration and development tool!


I'd be happy to use EMS products, but I really need Linux versions.
Any plans regarding *nix platforms support?




Ditto, when will we see the *nix versions?

--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Paypal and "going root"

[Kenneth Downs]

Richard Huxton wrote:

Kenneth Downs wrote:

Richard Huxton wrote:

Kenneth Downs wrote:
The last one left that I have is the sticky issue of a paypal IPN 
transaction coming in.  I believe it applies generally to financial 
transactions.  The user is sent by our application to the Paypal 
site.  When they pay, paypal sends a POST with various information 
that we need.  The user does not see this, it is behind the 
scenes.  The POST request must run as an anonymous user because I 
have no state whatsoever.  But the request must also commit 
financial data.  This creates a vulnerability, at least in theory.


Well, your POST will be authenticating as some sort of PG user, 
presumably. Give it its own account and make sure the only 
permissions it has is to insert into the paypal_rcpt table (or call 
a function that does it for you). Obviously it will only connect 
from the webserver(s) and only from the apache user account (or 
IIS/whatever). So, you can use the ~/.pgpass password file to keep 
that password protected.


I think this is the answer that I need.  This goes to the heart of 
how the user connects to PG.  The key concept that I'm taking away 
from your answer is that instead of connecting as a powerful user, 
connect as a severely limited user who can do only one thing: make 
that insert.  The rest should be conducted from there.


Ah, that's exactly what I was trying to say. Apologies if I phrased it 
badly, but you seem to have the gist anyway.


I can put some rules on the receipts table that require the row to 
contain various hashes and verification codes obtained from the 
invoice table, and the user who inserts to this table must have no 
ability to read any other table in the system, so they cannot obtain 
the codes by any means.  In converse, I believe normal users should 
not be able to read or write this table, it would be completely 
invisible to your average Joe.


You might want to allow inserts and have a "validated" flag that you 
can check. Failing that, make sure you log the values on a failed 
insert - always useful to have an audit trail if there are problems.




If I can go off on a tangent, my Andromeda framework handles cases like 
this well, though I'm a bit embarrassed I did not think of it myself :)


The receipts table can be specified to fetch values from the invoice 
table, compare them to the inserted values, and reject the insert if 
they do not match.  Assuming they match, the invoices table has a count 
of receipts, when that number hits 1, it sets its valid flag, and can 
push the flag to the various items purchases.  None of this requires any 
code, which is why I'm mentioning it, it all is done as part of the 
database table definitions.


The logging of failures would actually be accomplished if I allowed 
failed inserts to proceed, but simply did not mark them as valid.  We 
could then "debug the data" as it were by just looking at what was inserted.



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Paypal and "going root"

[Kenneth Downs]

Richard Huxton wrote:

Kenneth Downs wrote:
The last one left that I have is the sticky issue of a paypal IPN 
transaction coming in.  I believe it applies generally to financial 
transactions.  The user is sent by our application to the Paypal 
site.  When they pay, paypal sends a POST with various information 
that we need.  The user does not see this, it is behind the scenes.  
The POST request must run as an anonymous user because I have no 
state whatsoever.  But the request must also commit financial data.  
This creates a vulnerability, at least in theory.


Well, your POST will be authenticating as some sort of PG user, 
presumably. Give it its own account and make sure the only permissions 
it has is to insert into the paypal_rcpt table (or call a function 
that does it for you). Obviously it will only connect from the 
webserver(s) and only from the apache user account (or IIS/whatever). 
So, you can use the ~/.pgpass password file to keep that password 
protected.


I think this is the answer that I need.  This goes to the heart of how 
the user connects to PG.  The key concept that I'm taking away from your 
answer is that instead of connecting as a powerful user, connect as a 
severely limited user who can do only one thing: make that insert.  The 
rest should be conducted from there.


I can put some rules on the receipts table that require the row to 
contain various hashes and verification codes obtained from the invoice 
table, and the user who inserts to this table must have no ability to 
read any other table in the system, so they cannot obtain the codes by 
any means.  In converse, I believe normal users should not be able to 
read or write this table, it would be completely invisible to your 
average Joe.



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Paypal and "going root"

[Kenneth Downs]

Dave Page wrote:

Kenneth Downs wrote:
  

The last one left that I have is the sticky issue of a paypal IPN
transaction coming in.  I believe it applies generally to financial
transactions.  The user is sent by our application to the Paypal site. 
When they pay, paypal sends a POST with various information that we

need.  The user does not see this, it is behind the scenes.  The POST
request must run as an anonymous user because I have no state
whatsoever.  But the request must also commit financial data.  This
creates a vulnerability, at least in theory.  There are fields contained
in the transaction meant to allow confirmation and prevent fraud, but I
just don't like that idea of running anonymously and committing
financial data.

In this case it seems creating a stored procedure will not automatically
help, as then we just execute the SP anonymously, and it strikes me as
no different.

Has anybody pondered this and come up with anything?




In response to the incoming IPN you can create a connection back to the
paypal server to validate it. Iirc, you basically just send the entire
request back again and it returns 'VERIFIED'.
  


Ah yes, that's true, thanks for the wake-up on that one.

--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010

[GENERAL] Paypal and "going root"

[Kenneth Downs]

I am seeking to have a system in which it is never necessary for 
application code to "go root" w/respect to the database server, where 
all commands issued to a server are as a regular logged in user with 
their privileges.


There are two holes I know of here.  Thanks to Tom I've got the answer 
to the first one: which is creating users.  We will implement stored 
procedures that create users and grant privileges, and then grant 
execute privileges to these stored procedures.  This means we don't have 
to "go root" to grant membership in groups.


The last one left that I have is the sticky issue of a paypal IPN 
transaction coming in.  I believe it applies generally to financial 
transactions.  The user is sent by our application to the Paypal site.  
When they pay, paypal sends a POST with various information that we 
need.  The user does not see this, it is behind the scenes.  The POST 
request must run as an anonymous user because I have no state 
whatsoever.  But the request must also commit financial data.  This 
creates a vulnerability, at least in theory.  There are fields contained 
in the transaction meant to allow confirmation and prevent fraud, but I 
just don't like that idea of running anonymously and committing 
financial data.


In this case it seems creating a stored procedure will not automatically 
help, as then we just execute the SP anonymously, and it strikes me as 
no different.


Has anybody pondered this and come up with anything?

--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] plperl functions not re-entrant?

[Kenneth Downs]

Martijn van Oosterhout wrote:

On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote:
  
What I have noticed is that once the innermost instance exits, none of 
the outer instances execute any further, suggesting that the plperl 
routine is not "re-entrant" (if I am using that term correctly).



Doesn't sound right, do you have a test case?

  


I've finally gotten back around to this.  Here is my test case.  There 
are three tables with one row in each.


Level_one: one row
Level_two: one row
Level_thr:  one row

The idea is that an AFTER STATEMENT UPDATE trigger on Level_thr calls a 
plperl routine.  The plperl routine has a hardcoded 3-pass loop that 
updates table Level_two.


The Level_Two table also has an AFTER STATEMENT UPDATE trigger that 
calls the same plperl routine.  The routine this time goes into its 
3-pass loop and updates Level_one.


The result is that there should be 9 passes altogether, and the 
accumulator in table level_one should have the value 9.  Except it only 
has the value 5, because the values from these two invocations of the 
routine are leaking into each other.


Here is the code to reproduce:

/*
*  This code builds the three tables, they are
*  all the same and all simple
*/
create table level_one ( col1 char(5),total int);
create table level_two ( col1 char(5),total int);
create table level_thr ( col1 char(5),total int);

/*
* Here is the re-entrant code.  For this example it
* is simplified and hard-coded.
*/
create or replace function ReEntrantTester() returns trigger as
$BODY$
  if($_TD->{relname} eq'level_thr') {
   $table_to_update = 'level_two' ;
  }
  else {
   $table_to_update = 'level_one' ;
  }
  elog(NOTICE,"We are in ".$_TD->{relname}." and we'd hit 
$table_to_update");

  $increment  =1;
  for(my $i=1; $i <= 3; $i++) {
 elog(NOTICE,"Updating $table_to_update, pass $i of 3, adding 
$increment");

 $qu="UPDATE $table_to_update SET total = total + $increment";
 elog(NOTICE,$qu);
 spi_exec_query($qu);
  }
  return;
$BODY$
language plperl SECURITY DEFINER;

/*
*  Now create two statement level triggers on level 3
*  and level 2 tables that each rolls up to the next
*  higher level.
*/
CREATE TRIGGER level_thr_aft_stm
   AFTER UPDATE ON level_thr
   FOR EACH STATEMENT EXECUTE PROCEDURE ReEntrantTester();
CREATE TRIGGER level_two_aft_stm
   AFTER UPDATE ON level_two
   FOR EACH STATEMENT EXECUTE PROCEDURE ReEntrantTester();


/*
* This code clears the tables to get ready for a run
*/
delete from level_one;
insert into level_one (col1,total) values ('LEV-1',0);

delete from level_two;
insert into level_two (col1,total) values ('LEV-2',0);

delete from level_thr;
insert into level_thr (col1,total) values ('LEV-3',0);

/*
* Before running the test, make sure the value in
* the level_one table is zero:
*/
select * from level_one;


/*
* PULL THE TRIGGER: This is the code that shows if it
* works or not.  If the perl routine is re-entrant, then
* the value in level_one will be 9.  Else it will not.
*/
UPDATE level_thr SET total=99;


The update that I get is this:


NOTICE:  We are in level_thr and we'd hit level_two
NOTICE:  Updating level_two, pass 1 of 3, adding 1
NOTICE:  UPDATE level_two SET total = total + 1
NOTICE:  We are in level_two and we'd hit level_one
CONTEXT:  SQL statement "UPDATE level_two SET total = total + 1"
NOTICE:  Updating level_one, pass 1 of 3, adding 1
CONTEXT:  SQL statement "UPDATE level_two SET total = total + 1"
NOTICE:  UPDATE level_one SET total = total + 1
CONTEXT:  SQL statement "UPDATE level_one SET total = total + 1"
NOTICE:  Updating level_one, pass 2 of 3, adding 1
CONTEXT:  SQL statement "UPDATE level_one SET total = total + 1"
NOTICE:  UPDATE level_one SET total = total + 1
CONTEXT:  SQL statement "UPDATE level_one SET total = total + 1"
NOTICE:  Updating level_one, pass 3 of 3, adding 1
CONTEXT:  SQL statement "UPDATE level_one SET total = total + 1"
NOTICE:  UPDATE level_one SET total = total + 1
CONTEXT:  SQL statement "UPDATE level_one SET total = total + 1"
NOTICE:  Updating level_one, pass 2 of 3, adding 1
NOTICE:  UPDATE level_one SET total = total + 1
NOTICE:  Updating level_one, pass 3 of 3, adding 1
NOTICE:  UPDATE level_one SET total = total + 1


Notice that the outermost loop does not finish correctly, it has somehow 
changed its mind on what table to update, is not actually executing the 
queries, but still has kept track of its iterations.


SELECT * FROM level_one gives:

"LEV-1";5


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-379-7200   Fax: 631-689-0527

Re: [GENERAL] Auditing a database

[Kenneth Downs]

Ask the question: can I make sure I always have a complete trail?  If 
you insert the old row, you will always have the old values and the 
table itself holds the new values.


Germán Hüttemann Arza wrote:

Hi,

I am developing a web application for auditing tables from a postgresql 
database.


My question is: when an update occurrs in the base table, should I insert in 
the auditing table the new record or the old one?


I was first inserting the new one but a job partner, who are testing the 
application suggested that probably would be better to insert the old record 
because it is simpler to follow the trace of updates.


What you suggest?

Regards,

  



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-379-7200   Fax: 631-689-0527


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Postgres data/form entry tool

[Kenneth Downs]

If you want end-users to be doing the data entry, Andromeda may be what 
you want.


http://www.andromeda-project.org/

The project is aimed at more complicated cases and may carry too much 
overhead for what you want, but perhaps not.



David Potts wrote:

Can any body recommend a generic opensource  data entry tool that can be
used to make some simple forms for entering data in to postgres?


---(end of broadcast)---
TIP 6: explain analyze is your friend
  



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-379-7200   Fax: 631-689-0527


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Cron'd dumpall failing?

[Kenneth Downs]

I truly hoping I'm missing something silly here.  I've got a cron job to 
run a dumpall each early am.  It fails, and I get a handful of emails.  
The first reads like this:


pg_dump: [archiver (db)] connection to database "adocs" failed: FATAL:  sorry, 
too many clients already
pg_dumpall: pg_dump failed on database "adocs", exiting


...and then as we go along we get this one repeating for each database:

pg_dump: WARNING:  out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of shared memory
HINT:  You may need to increase max_locks_per_transaction.
pg_dump: The command was: SELECT sequence_name, last_value, increment_by, CASE WHEN 
increment_by > 0 AND max_value = 9223372036854775807 THEN NULL  WHEN increment_by 
< 0 AND max_value = -1 THEN NULL  ELSE max_value END AS max_value, CASE WHEN 
increment_by > 0 AND min_value = 1 THEN NULL  WHEN increment_by < 0 AND min_value 
= -9223372036854775807 THEN NULL  ELSE min_value END AS min_value, cache_value, 
is_cycled, is_called from tabproj_skey
pg_dumpall: pg_dump failed on database "X", exiting




The cron entry (for user root) is

* 1 * * * /root/dumpall.sh > /dev/null

and the routine in question is this:

pg_dumpall -U postgres  > /home/bups/bsource/pg/dhost2.dumpall
chown bups:root /home/bups/bsource/pg/dhost2.dumpall
chmod 600   /home/bups/bsource/pg/dhost2.dumpall




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] cutting out the middleperl

[Kenneth Downs]

Merlin Moncure wrote:

On 3/27/07, Randal L. Schwartz  wrote:

>>>>> "Kenneth" == Kenneth Downs <[EMAIL PROTECTED]> writes:

Kenneth> This in effect makes the web server a proxy to the database, 
which
Kenneth> sounds like what you are after.  The "P" portion for us is 
PHP, not
Kenneth> Perl, and it is small though non-zero.  It has only two jobs 
really.
Kenneth> In the one direction it converts HTTP requests into SQL, and 
in the

Kenneth> other it converts SQL results into HTML.

How do you control trust?  I presume you're not accepting raw SQL 
queries (or
even snippets) over the wire, so you have to have enough server-side 
mapping

code to map domain objects into database objects and domain verbs into
queries, and then authenticate and authorize that this verb is 
permitted by

the incoming user. That can't be just a trivial amount of code.  That's
usually a serious pile of code.

And please don't tell me you do all of that client-side. :)


looking at his project, it looks like you create tables and forms
using simple rule based system. very elegant imo, although I would
greatly prefer to to have the rules be in tables them selves, so I can
manipulate with sql, or self hosting dialogs.   very interesting
project i must sasy, it feels like alphora but with web spin on it.


Actually we do put the rules in the tables and you can execute SQL 
directly, something that I so much take for granted now that I sometimes 
have to remind myself that most of the world cannot do this! 

If the website is not giving that impression, I'll have to correct that, 
ouch!  Can you tell me what gave you the impression we were just about 
web forms?



Thanks for the comments, "elegant", now that's something I'll have to 
forward to Mom :)





merlin

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] cutting out the middleperl

[Kenneth Downs]

Randal L. Schwartz wrote:

"Kenneth" == Kenneth Downs <[EMAIL PROTECTED]> writes:



Kenneth> This in effect makes the web server a proxy to the database, which
Kenneth> sounds like what you are after.  The "P" portion for us is PHP, not
Kenneth> Perl, and it is small though non-zero.  It has only two jobs really.
Kenneth> In the one direction it converts HTTP requests into SQL, and in the
Kenneth> other it converts SQL results into HTML.

How do you control trust?  I presume you're not accepting raw SQL queries (or
even snippets) over the wire, so you have to have enough server-side mapping
code to map domain objects into database objects and domain verbs into
queries, and then authenticate and authorize that this verb is permitted by
the incoming user. That can't be just a trivial amount of code.  That's
usually a serious pile of code.

  


In a proxy or quasi-proxy situation the simplest scenario is direct 
table access, all other scenarios are more complicated and reduce to 
table access in the end.  So because the problem must be considered in 
terms of table access we ask what is required to pull that off, and the 
answer is:


a) the database is implementing security
b) users are using real accounts instead of connecting as a superuser 
and having the client do the security


When this is the case, there are only two implementation issues.  The 
first is how to manage trust (or authentication), and the second is the 
mundane issue of how to encode the queries.


Just a couple of weeks ago we discussed the trust issue, it comes down 
to the known design tradeoffs off HTTPS, sessions, dongles, user habits 
and so forth.  'nuf said on that.


As for the mundane question of how to encode the queries, the KISS 
principle says they will come over looking like HTML FORM actions (post 
or get).  So you'll have a list of input values with some hidden 
variables that control the action.


You need precious little code to translate these into SQL if you have a 
description of the database, we use the old-fashioned term "data 
dictionary" for this.  Our data dictionary lists the column names, types 
and sizes for each table (among other things).  Since all simple SQL 
commands are lists of column names and values, the SQL generation is 
child's play.  Our typical code might look like this:


if(gp('gp_mode')=='ins') {  // gp() retrieves a get/post variable
  $rowvalues=aFromGP("txt_");  // convert group of post vars into an 
associative array

  $table=gp('gp_table');   // fetch the table name from the stream
  SQLX_insert($table,$rowvalues);  // this routine generates an insert 
statement

}

The server will throw an error for constraint violations or security 
violations, the web layer doesn't concern itself with these things 
except to report them.


The only thing the web layer need do is handle the escaping of quotes to 
prevent SQL injection, but again, this is only to prevent the user from 
shooting himself in the foot, anything he injects we'd be happy to 
execute for him, since it all runs at his security level!


The shocking conclusion from points a) and b) at the top of this reply 
is this:  there is absolutely no difference, from a security 
perspective, between these this HTTP request:


index.php?gp_table=example&gp_mode=ins&txt_colname=value&txt_colname=value

and this one:

index.php?gp_sql=insert+into+example+(column1,column2)+values+(value1,value2)

Amazing!  The simple fact is the user is either authorized to execute 
the query or he isn't.  If you connect to the database using his 
credentials then let him inject all the SQL he wants, if that's his idea 
of fun.




And please don't tell me you do all of that client-side. :)

  


Well, since you said please, and since we don't do it, I won't say it.

Re: [GENERAL] cutting out the middleperl

[Kenneth Downs]

Kev, we have a GPL'd product targeting Postgres that has significant 
overlap with what you want, though in other areas we went in another 
direction.  The site is www.andromeda-project.org, and I've put some 
comments below:


Kev wrote:

Hi everyone,

I'm still in the design phase of a project.  I was just wondering if
anyone has any thoughts or experience on the idea of cutting the P out
of the LAMP (or in my case, WAMP for now) stack.  What I mean is
having
everything encapsulated into sql (or plpgsql or plperl where needed)
functions stored in the pgsql server, and have Apache communicate with
pgsql via a tiny C program that pretty much just checks whether the
incoming function is on the allowed list and has the proper data
types,
then passes it straight in.  Any errors are logged as potential
security
breaches.
  


Andromeda's goal is to implement all biz rules: constraints, automations 
and security, in the server. 

This in effect makes the web server a proxy to the database, which 
sounds like what you are after.   The "P" portion for us is PHP, not 
Perl, and it is small though non-zero.  It has only two jobs really.  In 
the one direction it converts HTTP requests into SQL, and in the other 
it converts SQL results into HTML.


In terms of experience, I sat down to write the first code 33 months 
ago, and it began to pay my bills about six months later.  All of the 
commercial bragging stuff is on the company website: http://www.secdat.com.



I'm really new to mod_perl too, so another question would be if this
would be much faster than a simple perl script that did the same
thing.
  


Can't say there.  My personal preference is for PHP because I can't 
understand Perl five minutes after I've written it.



I ask this because I realize I need to carefully check data coming
into
pgsql functions as well as at the client end.  Why maintain a bunch of
scripts with names similar to the functions they're calling and all
performing similar checks anyway?
  


Well actually we tackled that problem by decided to *preserve* direct 
table access through SQL as the standard API, which I realize is not the 
standard, but for the life of me I can't understand why, since it is 
such an amazingly simpler way to get what everyone says they are after.


Here's what I mean.  We write out a database spec in a plaintext file 
that includes security, constraints, and automations.  A "builder" 
program then generates the DDL, encodes the biz logic in triggers, and 
assigns table sel/ins/upd/del permissions to the tables.


No messy API to remember or manage.  Just specify the tables and 
columns, who can do what, and what the formulas are, and its all automatic.


A huge benefit to this is the basic ability to manipulate user's 
databases through direct SQL. 

It's also IMHO the only way to ensure that you can accomplish the task 
of having the web server be a proxy.  Its easy to convert HTTP into 
simple SQL insert/update etc., much harder to make it try to learn an API.



I was kinda salivating at the thought of how fast things would be if
you
cut out the A as well, by using a Flash applet to give socket access
to
JavaScript.  But then I guess you have to make your pgsql server
itself
publicly accessible on some port.  Is that just asking for trouble?

I appreciate any comments or thoughts anyone might have on this.

Thanks,
Kev


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq
  



---(end of broadcast)---
TIP 1: 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: [GENERAL] plperl function called > once in cascading triggers

[Kenneth Downs]

Martijn van Oosterhout wrote:

On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote:
  
What I have noticed is that once the innermost instance exits, none of 
the outer instances execute any further, suggesting that the plperl 
routine is not "re-entrant" (if I am using that term correctly).



Doesn't sound right, do you have a test case?

Have a nice day,
  


Yes, but it is all tied up in my framework.  I'll put together a 
hardcoded example.

[GENERAL] plperl function called > once in cascading triggers

[Kenneth Downs]

Consider the case where an AFTER STATEMENT trigger calls a plperl 
function which performs a loop.  Inside of the loop it updates at least 
one other table.


The table being updated has an AFTER STATEMENT trigger that calls the 
same plperl function (w/different parms of course), which goes into 
another loop. 


Presumably this could go to any number of levels.

What I have noticed is that once the innermost instance exits, none of 
the outer instances execute any further, suggesting that the plperl 
routine is not "re-entrant" (if I am using that term correctly).


Is this right?  Am I missing something?


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Debugging Server Code

[Kenneth Downs]

I'm guessing that there is no step-wise debugger for pgsql.  If there 
is, then glory be and call me a dummy for not R'ing TFM. 

In fact, I've never heard of a step-wise debugger for any DB server, am 
I wrong?


As I grovel through some plPerl code in a statement level trigger that 
depends on an array of variables, I wonder if the lack of such a 
debugger can be considered one of the biggest impediments to larger 
adoption of server-side code.  I don't mean to be facetious here, how 
many serious and powerful programmers do you know who don't depend 
heavily on a good debugger to step through the code and find the nasty 
parts?  Anyway, just a thought.


We now return to our regularly scheduled mailing list...

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Alvaro Herrera wrote:

David Legault escribió:
  

That's basically what I've done with my past questions on the ROLE system in
place. Since roles are global, I wanted it fine grained to the DB level so I
had to append DB_ in front of each role name and by using current_database()
inside my functions, I could hide that from the exterior.



Hmm, there used to be a facility to restrict users to specific
databases, enabled by db_user_namespace (not by default).

It seems to still work on 8.2 ...

  


there is also the 'samegroup' facility in pg_hba.conf.  We create a 
group named after each database, and a person cannot get into a database 
unless they are in that group.

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Awesome!  That never occurred to me.  This is really cool.

Tom Lane wrote:

Kenneth Downs <[EMAIL PROTECTED]> writes:
  
Perhaps a lesser form of CREATEROLE, CREATEROLE_LIMITED, who can create 
roles and only grant to the roles he himself is a member of.



You can make that out of spare parts today, by granting non-superusers
execute rights on functions that create users.

regression=# create or replace function makeuser(text) returns void as $$
begin
  execute 'create role ' || quote_ident($1) || ' login';
end$$ language plpgsql security definer;
CREATE FUNCTION
regression=# revoke all on function makeuser(text) from public;
REVOKE
regression=# create user joe;
CREATE ROLE
regression=# grant execute on function makeuser(text) to joe;
GRANT
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> create user foo;
ERROR:  permission denied to create role
regression=> select makeuser('foo');
 makeuser
--

(1 row)

regression=> \c - foo
You are now connected to database "regression" as user "foo".
regression=>

regards, tom lane
  

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Tom Lane wrote:

Kenneth Downs <[EMAIL PROTECTED]> writes:
  
The biggest security limitation we have is actually a weakness in 
Postgres - the inability to restrict the abilities of a user with 
CREATUSER rights, they can make somebody who can do anything.  For 
higher security this requires no ability for public registration of 
accounts.  This would be solved if we could restrict a CREATUSER user to 
only GRANTing to roles they themselves are in.



I thought about this for awhile, but I think you are missing the reason
why it's designed the way it is.  The point of CREATEROLE privilege is
to be a slightly safer form of superuser: that is, to allow the DBA to
do all his day-to-day management of user accounts without being a real
superuser who can corrupt the database arbitrarily badly.  If we
restricted CREATEROLE as you suggest, then either DBAs would have to
make their CREATEROLE account a member of every role they manage, or
they'd have to run as real superusers.  Either choice represents a
significant increase in the capabilities of the CREATEROLE account and
thus more chance for mistakes.  So while a miscreant with CREATEROLE
can certainly avail himself of any database privilege short of
superuserness, in the intended use of the feature it is actually
possible for DBAs to operate with *fewer* privileges than they would
need to get useful work done if we adopted your suggestion.
  


Tom, it sounds like you've thought this through, and I can't disagree 
with the reality of what DBA's are doing, but does it have to be one or 
the other?


Perhaps a lesser form of CREATEROLE, CREATEROLE_LIMITED, who can create 
roles and only grant to the roles he himself is a member of.


This suggestion I think would be in line with your own reasoning.  Just 
as CREATEROLE is a lesser SUPERUSER, so CREATEROLE_LIMITED is the next 
logical extension, a lesser CREATEROLE.


At any rate, I hope I can convince somebody, cuz ole Ken don't code in C 
no more :)

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Kevin Hunter wrote:

What about an SQL injection bug that allows for increased privileges?


Um, web programming 101 is that you escape quotes on user-supplied 
inputs.  That ends SQL injection.


Pardon my naivete (I'm fairly new to web/DB programming) . . . is this 
the current standard method of protection from SQL injection?  How 
does it compare to SQL preparation with bound variables?


When you use SQL Prepared statements it is normal for the db driver to 
escape out the variables for you.  Well at least it is in PHP, I can't 
say for other systems.




Kevin



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Bill Moran wrote:
If a user has not logged in, that is, if they are an anonymous visitor, 
the web framework will connect to the database as the default "public" 
user.  Our system is deny-by-default, so this user cannot actually read 
from any table unless specifically granted permission.  In the case 
being discussed, the public user is given SELECT permission on some 
columns of the insurance carriers table, and on the schedules table.
  
Huh.  Does that imply that the web framework still holds a number of 
different DB credentials?  Or does each user need to supply their 
specific DB credentials as their authentication so the web framework is 
merely a proxy to the DB?


(Having recently discovered a major security oversight in one of my 
employer's webapps, my mind's hot on this kind of thing.)



What's hot in my mind is "how do you securely maintain the database connection
information between page loads?"

  



I suppose we could ask JP Morgan Chase bank what they do.  As I 
mentioned to Kevin, sooner or later the security implementation comes 
down to sessions, the user's protection of their password, whether to 
use certificates, whether to use dongles, etc.

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Kevin Hunter wrote:


If a user has not logged in, that is, if they are an anonymous 
visitor, the web framework will connect to the database as the 
default "public" user.  Our system is deny-by-default, so this user 
cannot actually read from any table unless specifically granted 
permission.  In the case being discussed, the public user is given 
SELECT permission on some columns of the insurance carriers table, 
and on the schedules table.


Huh.  Does that imply that the web framework still holds a number of 
different DB credentials?  Or does each user need to supply their 
specific DB credentials as their authentication so the web framework 
is merely a proxy to the DB?


Yes, exactly, the web framework can be thought of as a proxy, it is 
connecting to the DB using credentials provided by the user.


Which, I will take pains to point out, is far far superior to having it 
connect as a super-user and then trusting that the code is bug-free.  
Ouch, I don't even want to think about that one.


But anyway, once we arrive at this point you arrive at the standard 
questions surrounding session security and the possible use of 
certificates.  The system is now as secure as your user's password 
habits and your server's general security. 




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/07 10:02, Kenneth Downs wrote:
  

Karsten Hilbert wrote:


On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

 
  

First, security is defined directly in terms of tables, it is not
arbitrated by code.  The "public" group has SELECT access to the
articles table and the schedules tables, that's it.  If a person
figures out how our links work and tries to access the "claims" table
it will simply come up blank (and we get an email).



How ?

Karsten
  
  

If a user has not logged in, that is, if they are an anonymous visitor,
the web framework will connect to the database as the default "public"
user.  Our system is deny-by-default, so this user cannot actually read
from any table unless specifically granted permission.  In the case
being discussed, the public user is given SELECT permission on some
columns of the insurance carriers table, and on the schedules table.

The column-level security is important, as you don't want anybody seeing
the provider id!

If the user figures out our URL scheme, they might try something like
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the
patients table", except that the public user has no privilege on the
table.  The db server will throw a permission denied error.



What about an SQL injection bug that allows for increased privileges?
  


Um, web programming 101 is that you escape quotes on user-supplied 
inputs.  That ends SQL injection.


After that, as stated above, anything the user attempts is executed at 
his privilege level.  For an anonymous user, that's the lowest.


The biggest security limitation we have is actually a weakness in 
Postgres - the inability to restrict the abilities of a user with 
CREATUSER rights, they can make somebody who can do anything.  For 
higher security this requires no ability for public registration of 
accounts.  This would be solved if we could restrict a CREATUSER user to 
only GRANTing to roles they themselves are in.

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Karsten Hilbert wrote:
If the user figures out our URL scheme, they might try something like 
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the 
patients table", except that the public user has no privilege on the 
table.  The db server will throw a permission denied error.



My interest was more towards the "we get an email" part.
What level do you send that from ? A trigger ?


  


The web framework does that.  The web framework decodes the HTTP request 
and executes any SQL it thinks the user wants.  If there is a 
permissions error then it sends an email to the administrator.


The underlying idea is that the GET/POST parameters are pretty standard 
and easy to decode and convert into SQL commands.  For instance, by 
default we assume a page = a table, and lacking any code that overrides 
that assumption, a request for a page becomes a search request in the 
table of the same name.  This is the first thing a cracker would depend 
upon if he were trying to pry.

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Karsten Hilbert wrote:

On Fri, Mar 09, 2007 at 08:08:11AM -0500, Kenneth Downs wrote:

  
First, security is defined directly in terms of tables, it is not 
arbitrated by code.  The "public" group has SELECT access to the 
articles table and the schedules tables, that's it.  If a person figures 
out how our links work and tries to access the "claims" table it will 
simply come up blank (and we get an email).


How ?

Karsten
  



If a user has not logged in, that is, if they are an anonymous visitor, 
the web framework will connect to the database as the default "public" 
user.  Our system is deny-by-default, so this user cannot actually read 
from any table unless specifically granted permission.  In the case 
being discussed, the public user is given SELECT permission on some 
columns of the insurance carriers table, and on the schedules table.


The column-level security is important, as you don't want anybody seeing 
the provider id!


If the user figures out our URL scheme, they might try something like 
"?gp_page=patients" and say "Wow I'm clever I'm going to look at the 
patients table", except that the public user has no privilege on the 
table.  The db server will throw a permission denied error.

Re: HIPPA (was Re: [GENERAL] Anyone know ...)

[Kenneth Downs]

Ron Johnson wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/08/07 20:38, Kenneth Downs wrote:
[snip]
  

Management and we are about to add the CRM to it so that the
scheduling/billing database also serves the doctor's public website,



Is that wise?  One bug and a cracker is poking around some very
private stuff!!
  


We use the "Spartan" security model rather than perimeter defense, which 
gives us the confidence to do things that others may not.


The general outline is as follows.

First, security is defined directly in terms of tables, it is not 
arbitrated by code.  The "public" group has SELECT access to the 
articles table and the schedules tables, that's it.  If a person figures 
out how our links work and tries to access the "claims" table it will 
simply come up blank (and we get an email).  The "staff" group has 
read/write access to scheduling, and so forth.


Second, the security assignments to tables are generated by a builder, 
which revokes and grants all priveleges to all groups on all tables.  
This is to prevent  the possibility of error or omission if a person 
were to implement it table-by-table.


Third, as you probably have guessed, we do not connect to the database 
as a super-user and then arbitrate in code.  Regular staff connect with 
real database accounts and their security in the database is limited to 
those accounts.  Bugs in code that try to provide unauthorized 
information will return server errors.  A person with no account, such 
as the public user, has the lowest security priveleges, as mentioned above.


Finally, our URL parameter scheme is really very simple and easy to 
figure out, we are not hiding it.  Anybody trying to get something they 
can't would not take long to work it out (especially as the underlying 
tools are GPL), they would simply find it did them no good. 

It is good to be very concerned about security, especially HIPPA, where 
there are legal ramifications.  It is also very good to be able to 
provide convenience and security in one package.

Re: [GENERAL] Anyone know a good opensource CRM that actually installs with Posgtres?

[Kenneth Downs]

Bradley Kieser wrote:
I hope that someone has cracked this one because I have run into a 
brick wall the entire week and after 3 all-nighters with bad 
installations, I would appreciate hearing from others!


I am looking for a decent OpenSource CRM system that will run with 
Postgres. SugarCRM seems to be the most popular but it's MySQL-centric 
and its opensource parts are very restricted.




Bradley, I've got about 2.5 out of 3 of what you are looking for, 
perhaps it might work out for you.  We have a GPL database application 
framework that we have used for a handful of CRM-style applications 
(links below).  It runs on Postgres, is completely GPL, and is written 
in PHP.


Now the bad news :)

We have only crude CRM stuff, and you may end up having to put more into 
this area than you want to.  As I said, we use it ourselves for some 
stuff, but our needs are simple in that area.  Its primary purpose is 
high-powered business database apps.


Now on the third hand, we have a pure business app for Medical Practice 
Management and we are about to add the CRM to it so that the 
scheduling/billing database also serves the doctor's public website, 
doing things like showing schedules, listing active insurances and other 
nifty stuff like that.  And of course the doc can enter new articles.  
We think its really cool to be able to integrate CRM with business this way.


The only other bad news is that it is Linux only.  It has been installed 
on Mac but nobody here can support you with that.  In principle it can 
run on Windows because Apache, PHP and Postgres run on windows, but 
again, you'd become the guy I send other people to once you get it going :)


Here are three CRM sites running it.  They are my company site, the 
project site itself, and a site for a rental home:


www.secdat.com
www.andromeda-project.org
www.manisteeforestretreat.com

The middle one is the actual project, its got some docs, some tutorials, 
and a link to the sourceforge download.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] M:M table conditional delete for parents

[Kenneth Downs]

[EMAIL PROTECTED] wrote:


Postgresql 8.1.4 on Redhat 9

I have a table which stores M:M relationships. I can't put foreign 
keys to the parents of this table because the relationships being 
stored go to several tables. This was done so that only two fields 
have to be searched in order for all relationships to be found for an 
item. For an oem number there might be 50 to 100 relationships and 40 
different tables having to do with materials, locations, revisions, 
specifications, customer, etc. that might be referenced.


Have you considered creating real cross-reference tables (aka M:M) 
between all pairs of tables, and then having a view that UNIONs them 
together?


This way you don't have to re-invent the foreign key to get it all working.



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?

Re: [GENERAL] RFC tool to support development / operations work with slony replicated databases

[Kenneth Downs]

Andrew Hammond wrote:

Each release will include a directory that has the same name as the
full release tag. This directory must contain all the scripts to be
applied.


Have you considered using a data dictionary instead, so that you can 
simply diff the structures and generate DDL to bring a database to its 
current state?


If your scripts contain data modification code, they can be preserved, 
but using a dictionary smooths over a lot of the quirkiness of the 
scripts-based approach.



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] usage for 'with recursive'?

[Kenneth Downs]

hubert depesz lubaczewski wrote:

On 3/2/07, Kenneth Downs <[EMAIL PROTECTED]> wrote:

This reminds me of another advantage of the WITH RECURSIVE, which is
that it pushes to overhead to SELECT, with no associated write-time
overheads.


hmm .. why do you consider this as advantage? i would say it's rather 
drawback.



One school of thought aims for overall system performance gains by 
keeping transactions as small as possible.  WITH RECURSIVE allows an 
UPDATE to affect exactly one row, where other methods affect more rows.  
Therefore the WITH RECURSIVE gives you the smallest possible transaction 
at write time.


Further, it seems the actual number of rows pulled in all approaches 
should be the same, so now I wonder if there really even is any overhead 
at SELECT time, making the argument for WITH RECURSIVE rather conclusive 
I'd say.




depesz

---(end of broadcast)---
TIP 1: 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



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Custom session variables?

[Kenneth Downs]

Adam Rich wrote:


Is there any way to define custom variables per session scope?
In oracle, we do this using package variables.




This would be so cool to have.


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?

Re: [GENERAL] usage for 'with recursive'?

[Kenneth Downs]

hubert depesz lubaczewski wrote:

On 3/1/07, Kenneth Downs <[EMAIL PROTECTED]> wrote:

Better?  I think perhaps different.  There is materialized path, which
requires a very problematic unlimited-length column to hold the path,
and there is upper/lower bounds, which again requires client-side
row-by-row processing.  Both have the unpleasant problem that changes to
one row may affect many others.


there are also other approaches.
i know that they are not that well known, but there are, and they
allow many things to be done with very simple queries.
i use a solution which basically allows me to query every kind of
tree-structure data without loops or recursion. drawbacks are 2 and
are very limited:
1. it uses some triggers
2. it has some (slight in my opinion) overhead.


I see you've moved the client-side code to the server, which is a Good 
Thing in my book.


The overhead would be proportional to the number of rows that have to be 
looked at when a new row goes in.


This reminds me of another advantage of the WITH RECURSIVE, which is 
that it pushes to overhead to SELECT, with no associated write-time 
overheads.



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] usage for 'with recursive'?

[Kenneth Downs]

hubert depesz lubaczewski wrote:

there have been a discussions about how posdtgresql needs 'with
recursive' queries.

not that i would like to object the idea (new feature is always  a
good thing), but is anybody able to show me real usage of this kind of
queries?
as i see it the only usage for 'with recursive' is when one have a
tree-structure stored as:
create table objects (id serial primary key, parent_id int references
objects (id), ...)
and one want to do some "deep queries" without client-side 
recursion/loops.


is it the only thing 'with recursive' is useful for? i mean it sounds
unrealistic given that better data-structures for tree hierarchies
have been proposed and implemented.


Better?  I think perhaps different.  There is materialized path, which 
requires a very problematic unlimited-length column to hold the path, 
and there is upper/lower bounds, which again requires client-side 
row-by-row processing.  Both have the unpleasant problem that changes to 
one row may affect many others.


AFAIK, the "WITH RECURSE" allows the simplest data structure, being 
key/parent_key.  The best benefit of this method is that it is a simple 
foreign key and no action on a row ever affects another row, unlike the 
other two.  If we could query out a list using WITH RECURSE it would 
become very powerful.




best regards,

depesz




--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] General Ledger db design

[Kenneth Downs]

Karl O. Pinc wrote:


You can put triggers into the financial transaction detail table
that says that the batch id has to be valid if it exists
to get your referential integrity right.


Right.


You can also not allow new rows to be inserted if there
is already a batch row, thus the insertion of a
batch row "closes" the batch.


Not sure what you mean, but you can in fact have any number of open 
batches, on the assumption that it is a multi-user system.



Trying to add new credits
or debits or change the values of existing credits or
debits (prevented in the cr/db table's update trigger), things
would cause the batch to go out of balance, are thus
prevented.


Using the batch # as the foreign key allows all batches to be isolated 
from each other.





If something
goes wrong, you've got some extra rows laying about and
you can easily identify them because there's no corresponding
row in in the batches table.  (Your proposal has good error
recovery too, but seems like it's more work to impliment,
as far as having to go back and update the "closed" flag,
and even more instruction needs to be given to the
application programmer come time to use the db.)


Well to be honest I don't manually code any of it, I have a generator 
that does it, I don't trust myself to code something like that properly :)


The code generator lets me do necessary things like sum the transactions 
to the batch row, preventing a close unless they balance, preventing an 
update to the batch row when it is already closed, which as a bonus 
prevents new rows being added, and "Distributing" (as we call it) the 
close flag to the transaction rows when the batch closes.





Karl <[EMAIL PROTECTED]>
Free Software:  "You don't pay back, you pay forward."
 -- Robert A. Heinlein




--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 1: 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: [GENERAL] General Ledger db design

[Kenneth Downs]

Karl O. Pinc wrote:


On 02/25/2007 06:21:45 PM, Kenneth Downs wrote:

Martin Winsler wrote:



This is a real world situation where referential
integrity needs to be broken in theory, I believe.  Does anybody 
have any experience or knowledge of building financial accounting 
databases?  Am I wrong about this?


The problem is that with "double entry accounting" you have records 
in tables that both reference other records in the same table as 
well as different records in other tables depending on some fairly 
complex logic.
For instance an invoice is a financial instrument, so the "parent 
record" would naturally want to be part of a company wide "journal" 
or "ledger."  However, its child records would be actual invoice 
lines as well as two different sets of entries in the general ledger 
detail, all 3 sets of records must agree with each other on the 
invoice parent record total.


The solution I've always used is to introduce a table of batches.  
This is the table that unifies all of the others.  When you post an 
invoice, you generate a new batch, give it type "AR".  The invoice is 
stamped with the batch #, as are the GL transaction rows.   When you 
post an AP voucher, do the same thing.   Same for checks received, 
checks paid, etc, all of them have different batch types.


It's been a while since I've done finance apps but
this is my recollection of the situation.

The above proposal takes care of the data
structure/referential integrity
issues, but does not solve the data integrity issues.

The only way, at present, to solve the data integrity
issues is to write a FOR EACH STATEMENT trigger to be sure that
all the rows agree with each other and everything balances.
But this can only be done after all the data goes into the database.
For instance, insert the credit and debit rows
into a temporary table, then insert from the temporary
table into the actual GL transaction table in one go,
and have a AFTER ... FOR EACH STATEMENT go through
and make sure the entire ledger is still in balance.
From a performance standpoint this bites.


Yeah, there is going to be some kind of extra work here.

My own solution is to add a "closed flag" to the batch and a calculated 
column on the GL entries.  If the closed flag is "N", the calculated 
column is zero, so that the ledger remains in balance while the entries 
are going in one-by-one.


A trigger on the batch table traps the setting of closed="Y" and sets 
the calculated values to the trx values, so the entire batch is 
committed inside of a single transaction.  If the batch is not balanced, 
it will reject a setting of closed="Y".


Other trigger code prevents new entries to a closed batch or the 
re-opening of a batch.



--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] General Ledger db design

[Kenneth Downs]

Martin Winsler wrote:
I hope this isn't too far off topic.  I've noticed some discussion 
about referential integrity, the use of nulls, and database design 
recently here.  This is a real world situation where referential 
integrity needs to be broken in theory, I believe.  Does anybody have 
any experience or knowledge of building financial accounting 
databases?  Am I wrong about this?


The problem is that with "double entry accounting" you have records in 
tables that both reference other records in the same table as well as 
different records in other tables depending on some fairly complex 
logic. 

For instance an invoice is a financial instrument, so the "parent 
record" would naturally want to be part of a company wide "journal" or 
"ledger."  However, its child records would be actual invoice lines as 
well as two different sets of entries in the general ledger detail, 
all 3 sets of records must agree with each other on the invoice parent 
record total.


I can only tell you what I've done in the past, you can take it from there.

First, there are two very basic tables, the chart of accounts and the 
list of transactions.  Every line in the transaction table links to one 
account.


So far so good.  Now we have AR invoices, and AP vouchers.  Let's 
oversimplify for argument and say that when you post an AR invoice you 
post two entries in the transactions table, a debit to AR and a credit 
to sales (forget about tax and stuff for now).  Likewise for an AP 
voucher, debit some expense account and credit AP.


So now the problem is we've got at least two more potential parent 
tables for the transaction, being invoices and vouchers, and it gets 
worse when we add checks received, checks paid, overpayments, allowances 
and so on and so on.


The solution I've always used is to introduce a table of batches.  This 
is the table that unifies all of the others.  When you post an invoice, 
you generate a new batch, give it type "AR".  The invoice is stamped 
with the batch #, as are the GL transaction rows.   When you post an AP 
voucher, do the same thing.   Same for checks received, checks paid, 
etc, all of them have different batch types.


In short, the problem of too many parents is inverted to produce many 
children instead, and the problem goes away.







--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

[Kenneth Downs]

Tomas Vondra wrote:



Store the pictures in the filesystem and only the path, description and
other metadata in the database. My suggestion ;-)


Andreas
  
Don't do that - the filesystems are not transactional (at least not 
the usual ones), so you'll lose the ability to use transactions. 
Imagine what happens when you do an unlink() and then the transaction 
fails for some reason - there's no way to 'rollback' the filesystem 
operation. I've seen this solution (storing images in filesystem) 
mostly in MySQL applications, but that's because of (a) lack of 
transactions in MySQL and (b) somehow sub-optimal handling of binary 
data as MySQL loads all the data even if it's not needed (this was 
true for MySQL 3.23 - I'm not sure about the current releases).




Dumb question: the picture data is written twice, right?  Once in the 
WAL and once into the table?  So the argument can be reduced to:


1)  Load into tables for transactional support, con is the write-time hit
2)  Load into filesystem for faster load, but you have to provide 
integrity by another route




--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?



---(end of broadcast)---
TIP 1: 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: [GENERAL] ROLE INHERIT

[Kenneth Downs]

Tom Lane wrote:

Kenneth Downs <[EMAIL PROTECTED]> writes:
  
Except for the hole.  On a public site that lets users register, we have 
to have  way to let the web server assume the role of somebody who has 
createuser privelege, and that's pretty much the end of the no-root 
policy.  If an exploit could be placed, it could simply go into that 
mode and create a superuser. 



  
What would be really nice is if you could limit the ability of 
CREATEUSER to grant roles.



I believe that a role that has CREATEROLE but not SUPERUSER can only
create non-SUPERUSER roles.  Does that help?

regards, tom lane
  


Probably not.  The problem is that a person with createrole can create 
any role, so by mistake or exploit a user can be given admin access 
(admin here defined by roles given, not by SUPERUSER flag) to another 
database by a role that itself is supposed to be a public-only mostly 
read-only role.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
adr;dom:;;347 Main Street;East Setauket;NY;11733
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
url:http://www.secdat.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] ROLE INHERIT

[Kenneth Downs]

Tom Lane wrote:

"David Legault" <[EMAIL PROTECTED]> writes:
  

I thought it would transfer that CREATEROLE privilege too.



  


I've been dying to get 2 cents in on this.  Tell me if this suggestion 
makes any sense.


We use real database users in our systems, we don't connect in with an 
over-endowed user and then arbitrate security in client code.   
Therefore, we depend entirely upon the server's ability to enforce security.


The practical advantage of this, which is huge, is that nowhere in my 
Postgres settings do I have to make allowance for the web user (apache) 
to "go root" with respect to Postgres.  As a general rule we consider 
this good of course, because a remote exploit on the web server could 
not do anything the user could not do anyway.


Except for the hole.  On a public site that lets users register, we have 
to have  way to let the web server assume the role of somebody who has 
createuser privelege, and that's pretty much the end of the no-root 
policy.  If an exploit could be placed, it could simply go into that 
mode and create a superuser. 

What would be really nice is if you could limit the ability of 
CREATEUSER to grant roles.  A nice general solution would be to allow a 
user with CREATEUSER privelege to only put other users into the same 
groups that person is in, or perhaps into a list specified by a 
higher-privelege user.


What's chances of anything like that showing up?

And, dumb question, am I mistaking the purpose of INHERIT and it already 
does what I'm saying?  I don't think so because INHERIT does not let 
somebody create users out of the void.



This is documented someplace ... ah, under CREATE ROLE:

: The INHERIT attribute governs inheritance of grantable privileges (that
: is, access privileges for database objects and role memberships). It
: does not apply to the special role attributes set by CREATE ROLE and
: ALTER ROLE. For example, being a member of a role with CREATEDB
: privilege does not immediately grant the ability to create databases,
: even if INHERIT is set; it would be necessary to become that role via
: SET ROLE before creating a database.

The main reason we did that is that SUPERUSER seemed a bit too dangerous
to be an inheritable privilege.  You could argue the other role
attribute bits either way, but for simplicity they all act the same.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend
  


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
adr;dom:;;347 Main Street;East Setauket;NY;11733
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
url:http://www.secdat.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions

[Kenneth Downs]

Peter Eisentraut wrote:
The proper fix for this problem is to insert explicit SET search_path 
commands into each affected function to produce a known safe schema 
search path.  Note that using the default search path, which includes a 
reference to the "$user" schema, is not safe when unqualified 
references are intended to be found in the "public" schema and "$user" 
schemas exist or can be created by other users.  It is also not 
recommended to rely on rigorously schema-qualifying all function and 
operator invocations in function source texts, as such measures are 
likely to induce mistakes and will furthermore make the source code 
harder to read and maintain.


  



I do enjoy code generators.  This was a one-line fix for me.

begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
adr;dom:;;347 Main Street;East Setauket;NY;11733
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
url:http://www.secdat.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Using PITR for creating Hot Standby

[Kenneth Downs]

Dhaval Shah wrote:

I am in a situation where we have to deploy a hot standby to a
postgres db server. There is no custom tablespace and all data files
are in $PGDATA

I was thinking of using PITR
[]http://www.postgresql.org/docs/8.1/static/backup-online.html] to
achieve that and here are my thoughts:


Same here.



1. Continuously copy WAL files to the standby.


Right.


2. The standby is always in "recovery" mode, that is whenever it gets
a WAL file, it recovers to that WAL file.


This I decided against, only because traffic does not warrant it.  The 
idea at this point was to let the WAL files pile up in the offsite 
location and then work out a periodic schedule for running them, but it 
would be more like once/week than continuously.


My thinking was that maintaining a 60-second failover is not really what 
I'm after.  If the main server goes down, we would likely wait and hope 
for up to 10 minutes before starting to make DNS changes.  During that 
time we could always run a recovery of the WAL files and have the 
standby easily ready in time.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
adr;dom:;;347 Main Street;East Setauket;NY;11733
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
url:http://www.secdat.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Trouble w/plperl sproc on red hat 9

[Kenneth Downs]

Not sure if this one is fixable, but a user of my GPL'd package was 
unable to run our install.


Eventually we pinned it down to a failed load of a sproc written in 
plperl.  He says he's running a Red Hat 9 system with Postgres 8.1 and 
perl is 5.8.5.  When he takes the plperl sproc and attempts to load it 
through pgsql he gets:



 SQL ERROR : server closed the connection unexpectedly
   This probably means the server terminated abnormally
   before or while processing the request.

Does this ring any bells w/regard to possible version mismatches or 
known issues?
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
adr;dom:;;347 Main Street;East Setauket;NY;11733
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
url:http://www.secdat.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 1: 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: [GENERAL] trigger for pg_authid

[Kenneth Downs]

Diego de Blas wrote:

Hello,
 
I'm trying to set a new trigger for "pg_authid" connected as 
"postgres" but system returns always the same error "Permission 
denied: "pg_authid" is a system catalog"... I have checked privileges 
and I can teorically add new triggers. I don't know whta's wrong...
 
Thanks


We went the other way with this.  We created our own table of users, and 
put a trigger on that so that INSERTS to that table created new users in 
postgres, plus anything else we needed to happen.  We did the same with 
a table of user-group membership.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
adr;dom:;;347 Main Street;East Setauket;NY;11733
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
url:http://www.secdat.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Not so much load balancing as load limits

[Kenneth Downs]

I'm having some trouble getting a google hit on this topic, and the docs 
aren't doing much for me.


What I'm wondering is, how do I limit any particular postgres operation, 
in particular a long-running batch operation with lots of inserts, from 
bogging down a server?


This is not so much a question of how to code the SQL, as it is how to 
keep a selfish process from causing a self-inflicted DoS situation. 

Can anybody get me pointed in the right direction with a link or two?  
Thanks.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Disabling and enabling constraints and triggers to

[Kenneth Downs]

Ken Winter wrote:


Tom et al ~

I understand that this is all a risky business.  So maybe someone can tell
me a safer way to accomplish what I was trying to accomplish with pg_dump
and data-only pg_restore.

It's a basic bit of database administration work.  I'm trying to establish
two (or if necessary more) instances of the same database - a production
instance and a development instance - and a change management process for
coordinating them.  As you can probably guess:

1. The production instance is the one the users are actually using.  Its
data are The Truth.

2. The development instance is where design changes (to tables, procedures,
and all other database objects) are developed, tested, and readied to go
into production.  Its data are of no value except for testing purposes.  


3. The crucial purpose of the change management process is to put into
production each new release of the database design. 


Ken,

The approach that has worked very well for me over the years is to use a 
data dictionary outside of the system that is used to build the tables.  
A large part of the development process is making changes to this 
dictionary.  You "post" a unit of work to the dev system by running a 
diff builder that updates the dev system.  When the work is deemed 
acceptable, you post the same alterations to the production server, or 
you copy the spec in total from dev to production and run a build 
there.  A good system lets you provide values for system tables as part 
of the spec.


This approach is far more general-purpose than what you are attempting.  
It may be too general-purpose, and your backup/restore system may be 
just fine, but the backup/restore system will not scale well.




begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Best Procedural Language?

[Kenneth Downs]

Joshua D. Drake wrote:

However, Alvaro is correct there are currently no resources dedicated 
to PL/php.

There will be in the future but for now we are busy with other things.


Well consider me your biggest cheerleader, and when circumstances bring 
it to the front burner that will be great.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Best Procedural Language?

[Kenneth Downs]

Joshua D. Drake wrote:






ww.commandprompt.com/community/plphp/

Last release was 2005.  This is the first release that is actually 
useful, IMHO, because it allows SQL commands buried in the code, 
prior releases did not.




That is actually wrong. Go here:


Well I took it from your home page! :)

begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Best Procedural Language?

[Kenneth Downs]

Carlo Stonebanks wrote:

  
plPHP is not as mature as plTcl (or is that plTclng). However it is very 
well developed and maintained. Heck, companies are even holding talks and 
training classes on it now.

  
  
What is lacking in plPHP? To be honest, even though I am a Tcl developer I 
would rather develop in PHP, and I know next to NOTHING about PHP!

The thing is that Tcl leaves a bad taste in a lot of programmer's mouths - 
and I can't blame them. PHP looks and behaves like a "normal" programming 
language, so there's more likelyhood that other programmers will be able to 
maintain my code. (Imagine that - a developer worrying about how the NEXT 
developer will maintain his code! Think the idea will catch on?)

I couldn't find a recent release of plPHP, and have no idea of its status.
  

ww.commandprompt.com/community/plphp/

Last release was 2005.  This is the first release that is actually
useful, IMHO, because it allows SQL commands buried in the code, prior
releases did not.

My own totally unscientific I-didn't-get-very-thorough result from a
trial installation was that it was sloow, as in less than half the
speed of some comparable code in plperl.   But I never isolated what
was causing the slowdown and so I can't really say much more.  It was
bad enough though that I abandoned it very quickly, sucked in my gut
and coded some perl.  

  
Carlo 



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org
  




begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Best Procedural Language?

[Kenneth Downs]

Christopher Browne wrote:

  Martha Stewart called it a Good Thing when "Carlo Stonebanks" <[EMAIL PROTECTED]> wrote:
  
  
I am interested in finding out a "non-religious" answer to which
procedural language has the richest and most robust implementation
for Postgres. C is at the bottom of my list because of how much
damage runaway code can cause. I also would like a solution which is
platorm-independent; we develop on Windows but may deploy on Linux.

  
  
  

I would dearly love to see plPHP join the family as a real supported
language, right in the distribution.

Just thought I'd mention that while we're on the topic.  


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Constraint on an aggregate? (need help writing trigger,

[Kenneth Downs]

Isak Hansen wrote:


Each entry in 'A' belongs to a single 'business event'. E.g.
registering a phone bill modifies your accounts payable, phone
expenses and vat paid accounts. Those transactions better balance out.

There's no 'A' table in the system we base ours on, you'd just have X
lines with an equal marker field, but it seemed like a good target for
normalization as each batch of lines had a lot of common data.


The journal entries are always balanced.

Ideally we would store the data somewhere else during entry, and only
let users save their data when they balanced out, but today we save on
every submit (web app) and use some wonky heuristics to balance them
out. (not a technical issue..)

Either way, the db should reject any commit which doesn't sum to zero.


A simple way to do this without a lot of tables is as follows:

1)  Add a column "closed char(1)" to table A
2)  Do not enforce the constraint if closed="N".  This allows data entry 
of individual lines.

3)  Do not allow closed="Y" unless total=0
4)  Once closed="Y", disallow all updates (prevents changes to closed batch)


In the "shameless plug" department, our website also has an example of 
how to do this with our tool, email me off-list if you want more info on 
that.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 1: 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: [GENERAL] loop with circular updates

[Kenneth Downs]

Jessica M Salmon wrote:


Hi All.

I'm writing a plpgsql function that creates a table and loops over the
items in that table, using a FOR ... IN EXECUTE ... loop. The thing is, on
each iteration I update the table over which I am looping. Sometimes, the
result of this update makes it no longer necessary/desirable to process
subsequent records in the loop.
 

Can you tell us more about the app?  Sounds like an ERP allocation or 
something like that.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Constraint on an aggregate? (need help writing trigger,

[Kenneth Downs]

Isak Hansen wrote:

Hello Isak!  I was speaking to you about this on comp.databases, glad to 
see you here on the Postgres group.


What you want to do is easy enough in the single case, but can get 
complicated if you do a lot of it.



We have a framework that is freely available that does exactly what you 
are trying to do, and works against PostgreSQL and is written in PHP.  
It writes all of the triggers for you to save the hassle and prevent 
mistakes. We even have on our website an example of the kind of 
constraint you are doing:


http://docs.secdat.com/index.php?gp_page=x_docview&gppn=Customer+Credit+Limit


If you wish to code this by hand, here is what you must do:

1)  Add column "amount" to table A
2)  Add insert, update, and delete triggers to B that increase and 
decrease the value of A.amount
3)  Add an update trigger to A (insert and delete not necessary) that 
enforces your constraint, or just do it as a check constraint (i 
personally prefer triggers)


BTW, is table A supposed to be a GL batch summary table or something?  
Why must it always be zero?  If it is a GL batch table, can it be out of 
balance while people are actually entering the data?  Should the 
constraint only be enforced when the batch is closed?




create table a (
 id serial primary key,
);
create table b (
 id serial primary key,
 a_id int4 references a (id),
 amount decimal(16, 2)
);

and would like a constraint to guarantee that "sum(b.amount) = 0 group
by b.a_id".


From my testing so far, and this thread:
, 


i think a trigger is the way to go.

Problem is, i have no idea where to go from here. Getting the model
nearly to to 3NF and writing some simple queries is about the extent
of my db-related skillset..

Anyone feel the calling..? ;)


Also, how would this kind of check affect performance? Table 'b' is
our ledger table, busiest one in the app i assume, while 'a' groups
related transactions and holds common info. We inherited the term
voucher for 'a', anyone know if that is(n't) appropriate?

If someone are interested, the actual tables are here:
a: http://trac.lodo.no/wiki/vouchers
b: http://trac.lodo.no/wiki/voucher_lines


Any feedback appreciated,
Isak

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] sequences vs oids as primary keys

[Kenneth Downs]

craigp wrote:


1) does it make sense (and would it be possible) to make a rule which would,
say, somehow write into the oid field of a tuple to be returned by lastoid? i'm
assuming here that the database would not have oid's enabled.

 

We do this in a trigger.  We assign the NEXTVAL to a variable, write 
that to the row, then raise its value as a notice.  Then we just 
retrieve the notice.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Version/Change Management of functions?

[Kenneth Downs]

Jorge Godoy wrote:

  Kenneth Downs <[EMAIL PROTECTED]> writes:

  
  
We went for generating all server-side code out of a data dictionary.  This
makes for a significant change in the way change management is handled.

In this scenario change management becomes the analysis of "before" and
"after" data dictionaries.  If the changes are all valid, build the code.

  
  
Ken, could you explain it a bit better?  I think this is an interesting idea.

  

Sure.  To start off I'd say I'm one of those "biz rules belong in the
server" guys.  My guess is we are on the same page there so we'll take
that as a given.

So anyway, some years ago I joined an existing project and was
eventually promoted to systems architect.  Along the way I developed
their change management system from scratch (we had more salary dollars
than tools dollars).  The "Aha!" moment came when I realized what may
seem obvious to many, which was that you can never, nohow, noway, never
prove ahead of time that any particular piece of code was not going to
break something.  You can't even prove it will do what anybody
claims.   

I wanted a way to know by analysis, just by looking, that any
particular change to a spec would work.  That is, it would do what it
was supposed to do, without stopping other things from doing what they
were supposed to do.

It so happens you can have this if you generate your code out of a spec
that is itself data.  The spec has to be comprehensive, it can't just
be columns and tables.   You need to be able to specify security and
derivations all in one place, that is the only way to specify all
business rules in a single place.

There are two major things you can do to make sure a spec is workable
before you start generating DDL and triggers.

First, you look for mistakes in the spec itself, such as duplicate
column names in tables, references to non-existent tables, and so forth.

Second, you look for mistakes or impossibilities in the delta-spec, the
changes to the spec.  For instance, if column COL1 is char(7) and the 
new spec has it listed as INT, you can stop there and tell the person
the change is not valid.

Futhermore, you can then do really cool things like generate a report
of what *would* happen if you did an upgrade, such as the creation of
new tables, changes in formulas for existing columns, new cascades,
changes in definitions of keys (added a delete cascade, removed a
delete cascade), and then give it to the customer to sign.  Ha!  I love
that one :)

What falls out of all of this for free is that once you have that data
dictionary you don't have to code maintenance forms anymore, because a
library file can generate any maintenance from from the dictionary
description of a particular table.

So anyway, that's the tip of the iceberg on that.  Once you go to a
dictionary-based generation system, it actually changes a lot of how
you do things, not just change management.





begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Version/Change Management of functions?

[Kenneth Downs]

Michael Loftis wrote:

OK I know this is an odd question but I'm working on an app that will 
rely more and more on database driven functions, and while the app's 
source is in SVN, and I intend for the source of the SQL scripts to 
also be there, I was wondering...what are people doing for version 
control and change management on their custom (esp PL/pgSQL and say 
PL/Perl) functions?


We went for generating all server-side code out of a data dictionary.  
This makes for a significant change in the way change management is handled.


In this scenario change management becomes the analysis of "before" and 
"after" data dictionaries.  If the changes are all valid, build the code.




--
"Genius might be described as a supreme capacity for getting its 
possessors

into trouble of all kinds."
-- Samuel Butler

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Form builder?

[Kenneth Downs]

[EMAIL PROTECTED] wrote:


So far, here are the candidates:  Andromeda, Lazarus, and Rekall.

I was probably fairly inarticulate in my first post, but none of these
seem to meet my criteria for automatic generation of forms based on the
database definition.  Most of the above frameworks have a good deal
more functionality than I need, at least at first.  Really I want to be
able to open, say, ipython and type:

Someobject.form(table='sometablename', times=3)

(Maybe at the SQL prompt:  "> \form name=name times=3")

And have it give cycle three times through a reasonable (though
possibly still imperfect) form for entering three rows in table
sometablename.  I don't want to do any developing except for finding
out the table names in the database.  

This is no small task. 

I can say with plenty of confidence that we have tried almost every 
approach to automating the generation of code. 

The problem you run into is that there is no end to the kind of "macros" 
that can be made.  What we settled upon was to create the most necessary 
and basic stuff, and then to embellish it later as needed.  The two 
fundamentals turn out to be a browse of search results and a display of 
detail. 

For one customer we then experimented with a three-row editable grid of 
data from a child table, but found when working with it that users shied 
away from it, and back we were to the primitives that have been working 
so well since day 1.


All of that being said, if you want to do it yourself, I would still 
claim that you'd get there a lot faster adopting Andromeda, because all 
you are really trying to do is embellish what we've already done.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] User privileges in web database applications

[Kenneth Downs]

Antonis Christofides wrote:


But I think that checking user privileges at the database level is
better.  I think it's simpler and more secure, and if later you also
want to create nonweb apps, you won't have any more
authentication/privilege headaches.  

Couldn't agree more.  But consider this reasoning as perhaps more 
fundamental.


For a database app all security resolves to the basic permissions of a 
single user being allowed to insert, update, delete or select any 
particular row from any particular table.  Every security system that is 
implemented on some other basis will have to be resolved down to this.  
So why not just implement this in the first place?


Our own approach was to build security directly into the specification.  
Our table definitions include security definitions, which groups can do 
what do the table.  The generator builds the security commands the same 
way it builds the CREATE TABLE commands.



For this reason, in a web app
I've made, the app connects to the database as user postgres, and
after authenticating (receives user's password, checks with pg_shadow,
and uses session cookie) uses "set session authorization" in order to
lower its privileges.  

I've considered this.  How is it working out in real life?  We connect 
using real user credentials, and where necessary elevate to super-user, 
which I think is probably marginally safer but more expensive.



I've even written triggers to implement
row-level permissions checking.

Yeah, this is cool.  What kind of features have you implemented here?  
We've just done the very basics, not much to brag about.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] minimizing downtime when upgrading

[Kenneth Downs]

Jim Nasby wrote:


On Jun 21, 2006, at 7:42 AM, H.J. Sanders wrote:

The last 15 years we also used Informix and we never, never had to  
unload/load

the database because of an upgrade.

Perhaps somebody knows how they do the trick?



Do they provide a migration/upgrade utility?


In the case of MS SQL Server the issue doesn't come up.  Here are some 
user experiences:


1) Create a database in MS SS 6
2) Upgrade software to MS SS 7
3) Use software


1) Create a database in MS SS 6
2) Backup database (closest analog is -Fc option of pg_dump)
3) Move backup to machine running MS SS 7
4) Attempt to restore.  Program warns it will upgrade database and you 
can't go back, proceed?  You say yes.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Form builder?

[Kenneth Downs]

John DeSoi wrote:



On Jun 22, 2006, at 7:38 AM, Kenneth Downs wrote:

We wrote a system that does exactly that, its called "Andromeda",   
and it is GPL.


http://docs.andromeda.com



OOPS:

http://docs.secdat.com




Sounds interesting but this link does not work (apparently no  
server  at that address).




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 1: 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: [GENERAL] Form builder?

[Kenneth Downs]

[EMAIL PROTECTED] wrote:

We wrote a system that does exactly that, its called "Andromeda", and it 
is GPL.


http://docs.andromeda.com

It uses a data dictionary to do two things: 1) build the database and 2) 
generate HTML maintenance forms.  But it can also have multiple virtual 
sites going into the same database, so in many cases we have the "admin" 
site which is generated for free and then 1 or more public sites reading 
the same database but intended for anonymous access.  These other sites 
go through a professional design process quite different from the 
table-maintence sites.


It also automatically generates links to child and parent tables based 
on foreign keys, so for a table "customers" you will see a link 
automatically generated for "orders" that goes to that customer's orders.


Our largest project technically has about 290+ tables, our most active 
project is a system of about 30 tables in which we've made small hacks 
to a couple of pages to enhance the defaults.


The codebase is extremely small.  The main library is less than 10,000 
lines, easy to walk through and change.


The default interface has been tested on IE and Firefox, and also 
supports keyboard navigation (at least on Firefox). 

The project is running on PHP and currently targets postgres.  We run on 
Linux.  In principle it can run on Windows but we haven't tried.


If you would like to see a running system I can give you an account any 
of our systems under development and you can see it.



I don't want to revisit or be redundant... but is there a quick and
dirty and cross-platform system for developing user input forms for
Postgres?  Ideally, I am interested in something such that you can give
it ("it" being something like a Python function) a table name,
resulting in a magically appearing input form.  It should be able to
deal with getting the format more or less correct for a datatype, not
displaying system columns, etc, using the system tables to get the
necessary info.

I thought first about hacking xdialog, but they don't have multiple
field forms.  I have looked at wx, but real gui programing is complex
and not general.  Right now I have an ugly hack that writes out a text
file with colon separated lines for each row, with a defaults option so
that you can chain together a set of One-Many forms (eg you enter a
person, then you can cycle through with library books each referencing
that person in a for loop).

I would rather trade ease of use for functionality, if it can get it
90% correct just by the table name and the defaults.  And I refuse to
use XML.  And I want pure Linux.  If I have to develop the darn thing,
of course I am happy to share.

I want to develop an anthropological fieldwork database, but the form
based data entry is important for ease of use.

Cheers.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
 



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Return the primary key of a newly inserted row?

[Kenneth Downs]

Tim Allen wrote:



using syntax along the lines of INSERT ... RETURNING ...

SQL Server had a nifty feature here.  You could simply toss a SELECT 
statement at the end of a trigger of sproc and the results would be 
returned.


This in effect made a table the potential return type of all commands, 
which could be exploited very powerfully.


Do the hackers have any thoughts along those lines?
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Return the primary key of a newly inserted row?

[Kenneth Downs]

John Tregea wrote:


Greeting again,

I am writing records to postgreSQL from an IDE called revolution. At 
the time I perform the INSERT command I need to retrieve the value of 
the serial_id column from the newly created row.


We have an after-insert trigger that raises it as a notice.  NOTICE 
SKEY(xxx)
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] minimizing downtime when upgrading

[Kenneth Downs]

Richard Huxton wrote:


Kenneth Downs wrote:

AFAIK it has always been the case that you should expect to have to 
dump out your databases and reload them for version upgrades.


Is anybody over at the dev team considering what an onerous burden 
this is?  Is anyone considering doing away with it?



Far from trivial. 


Kind of gets to the heart of things, though, doesn't it.

It's the non-trivial stuff where we look to the machine to help us out. 

As a user of PostgreSQL, I benefit from a lot of things.  I gain a total 
advantage of "X" units of time/money.  Then its time to upgrade and I 
have to give a lot of it back.  The more I use the package, the more 
non-trivial is my upgrade, and the more I give back. 

Regardless of whether a package is commercial or free, it strikes me as 
counter to the very soul of programming to build in a burden that 
increases with the user's use of the program, threatening even to tip 
the balance altogether away from its use.  This seems to be the very 
kind of feature that you want to programmatically control precisely 
because it is non-trivial.



You have changes in on-disk formats and actual functionality between 
major version numbers. For instance - what would you do to deal with 
the recent changes in unicode validation?




begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Interface Guidance and Opinions Needed

[Kenneth Downs]

Chris Golden wrote:

  
  
  
  
   
  Hello to all,
  

I use PHP as the web programming layer, it has a great community and
support for such things as XML RPC, which may fit the bill.  


  
   
  This is my first time
posting to this forum and I am very
new to PostgreSQL.  I am very excited about using it.  I have set up
a database and just need a point in the right direction on
interfacing.   I have an orders and messages database.  One of
the providers I would like to interface with has sent me an XML spec to
make HTTPS
posts to.  I am just wondering what would be the easiest thing to use
to
implement the two.  I would like to scan my database for new orders
then
do an HTTPS post to the provider.  I would also like to periodically
check
for new orders/messages from the provider via an HTTPS post and bring
them into
my database (a constant exchange of information).  I am wondering if
something like PHP would be a universal solution, or something similar?
   
  Sorry if the information
I have given is basic, I have been
doing research on the internet trying to figure out which direction to
go and
starting off is a little overwhelming.
   
  Thanks in advance for any
information
   
  Chris Golden
   
  




begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] minimizing downtime when upgrading

[Kenneth Downs]

snacktime wrote:


Anyone have any tips for minimizing downtime when upgrading?  So far
we have done upgrades during scheduled downtimes.  Now we are getting
to the point where the time required for a standard dump/restore is
just too long.  What have others done when downtime is critical?  The
only solution we have been able to come up with is to migrate the data
on a per user basis to a new database server.  Each user is a
merchant, and the data in the database is order data.  Migrating one
merchant at a time will keep the downtime per merchant limited to just
the time it takes to migrate the data for that merchant, which is
acceptable.



AFAIK it has always been the case that you should expect to have to dump 
out your databases and reload them for version upgrades.


Is anybody over at the dev team considering what an onerous burden this 
is?  Is anyone considering doing away with it?



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] More on state variables

[Kenneth Downs]

Yesterday on this list I found out about the nifty setting

custom_variable_classes='global'


which allows the setting and retrieving of arbitrary values that persist 
across statements.


I wonder if there is a way to do the same thing within the scope of a 
transaction?  Is there a "magic" value of custom_variable_classes that 
displays any such behavior?  Or is it available by other means?
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PostgreSQL and Apache: authentication and authorization

[Kenneth Downs]

jqpx37 wrote:


I can envision, broadly, two authentication schemes:
(1) Users authenticate to Apache.
(2) Users authenticate to PG.

 


This is a little too complicated.

The most accurate and precise security is obtained by having the user 
log in with a real postgres account, and to grant table priveleges to 
that account (though usually we make the account a member of a group 
that has certain priveleges (except now we call them all roles to make 
it more confusing)).


In our system we store the username's userid and password in the PHP 
session and nothing more.  On each round trip we make a connection and 
run the request.


No Apache configuration is required. 

Our package that does all of this is available as GPL, you are free to 
trawl the code: http://docs.secdat.com.


Folks with a heavy coding background who distrust databases will usually 
recommend connecting as superuser and enforcing security with 
application code.  This is bad because A) a mistake in code could be 
catastropic, and B) it takes so much longer to code up security in the 
app layer and C) anybody connecting directly to the database can subvert 
it all.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] are there static variables in pgsql?

[Kenneth Downs]

Kenneth Downs wrote:

OK, cancel the question, the answer is

SELECT current_setting('global.val2');

This is very intriguing, but I'd like to make sure it is doing what I 
think it is doing.  Is it tracking variables in a connection across 
SQL commands?  If so, shouldn't this work (Assume the existence of 
table "detail" with a column called "val2").


create or replace function SetVal2() returns void as
$$
begin
   update detail set val2=global.val2;
end;
$$
language plpgsql

set global.val2=5;
select SetVal2();

I'm getting the following error which I don't understand.  Seems I 
don't know how to access the variable once it is set.


ERROR:  missing FROM-clause entry for table "global"
CONTEXT:  SQL statement "update detail set val2=global.val2"
PL/pgSQL function "setval2" line 2 at SQL statement

---(end of broadcast)---
TIP 1: 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
 



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] are there static variables in pgsql?

[Kenneth Downs]

Patrick TJ McPhee wrote:


I'm not sure a static variable is the right way to achieve this, but
you could use a custom_variable_class for this. Add this to your
postgresql.conf:
custom_variable_classes='global'

Then you can set and show variables prefixed by global.:
set global.success = 'true';

 

This is very intriguing, but I'd like to make sure it is doing what I 
think it is doing.  Is it tracking variables in a connection across SQL 
commands?  If so, shouldn't this work (Assume the existence of table 
"detail" with a column called "val2").


create or replace function SetVal2() returns void as
$$
begin
   update detail set val2=global.val2;
end;
$$
language plpgsql

set global.val2=5;
select SetVal2();

I'm getting the following error which I don't understand.  Seems I don't 
know how to access the variable once it is set.


ERROR:  missing FROM-clause entry for table "global"
CONTEXT:  SQL statement "update detail set val2=global.val2"
PL/pgSQL function "setval2" line 2 at SQL statement

begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 1: 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: [GENERAL] Best open source tool for database design / ERDs?

[Kenneth Downs]

[EMAIL PROTECTED] wrote:


Seems like the open source data modeling tools aren't feature-rich
quite yet.
 

Disclaimer: this is probably *not* what you want, but I will throw it 
out for completeness.


We have a non-graphical tool that builds databases out of text files 
that resemble CSS, such as:


table customers {
  column customer { primary_key: Y; }
...
}

Very likely we share the same purpose as you do, to capture data 
structure outside of DDL.   But going further, we designed with nothing 
less in mind than to capture the entire system requirements in the 
database spec, including derived columns and other automations, and 
including security as well.  A generator builds the DDL.


Going one better, the generator also works as a diff engine, so when you 
make changes it generates the DDL to change the structure and also the 
trigger code to enforce the rules.


The twist is that we found when the file format was complete and the 
features were in we really didn't need the GUI.  We may put one in 
someday, but jedit is our GUI now :)


So if you are at all willing to consider non-GUI tools that aim for the 
same purpose, you may wish to check it out:


http://docs.secdat.com
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Restricting access to rows?

[Kenneth Downs]

Josue E. Maldonado wrote:


Benjamin Smith wrote:

How can I set up a user so that Bob can update his records, without 
letting Bob update Jane's records? Is it possible, say with a view or 
some other intermediate data type? 



I've done something similar using a separate control table where I set 
what accounts an user can "see", then I wrote a psql that returns just 
the rows for that especific user, it could also be done with pure SQL 
joins tough.




You can put in a some triggers that do a few things, and I think a rule 
on SELECT will round it off.


on Insert: populate a column with CURRENT_USER
on Update and Delete: refuse unless CURRENT_USER matches the column
on SELECT rules, apply a filter that column = CURRENT_USER

You also may put in an override for all three that if the CURRENT_USER 
is in some particular group these filters will not apply.  One level 
might be just for selects, a higher level for updates/deletes.


Or you can do the reverse, and say that these filters only apply if the 
user is in a certain group.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] 8.1 on gentoo

[Kenneth Downs]

Tim Allen wrote:


Holger Hoffstaette wrote:


On Thu, 25 May 2006 08:55:51 +0200, Sim Zacks wrote:

Does anybody know when postgresql 8.1 will be considered stable on 
gentoo

for x86?



No, maybe ask in gentoo-users or -dev? Anyway just because it's not 
marked
"stable" does not mean it isn't. It's very unfortunate that 
distributions
have adopted these terms because more often than not they have 
absolutely

nothing to do with the *runtime* stability of the software.



We've been running 8.1 on gentoo almost since it came out into the ~x86 
tree, and have had no problems.


Not very scientific, but thats our experience, for what its worth
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] background triggers?

[Kenneth Downs]

Sim Zacks wrote:

The problem with client code processing a function is that unless you 
are using threads (my client application is not in a multi-threaded 
environment), the client has to wait for the server to return from the 
end of the function. I don't want the client to wait and the result 
doesn't affect the user at all, so there is no reason why he should wait.


No reason for him to wait.  Even Windows supports background processing 
(geez I think so anyway).


If you are on Linux spawning a background process is a no-brainer, 
there's plenty of ways to do it.  There ought to be a way on windows I 
would think. 



Kenneth Downs wrote:


Rafal Pietrak wrote:


A plain INSERT of batch takes 5-10minutes on desktop postgresql (800MHz
machine, ATA disks). When I attach trigger (*Very* simple funciton) to
update the accounts, the INSERT take hours (2-4). But when I make just
one single update of all accounts at the end of the batch insert, it
takes 20-30min.

 

Why not have the INSERT go to an "inbox" table, a table whose only 
job is to receive the data for future processing.


Your client code should mark all rows with a batch number as they go 
in.  Then when the batch is loaded, simply invoke a stored procedure 
to process them.  Pass the stored procedure the batch number.


IOW, have your "background trigger" be a stored procedure that is 
invoked by the client, instead of trying to get the server to do it.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] background triggers?

[Kenneth Downs]

Rafal Pietrak wrote:

  On Wed, 2006-05-24 at 07:41 -0400, Kenneth Downs wrote:
  
  
Why not have the INSERT go to an "inbox" table, a table whose only job 
is to receive the data for future processing.

  
  
Actually, it 'sort of' works that way.

  
  
Your client code should mark all rows with a batch number as they go 
in.  Then when the batch is loaded, simply invoke a stored procedure to 
process them.  Pass the stored procedure the batch number.

  
  
If I have that stored procedure and if I issue command that would launch
such stored procedure from "psql>" prompt: how long will I have to wait
for another prompt? 1) until the procedure ends its job. 2) right away,
the procedure does its job unabidedly 'in the background'. 

  

What web server and OS are you using?  In linux/apache you can fork off
a process that runs the SP and then detach from it.  


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] challenging constraint situation - how do I make it

[Kenneth Downs]

Alban Hertroys wrote:


Kenneth Downs wrote:


Alban Hertroys wrote:

When encountering this problem I usually wonder why there isn't a 
data type that can store a timestamp and can be used to create a 
UNIQUE INDEX over it's values. That'd be wonderful.

Well, maybe one day I'll actually have time to create one...

I tried this at trigger level.  The real bear is in the fact that 
there are two columns, not one.  It is trivial to write an exclusion 
constraint that disallows overlapping (including nested) values.  
What was hard was determining the meta-data structure, how do you 
have two columns that are sometimes treated as one and sometimes as two?



Are you refering to a 'timespan' data type that can be determined to 
be unique?


I can see some problems there, as both value and range matter; it'd be 
similar to determining the uniqueness of an area in a rectangle 
(though 1 dimensional only, of course).


I've never really dug into this, so I don't know what possibilities 
PostgreSQL offers in this field. Basing this on faith :)


Yes. 

I use a heavily dictionary-based toolset.  I write out database specs in 
CSS-like syntax and it diff's and builds the databases and writes all 
triggers, indexes and so forth.


The approach I tried was to have a "range" or "interval" type.  You 
place a column into a table named "resv_date" or whatever and it would 
expand the definition into two columns, you'd get resv_date_beg and 
resv_date_end.  If you declared the "resv_date" column a primary key 
column, it would build trigger code to detect overlaps and nesting and 
reject those.


As I said, defining behavior and implementing it was not hard.  I even 
had foreign keys into ranges that were "smart".  If the foreign key was 
a single column instead of two, it would satisfy RI if the single value 
was between the interval values in the parent table.


The problem comes from the split-personality of the "resv_date" column.  
Sometimes its one column, sometimes its two.  This made writing the 
tools nasty and difficult, and I scratched it and (gasp!) did some 
validation in client code.


I have it in mind to restore the feature, but in a different way.  The 
two columns should be defined separately, not as one, and then the 
second of the two gets a flag setting, like:


column range_beg { primary_key: Y; }
column range_end { primary_key: Y; range_from: range_beg; }

The "range_from" setting ties one column to the other and should give me 
all the behavior I had without all of the confusion.  It would have 
three effects:


1)  Force range_end >= range_beg
2)  Convert the primary key into overlap/nest exclusion
3)  Allow a single column foreign key in another table to "know" that it 
should do a within match instead of an equality match







begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] background triggers?

[Kenneth Downs]

Rafal Pietrak wrote:


A plain INSERT of batch takes 5-10minutes on desktop postgresql (800MHz
machine, ATA disks). When I attach trigger (*Very* simple funciton) to
update the accounts, the INSERT take hours (2-4). But when I make just
one single update of all accounts at the end of the batch insert, it
takes 20-30min.

 

Why not have the INSERT go to an "inbox" table, a table whose only job 
is to receive the data for future processing.


Your client code should mark all rows with a batch number as they go 
in.  Then when the batch is loaded, simply invoke a stored procedure to 
process them.  Pass the stored procedure the batch number.


IOW, have your "background trigger" be a stored procedure that is 
invoked by the client, instead of trying to get the server to do it.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] challenging constraint situation - how do I make it

[Kenneth Downs]

Alban Hertroys wrote:



When encountering this problem I usually wonder why there isn't a data 
type that can store a timestamp and can be used to create a UNIQUE 
INDEX over it's values. That'd be wonderful.

Well, maybe one day I'll actually have time to create one...

I tried this at trigger level.  The real bear is in the fact that there 
are two columns, not one.  It is trivial to write an exclusion 
constraint that disallows overlapping (including nested) values.  What 
was hard was determining the meta-data structure, how do you have two 
columns that are sometimes treated as one and sometimes as two?


Possible, but devilish in the details.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] background triggers?

[Kenneth Downs]

Rafal Pietrak wrote:


So if I may re-phrase the question: "is there a way to have a trigger,
that, when launched, can check if it's already running in backgroung for
some other INSERT, return imediately if so, but turn into background for
a long-lasting job if not".

 

Rafal, I'm wondering why you want to do this.  You may be "fighting the 
framework".


If you are trying to do something that is totally unsupported, it is 
probably for a pretty good reason, usually dealing with security or data 
loss.  You can probably get what you want by supported methods, but it 
may require looking at the problem in a different way.


What is it you are trying to accomplish?  Is it just performance? 
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] More confirmation: pgadmin3 freezeup fixed by wxgtk

[Kenneth Downs]

Dave Page wrote:

   

  
  
-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]] On Behalf Of Kenneth Downs
Sent: 23 May 2006 12:24
To: pgsql general
Subject: [GENERAL] More confirmation: pgadmin3 freezeup fixed 
by wxgtk 2.6.3

I've gotten a chance to upgrade wxGTK to 2.6.3.2.  I can no 
longer deliberately reproduce the freezeup caused by a 
double-click on selected text in the query analyzer.

  
  
Good, thanks for letting us know.
 
  
  
This btw is using pgadmin3 1.2.

  
  
Any reason for not using 1.4.2?

  

Only my negligence :)

My distro, which is gentoo, automatically installed its latest stable
version when I recompiled, which downgraded me to 1.2.  I forgot about
that and have to explicitly tell it to compile 1.4


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 1: 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

[GENERAL] More confirmation: pgadmin3 freezeup fixed by wxgtk 2.6.3

[Kenneth Downs]

I've gotten a chance to upgrade wxGTK to 2.6.3.2.  I can no longer 
deliberately reproduce the freezeup caused by a double-click on selected 
text in the query analyzer.


This btw is using pgadmin3 1.2.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] GUI Interface

[Kenneth Downs]

Florian G. Pflug wrote:



It took me a while to find out how exactly to reproducte the hang (It 
would happen to me about once a day when using pgadmin3 heavily on 
gnome/metacity, but when trying to reproduce it, I couldn't).


You have to select some text, and then _doubleclick_ on the selected 
part. When I do this, the cursor is changes to some strange shape (

Kind of a upper-left corner with a "+"-sign inside). Ever click is
ignored, but the keyboard remains working (I can edit text in the 
sql-editor). When I press Alt-f in this state, the File-Menu opens,

the cursor resumes it's normal shape, and clicks are handled again.

If, however, the sql-editor looses focus while in the "funny mode", then
I know of now way to get X working again apart from changing to a 
console, and killling pgadmin3.



Aha!  Reproduced.  The doubleclick is the trick.

But on my system once this happens that's that.  No keyboard rescue, and 
it doesn't matter who has focus, only way to rescue is to go to another 
system and ssh in and kill pgadmin3.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Announce: GPL Framework centered on Postgres

[Kenneth Downs]

Florian Weimer wrote:

  * Kenneth Downs:

  
  
If you seek to provide a closed source app that is built upon
Andromeda, you are required to provide the source code to Andromeda
itself.  However, your app is not a derivative work in the strict
sense because your code is not mixed in with mine in any sense.  You
never modify a file, and your files and mine are actually in separate
directories.

  
  
Many proprietary software vendors think that if you program to an
interface which has a sole implementation, your code becomes a derived
work of that implementation.  If you sell different licenses for
run-time and development environments, such an attitude towards
copyright law seems inevitable.
  

I am not understanding you.  By sole implementation do you mean sole
license, or single codebase, or cant-run-without-the-library?

The last sentence I don't understand at all, can you elaborate?



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Announce: GPL Framework centered on Postgres

[Kenneth Downs]

Florian Weimer wrote:

  * Joshua D. Drake:

  
  

  
Sounds great! But why GPL? Are you looking to sell licenses?

  
  GPL is to spread it as far and wide as possible as fast as possible.
  

LGPL?

My concern would be, I can't use this toolkit for a closed source
application if it is GPL.

  
  
Closed source?  It's a PHP framework. 8-)
  

LOL.  I was thinking something of the same thing.  But as you point
out...

  
Anyway, for a web application, the GPL is usually *less* restrictive
than various BSD license variants because you do not need to mention
the software in the end user documentation.  
  

The paradox is that web is a more closed environment.  The user need
never download and install the source.

Which has me thinking of the idea of requiring a copyright notice in
the HTML files sent to the browser, or some type of "powered by"
notice.  I will add that to the list of ponderables along with LGPL.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Announce: GPL Framework centered on Postgres

[Kenneth Downs]

Anastasios Hatzis wrote:


Kenneth Downs wrote:



My company has developed an application development framework that 
targets PostgreSQL as its back-end, with PHP in the web layer.


Is this product somehow related to AndroMDA (which is usually 
pronounced 'Andromeda')?


http://www.andromda.org/


Nope, separate projects.

Theirs is java, we are php.

Theirs is windows, we are linux.

They don't mention a database, I'm sure they're using something, we 
target Postgres  (though our methodology is platform-neutral).


They look like a code generator, while ours uses libraries + data 
dictionary on web server layer, and only generates code on the db server.


Finally, we are "radically table oriented", focusing entirely on 
automating software development based on a detailed database 
specification which includes derived values and security.  They use UML, 
while we have a CSS-like way of specifying tables as in:


table customers {
 module: ar;
 description: customers;
 column customer { primary_key: Y; uisearch: Y; }

more columns and stuff

}

begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Announce: GPL Framework centered on Postgres

[Kenneth Downs]

Tom Lane wrote:

  Kenneth Downs <[EMAIL PROTECTED]> writes:
  
  
If it turns out that nobody can release a closed source app, I will 
definitely reconsider and look again at LGPL, but I am not convinced you 
cannot do so.

  
  
  
  
If you seek to provide a closed source app that is built upon Andromeda, 
you are required to provide the source code to Andromeda itself.  
However, your app is not a derivative work in the strict sense because 
your code is not mixed in with mine in any sense.

  
  
This may well be what a sane person would think after perusing the
license text, but you need to be aware that the FSF takes a much more
expansive reading of that text.  AFAIK those details haven't been tested
yet in any court of law --- but until a reading is settled by court
precedents, people tend to look to the FSF's interpretation.  And the
FSF is on record as saying that if code A depends on code B then B's
GPL license infects A, even for pretty weak values of "depends".
You should carefully read http://www.gnu.org/licenses/gpl-faq.html,
which contains statements such as

   If the program dynamically links plug-ins, and they make function
   calls to each other and share data structures, we believe they form a
   single program, which must be treated as an extension of both the
   main program and the plug-ins.

I don't have anything against the GPL's goals, but those goals are very
clearly that the entire software universe should be GPL code.  If that's
not what you have in mind, then you should think twice about licensing a
software component (as opposed to a standalone product that isn't meant
to have other code depending on it) under GPL.

			regards, tom lane
  

Tom, thanks much.  That points me pretty firmly towards LGPL.  I will
reflect on this and likely make a change in the coming weeks.



begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Is it me, or the list?

[Kenneth Downs]

Scott Marlowe wrote:

  On Tue, 2006-05-16 at 15:04, Kenneth Downs wrote:
  
  
I participate regularly on three mailing lists, including 
pgsql-general.  I use thunderbird for mail.

pgsql-general has the peculiar property, when I hit reply, of replying 
not to the list, but the individual who emailed to the list.

Is this me, or is this the list? 

  
  

It's the standard way most development lists work.  The reason why is
that direct mail always takes the shortest route.  So, this email I'm
replying to will go to the list (then to you, cause you're a member) as
well as straight to you.

  

Thanks, now I understand.

Thanks, now I understand.






(ha ha)


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 1: 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: [GENERAL] Announce: GPL Framework centered on Postgres

[Kenneth Downs]

Joshua D. Drake wrote:




Sounds great! But why GPL? Are you looking to sell licenses?



GPL is to spread it as far and wide as possible as fast as possible.



LGPL?

My concern would be, I can't use this toolkit for a closed source 
application if it is GPL.


That may be your intent (which I actually don't have a business 
problem with), I was just curious as to your decision.


If it turns out that nobody can release a closed source app, I will 
definitely reconsider and look again at LGPL, but I am not convinced you 
cannot do so.


If you seek to provide a closed source app that is built upon Andromeda, 
you are required to provide the source code to Andromeda itself.  
However, your app is not a derivative work in the strict sense because 
your code is not mixed in with mine in any sense.  You never modify a 
file, and your files and mine are actually in separate directories.


I greatly appreciate your asking the question though because I'd like to 
make sure that people feel safe with the project.  My goal is to provide 
the freedoms typically associated with the "plain old GPL", and 
certainly not to restrict the creation of closed apps.  I just don't 
want anybody closing *my* app.


For corporate customers, it does not matter much whether you call it 
a license or something else, some expenditures are made to support 
the customer's effort and the customer is asked to provide the funds 
for that.  Call it consulting fees, call it licensing, call it 
training or support, it is all the same thing.



O.k. so you are going to charge a corporate customer to allow them to 
get their code their own?


Only if they want to talk to me :)  It is GPL, so anybody can download 
it and use it, person, gov, NGO, corp.  There is a documentation site 
that IMHO is pretty nice and getting better every day.  But if you want 
my time, that's for sale.


begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Is it me, or the list?

[Kenneth Downs]

I participate regularly on three mailing lists, including 
pgsql-general.  I use thunderbird for mail.


pgsql-general has the peculiar property, when I hit reply, of replying 
not to the list, but the individual who emailed to the list.


Is this me, or is this the list? 
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Announce: GPL Framework centered on Postgres

[Kenneth Downs]

Joshua D. Drake wrote:


Kenneth Downs wrote:


Hello folks,

My company has developed an application development framework that 
targets PostgreSQL as its back-end, with PHP in the web layer.


We are inviting any early adopters who may wish to experiment to 
download and install the code.   You may contact me off-list with any 
support or other questions.


Licensing is GPL.



Sounds great! But why GPL? Are you looking to sell licenses?


GPL is to spread it as far and wide as possible as fast as possible.

As is customary, GPL downloaders get no support, but folks who want a 
closer relationship with us can pursue any avenue that is reasonable for 
both of us.  We can do complete development, as we do for our current 
customers who know little and care less about the tools we use, or 
training, consulting and so forth. 

For corporate customers, it does not matter much whether you call it a 
license or something else, some expenditures are made to support the 
customer's effort and the customer is asked to provide the funds for 
that.  Call it consulting fees, call it licensing, call it training or 
support, it is all the same thing.


We also anticipate a closed commercial license for the more stable 
project, where the GPL project is the wide-open bleeding edge version, 
but that is down the road and a full model will have to await further 
developments.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster