Re: [HACKERS] binds only for s,u,i,d?
On Mon, 2006-07-03 at 23:28 -0400, Agent M wrote: Why are only select, insert, update, and delete supported for $X binds? This is a property of the way prepared statements are implemented. Prepared statement parameters can be used in the place of expressions in optimizeable statements (the actual parameter substitution is done by the executor). Hence you can only have parameters in places where you can have expressions. Why can't preparation be used as a global anti-injection facility? Well, you can't reasonably allow parameters to appear just anywhere in a statement, if you want to have a hope of parsing the statement: consider PREPARE foo AS $1; EXECUTE foo(SELECT 1);, for example. It would be somewhat more reasonable to allow parameters to be used in the place of identifiers, but even then, you wouldn't be able to do very much meaningful analysis or optimization when the statement was prepared (for example, adding new relations to a SELECT query at EXECUTE-time could change the semantics of the query). All that work would need to be deferred to EXECUTE-time, which would largely defeat the purpose of server-side prepared statements, no? -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] update/insert, delete/insert efficiency WRT vacuum
Ühel kenal päeval, T, 2006-07-04 kell 14:53, kirjutas Zeugswetter
Andreas DCP SD:
Is there a difference in PostgreSQL performance between these two
different strategies:
if(!exec(update foo set bar='blahblah' where name = 'xx'))
exec(insert into foo(name, bar) values('xx','blahblah'); or
In pg, this strategy is generally more efficient, since a pk failing
insert would create
a tx abort and a heap tuple. (so in pg, I would choose the insert first
strategy only when
the insert succeeds most of the time (say 95%))
Note however that the above error handling is not enough, because two
different sessions
can still both end up trying the insert (This is true for all db systems
when using this strategy).
I think the recommended strategy is to first try tu UPDATE, if not found
then INSERT, if primary key violation on insert, then UPDATE
--
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] Creating custom Win32 installer
I need to build custom win32 binary package for PostgreSQL. I've downloaded source for PGinstaller but found them hard to understand - WiX toolkit and MSI is totally alien territory for me. Things I need to modify: 1. Exclude all unneccessary extensions such as PostGIS 2. Add some other extension 3. Add some environment variables to server process (as needed by modified version of OpenSSL) 4. Enable openssl by default and create certificate signing request during installation. By quick examination of pginstaller sources I haven't found how it does register postgresql as service. When I compile postgres from sources and start it using pg_ctl, it starts as console process, and closing of command line window where it have been started, kill it. I know about separate tools to run arbitrary program as service, such as one in Cygwin suite, but it seems that PGinstaller doesn't include such tool. Can anyone provide some hints how postgresql on Windows work and where to dig to make custom installer? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Creating custom Win32 installer
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Victor B. Wagner Sent: 05 July 2006 12:28 To: pgsql-hackers@postgresql.org Subject: [HACKERS] Creating custom Win32 installer I need to build custom win32 binary package for PostgreSQL. I've downloaded source for PGinstaller but found them hard to understand - WiX toolkit and MSI is totally alien territory for me. Things I need to modify: 1. Exclude all unneccessary extensions such as PostGIS In wxs/pginst.wxs, remove the appropriate Component and Feature sections. E.g. To remove JDBC, you would take out: !-- *** JDBC *** -- Directory Id=JDBCDIR Name=jdbc Component Id=jdbc Guid=412ED45F-047B-4A63-8C09-590DE16B4C5E File Id=jdbc2 LongName=postgresql-8.1-405.jdbc2.jar Name=jdbc2.jar DiskId=1 src=$(var.PKGDIR)/jdbc/postgresql-8.1-405.jdbc2.jar / File Id=jdbc2ee LongName=postgresql-8.1-405.jdbc2ee.jar Name=jdbc2ee.jar DiskId=1 src=$(var.PKGDIR)/jdbc/postgresql-8.1-405.jdbc2ee.jar / File Id=jdbc3 LongName=postgresql-8.1-405.jdbc3.jar Name=jdbc3.jar DiskId=1 src=$(var.PKGDIR)/jdbc/postgresql-8.1-405.jdbc3.jar / /Component Component Id=postgisjdbc Guid=2A1DA975-176C-486E-BC27-D67EB98D2B4F File Id=postgisjdbc LongName=postgis_1_0_0.jar Name=postgis.jar DiskId=1 src=$(var.PKGDIR)/postgis/jdbc/postgis_1_0_0.jar / /Component /Directory ... And ... Feature Id=jdbc Title=JDBC Driver Level=1 Description=The PostgreSQL JDBC driver. AllowAdvertise=no ComponentRef Id=jdbc / /Feature (as well as the postgisjdbc feature). Some parts (those that require additional processing to install) are harder to remove, e.g. PostGIS. In these cases, remove the sections as above, but also look out for: Custom Action=PrepInstallPostgis After=InstallFilesamp;postgis=3 AND DOSERVICE=1 AND DOINITDB=1 AND MaintenanceType=Modify/Custom Custom Action=InstallPostgis After=InstallContribamp;postgis=3 AND DOSERVICE=1 AND DOINITDB=1 AND MaintenanceType=Modify/Custom ... And ... Property Id=POSTGIS Value= Secure=yes / ... And ... ProgressText Action=InstallPostgisActivating PostGIS.../ProgressText ... And ... CustomAction Id=PrepInstallPostgis Property=InstallPostgis Value=[UILANG];[SUPERUSER];[SUPERPASSWORD];[LISTENPORT];[SHARECONTRIBDI R];[POSTGIS] Execute=immediate / CustomAction Id=InstallPostgis Return=check BinaryKey=pginstca DllEntry=[EMAIL PROTECTED] Execute=deferred / As well as the UI in wxs/uidata.wxs, and the custom action code that installs PostGIS in ca/pginstca.c It sounds a lot, but once you get used to it it's pretty straightforward. 2. Add some other extension To simply install a new feature, just add the required components and a feature section. If you need post-processing of any kind, you'll need to add an appropriate CustomAction, and the required C function. 3. Add some environment variables to server process (as needed by modified version of OpenSSL) You'll need to write a CustomAction to modify the service user account's environment. 4. Enable openssl by default and create certificate signing request during installation. CustomAction again. By quick examination of pginstaller sources I haven't found how it does register postgresql as service. When I compile postgres from sources and start it using pg_ctl, it starts as console process, and closing of command line window where it have been started, kill it. I know about separate tools to run arbitrary program as service, such as one in Cygwin suite, but it seems that PGinstaller doesn't include such tool. No, pg_ctl.exe is a service as well as a standalone app. Look at the service component in wxs/pginst.wxs. It calls ServiceInstall to install the service. For more info, look at the WiX schema docs, and in particular, the docs on MSDN (http://windowssdk.msdn.microsoft.com/en-us/library/ms710796.aspx). They tend to speak of 'Tables', just remember that the WiX is what builds those tables so you can see that tags in the wxs file relate to rows being added to tables. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] The problem of an inline definition by construction in
Hello,
I've got problems building the client libraries. It seems that there this
problem is already known and dicussed this mailing list earlier:
(snip)
Patch applied to CVS HEAD and 8.1.X. Thanks.
Borland CC also needed this change, so I modified your patch appropriately.
---
Hiroshi Saito wrote:
Dear Bruce san.
I neglected sufficient test before a release.:-(
Problem appears by construction in win32 of 8.1.4.
nmake -f win32.mak
...
(snap)
I've searched for the Patch applied to ..., but cannot find it. So I
tried to solve the problem by myself.
My platform is WindowsXP and I've installed PostreSQL 8.1.4 and Microsoft
Visual Studio 6 SP6 (yes, I know it's not the newest version...). I've
committed the following steps:
(snip)
d:\Programme\PostgreSQL\8.1\source\srcnmake /f win32.mak
Microsoft (R) Program Maintenance-Dienstprogramm: Version 6.00.9782.0
Copyright (C) Microsoft Corp 1988-1998. Alle Rechte vorbehalten.
cd include
if not exist pg_config.h copy pg_config.h.win32 pg_config.h
cd ..
cd interfaces\libpq
nmake /f win32.mak
Microsoft (R) Program Maintenance-Dienstprogramm: Version 6.00.9782.0
Copyright (C) Microsoft Corp 1988-1998. Alle Rechte vorbehalten.
Building the Win32 static library...
cl.exe @C:\DOKUME~1\Max\LOKALE~1\Temp\nma02636.
wchar.c
..\..\backend\utils\mb\wchar.c(100) : error C2054: Nach 'inline' muss '('
folgen
..\..\backend\utils\mb\wchar.c(101) : error C2085: 'pg_euc_mblen' : Nicht in
der
formalen Parameterliste enthalten
..\..\backend\utils\mb\wchar.c(101) : error C2143: Syntaxfehler : Fehlendes
';'
vor '{'
..\..\backend\utils\mb\wchar.c(116) : error C2054: Nach 'inline' muss '('
folgen
..\..\backend\utils\mb\wchar.c(117) : error C2085: 'pg_euc_dsplen' : Nicht
in de
r formalen Parameterliste enthalten
..\..\backend\utils\mb\wchar.c(117) : error C2143: Syntaxfehler : Fehlendes
';'
vor '{'
..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'
undefiniert;
Annahme: extern mit Rueckgabetyp int
..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'
undefiniert
; Annahme: extern mit Rueckgabetyp int
NMAKE : fatal error U1077: 'cl.exe' : Rueckgabe-Code '0x2'
Stop.
NMAKE : fatal error U1077: 'D:\Programme\Microsoft Visual
Studio\VC98\bin\NMAKE
.EXE' : Rueckgabe-Code '0x2'
Stop.
(snap)
In order to work around the problem I've tried numberous syntax-variations
in wchar.c(99) and wchar.c(115)
static int inline ... does not work
inline static int ... does not work
static __inline int ... worked
Static int ... worked (without inline)
Worked with restrictions. Next call of nmake /f win32.mak resulted in
this:
(snip)
d:\Programme\PostgreSQL\8.1\source\srcnmake /f win32.mak
Microsoft (R) Program Maintenance-Dienstprogramm: Version 6.00.9782.0
Copyright (C) Microsoft Corp 1988-1998. Alle Rechte vorbehalten.
cd include
if not exist pg_config.h copy pg_config.h.win32 pg_config.h
cd ..
cd interfaces\libpq
nmake /f win32.mak
Microsoft (R) Program Maintenance-Dienstprogramm: Version 6.00.9782.0
Copyright (C) Microsoft Corp 1988-1998. Alle Rechte vorbehalten.
Building the Win32 static library...
link.exe @C:\DOKUME~1\Max\LOKALE~1\Temp\nma03456.
LINK : fatal error LNK1181: Eingabedatei shfolder.lib kann nicht ge÷ffnet
werd
en
NMAKE : fatal error U1077: 'link.exe' : Rueckgabe-Code '0x49d'
Stop.
NMAKE : fatal error U1077: 'D:\Programme\Microsoft Visual
Studio\VC98\bin\NMAKE
.EXE' : Rueckgabe-Code '0x2'
Stop.
(snap)
There was no ShFolder.lib on my system. I found out that this is a part of
the Platform SDK whih comes with Visual Studio 2003. So I borrowed this PC
from a fellow and next call resulted in this:
(snip)
C:\Programme\PostgreSQL\8.1\source\srcnmake /f win32.mak
Microsoft (R) Program Maintenance Utility, Version 7.10.3077
Copyright (C) Microsoft Corporation. Alle Rechte vorbehalten.
cd include
if not exist pg_config.h copy pg_config.h.win32 pg_config.h
cd ..
cd interfaces\libpq
nmake /f win32.mak
Microsoft (R) Program Maintenance Utility, Version 7.10.3077
Copyright (C) Microsoft Corporation. Alle Rechte vorbehalten.
Building the Win32 static library...
cl.exe @C:\DOKUME~1\sfr\LOKALE~1\Temp\nm57.tmp
wchar.c
link.exe -lib @C:\DOKUME~1\sfr\LOKALE~1\Temp\nm58.tmp
link.exe @C:\DOKUME~1\sfr\LOKALE~1\Temp\nm59.tmp
libpqdll.def(3) : warning LNK4017: DESCRIPTION-Anweisung wird von der
Zielplattf
orm nicht unterstützt; ignoriert
Bibliothek '.\Release\libpqdll.lib' und Objekt '.\Release\libpqdll.exp'
wird
erstellt
libpq.lib(fe-connect.obj) : error LNK2019: Nicht aufgelöstes externes Symbol
'_S
HGetFolderPath', verwiesen in Funktion '_pqGetHomeDirectory'
.\Release\libpq.dll : fatal error LNK1120: 1 unaufgelöste externe Verweise
NMAKE : fatal error U1077: 'link.exe': R³ckgabe-Code '0x460'
Stop.
NMAKE :
Re: [HACKERS] passing parameters to CREATE INDEX
http://archives.postgresql.org/pgsql-hackers/2005-09/msg00851.php Just to follow up on the discussion of that thread: what's been implemented is a way to store arbitrary name=value strings in an index's pg_class entry, and to make these available in a pre-parsed form through the index relcache entry. However you'd have to be cautious about using the values directly for any fundamental index structure decisions, because ALTER INDEX will just change them without giving you an opportunity to modify the index in response. So depending on what you are doing, you might need to store the real values in the index metapage, and set those values from the reloptions parameters only at ambuild() time. This would mean that ALTER INDEX + REINDEX would be the I see. There is one more problem: pluggable parameters for index. For example, the parameter needed for tsearch2 (size of signature) isn't useful for others modules/opclasses. Another issue, GiST (and GIN too) doesn't have metapage at all for now, it's not a problem, but until now it wasn't needed. I think, we may can add to pg_opclass's definition method/parameter name and create some API (may be, index specific) to propagate parameter's to module's interface functions to index. Also: as of CVS tip ginoptions() accepts FILLFACTOR but nothing is done with it. Can you do something useful with FILLFACTOR in GIN? Now GIN is nested B-Tree: B-tree for entries (lexemes for tsearch2) and B-Tree for ItemPointers per entry if entry is popular enough. So fillfactor may be used as usual. Small advertising :) : http://www.sigaev.ru/gin/GinStructure.pdf -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] passing parameters to CREATE INDEX
Teodor Sigaev [EMAIL PROTECTED] writes: can add to pg_opclass's definition method/parameter name and create some API (may be, index specific) to propagate parameter's to module's interface functions to index. Huh? You can get them from the index's Relation structure. I don't think there's anything missing in the API. About all you need is an extended struct definition for rd_options, and to provide your own code substituting for default_reloptions(). An index AM can do both of those locally to itself. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] set search_path in dump output considered harmful
I've recently migrated one of my databases to using veil. This involved
creating a 'private' schema and moving all tables to it. Functions
remain in public, and secured views are created there which can be
accessed by normal users.
In doing so, I found to my extreme displeasure that although the
database continues to function flawlessly, I can no longer restore dumps
produced by pg_dump even after hours of manual tweaking. In all cases,
this is due to search_path being frobbed during the restore.
CASE 1: serial column not in the same schema as associated table
create table a(i serial primary key);
create schema notpublic;
alter SEQUENCE a_i_seq set schema notpublic;
Attempting to restore the output of pg_dump on a database in which the
above has been executed will result in the error:
SET search_path = notpublic, pg_catalog;
SET
--
-- Name: a_i_seq; Type: SEQUENCE SET; Schema: notpublic; Owner: pfrost
--
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('a', 'i'), 1, false);
ERROR: relation a does not exist
CASE 2: default set to the serial sequence of another table
create schema private;
create table private.t(i serial primary key);
alter sequence private.t_i_seq set schema public;
create table public.t(i integer primary key default nextval('t_i_seq'));
This is similar to case 1, and will encounter the same error first.
However, if that error is manually corrected, restoring the dump will
yield:
SET search_path = public, pg_catalog;
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('private.t', 'i'),
1, false);-- manually fixed
CREATE TABLE t (
i integer DEFAULT nextval('t_i_seq'::regclass) NOT NULL
);
ERROR: relation t_i_seq does not exist
The problem here seems to be that although the sequence t_i_seq is in
schema public in the dumped database, restoring the dump places it in
schema private.
CASE 3: functions containing unqualified function references
create schema private;
create function private.a(text) returns text language sql immutable as $$
select $1 || 'a'; $$;
set search_path = public, private;
create function public.b(text) returns text language sql immutable as $$ select
a($1); $$;
create table foo(t text);
insert into foo values ('foo');
create index foo_idx on foo ((b(t)));
Restoring the dump of this database yields:
...
CREATE INDEX foo_idx ON foo USING btree (b(t));
ERROR: function a(text) does not exist
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
CONTEXT: SQL function b during startup
The way I encountered this actually has little to do with veil. The
function involved in my case takes as parameters some values from
columns of a table and returns a tsvector to be indexed by tsearch2. I
suspect this would be common practice if the tsearch2 documentation did
not store the vector in an additional column.
CASE 4: functions using extension operators
Essentially the same as above, but the body of a function contains a
reference to an operator without specifying the schema with the
operator(schema.name) syntax. Again, contrib modules like tsearch2 are a
great way to encounter this problem.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] buildfarm stats
Joshua D. Drake wrote: On Tuesday 04 July 2006 22:14, Chris Mair wrote: Thanks for the stats Andrew. Out of interest, can you easily tabulate the number of failures against OS? Or, more generally, even put a dump of the DB (without personal infos of course :) somewhere? Bye, Chris. PS: and don't say you're running it in MySQL ;) Well as the host, I guarantee you that it is NOT running mySQL :) but it is about 2Gb of data, so just putting a dump cleaned of personal data somewhere isn't really an option. I could arrange a dump without the diagnostics, in these 2 tables: system: name | operating_system | os_version | compiler | compiler_version | architecture build: name | snapshot | stage | branch | build_flags (stage in the latter table is OK on success or the name of the stage that failed otherwise). But what do you want it for? And do you want it one-off or continuously? cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] update/insert,
On Tue, Jul 04, 2006 at 11:59:27AM +0200, Zdenek Kotala wrote:
Mark,
I don't know how it will exactly works in postgres but my expectations
are:
Mark Woodward wrote:
Is there a difference in PostgreSQL performance between these two
different strategies:
if(!exec(update foo set bar='blahblah' where name = 'xx'))
exec(insert into foo(name, bar) values('xx','blahblah');
or
The update code generates new tuple in the datafile and pointer has been
changed in the indexfile to the new version of tuple. This action does
not generate B-Tree structure changes. If update falls than insert
command creates new tuple in the datafile and it adds new item into
B-Tree. It should be generate B-Tree node split.
Actually, not true. Both versions will generate a row row and create a
new index tuple. The only difference may be that in the update case the
may be a ctid link from the old version to the new one, but that's
about it...
Which is faster will probably depends on what is more common in your DB:
row already exists or not. If you know that 99% of the time the row
will exist, the update will probably be faster because you'll only
execute one query 99% of the time.
OK, but the point of the question is that constantly updating a single row
steadily degrades performance, would delete/insery also do the same?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] update/insert,
Mark Woodward wrote:
On Tue, Jul 04, 2006 at 11:59:27AM +0200, Zdenek Kotala wrote:
Mark,
I don't know how it will exactly works in postgres but my expectations
are:
Mark Woodward wrote:
Is there a difference in PostgreSQL performance between these two
different strategies:
if(!exec(update foo set bar='blahblah' where name = 'xx'))
exec(insert into foo(name, bar) values('xx','blahblah');
or
The update code generates new tuple in the datafile and pointer has been
changed in the indexfile to the new version of tuple. This action does
not generate B-Tree structure changes. If update falls than insert
command creates new tuple in the datafile and it adds new item into
B-Tree. It should be generate B-Tree node split.
Actually, not true. Both versions will generate a row row and create a
new index tuple. The only difference may be that in the update case the
may be a ctid link from the old version to the new one, but that's
about it...
Which is faster will probably depends on what is more common in your DB:
row already exists or not. If you know that 99% of the time the row
will exist, the update will probably be faster because you'll only
execute one query 99% of the time.
OK, but the point of the question is that constantly updating a single row
steadily degrades performance, would delete/insery also do the same?
If that was the point of the question, you should have said so.
And unless I am much mistaken the answer is of course it will.
cheers
andrew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] update/insert,
OK, but the point of the question is that constantly updating a single row steadily degrades performance, would delete/insery also do the same? Yes, there is currently no difference (so you should do the update). Of course performance only degrades if vaccuum is not setup correctly. Andreas ---(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] update/insert,
On Wed, Jul 05, 2006 at 04:59:52PM +0200, Zeugswetter Andreas DCP SD wrote: OK, but the point of the question is that constantly updating a single row steadily degrades performance, would delete/insery also do the same? Yes, there is currently no difference (so you should do the update). Of course performance only degrades if vaccuum is not setup correctly. As Martijn pointed out, there are two differences. One almost insignificant having to do with internal linkage. The other that multiples queries are being executed. I would presume with separate query plans, and so on, therefore you should do the update. For the case you are talking about, the difference is: 1) Delete which will always succeed 2) Insert that will probably succeed Vs: 1) Update which if it succeeds, will stop 2) Insert that will probably succeed In the first case, you are always executing two queries. In the second, you can sometimes get away with only one query. Note what other people mentioned, though, that neither of the above is safe against parallel transactions updating or inserting rows with the same key. In both cases, a 'safe' implementation should loop if 2) fails and restart the operation. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] update/insert,
Which is faster will probably depends on what is more common in your DB: row already exists or not. If you know that 99% of the time the row will exist, the update will probably be faster because you'll only execute one query 99% of the time. OK, but the point of the question is that constantly updating a single row steadily degrades performance, would delete/insery also do the same? Yes. Delete still creates a dead row. There are programatic ways around this but keeping a delete table that can be truncated at intervals. Joshua D. Drake ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Scan Keys
I'm a bit confused about how scan keys work. Is there any simple way given a list of Datums of the same type as the index tuple attributes to get all matching index entries? This is for a non-system index. It seems like the only place in the code where non-system index lookups are done is nodeIndexscan.c where it has the strategy number, subtype, and function to use from the work that's previously been done on the expression. But I want to do something more like what btree does inside btinsert where it knows that no cross-type functions could be necessary and the only function of interest is equality. I tried just using index_getprocinfo(...,BTORDER) with InvalidStrategy like btree does but _bt_preprocess_keys runs into problems without a valid strategy number. And in any case that would be btree specific which seems like it ought not be necessary. -- greg ---(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: [HACKERS] binds only for s,u,i,d?
Neil Conway [EMAIL PROTECTED] writes: On Mon, 2006-07-03 at 23:28 -0400, Agent M wrote: Why can't preparation be used as a global anti-injection facility? All that work would need to be deferred to EXECUTE-time, which would largely defeat the purpose of server-side prepared statements, no? It would also defeat the anti-injection purpose. If you can use parameters to change the semantics of the query then you're not really protected any more. The whole security advantage of using parameters comes from knowing exactly what a query will do with the data you provide. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] binds only for s,u,i,d?
Greg Stark wrote: Neil Conway [EMAIL PROTECTED] writes: On Mon, 2006-07-03 at 23:28 -0400, Agent M wrote: Why can't preparation be used as a global anti-injection facility? All that work would need to be deferred to EXECUTE-time, which would largely defeat the purpose of server-side prepared statements, no? It would also defeat the anti-injection purpose. If you can use parameters to change the semantics of the query then you're not really protected any more. The whole security advantage of using parameters comes from knowing exactly what a query will do with the data you provide. Exactly. In particular, the suspect data should never hit the parser. You can defeat that with a function call, of course, but you have to work at it. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] lastval exposes information that currval does not
test=# create schema private;
CREATE SCHEMA
test=# create sequence private.seq;
CREATE SEQUENCE
test=# create function bump() returns bigint language sql security definer as
$$ select nextval('private.seq'); $$;
CREATE FUNCTION
test=# revoke usage on schema private from pfrost;
REVOKE
test=# grant select, update on private.seq to pfrost;
GRANT
test=# set role pfrost;
SET
test= select bump();
bump
--
1
(1 row)
test= select nextval('private.seq');
ERROR: permission denied for schema private
test= select currval('private.seq');
ERROR: permission denied for schema private
test= select lastval();
lastval
-
1
(1 row)
Aparrently, lastval remembers the last sequence by OID, and the check
for usage on a schema is made when resolving a name to an OID. Thus, the
schema usage check is never made for lastval.
Firstly there is the problem that this potentially reveals information
that was not visible prior to 8.1. Granted, I don't think this is a
serious security issue for most applications, but it does suprise me.
There is also the larger problem of the implementation of schema usage
checks. More serious functions might be added in the future that suffer
from the same vulnerability. For all I know, there might be some now. I
should think that a much better place for this check would be in the
same place that checks the ACL for the object itself.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] buildfarm stats
but it is about 2Gb of data, so just putting a dump cleaned of personal data somewhere isn't really an option. I could arrange a dump without the diagnostics, in these 2 tables: system: name | operating_system | os_version | compiler | compiler_version | architecture build: name | snapshot | stage | branch | build_flags (stage in the latter table is OK on success or the name of the stage that failed otherwise). But what do you want it for? And do you want it one-off or continuously? Nothing important at all. I'd just thought about a few interesting stats, like failures vs OS (as the first poster said) or failures vs gcc version or timings vs. arch / RAM or gcc version, etc. For the timings I guess there are some timestamps embedded that might be extracted... But I didn't really think about it, before posting (classic mailing list syndrome ;) Bye, Chris. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] buildfarm stats
Chris Mair wrote: but it is about 2Gb of data, so just putting a dump cleaned of personal data somewhere isn't really an option. I could arrange a dump without the diagnostics, in these 2 tables: system: name | operating_system | os_version | compiler | compiler_version | architecture build: name | snapshot | stage | branch | build_flags (stage in the latter table is OK on success or the name of the stage that failed otherwise). But what do you want it for? And do you want it one-off or continuously? Nothing important at all. I'd just thought about a few interesting stats, like failures vs OS (as the first poster said) or failures vs gcc version or timings vs. arch / RAM or gcc version, etc. For the timings I guess there are some timestamps embedded that might be extracted... But I didn't really think about it, before posting (classic mailing list syndrome ;) We don't have any timing info. For now this goes on the wishlist. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Scan Keys
On Wed, Jul 05, 2006 at 12:00:05PM -0400, Greg Stark wrote: I'm a bit confused about how scan keys work. Is there any simple way given a list of Datums of the same type as the index tuple attributes to get all matching index entries? This is for a non-system index. A scankey determines which values you want. It consists of a value and an operator. Using that the index code returns tuples matching. So if you want all values equal to 4, you pass '4' for the Datum and the Equal strategy, with the operator as '='. The info you need is in the operator class. In a sense you do need to know the type of index you're scanning, not all indexes use the same strategy numbers. But I want to do something more like what btree does inside btinsert where it knows that no cross-type functions could be necessary and the only function of interest is equality. By the time the btree code gets involved, everything in the scankey should already have been filled in. I don't beleive the code actually checks if the operator is of the type you specify. Hope this helps a bit, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] binds only for s,u,i,d?
On Wed, 2006-07-05 at 06:55 -0400, Agent M wrote: Like you said, it would make sense to have binds anywhere where there are quoted strings- if only for anti-injection. There could be a flat plan which simply did the string substitution with the proper escaping at execute time. I don't see the point of implementing this in the backend. Perhaps what you're really asking for is basically PQescapeIdentifier()? Escaping vulnerabilities would then be taken care of by server updates. Escaping vulnerabilities are hardly the common case; in any case, implementing this in libpq would allow a similar upgrade path. -Neil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] lastval exposes information that currval does not
On Jul 5, 2006, at 14:51, Phil Frost wrote:
test=# create function bump() returns bigint language sql security
definer as $$ select nextval('private.seq'); $$;
SECURITY DEFINER means that the function runs with the permissions of
the role used to create the function (ran the CREATE FUNCTION
command). Due to your # prompt, I'm guessing that you were a
superuser when you ran this command. Thus, bump() will be run with
the superuser's permissions.
The superuser most definitely has permissions to access private.seq.
This has nothing to do with schema security or lastval() versus
currval().
Check out the CREATE FUNCTION documentation:
http://www.postgresql.org/docs/8.1/interactive/sql-
createfunction.html
- Chris
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] lastval exposes information that currval does not
On Wed, Jul 05, 2006 at 08:06:12PM -0400, Chris Campbell wrote:
On Jul 5, 2006, at 14:51, Phil Frost wrote:
test=# create function bump() returns bigint language sql security
definer as $$ select nextval('private.seq'); $$;
SECURITY DEFINER means that the function runs with the permissions of
the role used to create the function (ran the CREATE FUNCTION
command). Due to your # prompt, I'm guessing that you were a
superuser when you ran this command. Thus, bump() will be run with
the superuser's permissions.
The superuser most definitely has permissions to access private.seq.
This has nothing to do with schema security or lastval() versus
currval().
Check out the CREATE FUNCTION documentation:
http://www.postgresql.org/docs/8.1/interactive/sql-
createfunction.html
I am well aware of what security definer means. The significant part of
this example is that lastval() will allow the caller to see the value of
a sequence where currval('seq') will not. This means that things which
might have been forbidden in 8.0 are now accessible in 8.1.
It also means that revoking usage on a schema is not sufficient to
prevent a user from accessing things within that schema, a property that
makes me quite uncomfortable.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] lastval exposes information that currval does not
I am well aware of what security definer means. The significant part of
this example is that lastval() will allow the caller to see the value of
a sequence where currval('seq') will not. This means that things which
might have been forbidden in 8.0 are now accessible in 8.1.
It also means that revoking usage on a schema is not sufficient to
prevent a user from accessing things within that schema, a property that
makes me quite uncomfortable.
Then the public schema must drive you nuts :). If you were to create the
function as a non-super user you would probably be good.
Joshua D. Drake
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Scan Keys
Martijn van Oosterhout kleptog@svana.org writes: The info you need is in the operator class. In a sense you do need to know the type of index you're scanning, not all indexes use the same strategy numbers. Well what was tripping me up was figuring out the operator class. I just realized it's in the index's Relation object. But yes what you describe is really a problem. Even given the operator class there's no way for me to know which strategy number to pick. There might not be any strategy number for equals in which case I'm in trouble. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
