Re: [PATCHES] display and expression of the home directory in Win32
Am Freitag, 6. Januar 2006 04:03 schrieb Hiroshi Saito: [pg_config] HOMEDIR = C:/Documents and Settings/saito/Application Data/postgresql Where does this come from and what would it be good for? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] display and expression of the home directory in Win32
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Peter Eisentraut Sent: 06 January 2006 11:07 To: pgsql-patches@postgresql.org Cc: Hiroshi Saito Subject: Re: [PATCHES] display and expression of the home directory in Win32 Am Freitag, 6. Januar 2006 04:03 schrieb Hiroshi Saito: [pg_config] HOMEDIR = C:/Documents and Settings/saito/Application Data/postgresql Where does this come from and what would it be good for? It's not always obvious on Windows where this directory is because apps generally write their files there themselves, thus the user doesn't necessarily even know it exists. Pgpass.conf (which we store there) is a bit of odd case because it may be used by various front-end applications or drivers and may need manual editting. That's not to say I necessarily think it should be added to pg_config - I could be persuaded either way. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruno Wolff III wrote: On Thu, Jan 05, 2006 at 11:44:24 -0800, Josh Berkus josh@agliodbs.com wrote: Bruce, Tom, The permissions for a sequence aren't the same as they are for a table. We've sort of ignored the point to date, but if we're going to add special syntax for granting on a sequence, I don't think we should continue to ignore it. Uh, how are they different? You mean just UPDATE and none of the others do anything? Yes, it would be nice to have real permissions for sequences, specifically USE (which allows nextval() and currval()) and UPDATE (which would allow setval() ). However, I don't know that the added functionality would justify breaking backwards-compatibility. It might be nice to split nextval and currval access as well. nextval access corresponds to INSERT and currval access to SELECT. Uh, that is already in the code. nextval()/setval() is UPDATE, and currval() is SELECT. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [PATCHES] display and expression of the home directory in Win32
I receive an inquiry from a user frequently. Where is it about a home directory in a place? Then, In offer of the initial state of Windows, the place is hidden and is not visible to a user. I considered what can be offered this way and that. Then, using pg_config thinks that it is good. This patch show a user a clear place. Please take this into consideration. F:\postgresql-8.1.2stable\src\bin\pg_config\Releasepg_config.exe BINDIR = F:/postgresql-8.1.2stable/src/bin/pg_config/Release DOCDIR = INCLUDEDIR = PKGINCLUDEDIR = INCLUDEDIR-SERVER = LIBDIR = PKGLIBDIR = LOCALEDIR = MANDIR = SHAREDIR = SYSCONFDIR = PGXS = /pgxs/src/makefiles/pgxs.mk HOMEDIR = C:/Documents and Settings/saito/Application Data/postgresql VERSION = PostgreSQL 8.1.1 HOMEDIR is a very bad name for this variable, since it's *not* the home directory. It could easily be confused. If we put it in, I definitly think it should be called something else.. (USERCONFDIR based on that we have SYSCONFDIR, perhaps?). Also, there is no reason not to include it on Unix as well - it would actrually be much better to do that, in case someone wants to write a script that relies on it. I'm also not sure this really buys us buch. There is already an environment variable that points to this (unless you've applied some very unusual modifications to your windows installation, in which case you probably know where to be looking anyway), and that is: %APPDATA%\postgresql dir C:/Documents and Settings/saito/Application Data/postgresql This would then be: dir %APPDATA%\postgresql which is actually much easier to use than getting it from pg_config, since you can use the environment variable in any command you want. //Magnus ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Bruno Wolff III wrote: It might be nice to split nextval and currval access as well. nextval access corresponds to INSERT and currval access to SELECT. Uh, that is already in the code. nextval()/setval() is UPDATE, and currval() is SELECT. This seems weird. Shouldn't nextval/currval go together and setval separately? Considering there's no currval() without nextval(), what point is disallowing currval() when user is able to call nextval()? I rather want to allow nextval/currval and disable setval as it allows regular user to DoS the database. -- marko [removing Tom from CC as he bounces gmail] ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Tom Lane wrote:
Josh Berkus josh@agliodbs.com writes:
Uh, how are they different? You mean just UPDATE and none of the
others do anything?
Yes, it would be nice to have real permissions for sequences, specifically
USE (which allows nextval() and currval()) and UPDATE (which would allow
setval() ). However, I don't know that the added functionality would
justify breaking backwards-compatibility.
We could maintain backwards compatibility by continuing to accept the
old equivalences when you say GRANT ON TABLE. But when you say GRANT ON
SEQUENCE, I think it should use sequence-specific privilege keywords,
and not allow the privileges that don't mean anything for sequences,
like DELETE.
OK.
I'm not sure offhand what keywords we'd want to use, but now is the time
to look at it, *before* it becomes set in stone that GRANT ON SEQUENCE
is just another spelling of GRANT ON TABLE.
Sequences do not support INSERT, UPDATE, or DELETE, but we overload
UPDATE to control nextval()/setval(), so I just allowed SELECT and
UPDATE. I am not sure it makes any sense to allow rules, references,
and triggers on sequences. However, using ALL or TABLE keywords you can
define those permissions to a sequence.
(The subtext of this is that I don't have a lot of use for allowing
variant syntaxes that don't actually do anything different ...)
FYI, SQL03 defines GRANT SEQUENCE.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/grant.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.50
diff -c -c -r1.50 grant.sgml
*** doc/src/sgml/ref/grant.sgml 20 Oct 2005 19:18:01 - 1.50
--- doc/src/sgml/ref/grant.sgml 6 Jan 2006 15:23:16 -
***
*** 25,30
--- 25,35
ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
+ GRANT { { SELECT | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] }
+ ON SEQUENCE replaceable class=PARAMETERtablename/replaceable [, ...]
+ TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
+
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE replaceabledbname/replaceable [, ...]
TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
***
*** 511,517
para
The literalRULE/literal privilege, and privileges on
! databases, tablespaces, schemas, languages, and sequences are
productnamePostgreSQL/productname extensions.
/para
/refsect1
--- 516,522
para
The literalRULE/literal privilege, and privileges on
! databases, tablespaces, schemas, and languages are
productnamePostgreSQL/productname extensions.
/para
/refsect1
Index: doc/src/sgml/ref/revoke.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v
retrieving revision 1.35
diff -c -c -r1.35 revoke.sgml
*** doc/src/sgml/ref/revoke.sgml20 Oct 2005 19:18:01 - 1.35
--- doc/src/sgml/ref/revoke.sgml6 Jan 2006 15:23:16 -
***
*** 28,33
--- 28,40
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] }
+ ON SEQUENCE replaceable class=PARAMETERtablename/replaceable [, ...]
+ FROM { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
+
+ REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE replaceabledbname/replaceable [, ...]
FROM { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...]
Index: src/backend/catalog/aclchk.c
===
RCS file: /cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.123
diff -c -c -r1.123 aclchk.c
*** src/backend/catalog/aclchk.c1 Dec 2005 02:03:00 - 1.123
--- src/backend/catalog/aclchk.c6 Jan 2006 15:23:17 -
***
*** 283,288
--- 283,289
switch (stmt-objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_SEQUENCE:
all_privileges =
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruce Momjian pgman@candle.pha.pa.us writes: FYI, SQL03 defines GRANT SEQUENCE. Oh. Well, then that gives us precedent to go by. What do they specify as the privileges for sequences? regards, tom lane ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Marko Kreen wrote: On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Bruno Wolff III wrote: It might be nice to split nextval and currval access as well. nextval access corresponds to INSERT and currval access to SELECT. Uh, that is already in the code. nextval()/setval() is UPDATE, and currval() is SELECT. This seems weird. Shouldn't nextval/currval go together and setval separately? Uh, logically, yes, but practially currval just reads/SELECTs, while nextval modifies/UPDATEs. Considering there's no currval() without nextval(), what point is disallowing currval() when user is able to call nextval()? Not sure. I think SET SESSION AUTHORIZATION would make it possible. I rather want to allow nextval/currval and disable setval as it allows regular user to DoS the database. Oh, interesting. We could easily have INSERT control that if we wanted, but I think you have to make a clear use case to override the risk of breaking applications. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: FYI, SQL03 defines GRANT SEQUENCE. Oh. Well, then that gives us precedent to go by. What do they specify as the privileges for sequences? They don't seem to specify which actions go with which objects in the GRANT statement, nor do they specify what permissions should control the nextval-style statements. Seems like something they should have specified. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Marko Kreen wrote: On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Bruno Wolff III wrote: It might be nice to split nextval and currval access as well. nextval access corresponds to INSERT and currval access to SELECT. Uh, that is already in the code. nextval()/setval() is UPDATE, and currval() is SELECT. This seems weird. Shouldn't nextval/currval go together and setval separately? Uh, logically, yes, but practially currval just reads/SELECTs, while nextval modifies/UPDATEs. Yeah, thats the mechanics behind it, but the currval() only works if the user was already able to call nextval(), so I see no point in separating them. In other words: there is nothing to do with only access to currval(), and with access to nextval() but not to currval() user loses only in convinience. Considering there's no currval() without nextval(), what point is disallowing currval() when user is able to call nextval()? Not sure. I think SET SESSION AUTHORIZATION would make it possible. /me confused, looks at docs... Huh? I really hope you are mistaken. This would mean the sequence state for currval() is kept per-user not per-backend. This would make impossible to make several connections as same user. Is Postgres really that broken? I rather want to allow nextval/currval and disable setval as it allows regular user to DoS the database. Oh, interesting. We could easily have INSERT control that if we wanted, but I think you have to make a clear use case to override the risk of breaking applications. I'd turn it around: is there any use-case for setval() for regular user? IMHO it's a admin-level operation, dangerous, and not needed for regular work. -- marko ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Marko Kreen [EMAIL PROTECTED] writes: On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Uh, logically, yes, but practially currval just reads/SELECTs, while nextval modifies/UPDATEs. Yeah, thats the mechanics behind it, but the currval() only works if the user was already able to call nextval(), so I see no point in separating them. You are completely wrong on this, because not all the code in a session necessarily executes at the same privilege level. For instance, the nextval() might be executed inside a SECURITY DEFINER function. It might be reasonable to give code outside that function the right to see what had been assigned (by executing currval()) without also saying that it could do further nextvals(). I do agree that it would be a good idea to support a privilege distinction between nextval() and setval(). Oh, interesting. We could easily have INSERT control that if we wanted, but I think you have to make a clear use case to override the risk of breaking applications. There is no backwards-compatibility risk, because we'd still have the old GRANT ON TABLE syntax grant both underlying rights. You'd have to use the new syntax to get to a state where you had nextval but not setval privilege or vice versa. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On Fri, Jan 06, 2006 at 19:11:27 +0200, Marko Kreen [EMAIL PROTECTED] wrote: On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Considering there's no currval() without nextval(), what point is disallowing currval() when user is able to call nextval()? I rather want to allow nextval/currval and disable setval as it allows regular user to DoS the database. What I was thinking with this, is that you might allow someone the ability to insert records into a table which would make use of nextval, but not allow them to run nextval directly. But after inserting a record allow them to use currval to see what value was assigned. People could still mess with things by doing INSERTs and aborting the transaction, so this may not be the best example for why you would want this. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] display and expression of the home directory in Win32
From: Magnus Hagander HOMEDIR = C:/Documents and Settings/saito/Application Data/postgresql VERSION = PostgreSQL 8.1.1 HOMEDIR is a very bad name for this variable, since it's *not* the home directory. It could easily be confused. If we put it in, I definitly think it should be called something else.. (USERCONFDIR based on that we Um, About a name, I think that it is right. have SYSCONFDIR, perhaps?). Also, there is no reason not to include it on Unix as well - it would actrually be much better to do that, in case someone wants to write a script that relies on it. It will be good to be clearly shown in all environments. dir C:/Documents and Settings/saito/Application Data/postgresql This would then be: dir %APPDATA%\postgresql which is actually much easier to use than getting it from pg_config, since you can use the environment variable in any command you want. Probably, there is a user who notices after the directory is shown. Regards, Hiroshi Saito ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruno Wolff III wrote: On Fri, Jan 06, 2006 at 19:11:27 +0200, Marko Kreen [EMAIL PROTECTED] wrote: On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Considering there's no currval() without nextval(), what point is disallowing currval() when user is able to call nextval()? I rather want to allow nextval/currval and disable setval as it allows regular user to DoS the database. What I was thinking with this, is that you might allow someone the ability to insert records into a table which would make use of nextval, but not allow them to run nextval directly. But after inserting a record allow them to use currval to see what value was assigned. People could still mess with things by doing INSERTs and aborting the transaction, so this may not be the best example for why you would want this. That seems too confusing to support based on usefulness of the new capability. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] To build client can on Windows using Microsoft Visual C++
Dear Bruce san. I think that a certain user has a meaning as for this client construction. There is a user who demands to build only by Microsoft thoroughly. However, it may be ignored if support load is disliked. What is necessary will be just to remain in a mail archive, even if this is not applied. Thanks.! Regards, Hiroshi Saito --- doc/src/sgml/install-win32.sgml.origFri Jan 6 14:09:29 2006 +++ doc/src/sgml/install-win32.sgml Fri Jan 6 15:06:49 2006 @@ -83,13 +83,27 @@ /varlistentry varlistentry +termfilenamebin\pg_config\Release\pg_config.exe/filename/term termfilenamebin\psql\Release\psql.exe/filename/term +termfilenamebin\pg_dump\Release\pg_dump.exe/filename/term +termfilenamebin\pg_dump\Release\pg_dumpall.exe/filename/term +termfilenamebin\pg_dump\Release\pg_restore.exe/filename/term +termfilenamebin\scripts\Release\clusterdb.exe/filename/term +termfilenamebin\scripts\Release\createdb.exe/filename/term +termfilenamebin\scripts\Release\createuser.exe/filename/term +termfilenamebin\scripts\Release\createlang.exe/filename/term +termfilenamebin\scripts\Release\dropdb.exe/filename/term +termfilenamebin\scripts\Release\dropuser.exe/filename/term +termfilenamebin\scripts\Release\droplang.exe/filename/term +termfilenamebin\scripts\Release\vacuumdb.exe/filename/term +termfilenamebin\scripts\Release\reindexdb.exe/filename/term listitem para - The productnamePostgreSQL/productname interactive terminal + The productnamePostgreSQL/productname client applications and utilities. /para /listitem /varlistentry + /variablelist /para --- src/bin/pg_dump/pg_backup_archiver.c.orig Thu Jan 5 15:09:55 2006 +++ src/bin/pg_dump/pg_backup_archiver.cThu Jan 5 15:10:50 2006 @@ -27,7 +27,10 @@ #include dumputils.h #include ctype.h + +#ifndef WIN32_CLIENT_ONLY #include unistd.h +#endif #ifdef WIN32 #include io.h --- src/bin/pg_dump/pg_backup_db.c.orig Thu Jan 5 15:09:55 2006 +++ src/bin/pg_dump/pg_backup_db.c Thu Jan 5 15:11:01 2006 @@ -15,7 +15,10 @@ #include pg_backup_db.h #include dumputils.h +#ifndef WIN32_CLIENT_ONLY #include unistd.h +#endif + #include ctype.h #ifdef HAVE_TERMIOS_H --- src/bin/pg_dump/pg_backup_null.c.orig Thu Jan 5 15:09:55 2006 +++ src/bin/pg_dump/pg_backup_null.cThu Jan 5 15:11:13 2006 @@ -25,7 +25,9 @@ #include pg_backup.h #include pg_backup_archiver.h +#ifndef WIN32_CLIENT_ONLY #include unistd.h /* for dup */ +#endif #include libpq/libpq-fs.h --- src/bin/pg_dump/pg_backup_tar.c.origThu Jan 5 15:09:55 2006 +++ src/bin/pg_dump/pg_backup_tar.c Thu Jan 5 15:11:25 2006 @@ -27,7 +27,10 @@ #include ctype.h #include limits.h + +#ifndef WIN32_CLIENT_ONLY #include unistd.h +#endif static void _ArchiveEntry(ArchiveHandle *AH, TocEntry *te); static void _StartData(ArchiveHandle *AH, TocEntry *te); --- src/bin/pg_dump/pg_dump.c.orig Thu Jan 5 15:09:55 2006 +++ src/bin/pg_dump/pg_dump.c Thu Jan 5 15:11:36 2006 @@ -24,7 +24,10 @@ */ #include postgres.h +#ifndef WIN32_CLIENT_ONLY #include unistd.h +#endif + #include ctype.h #ifdef ENABLE_NLS #include locale.h --- src/bin/pg_dump/pg_dumpall.c.orig Thu Jan 5 15:09:55 2006 +++ src/bin/pg_dump/pg_dumpall.cThu Jan 5 15:11:54 2006 @@ -14,7 +14,11 @@ #include postgres_fe.h #include time.h + +#ifndef WIN32_CLIENT_ONLY #include unistd.h +#endif + #ifdef ENABLE_NLS #include locale.h #endif --- src/bin/pg_dump/pg_restore.c.orig Thu Jan 5 15:09:55 2006 +++ src/bin/pg_dump/pg_restore.cThu Jan 5 15:12:06 2006 @@ -53,7 +53,9 @@ #include termios.h #endif +#ifndef WIN32_CLIENT_ONLY #include unistd.h +#endif #include getopt_long.h --- src/bin/pg_dump/win32.mak.orig Thu Jan 5 15:20:24 2006 +++ src/bin/pg_dump/win32.mak Fri Jan 6 10:49:49 2006 @@ -0,0 +1,186 @@ +# Makefile for Microsoft Visual C++ 5.0 (or compat) + +!IF $(OS) == Windows_NT +NULL= +!ELSE +NULL=nul +!ENDIF + +CPP=cl.exe +PERL=perl.exe +FLEX=flex.exe +YACC=bison.exe +MV=move + +!IFDEF DEBUG +OPT=/Od /Zi /MDd +LOPT=/DEBUG +DEBUGDEF=/D _DEBUG +OUTDIR=.\Debug +INTDIR=.\Debug +!ELSE +OPT=/O2 /MD +LOPT= +DEBUGDEF=/D NDEBUG +OUTDIR=.\Release +INTDIR=.\Release +!ENDIF + +REFDOCDIR= ../../../doc/src/sgml/ref + +CPP_PROJ=/nologo $(OPT) /W3 /GX /D WIN32 $(DEBUGDEF) /D _CONSOLE /D\ + _MBCS /Fp$(INTDIR)\pg_dump.pch /YX /Fo$(INTDIR)\\ /Fd$(INTDIR)\\ /FD /c \ + /I ..\..\include /I ..\..\interfaces\libpq /I ..\..\include\port\win32 /I ..\..\backend \ + /D HAVE_STRDUP /D FRONTEND + +CPP_OBJS=$(INTDIR)/ +CPP_SBRS=. + +ALL : ..\..\backend\parser\parse.h ..\..\port\pg_config_paths.h \ + $(OUTDIR)\pg_dump.exe $(OUTDIR)\pg_dumpall.exe $(OUTDIR)\pg_restore.exe + +CLEAN : + [EMAIL PROTECTED] $(INTDIR)\pg_backup_archiver.obj + [EMAIL PROTECTED] $(INTDIR)\pg_backup_db.obj + [EMAIL PROTECTED] $(INTDIR)\pg_backup_custom.obj + [EMAIL PROTECTED] $(INTDIR)\pg_backup_files.obj
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On 1/6/06, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Uh, logically, yes, but practially currval just reads/SELECTs, while nextval modifies/UPDATEs. Yeah, thats the mechanics behind it, but the currval() only works if the user was already able to call nextval(), so I see no point in separating them. You are completely wrong on this, because not all the code in a session necessarily executes at the same privilege level. For instance, the nextval() might be executed inside a SECURITY DEFINER function. It might be reasonable to give code outside that function the right to see what had been assigned (by executing currval()) without also saying that it could do further nextvals(). Ah, I did not think of this. Indeed, it's useful to keep them separate. I just wanted to point out that I see much more use to keep setval() separate from nextval/currval. (that is - always) I do agree that it would be a good idea to support a privilege distinction between nextval() and setval(). I tried to imagine a usage scenario for setval() but only single-user bulk data load comes in mind. Is there any actual scenario where it could be useful in multi-user setting? Oh, interesting. We could easily have INSERT control that if we wanted, but I think you have to make a clear use case to override the risk of breaking applications. There is no backwards-compatibility risk, because we'd still have the old GRANT ON TABLE syntax grant both underlying rights. You'd have to use the new syntax to get to a state where you had nextval but not setval privilege or vice versa. Good idea. -- marko ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On 1/6/06, Bruno Wolff III [EMAIL PROTECTED] wrote: On Fri, Jan 06, 2006 at 19:11:27 +0200, Marko Kreen [EMAIL PROTECTED] wrote: On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Considering there's no currval() without nextval(), what point is disallowing currval() when user is able to call nextval()? I rather want to allow nextval/currval and disable setval as it allows regular user to DoS the database. What I was thinking with this, is that you might allow someone the ability to insert records into a table which would make use of nextval, but not allow them to run nextval directly. But after inserting a record allow them to use currval to see what value was assigned. People could still mess with things by doing INSERTs and aborting the transaction, so this may not be the best example for why you would want this. This is similar to Tom's scenario. I'm not against keeping them separate. But my question is rather - is there any scenario where setval() should go with nextval()? It seems that their pairing is an accident and should be fixed. -- marko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] psql tab completion enhancements
Hi,
psql's tab completion has the following problem:
If we have the following syntax for example:
SET SESSION AUTHORIZATION user;
SET SESSION AUTHORIZATION DEFAULT;
After SET SESSION AUTHORIZATION, the tab completion can offer a list of
roles or the string constant DEFAULT. However it can't offer both because
it can't get a list of roles and add a string constant to this list.
The appended patch adds the functionality of lists that can be extended with
constants.
Then you get:
template1=# SET session AUTHORIZATION tab
DEFAULT fred joe john
I did proof-of-concept examples to add a constant to a
- list from a query
- list from a schema query
- list of table attributes
Joachim
diff -cr cvs/pgsql/src/bin/psql/tab-complete.c
cvs.build/pgsql/src/bin/psql/tab-complete.c
*** cvs/pgsql/src/bin/psql/tab-complete.c 2005-12-23 16:58:19.0
+0100
--- cvs.build/pgsql/src/bin/psql/tab-complete.c 2006-01-06 19:38:26.0
+0100
***
*** 137,142
--- 137,143
3) The items from a null-pointer-terminated list.
4) A string constant
5) The list of attributes to the given table.
+6) A list that can contain several of the above (malloc'ed list).
*/
#define COMPLETE_WITH_QUERY(query) \
do { completion_charp = query; matches = completion_matches(text,
complete_from_query); } while(0)
***
*** 150,155
--- 151,171
do {completion_charp = Query_for_list_of_attributes; completion_info_charp =
table; matches = completion_matches(text, complete_from_query); } while(0)
/*
+ * Keep the malloced keyword in all the names such that we remember that
+ * memory got allocated here. COMPLETE_WITH_MALLOCED_LIST frees this memory.
+ */
+ #define COMPLETE_WITH_MALLOCED_LIST(list) \
+ do { COMPLETE_WITH_LIST((const char**) list); free(list); list =
(char**) 0; } while(0)
+ #define MALLOCED_LIST_ADD_ITEM(list, item) \
+ ((list) = list_add_item((list), (item)))
+ #define GET_MALLOCED_LIST_WITH_ATTR(table) \
+ (get_query_list(text, Query_for_list_of_attributes, (table)))
+ #define GET_MALLOCED_LIST_WITH_QUERY(query) \
+ (get_query_list(text, (query), NULL))
+ #define GET_MALLOCED_LIST_WITH_SCHEMA_QUERY(query,addon) \
+ (get_schema_query_list(text, (query), addon))
+
+ /*
* Assembly instructions for schema queries
*/
***
*** 463,468
--- 479,494
/* Forward declaration of functions */
+ static char **get_empty_list();
+ static char **_get_query_list(int is_schema_query,
+ const char *text,
const char *query,
+ const char
*completion_info);
+ static char **get_query_list(const char *text, const char *query,
+const char
*completion_info);
+ static char **get_schema_query_list(const char *text, const SchemaQuery*
squery,
+ const
char *completion_info);
+ static char **list_add_item(char **list, char *item);
+
static char **psql_completion(char *text, int start, int end);
static char *create_command_generator(const char *text, int state);
static char *complete_from_query(const char *text, int state);
***
*** 754,760
else if (pg_strcasecmp(prev3_wd, TABLE) == 0
(pg_strcasecmp(prev_wd, ALTER) == 0 ||
pg_strcasecmp(prev_wd, RENAME) == 0))
! COMPLETE_WITH_ATTR(prev2_wd);
/* ALTER TABLE xxx RENAME yyy */
else if (pg_strcasecmp(prev4_wd, TABLE) == 0
--- 780,796
else if (pg_strcasecmp(prev3_wd, TABLE) == 0
(pg_strcasecmp(prev_wd, ALTER) == 0 ||
pg_strcasecmp(prev_wd, RENAME) == 0))
! {
! char** list = GET_MALLOCED_LIST_WITH_ATTR(prev2_wd);
! MALLOCED_LIST_ADD_ITEM(list, COLUMN);
! COMPLETE_WITH_MALLOCED_LIST(list);
! }
! /* If we have TABLE sth ALTER COLUMN|RENAME COLUMN, provide list of
columns */
! else if (pg_strcasecmp(prev4_wd, TABLE) == 0
!pg_strcasecmp(prev_wd, COLUMN) == 0
!(pg_strcasecmp(prev2_wd, ALTER) == 0 ||
! pg_strcasecmp(prev2_wd, RENAME) == 0))
! COMPLETE_WITH_ATTR(prev3_wd);
/* ALTER TABLE xxx RENAME yyy */
else if (pg_strcasecmp(prev4_wd, TABLE) == 0
***
*** 762,767
--- 798,810
pg_strcasecmp(prev_wd, TO) != 0)
COMPLETE_WITH_CONST(TO);
+ /* ALTER TABLE xxx RENAME COLUMN yyy */
+ else if (pg_strcasecmp(prev5_wd, TABLE) == 0
+pg_strcasecmp(prev3_wd, RENAME) == 0
+pg_strcasecmp(prev2_wd, COLUMN) == 0
+pg_strcasecmp(prev_wd,
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Marko Kreen [EMAIL PROTECTED] writes: But my question is rather - is there any scenario where setval() should go with nextval()? It seems that their pairing is an accident and should be fixed. I think the original argument for the current design was that with enough nextval's you can duplicate the effect of a setval. This is only strictly true if the sequence is CYCLE mode, and even then it'd take a whole lot of patience to wrap an int8 sequence around ... but the distinction between them is not so large as you make it out to be. In any case I think we are wasting our time discussing it, and instead should be looking through the SQL2003 spec to see what it requires. Bruce couldn't find anything in it about this but I can't believe the info isn't there somewhere. regards, tom lane ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruce Momjian wrote:
I'm not sure offhand what keywords we'd want to use, but now is the time
to look at it, *before* it becomes set in stone that GRANT ON SEQUENCE
is just another spelling of GRANT ON TABLE.
Sequences do not support INSERT, UPDATE, or DELETE, but we overload
UPDATE to control nextval()/setval(), so I just allowed SELECT and
UPDATE. I am not sure it makes any sense to allow rules, references,
and triggers on sequences. However, using ALL or TABLE keywords you can
define those permissions to a sequence.
Here is an updated patch. The standard doesn't have GRANT VIEW so I
didn't implement that.
One tricky issue I realized is that we should dump out GRANT SEQUENCE,
if possible. I have added code to check in pg_dump and use GRANT
SEQUENCE if only SELECT, UPDATE, or ALL are used.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/grant.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.50
diff -c -c -r1.50 grant.sgml
*** doc/src/sgml/ref/grant.sgml 20 Oct 2005 19:18:01 - 1.50
--- doc/src/sgml/ref/grant.sgml 6 Jan 2006 20:33:45 -
***
*** 25,30
--- 25,35
ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
+ GRANT { { SELECT | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] }
+ ON SEQUENCE replaceable class=PARAMETERtablename/replaceable [, ...]
+ TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
+
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE replaceabledbname/replaceable [, ...]
TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
***
*** 511,517
para
The literalRULE/literal privilege, and privileges on
! databases, tablespaces, schemas, languages, and sequences are
productnamePostgreSQL/productname extensions.
/para
/refsect1
--- 516,522
para
The literalRULE/literal privilege, and privileges on
! databases, tablespaces, schemas, and languages are
productnamePostgreSQL/productname extensions.
/para
/refsect1
Index: doc/src/sgml/ref/revoke.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v
retrieving revision 1.35
diff -c -c -r1.35 revoke.sgml
*** doc/src/sgml/ref/revoke.sgml20 Oct 2005 19:18:01 - 1.35
--- doc/src/sgml/ref/revoke.sgml6 Jan 2006 20:33:46 -
***
*** 28,33
--- 28,40
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] }
+ ON SEQUENCE replaceable class=PARAMETERtablename/replaceable [, ...]
+ FROM { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
+
+ REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE replaceabledbname/replaceable [, ...]
FROM { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...]
Index: src/backend/catalog/aclchk.c
===
RCS file: /cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.123
diff -c -c -r1.123 aclchk.c
*** src/backend/catalog/aclchk.c1 Dec 2005 02:03:00 - 1.123
--- src/backend/catalog/aclchk.c6 Jan 2006 20:33:46 -
***
*** 283,288
--- 283,289
switch (stmt-objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_SEQUENCE:
all_privileges = ACL_ALL_RIGHTS_RELATION;
errormsg = _(invalid privilege type %s for table);
break;
***
*** 356,361
--- 357,363
switch (istmt-objtype)
{
case ACL_OBJECT_RELATION:
+ case ACL_OBJECT_SEQUENCE:
ExecGrant_Relation(istmt);
break;
case ACL_OBJECT_DATABASE:
***
*** 395,400
--- 397,403
switch (objtype)
{
case ACL_OBJECT_RELATION:
+ case
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Tom Lane wrote: Marko Kreen [EMAIL PROTECTED] writes: But my question is rather - is there any scenario where setval() should go with nextval()? It seems that their pairing is an accident and should be fixed. I think the original argument for the current design was that with enough nextval's you can duplicate the effect of a setval. This is only strictly true if the sequence is CYCLE mode, and even then it'd take a whole lot of patience to wrap an int8 sequence around ... but the distinction between them is not so large as you make it out to be. In any case I think we are wasting our time discussing it, and instead should be looking through the SQL2003 spec to see what it requires. Bruce couldn't find anything in it about this but I can't believe the info isn't there somewhere. What I did was to read through the GRANT and SEQUENCE sections, then I dumped it to text and did a grep for 'grant' or perm* appearing on the same line as sequence, and came up with nothing. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On 1/6/06, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: But my question is rather - is there any scenario where setval() should go with nextval()? It seems that their pairing is an accident and should be fixed. I think the original argument for the current design was that with enough nextval's you can duplicate the effect of a setval. This is only strictly true if the sequence is CYCLE mode, and even then it'd take a whole lot of patience to wrap an int8 sequence around ... but the distinction between them is not so large as you make it out to be. With bigserial this is more like CPU DoS, while other users can work normally. In any case I think we are wasting our time discussing it, and instead should be looking through the SQL2003 spec to see what it requires. Bruce couldn't find anything in it about this but I can't believe the info isn't there somewhere. Google tells that Oracle has ALTER and SELECT; DB2 has ALTER and USAGE. I found SQL2003 pdf's too ... from my reading it has only USAGE. 5WD-02-Foundation-2003-09.pdf: page 724 - General Rules - #2 page 740 - Syntax rules - #3 Everything combined: SELECT: currval UPDATE: nextval USAGE: currval, nextval ALTER: setval Confusing? -- marko ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Marko Kreen wrote: In any case I think we are wasting our time discussing it, and instead should be looking through the SQL2003 spec to see what it requires. Bruce couldn't find anything in it about this but I can't believe the info isn't there somewhere. Google tells that Oracle has ALTER and SELECT; DB2 has ALTER and USAGE. I found SQL2003 pdf's too ... from my reading it has only USAGE. 5WD-02-Foundation-2003-09.pdf: page 724 - General Rules - #2 page 740 - Syntax rules - #3 I admit I am terrible at understanding the standard, but I can't find anything relevant on the page numbers you mentioned. Are those the document pages or the page numbers displayed by the PDF viewer? What is the section heading? I am using the same filename you have. Everything combined: SELECT: currval UPDATE: nextval USAGE: currval, nextval ALTER: setval Confusing? I see USAGE in the standard, but not ALTER. We don't support USAGE so I am guessing our SELECT/UPDATE behavior is OK. Does this mean we should only allow owners to do setval(), rather than binding it to INSERT? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Jaime Casanova wrote: On 1/6/06, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: But my question is rather - is there any scenario where setval() should go with nextval()? It seems that their pairing is an accident and should be fixed. I think the original argument for the current design was that with enough nextval's you can duplicate the effect of a setval. This is only strictly true if the sequence is CYCLE mode, and even then it'd take a whole lot of patience to wrap an int8 sequence around ... but the distinction between them is not so large as you make it out to be. In any case I think we are wasting our time discussing it, and instead should be looking through the SQL2003 spec to see what it requires. 5WD-02-Foundation-2003-09.pdf look at: 4.34.2 Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 and 12.3 privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739 this is taken from the 12.3 3) If object name specifies a domain name, collation name, character set name, transliteration name, schema-resolved user-defined type name, or sequence generator name, then privileges may specify USAGE. Otherwise, USAGE shall not be specified. Yes, I saw that, but how does that hook into nextval/setval/currval()? I think I see it in 6.13: a) If next value expression is contained in a schema definition, then the applicable privileges for the authorization identifier that owns the containing schema shall include USAGE privilege on the sequence generator identified by sequence generator name. Is that it? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Marko Kreen wrote: I found SQL2003 pdf's too ... from my reading it has only USAGE. 5WD-02-Foundation-2003-09.pdf: page 724 - General Rules - #2 page 740 - Syntax rules - #3 I admit I am terrible at understanding the standard, but I can't find anything relevant on the page numbers you mentioned. Are those the document pages or the page numbers displayed by the PDF viewer? What is the section heading? I am using the same filename you have. Those are print page numbers. (In case you have dead-tree variant :) And I got them here: http://www.wiscorp.com/SQLStandards.html Uh, and they are bit wrong. Ok here are they fully: 11.62 sequence generator definition General rules (page 727 printed/751 real) point #2 12.3 privileges Syntax rules (page 740 printed/764 real) point #3 Everything combined: SELECT: currval UPDATE: nextval USAGE: currval, nextval ALTER: setval Confusing? I see USAGE in the standard, but not ALTER. We don't support USAGE so I am guessing our SELECT/UPDATE behavior is OK. No, we still want to separate setval from nextval. Does this mean we should only allow owners to do setval(), rather than binding it to INSERT? My first reaction is that it should be grantable, although I can't find any reasons for it, except backwards compatibility. How about this: SELECT: currval INSERT: nextval USAGE: currval, nextval UPDATE: setval -- marko ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Jaime Casanova wrote: On 1/6/06, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: But my question is rather - is there any scenario where setval() should go with nextval()? It seems that their pairing is an accident and should be fixed. I think the original argument for the current design was that with enough nextval's you can duplicate the effect of a setval. This is only strictly true if the sequence is CYCLE mode, and even then it'd take a whole lot of patience to wrap an int8 sequence around ... but the distinction between them is not so large as you make it out to be. In any case I think we are wasting our time discussing it, and instead should be looking through the SQL2003 spec to see what it requires. 5WD-02-Foundation-2003-09.pdf look at: 4.34.2 Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 and 12.3 privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739 this is taken from the 12.3 3) If object name specifies a domain name, collation name, character set name, transliteration name, schema-resolved user-defined type name, or sequence generator name, then privileges may specify USAGE. Otherwise, USAGE shall not be specified. Yes, I saw that, but how does that hook into nextval/setval/currval()? I think I see it in 6.13: a) If next value expression is contained in a schema definition, then the applicable privileges for the authorization identifier that owns the containing schema shall include USAGE privilege on the sequence generator identified by sequence generator name. Is that it? USAGE is the only privilege that the standard consider applicable for the owner? it covers all operations in sequences... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Marko Kreen wrote: On 1/6/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Marko Kreen wrote: I found SQL2003 pdf's too ... from my reading it has only USAGE. 5WD-02-Foundation-2003-09.pdf: page 724 - General Rules - #2 page 740 - Syntax rules - #3 I admit I am terrible at understanding the standard, but I can't find anything relevant on the page numbers you mentioned. Are those the document pages or the page numbers displayed by the PDF viewer? What is the section heading? I am using the same filename you have. Those are print page numbers. (In case you have dead-tree variant :) And I got them here: http://www.wiscorp.com/SQLStandards.html Uh, and they are bit wrong. Ok here are they fully: 11.62 sequence generator definition General rules (page 727 printed/751 real) point #2 12.3 privileges Syntax rules (page 740 printed/764 real) point #3 OK, I see it now, and in an earlier email I quoted the part where I think USAGE links in to nextval(). I was looking for something obvious. :-) Everything combined: SELECT: currval UPDATE: nextval USAGE: currval, nextval ALTER: setval Confusing? I see USAGE in the standard, but not ALTER. We don't support USAGE so I am guessing our SELECT/UPDATE behavior is OK. No, we still want to separate setval from nextval. My point was that currval - SELECT and nextval - UPDATE was correct. I see now that I am wrong and that the standard wants USAGE. That combined was every db's behavior combined, right? I got confused. Does this mean we should only allow owners to do setval(), rather than binding it to INSERT? My first reaction is that it should be grantable, although I can't find any reasons for it, except backwards compatibility. How about this: SELECT: currval INSERT: nextval USAGE: currval, nextval UPDATE: setval I think nextval() is naturally UPDATE. I am thinking setval would be INSERT, and with setval() being used less, it would perhaps be a better choice for a change anyway. However, in doing the pg_dump part of the patch, I perhaps see a problem. If someone does: GRANT UPDATE ON seq1 TO PUBLIC; do we give them nextval() and setval() permissions? If they do: GRANT UPDATE ON SEQUENCE seq1 TO PUBLIC; they only set nextval()? That seems quite confusing. Can we change UPDATE for both GRANT syntaxes, and somehow have people fix them up after they load in 8.2? How many non-owners do setval()? FYI, we could support USAGE just on sequences, and have it map to UPDATE, but pg_dump it out as USAGE. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruce Momjian pgman@candle.pha.pa.us writes: FYI, we could support USAGE just on sequences, and have it map to UPDATE, but pg_dump it out as USAGE. It seems the spec doesn't cover setval() and currval(), which is not too surprising given those aren't standard. Here is a proposal: SELECT priv - allows currval() and SELECT * FROM seq USAGE priv - allows nextval() (required by SQL2003) UPDATE priv - allows setval() and nextval() I was originally thinking of a separate privilege bit for setval(), but that's sort of silly, as you can get (approximately) the effect of nextval() via setval(). Not much point in prohibiting nextval() to someone who can do setval(). This is 100% upward compatible with our current definition, and it meets both the SQL spec and Marko's desire to have a way of granting only nextval() privilege. BTW, what about lastval()? I'm not sure we can usefully associate any privilege check with that, since it's not clear which sequence it applies to. Does it make sense to remember what sequence the value came from and privilege-check against that, or is that just too weird? regards, tom lane ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Josh Berkus josh@agliodbs.com writes: BTW, what about lastval()? Overal, it's hard to get too concerned about this, since a user can't really get anything out of lastval() if he doesn't have permissions on the sequence he's trying to query, in order to run currval. Well, no, consider my example to Marko: there could be a SECURITY DEFINER function that has the privilege to run nextval(). After that, if lastval() isn't privilege-checked then code that doesn't have any privilege at all on the sequence could get at the value. However, looking at the source code I see that lastval() does in fact insist on SELECT rights on the sequence the value is coming from. So I guess we can just leave that as-is. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
On 1/7/06, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian pgman@candle.pha.pa.us writes: FYI, we could support USAGE just on sequences, and have it map to UPDATE, but pg_dump it out as USAGE. It seems the spec doesn't cover setval() and currval(), which is not too surprising given those aren't standard. Here is a proposal: SELECT priv - allows currval() and SELECT * FROM seq USAGE priv - allows nextval() (required by SQL2003) UPDATE priv - allows setval() and nextval() I was originally thinking of a separate privilege bit for setval(), but that's sort of silly, as you can get (approximately) the effect of nextval() via setval(). Not much point in prohibiting nextval() to someone who can do setval(). This is 100% upward compatible with our current definition, and it meets both the SQL spec and Marko's desire to have a way of granting only nextval() privilege. Good point about compatibility. But makes the common case ugly. For regular usage you need to grant SELECT, USAGE ... Huh? :) How about this: SELECT: currval INSERT: nextval UPDATE: nextval, setval USAGE: nextval, currval With this the user needs only to remember SQL2003 syntax to cover 99.9% use cases. And when he wants to play more finegrained then he can combine with the SELECT, INSERT, UPDATE. The above table seem bit messy, but I see it as much easier to explain to somebody. BTW, what about lastval()? I'm not sure we can usefully associate any privilege check with that, since it's not clear which sequence it applies to. Does it make sense to remember what sequence the value came from and privilege-check against that, or is that just too weird? Hmm. So it means with lastval() user can see the state of sequences he has no access to? Seems like the privilege check would be good idea. -- marko ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Marko Kreen [EMAIL PROTECTED] writes: Good point about compatibility. But makes the common case ugly. For regular usage you need to grant SELECT, USAGE ... Huh? :) How about this: SELECT: currval INSERT: nextval UPDATE: nextval, setval USAGE: nextval, currval Seems a little weird. Hmm ... what is the use-case for allowing someone to do nextval but not currval? I can't see one. How about we simplify this to SELECT: currval UPDATE: nextval, setval USAGE: nextval, currval This is still upward compatible with our old behavior, which is SELECT: currval UPDATE: nextval, setval and it still meets the SQL spec's requirement that USAGE allow nextval, and USAGE is the only one you need for normal usage. regards, tom lane ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Tom Lane wrote: Marko Kreen [EMAIL PROTECTED] writes: Good point about compatibility. But makes the common case ugly. For regular usage you need to grant SELECT, USAGE ... Huh? :) How about this: SELECT: currval INSERT: nextval UPDATE: nextval, setval USAGE: nextval, currval Seems a little weird. Hmm ... what is the use-case for allowing someone to do nextval but not currval? I can't see one. How about we simplify this to SELECT: currval UPDATE: nextval, setval USAGE: nextval, currval This is still upward compatible with our old behavior, which is SELECT: currval UPDATE: nextval, setval and it still meets the SQL spec's requirement that USAGE allow nextval, and USAGE is the only one you need for normal usage. I think your original proposal was better. Why is it important that we have a single-keyword usage for the common case? No one has complained about what we have now and that requires two keywords just like your proposal. We don't have a shorthand for GRANT INSERT/UPDATE/DELETE. In fact, if it was backward-compatible I would suggest we make UPDATE just setval. Does the standard require USAGE to support currval? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruce Momjian pgman@candle.pha.pa.us writes: Does the standard require USAGE to support currval? currval isn't in the standard (unless I missed something), so it has nothing to say one way or the other on the point. Basically what we seem to be homing in on is to keep SELECT and UPDATE privileges doing what they do now and then add a USAGE privilege. I think I agree with Marko that USAGE should mean nextval + currval; it already must overlap UPDATE and so there's no very good reason why it shouldn't overlap SELECT too. Furthermore there's no plausible use-case where you'd want to grant nextval but not currval, so why not keep the notation simple? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
I wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Does the standard require USAGE to support currval? currval isn't in the standard (unless I missed something), so it has nothing to say one way or the other on the point. Wait, I take that back. Remember our previous discussions about this point: the spec's NEXT VALUE FOR construct is *not* equivalent to nextval, because they specify that the sequence advances just once per command even if the command says NEXT VALUE FOR in multiple places. This means that NEXT VALUE FOR is effectively both nextval and currval; the first one in a command does nextval and the rest do currval. Accordingly, I think it's reasonable to read the spec as saying that USAGE privilege encompasses both nextval and currval. regards, tom lane ---(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: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Should UPDATE also allow currval()? Your logic below seems to suggest that. --- Tom Lane wrote: I wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Does the standard require USAGE to support currval? currval isn't in the standard (unless I missed something), so it has nothing to say one way or the other on the point. Wait, I take that back. Remember our previous discussions about this point: the spec's NEXT VALUE FOR construct is *not* equivalent to nextval, because they specify that the sequence advances just once per command even if the command says NEXT VALUE FOR in multiple places. This means that NEXT VALUE FOR is effectively both nextval and currval; the first one in a command does nextval and the rest do currval. Accordingly, I think it's reasonable to read the spec as saying that USAGE privilege encompasses both nextval and currval. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Tom Lane wrote:
I wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Does the standard require USAGE to support currval?
currval isn't in the standard (unless I missed something), so it has
nothing to say one way or the other on the point.
Wait, I take that back. Remember our previous discussions about this
point: the spec's NEXT VALUE FOR construct is *not* equivalent to
nextval, because they specify that the sequence advances just once per
command even if the command says NEXT VALUE FOR in multiple places.
This means that NEXT VALUE FOR is effectively both nextval and currval;
the first one in a command does nextval and the rest do currval.
Accordingly, I think it's reasonable to read the spec as saying that
USAGE privilege encompasses both nextval and currval.
Here's a patch that more closely matches the ideas proposed.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/grant.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.50
diff -c -c -r1.50 grant.sgml
*** doc/src/sgml/ref/grant.sgml 20 Oct 2005 19:18:01 - 1.50
--- doc/src/sgml/ref/grant.sgml 7 Jan 2006 06:00:14 -
***
*** 25,30
--- 25,35
ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
+ GRANT { { SELECT | USAGE | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] }
+ ON SEQUENCE replaceable class=PARAMETERtablename/replaceable [, ...]
+ TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
+
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE replaceabledbname/replaceable [, ...]
TO { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...] [
WITH GRANT OPTION ]
***
*** 260,265
--- 265,274
also met). Essentially this allows the grantee to quotelook up/
objects within the schema.
/para
+ para
+For sequences, this privilege allows the use of the
+functioncurrval/function and functionnextval/function
functions.
+ /para
/listitem
/varlistentry
***
*** 511,517
para
The literalRULE/literal privilege, and privileges on
! databases, tablespaces, schemas, languages, and sequences are
productnamePostgreSQL/productname extensions.
/para
/refsect1
--- 520,526
para
The literalRULE/literal privilege, and privileges on
! databases, tablespaces, schemas, and languages are
productnamePostgreSQL/productname extensions.
/para
/refsect1
Index: doc/src/sgml/ref/revoke.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v
retrieving revision 1.35
diff -c -c -r1.35 revoke.sgml
*** doc/src/sgml/ref/revoke.sgml20 Oct 2005 19:18:01 - 1.35
--- doc/src/sgml/ref/revoke.sgml7 Jan 2006 06:00:14 -
***
*** 28,33
--- 28,40
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE }
+ [,...] | ALL [ PRIVILEGES ] }
+ ON SEQUENCE replaceable class=PARAMETERtablename/replaceable [, ...]
+ FROM { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
+
+ REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE replaceabledbname/replaceable [, ...]
FROM { replaceable class=PARAMETERusername/replaceable | GROUP
replaceable class=PARAMETERgroupname/replaceable | PUBLIC } [, ...]
Index: src/backend/catalog/aclchk.c
===
RCS file: /cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.123
diff -c -c -r1.123 aclchk.c
*** src/backend/catalog/aclchk.c1 Dec 2005 02:03:00 - 1.123
--- src/backend/catalog/aclchk.c7 Jan 2006 06:00:27 -
***
*** 286,291
--- 286,295
all_privileges = ACL_ALL_RIGHTS_RELATION;
errormsg = _(invalid privilege type %s for table);
break;
+ case ACL_OBJECT_SEQUENCE:
+ all_privileges = ACL_ALL_RIGHTS_SEQUENCE;
+ errormsg = _(invalid
Re: [PATCHES] [HACKERS] Inconsistent syntax in GRANT
Bruce Momjian pgman@candle.pha.pa.us writes: Should UPDATE also allow currval()? Your logic below seems to suggest that. I thought about that, but there are a couple of reasons not to: 1. It'd be a change from the current behavior of UPDATE privilege. 2. If there's someone out there who really does want write-only privileges for sequences, they'd be out in the cold. I don't find either of these very compelling, but the case for changing the behavior of UPDATE isn't strong either. I think backwards compatibility should carry the day if there's not a strong argument in favor of change. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
