Re: [HACKERS] Feature suggestion: Database-Security-Modules (DSM)

[Pavel Stehule]

2017-03-28 9:51 GMT+02:00 Jan Kechel :

> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
>
>
> Hi PostgreSQL Hackers,
>
>
> I'm developing software using PostgreSQL for several years and want to
> propose a feature to improve security of (badly written?) Web-Apps.
>
> I call it 'Database Security Modules', derived from
> Linux-Security-Modules (aka SELinux, AppArmor, Tomoyo).
>
> Of course such a feature might already exist, or is in your opinion
> not a task for PostgreSQL at all. In that case please simply inform me
> about my mistake ;)
>
>
do you know SQL_firewall?

 http://pgsnaga.blogspot.cz/2015/08/postgresql-sql-firewall.html

Regards

Pavel

[HACKERS] Feature suggestion: Database-Security-Modules (DSM)

[Jan Kechel]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Hi PostgreSQL Hackers,


I'm developing software using PostgreSQL for several years and want to
propose a feature to improve security of (badly written?) Web-Apps.

I call it 'Database Security Modules', derived from
Linux-Security-Modules (aka SELinux, AppArmor, Tomoyo).

Of course such a feature might already exist, or is in your opinion
not a task for PostgreSQL at all. In that case please simply inform me
about my mistake ;)


What it's about:

It's to prevent SQL-Injection and/or unpriviliged data-retrievel once
the webserver has been hacked and/or the Application-Server security
measures have ceased.


How it works:

Users or Roles get attached Access-Rules to form a DSM-List.
Each Access-Rule specifies a specific query-plan, that hereby is
granted to be executed.


Why it helps:

A simple SQL-Injection could append to query sth. like ' or 1 = 1;
select * from table_with_secret_columns' to retrieve data it should
not have access to.

But, this always (?? not sure, is this true? does it help at all?)
changes the query-plans. Now we have to queries, but each doesn't have
a valid Access-Rule as the first adds a 1=1 and thus probably changes
completely and the second one never had a Rule.

Both queries will be rejected and thus render the SQL-Injection useless.


Further ideas:

Later, this could be enhanced with additional information given to the
DSM, like 'current user id', and some Access-Rules could scope queries
based on this additional information (like only allowing a specific
Query-Plan with the parameter user_id equal to the given id).
Another addition could be rate-limiting.


Convenience:

DSMs should have different modes like 'off', 'training' (logging
query-plans to copy into DSM-Lists), 'warn' (logging but not failing
violations) and of course 'enforce'.


Please let me know what you think and if you'd be interested to see a
first basic patch implementing this.


best regards,


Jan Kechel




-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iQIcBAEBAwAGBQJY2hYdAAoJEDdxXoVRJZEA0qoQAITW+Q0EUGsFouvR5VckYR3S
xQfL8ecMZEqYeAGwYYBUhK00DF/Wqa8GDyBq8BGbicQjmx6WFpeZYTqx6LF/twar
EAewuEueOLWs29sOt6Mn+pCATUZIzTIbF+h7QvqTucFK3kjrCe1qwD06ErVPExQ+
9NPgNZBxaivlwtuQk5xi495XyI3BVdN3zzaS6zfgQC1jXv7+tpy07TLZDGs8ckOv
sziPhwEDiWD81qz8TsH+YromiKu9pT/dMiQyxagm5G/BSTJq4/QveI94EzQh5LLk
AZiSbWg+JOhaJKh5YSgOfBlLfyDZFFdOtgqXHSrX8aRG4KPkXmsNWWLk2XRswsIr
SIga9CFda8f0Ou78MBAOQRI0baK76O5ycy+aYLawLM2z1A/sozQS+pqaK+xAQtjZ
VnNa0t99b89rAgozRrySfktjPeqQliiU4LYy/7Fy918io8kdxAm5j2zaTXdjxTto
Fz+OOgg03PjbGHtH9yJOpE6afcIOOwk1SFfAfusJqZXnlhEsASVoQVsz5sCvpCk6
xJ8LeYeRaucdbTp7BxFNz9p7klJlpGz64J4hjFUD/EeQMCf8KmOmHLy+WN7aCps+
2SncRLONv6UhuSbxhKFqfLgxBevF3pBXuCZLqSTwjNUipSqW4ITT3MhXWmM7DaKH
DZTfp6b3ZNMaW8xjkt3j
=a5v8
-END PGP SIGNATURE-


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

Re: [HACKERS] Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data

[Tom Dunstan]

> On 5 Oct 2016, at 8:11 PM, Pantelis Theodosiou  wrote:
> 
> This can be solved by chaining modifying CTEs. 
> 
> Something like this (not tested)  that can work with multiple rows inserted:

Thanks for the suggestion, but it was actually slower than our current 
implementation, I believe due to always looking up t1’s id in that join rather 
than only doing it when we didn’t get an id back from the insert. My hope with 
this feature suggestion / request was that we wouldn’t have to do that 
subsequent lookup at all, as pg would just give it back to us.

Maybe it would be a win if we were inserting multiple rows, but this code is 
actually in a trigger on a dummy table that we COPY data in to - thus it can’t 
be rewritten as a rule or a multi-row insert like that.

Thanks

Tom



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

Re: [HACKERS] Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data

[Pantelis Theodosiou]

This can be solved by chaining modifying CTEs.

Something like this (not tested)  that can work with multiple rows inserted:


WITH
  vals (bk1, bk2, other_t1_columns, other_t2_columns) AS
( VALUES (bk1val, bk2val, other_t1_values, other_t2_values),
 (bk1val, bk2val, other_t1_values, other_t2_values)
),
  ins_t1 AS
( INSERT INTO t1 (bk1, bk2, other columns)
  SELECT bk1, bk2, other_t1_columns
  FROM vals
  ON CONFLICT (bk1val, bk2val) DO NOTHING
  RETURNING id, bk1, bk2
)
INSERT INTO t2 (t1_id, other_t2_columns)
SELECT
COALESCE(t1.id, ins_t1,id),
val.bk1, val.bk2, val.other_t2_columns
FROM vals
  LEFT JOIN ins_t1 ON (vals.bk1, vals.bk2) = (ins_t1.bk1, ins_t1.bk2)
  LEFT JOIN t1 ON (vals.bk1, vals.bk2) = (t1.bk1, t1.bk2)
 ;

On Wed, Oct 5, 2016 at 1:53 AM, Tom Dunstan  wrote:

> Hi all
>
> We recently moved to using 9.5 and were hoping to use the new upsert
> functionality, but unfortunately it doesn’t quite do what we need.
>
> Our setup is something like this:
>
> CREATE TABLE t1 (
>   id BIGSERIAL NOT NULL PRIMARY KEY,
>   bk1 INT,
>   bk2 UUID
>   — other columns
> );
> CREATE UNIQUE INDEX t1_bk ON t1 (bk1, bk2);
>
> CREATE TABLE t2 (
>   t1_id BIGINT NOT NULL REFERENCES t1
>  — other stuff
> );
>
> Data comes in as inserts of one tuple each of t1 and t2. We expect inserts
> to t1 to be heavily duplicated. That is, for stuff coming in we expect a
> large number of rows to have duplicate (bk1, bk2), and we wish to discard
> those, but not discard the t2 tuple - those should always be inserted and
> reference the correct t1 record.
>
> So we currently have an insertion function that does this:
>
> BEGIN
>   INSERT INTO t1 (bk1, bk2, other columns)
>   VALUES (bk1val, bk2val, other values)
>   RETURNING id
>   INTO t1_id;
> EXCEPTION WHEN unique_violation THEN
>   SELECT id
>   FROM t1
>   WHERE bk1 = bk1val AND bk2 = bk2val
>   INTO t1_id;
> END;
>
> INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values);
>
> We were hoping that we’d be able to do something like this:
>
> INSERT INTO t1 (bk1, bk2, other columns)
>   VALUES (bk1val, bk2val, other values)
>   ON CONFLICT (bk1val, bk2val) DO NOTHING
>   RETURNING id
>   INTO t1_id;
> INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values);
>
> But unfortunately it seems that the RETURNING clause returns null when
> there’s a conflict, rather than the existing row’s value.
>
> I understand that there is ambiguity if there were multiple rows that were
> in conflict. I think this sort of functionality really only makes sense
> where the conflict target is a unique constraint, so IMO it would make
> sense to only support returning columns in that case.
>
> I imagine that this would be possible to do more efficiently than the
> subsequent query that we are currently doing given that postgres has
> already found the rows in question, in the index at least. I have no idea
> how hard it would actually be to implement though. FWIW my use-case would
> be supported even if this only worked for indexes where the to-be-returned
> columns were stored in the index using Anastasia’s covering + unique index
> patch, when that lands.
>
> Thoughts?
>
> Tom
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

[HACKERS] Feature suggestion: ON CONFLICT DO NOTHING RETURNING which returns existing row data

[Tom Dunstan]

Hi all

We recently moved to using 9.5 and were hoping to use the new upsert 
functionality, but unfortunately it doesn’t quite do what we need.

Our setup is something like this:

CREATE TABLE t1 (
  id BIGSERIAL NOT NULL PRIMARY KEY,
  bk1 INT,
  bk2 UUID
  — other columns
);
CREATE UNIQUE INDEX t1_bk ON t1 (bk1, bk2);

CREATE TABLE t2 (
  t1_id BIGINT NOT NULL REFERENCES t1
 — other stuff
);

Data comes in as inserts of one tuple each of t1 and t2. We expect inserts to 
t1 to be heavily duplicated. That is, for stuff coming in we expect a large 
number of rows to have duplicate (bk1, bk2), and we wish to discard those, but 
not discard the t2 tuple - those should always be inserted and reference the 
correct t1 record.

So we currently have an insertion function that does this:

BEGIN
  INSERT INTO t1 (bk1, bk2, other columns)
  VALUES (bk1val, bk2val, other values)
  RETURNING id
  INTO t1_id;
EXCEPTION WHEN unique_violation THEN
  SELECT id
  FROM t1
  WHERE bk1 = bk1val AND bk2 = bk2val
  INTO t1_id;
END;

INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values);

We were hoping that we’d be able to do something like this:

INSERT INTO t1 (bk1, bk2, other columns)
  VALUES (bk1val, bk2val, other values)
  ON CONFLICT (bk1val, bk2val) DO NOTHING
  RETURNING id
  INTO t1_id;
INSERT INTO t2(t1_id, other columns) VALUES(t1_id, other values);

But unfortunately it seems that the RETURNING clause returns null when there’s 
a conflict, rather than the existing row’s value.

I understand that there is ambiguity if there were multiple rows that were in 
conflict. I think this sort of functionality really only makes sense where the 
conflict target is a unique constraint, so IMO it would make sense to only 
support returning columns in that case.

I imagine that this would be possible to do more efficiently than the 
subsequent query that we are currently doing given that postgres has already 
found the rows in question, in the index at least. I have no idea how hard it 
would actually be to implement though. FWIW my use-case would be supported even 
if this only worked for indexes where the to-be-returned columns were stored in 
the index using Anastasia’s covering + unique index patch, when that lands.

Thoughts?

Tom



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

[HACKERS] Feature Suggestion: PL/Js

[Kiswono Prayogo]

by using latest v8 engine from google, is it possible to build PL/Js
just like other PL in Postgre? such as PL/PHP
what should i learn if i want to build PL/Js?
thanks in advance.

regards,
Kis
GB

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

Re: [HACKERS] Feature Suggestion: PL/Js

[Peter Eisentraut]

On Wed, 2009-10-07 at 15:00 +0700, Kiswono Prayogo wrote:
 by using latest v8 engine from google, is it possible to build PL/Js
 just like other PL in Postgre? such as PL/PHP
 what should i learn if i want to build PL/Js?

Start here:

http://developer.postgresql.org/pgdocs/postgres/plhandler.html

I notice that this has not been updated for the new inline handlers, but
that shouldn't stop you.


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

Re: [HACKERS] Feature Suggestion: PL/Js

[Alvaro Herrera]

Kiswono Prayogo escribió:
 by using latest v8 engine from google, is it possible to build PL/Js
 just like other PL in Postgre? such as PL/PHP
 what should i learn if i want to build PL/Js?

I think Josh Tolley has some slides on how we built PL/LOLCODE that
could prove useful.

BTW I've seen requests for PL/Js so I'm sure it'll be welcome.  What
license is v8 under?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] Feature Suggestion: PL/Js

[Andrew Dunstan]

Alvaro Herrera wrote:

Kiswono Prayogo escribió:
  

by using latest v8 engine from google, is it possible to build PL/Js
just like other PL in Postgre? such as PL/PHP
what should i learn if i want to build PL/Js?



I think Josh Tolley has some slides on how we built PL/LOLCODE that
could prove useful.

BTW I've seen requests for PL/Js so I'm sure it'll be welcome.  What
license is v8 under?

  


It's a BSD license, but it's a C++ API. While it looks cool, I think 
SpiderMonkey is possibly a better bet.


cheers

andrew

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

Re: [HACKERS] Feature Suggestion: PL/Js

[Joshua Tolley]

On Wed, Oct 07, 2009 at 10:22:02AM -0400, Alvaro Herrera wrote:
 Kiswono Prayogo escribió:
  by using latest v8 engine from google, is it possible to build PL/Js
  just like other PL in Postgre? such as PL/PHP
  what should i learn if i want to build PL/Js?
 
 I think Josh Tolley has some slides on how we built PL/LOLCODE that
 could prove useful.

Said slides are available here:
http://www.pgcon.org/2009/schedule/events/159.en.html

I hope they can be useful.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] Feature Suggestion: PL/Js

[Alvaro Herrera]

Joshua Tolley escribió:
 On Wed, Oct 07, 2009 at 10:22:02AM -0400, Alvaro Herrera wrote:
  Kiswono Prayogo escribió:
   by using latest v8 engine from google, is it possible to build PL/Js
   just like other PL in Postgre? such as PL/PHP
   what should i learn if i want to build PL/Js?
  
  I think Josh Tolley has some slides on how we built PL/LOLCODE that
  could prove useful.

Huh, I didn't mean we built but he built!

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Feature Suggestion: PL/Js

[Massa, Harald Armin]

 I think Josh Tolley has some slides on how we built PL/LOLCODE that
 could prove useful.

 BTW I've seen requests for PL/Js so I'm sure it'll be welcome.  What
 license is v8 under?


the new BSD License

http://code.google.com/p/v8/


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality

Re: [HACKERS] Feature Suggestion: PL/Js

[David E. Wheeler]

On Oct 7, 2009, at 7:55 AM, Andrew Dunstan wrote:


BTW I've seen requests for PL/Js so I'm sure it'll be welcome.  What
license is v8 under?


It's a BSD license, but it's a C++ API. While it looks cool, I think  
SpiderMonkey is possibly a better bet.


SquirrelFish? http://webkit.org/blog/189/announcing-squirrelfish/

Best,

David

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

Re: [HACKERS] Feature Suggestion: PL/Js

[Marcelo Costa]

On Wed, Oct 7, 2009 at 5:00 AM, Kiswono Prayogo kisw...@gmail.com wrote:

 by using latest v8 engine from google, is it possible to build PL/Js
 just like other PL in Postgre? such as PL/PHP
 what should i learn if i want to build PL/Js?
 thanks in advance.

 regards,
 Kis
 GB


You also can see this link:

http://xen.samason.me.uk/~sam/repos/pljs/README

-- 
Marcelo Costa
www.marcelocosta.net
-
“You can't always get what want”,

Doctor House in apology to Mike Jagger

Re: [HACKERS] Feature Suggestion: PL/Js

[Joshua Tolley]

On Wed, Oct 07, 2009 at 11:29:15AM -0400, Alvaro Herrera wrote:
 Joshua Tolley escribió:
  On Wed, Oct 07, 2009 at 10:22:02AM -0400, Alvaro Herrera wrote:
   Kiswono Prayogo escribió:
by using latest v8 engine from google, is it possible to build PL/Js
just like other PL in Postgre? such as PL/PHP
what should i learn if i want to build PL/Js?
   
   I think Josh Tolley has some slides on how we built PL/LOLCODE that
   could prove useful.
 
 Huh, I didn't mean we built but he built!

I didn't feel like you were stealing credit. I certainly couldn't have built
it, such as it is, without support from -hackers. :)

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] Feature Suggestion: PL/Js

[Kiswono Prayogo]

 Said slides are available here:
 http://www.pgcon.org/2009/schedule/events/159.en.html

 I hope they can be useful.

 --
 Joshua Tolley / eggyknap

ok i will read it, thanks ^^

On Wed, Oct 7, 2009 at 11:18 PM, David E. Wheeler da...@kineticode.com wrote:
 On Oct 7, 2009, at 7:55 AM, Andrew Dunstan wrote:

 BTW I've seen requests for PL/Js so I'm sure it'll be welcome.  What
 license is v8 under?

 It's a BSD license, but it's a C++ API. While it looks cool, I think
 SpiderMonkey is possibly a better bet.

 SquirrelFish? http://webkit.org/blog/189/announcing-squirrelfish/

 Best,

 David


i guess v8 still the best javascript interpreter
http://waynepan.com/2008/09/02/v8-tracemonkey-squirrelfish-ie8-benchmarks/
nitro from apple safari would be the second best..
http://www.computerworld.com/s/article/9128638/Safari_4_rivals_Google_Chrome_in_JavaScript_race?intsrc=news_ts_head
but possibly that benchmark is not really valid because it also
benchmark the rendering..

On Wed, Oct 7, 2009 at 11:38 PM, Marcelo Costa marcelojsco...@gmail.com wrote:


 On Wed, Oct 7, 2009 at 5:00 AM, Kiswono Prayogo kisw...@gmail.com wrote:

 by using latest v8 engine from google, is it possible to build PL/Js
 just like other PL in Postgre? such as PL/PHP
 what should i learn if i want to build PL/Js?
 thanks in advance.

 regards,
 Kis
 GB


 You also can see this link:

 http://xen.samason.me.uk/~sam/repos/pljs/README

 --
 Marcelo Costa
 www.marcelocosta.net
 -
 “You can't always get what want”,

 Doctor House in apology to Mike Jagger


hmm.. someone has built it first, i'll try it in the moment ^^

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

Re: [HACKERS] Feature Suggestion: PL/Js

[Joshua D. Drake]

On Thu, 2009-10-08 at 07:06 +0700, Kiswono Prayogo wrote:
  Said slides are available here:
  http://www.pgcon.org/2009/schedule/events/159.en.html
 
  I hope they can be useful.
 
  --
  Joshua Tolley / eggyknap
 
 ok i will read it, thanks ^^

And video:

http://www.vimeo.com/3728119

Joshua D. Drake

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - 
Salamander


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

Re: [HACKERS] Feature Suggestion: PL/Js

[Alvaro Herrera]

Kiswono Prayogo escribió:

 i guess v8 still the best javascript interpreter
 http://waynepan.com/2008/09/02/v8-tracemonkey-squirrelfish-ie8-benchmarks/
 nitro from apple safari would be the second best..
 http://www.computerworld.com/s/article/9128638/Safari_4_rivals_Google_Chrome_in_JavaScript_race?intsrc=news_ts_head
 but possibly that benchmark is not really valid because it also
 benchmark the rendering..

Performance is not the only useful measure.  Embeddability in Postgres
is very important, and it being C++ is not a good starting point.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Feature Suggestion: PL/Js

[Tom Lane]

Peter Eisentraut pete...@gmx.net writes:
 Start here:
 http://developer.postgresql.org/pgdocs/postgres/plhandler.html
 I notice that this has not been updated for the new inline handlers, but
 that shouldn't stop you.

Actually, that chapter is so old it didn't get updated for language
validators either :-(.  I added some text, and changed the link in
create_language.sgml so hopefully we'll notice it next time.

regards, tom lane

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

[HACKERS] feature suggestion

[Rafael Azevedo]

People,

I'd like to suggest you guys to implement a new feature.
Actually is an alias for a existent feature.

Unstead of having to type all the insert syntax, using (column) names, you
could do the same as MySQL does.
for example:

INSERT INTO Table SET
Field1 = 'text',
Field2 = 'text';

So it would make it easier and faster to develop applications using Postgre.

Thanks.
Rafael

Re: [HACKERS] feature suggestion

[Bruce Momjian]

Rafael Azevedo wrote:
 People,
 
 I'd like to suggest you guys to implement a new feature.
 Actually is an alias for a existent feature.
 
 Unstead of having to type all the insert syntax, using (column) names, you
 could do the same as MySQL does.
 for example:
 
 INSERT INTO Table SET
 Field1 = 'text',
 Field2 = 'text';
 
 So it would make it easier and faster to develop applications using Postgre.

There is an SQL standard way to do this and we have no intention of
extending that.  If you want standard code, use the standard syntax.
MySQL, if it supports this, is just encouraging you to write
non-standard SQL.  You can complain to them.

-- 
  Bruce Momjian  [EMAIL PROTECTED]  http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] feature suggestion

[Gregory Stark]

Rafael Azevedo [EMAIL PROTECTED] writes:

 Unstead of having to type all the insert syntax, using (column) names, you
 could do the same as MySQL does.
 for example:

 INSERT INTO Table SET
 Field1 = 'text',
 Field2 = 'text';

 So it would make it easier and faster to develop applications using Postgre.

I'm a bit mystified here. What exactly about this syntax is easier or faster?
You still have to list all the column names. It looks like it would require
just as much typing as the regular syntax, no?

Or is it that you get to reuse the same string you use for doing an update?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


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

Re: [HACKERS] feature suggestion

[Bruce Momjian]

Gregory Stark wrote:
 Rafael Azevedo [EMAIL PROTECTED] writes:
 
  Unstead of having to type all the insert syntax, using (column) names, you
  could do the same as MySQL does.
  for example:
 
  INSERT INTO Table SET
  Field1 = 'text',
  Field2 = 'text';
 
  So it would make it easier and faster to develop applications using Postgre.
 
 I'm a bit mystified here. What exactly about this syntax is easier or faster?
 You still have to list all the column names. It looks like it would require
 just as much typing as the regular syntax, no?
 
 Or is it that you get to reuse the same string you use for doing an update?

As far as I can see, the _feature_ is matching MySQL optional
non-standard syntax.

-- 
  Bruce Momjian  [EMAIL PROTECTED]  http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

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

Re: [HACKERS] feature suggestion

[Rafael Azevedo]

Imagine that you have about 30 fields.
Ok, then your first SQL is done.
Now, you just have to add 10 more fields.
Its very easy to get lost. If we have this implementation, you could just
add

Field31 = 'text',
Field32 = 'text'
...
wherever you want.

This is just a PLUS. I just don't see any problem by doing this.
Even knowing that this is not Standard SQL-Syntax, I just see this as a
benefit feature.

Another reason is that we have more people migrating from MySQL to Postgre
than any other database server. People don't migrate to Postgre from Oracle.
Hardly from MS SQL Server.

It just makes easier to migrate users from other db servers.
And this is sure not hard to implement.

Today its easier to migrate to PostgreSQL from MySQL than from PostgreSQL to
MySQL.

In few words I have given you more reason to add this feature than not to
add it.

Think about it.



2007/7/31, Rafael Azevedo [EMAIL PROTECTED]:

 Yes it is. And it makes easier to migrate from MySQL servers to
 PostgreSQL.
 Today its easier to migrate to MySQL from PostgreSQL than from PostgreSQL
 to MySQL.


 2007/7/31, Bruce Momjian [EMAIL PROTECTED]:
 
  Gregory Stark wrote:
   Rafael Azevedo [EMAIL PROTECTED]  writes:
  
Unstead of having to type all the insert syntax, using (column)
  names, you
could do the same as MySQL does.
for example:
   
INSERT INTO Table SET
Field1 = 'text',
Field2 = 'text';
   
So it would make it easier and faster to develop applications using
  Postgre.
  
   I'm a bit mystified here. What exactly about this syntax is easier or
  faster?
   You still have to list all the column names. It looks like it would
  require
   just as much typing as the regular syntax, no?
  
   Or is it that you get to reuse the same string you use for doing an
  update?
 
  As far as I can see, the _feature_ is matching MySQL optional
  non-standard syntax.
 
  --
  Bruce Momjian  [EMAIL PROTECTED]   http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com
 
  + If your life is a hard drive, Christ can be your backup. +
 

Re: [HACKERS] feature suggestion

[Josh Berkus]

Rafael,

 This is just a PLUS. I just don't see any problem by doing this.
 Even knowing that this is not Standard SQL-Syntax, I just see this as a
 benefit feature.

Our project has a policy of upholding the SQL standard whereever possible.  
For that reason, we don't approve non-standard syntax just for reasons of 
accessibility.  Any non-standard syntax we approve needs to add 
significant extra functionality to the DBMS, not just convenience, and 
certainly not because MySQL does it.

Standards are important is one of the themes of PostgreSQL which 
differentiates us from MySQL.

 Another reason is that we have more people migrating from MySQL to
 Postgre than any other database server. People don't migrate to Postgre
 from Oracle. Hardly from MS SQL Server.

You're mistaken.  I think we get more migrations from Oracle than from 
MySQL.  And quite a few from DB2 and Informix.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---(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: [HACKERS] feature suggestion

[Merlin Moncure]

On 8/1/07, Rafael Azevedo [EMAIL PROTECTED] wrote:
 Imagine that you have about 30 fields.
 Ok, then your first SQL is done.
 Now, you just have to add 10 more fields.
 Its very easy to get lost. If we have this implementation, you could just
 add

 Field31 = 'text',
 Field32 = 'text'

I have to admit this syntax has a lot of advantages over the
insert...values statement, especially in dynamic sql situations.  That
being said, more and more I just write queries insert..select which
would be an awkward fit.  mysql compatibility is usually pretty poor
justification of a feature (they have a million ways to do everything)
and things have to stand on general merit.

It is really quite unfortunate the way certain aspects of the sql
standard evolved (indirectly causing these types of issues) but that
is a topic for another day :)

merlin

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

Re: [HACKERS] feature suggestion

[Rafael Azevedo]

Well. Ok.
Then I'll just do it myself.
Just thought it would be good for thousands of users.
As I said, it was just a suggestion.
I surely aint the only one who ever thought about it.

Thanks anyway.


2007/7/31, Josh Berkus [EMAIL PROTECTED]:

 Rafael,

  This is just a PLUS. I just don't see any problem by doing this.
  Even knowing that this is not Standard SQL-Syntax, I just see this as a
  benefit feature.

 Our project has a policy of upholding the SQL standard whereever possible.
 For that reason, we don't approve non-standard syntax just for reasons of
 accessibility.  Any non-standard syntax we approve needs to add
 significant extra functionality to the DBMS, not just convenience, and
 certainly not because MySQL does it.

 Standards are important is one of the themes of PostgreSQL which
 differentiates us from MySQL.

  Another reason is that we have more people migrating from MySQL to
  Postgre than any other database server. People don't migrate to Postgre
  from Oracle. Hardly from MS SQL Server.

 You're mistaken.  I think we get more migrations from Oracle than from
 MySQL.  And quite a few from DB2 and Informix.

 --
 --Josh

 Josh Berkus
 PostgreSQL @ Sun
 San Francisco




-- 
Atenciosamente,

Rafael Azevedo
.: Diretor
:: WEBPRO SOLUÇÕES DIGITAIS
:: Telefone: 51 3266.3446
:: Celular: 51 9243.9893
:: http://www.webpro.com.br
:: Email: [EMAIL PROTECTED]
:::
:: Conheça o MAILMAN, Solução em E-mail Marketing
:: http://www.mailman.com.br/

Re: [HACKERS] Feature suggestion: Postgresql binding to one IP?

[Mark Pritchard]

Hi Lincoln,

Not sure why you would want to run multiple instances, since you can run
multiple dbs if you want to maintain separate environments but if you really
need to do this, the postmaster has some options which control ip/port
binds:

[pritchma@blade pritchma]$ /usr/local/pgsql/bin/postmaster --help
/usr/local/pgsql/bin/postmaster is the PostgreSQL server.

Usage:
  /usr/local/pgsql/bin/postmaster [options...]

Options:
  -B NBUFFERS number of shared buffers (default 64)
  -c NAME=VALUE   set run-time parameter
  -d 1-5  debugging level
  -D DATADIR  database directory
  -F  turn fsync off
  -h HOSTNAME host name or IP address to listen on
  -i  enable TCP/IP connections
  -k DIRECTORYUnix-domain socket location
  -N MAX-CONNECT  maximum number of allowed connections (1..1024, default
32)
  -o OPTIONS  pass 'OPTIONS' to each backend server
  -p PORT port number to listen on (default 5432)
  -S  silent mode (start in background without logging output)

Developer options:
  -n  do not reinitialize shared memory after abnormal exit
  -s  send SIGSTOP to all backend servers if one dies

I run postgres on a box with two interfaces, and I only want it to bind to a
single one:

# start postgres
nohup  /dev/null su -c '/usr/local/pgsql/bin/postmaster -h 10.4.0.1 -i -D
/usr/local/pgsql/data  /usr/local/pgsql/log/server.log 21' postgres 


Cheers,

Mark Pritchard


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

Re: [HACKERS] Feature suggestion: Postgresql binding to one

[Lincoln Yeoh]

At 11:16 PM 03-10-2001 -0400, Tom Lane wrote:
Lincoln Yeoh [EMAIL PROTECTED] writes:
 Is it possible for Postgresql to bind to one IP address? 

See 'virtual_host' GUC parameter.

   regards, tom lane

Thanks!

I'm using a redhat style postgresql init and somehow postgresql seems to
ignore the postgresql.conf file. What's the postmaster.opts file for?

Cheerio,
Link.


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

Re: [HACKERS] Feature suggestion: Postgresql binding to one

[Tom Lane]

Lincoln Yeoh [EMAIL PROTECTED] writes:
 I'm using a redhat style postgresql init and somehow postgresql seems to
 ignore the postgresql.conf file.

That seems unlikely, assuming you are running a PG version recent enough
to have a postgresql.conf file (ie 7.1 or better).  What exactly are you
putting into postgresql.conf?  Also, take a look at the postmaster log
to see if it's issuing any complaints.  I believe a syntax error
anywhere in the conf file will cause the whole file to be ignored ...

 What's the postmaster.opts file for?

It's to log the command-line options you gave to the postmaster.

regards, tom lane

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

http://archives.postgresql.org

[HACKERS] Feature suggestion: Postgresql binding to one IP?

[Lincoln Yeoh]

Hi people,

Is it possible for Postgresql to bind to one IP address? 

I'm trying to run multiple postgresql installations on one server.

The unix socket could be named accordingly:

Postgresql config bound to a particular port and all IPs.
.s.PGSQL.portnumber 

Postgresql config bound to a particular port and IP.
.s.PGSQL.portnumber.ipaddress

Any other suggestions/comments on running multiple instances of postgresql
are welcomed.

An less desirable alternative is to keep binding to all IP, use different
ports and name the ports, but specifying the port by name in -p doesn't work. 

Cheerio,
Link.


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Feature suggestion: Postgresql binding to one IP?

[Tom Lane]

Lincoln Yeoh [EMAIL PROTECTED] writes:
 Is it possible for Postgresql to bind to one IP address? 

See 'virtual_host' GUC parameter.

regards, tom lane

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