TRUNCATE, VACUUM, ANALYZE privileges
Greetings,
The following patch implements individual privileges for TRUNCATE,
VACUUM and ANALYZE. Includes documentation and regression test
updates. Resolves TODO item 'Add a separate TRUNCATE permission'.
Created off of current (2005/01/03) CVS TIP.
At least the 'no one interested has written a patch' argument is gone
now, fire away with other comments/concerns. :)
Thanks,
Stephen
Attachments:
privs.20060103.ctx.difftext/plain; charset=us-asciiDownload
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.301
diff -c -r1.301 func.sgml
*** doc/src/sgml/func.sgml 28 Dec 2005 01:29:58 -0000 1.301
--- doc/src/sgml/func.sgml 4 Jan 2006 03:38:01 -0000
***************
*** 8961,8968 ****
The desired access privilege type
is specified by a text string, which must evaluate to one of the
values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>,
! <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, or
! <literal>TRIGGER</literal>. (Case of the string is not significant, however.)
An example is:
<programlisting>
SELECT has_table_privilege('myschema.mytable', 'select');
--- 8961,8969 ----
The desired access privilege type
is specified by a text string, which must evaluate to one of the
values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>,
! <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>,
! <literal>TRIGGER</literal>, <literal>TRUNCATE</literal>, <literal>VACUUM</literal>, or
! <literal>ANALYZE</literal>. (Case of the string is not significant, however.)
An example is:
<programlisting>
SELECT has_table_privilege('myschema.mytable', 'select');
Index: doc/src/sgml/information_schema.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v
retrieving revision 1.23
diff -c -r1.23 information_schema.sgml
*** doc/src/sgml/information_schema.sgml 8 Dec 2005 20:48:10 -0000 1.23
--- doc/src/sgml/information_schema.sgml 4 Jan 2006 03:38:01 -0000
***************
*** 2395,2401 ****
Type of the privilege: <literal>SELECT</literal>,
<literal>DELETE</literal>, <literal>INSERT</literal>,
<literal>UPDATE</literal>, <literal>REFERENCES</literal>,
! <literal>RULE</literal>, or <literal>TRIGGER</literal>
</entry>
</row>
--- 2395,2403 ----
Type of the privilege: <literal>SELECT</literal>,
<literal>DELETE</literal>, <literal>INSERT</literal>,
<literal>UPDATE</literal>, <literal>REFERENCES</literal>,
! <literal>RULE</literal>, <literal>TRIGGER</literal>,
! <literal>TRUNCATE</literal>, <literal>VACUUM</literal>, or
! <literal>ANALYZE</literal>.
</entry>
</row>
***************
*** 3643,3649 ****
Type of the privilege: <literal>SELECT</literal>,
<literal>DELETE</literal>, <literal>INSERT</literal>,
<literal>UPDATE</literal>, <literal>REFERENCES</literal>,
! <literal>RULE</literal>, or <literal>TRIGGER</literal>
</entry>
</row>
--- 3645,3653 ----
Type of the privilege: <literal>SELECT</literal>,
<literal>DELETE</literal>, <literal>INSERT</literal>,
<literal>UPDATE</literal>, <literal>REFERENCES</literal>,
! <literal>RULE</literal>, <literal>TRIGGER</literal>,
! <literal>TRUNCATE</literal>, <literal>VACUUM</literal>, or
! <literal>ANALYZE</literal>.
</entry>
</row>
Index: doc/src/sgml/user-manag.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/user-manag.sgml,v
retrieving revision 1.33
diff -c -r1.33 user-manag.sgml
*** doc/src/sgml/user-manag.sgml 20 Oct 2005 19:18:00 -0000 1.33
--- doc/src/sgml/user-manag.sgml 4 Jan 2006 03:38:01 -0000
***************
*** 296,301 ****
--- 296,302 ----
There are several different kinds of privilege: <literal>SELECT</>,
<literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
<literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
+ <literal>TRUNCATE</>, <literal>VACUUM</>, <literal>ANALYZE</>,
<literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
and <literal>USAGE</>. For more
information on the different types of privileges supported by
Index: doc/src/sgml/ref/grant.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.50
diff -c -r1.50 grant.sgml
*** doc/src/sgml/ref/grant.sgml 20 Oct 2005 19:18:01 -0000 1.50
--- doc/src/sgml/ref/grant.sgml 4 Jan 2006 03:38:02 -0000
***************
*** 20,26 ****
<refsynopsisdiv>
<synopsis>
! GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
--- 20,26 ----
<refsynopsisdiv>
<synopsis>
! GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | TRUNCATE | VACUUM | ANALYZE }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
TO { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
***************
*** 205,210 ****
--- 205,243 ----
</varlistentry>
<varlistentry>
+ <term>TRUNCATE</term>
+ <listitem>
+ <para>
+ Allows the truncation of the specified table. NOTE: This will NOT execute
+ triggers defined on the table. Additionally, this requires locking the table
+ exclusivly because it does not follow normal MVCC rules. See the <xref
+ linkend="sql-truncate" endterm="sql-truncate-title"> statement.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>VACUUM</term>
+ <listitem>
+ <para>
+ Allows the vacuuming of the specified table. Note: This implies ANALYZE
+ rights. See the <xref linkend="sql-vacuum" endterm="sql-vacuum-title">
+ statement.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ANALYZE</term>
+ <listitem>
+ <para>
+ Allows the analyzing of the specified table. See the <xref
+ linkend="sql-analyze" endterm="sql-analyze-title"> statement.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term>CREATE</term>
<listitem>
<para>
***************
*** 402,408 ****
U -- USAGE
C -- CREATE
T -- TEMPORARY
! arwdRxt -- ALL PRIVILEGES (for tables)
* -- grant option for preceding privilege
/yyyy -- user who granted this privilege
--- 435,444 ----
U -- USAGE
C -- CREATE
T -- TEMPORARY
! e -- TRUNCATE
! V -- VACUUM
! A -- ANALYZE
! arwdRxteVA -- ALL PRIVILEGES (for tables)
* -- grant option for preceding privilege
/yyyy -- user who granted this privilege
Index: doc/src/sgml/ref/revoke.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v
retrieving revision 1.35
diff -c -r1.35 revoke.sgml
*** doc/src/sgml/ref/revoke.sgml 20 Oct 2005 19:18:01 -0000 1.35
--- doc/src/sgml/ref/revoke.sgml 4 Jan 2006 03:38:02 -0000
***************
*** 21,27 ****
<refsynopsisdiv>
<synopsis>
REVOKE [ GRANT OPTION FOR ]
! { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
REVOKE [ GRANT OPTION FOR ]
! { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | TRUNCATE | VACUUM | ANALYZE }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] <replaceable class="PARAMETER">tablename</replaceable> [, ...]
FROM { <replaceable class="PARAMETER">username</replaceable> | GROUP <replaceable class="PARAMETER">groupname</replaceable> | PUBLIC } [, ...]
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.123
diff -c -r1.123 aclchk.c
*** src/backend/catalog/aclchk.c 1 Dec 2005 02:03:00 -0000 1.123
--- src/backend/catalog/aclchk.c 4 Jan 2006 03:38:02 -0000
***************
*** 1286,1291 ****
--- 1286,1297 ----
return ACL_CREATE_TEMP;
if (strcmp(privname, "temp") == 0)
return ACL_CREATE_TEMP;
+ if (strcmp(privname, "truncate") == 0)
+ return ACL_TRUNCATE;
+ if (strcmp(privname, "vacuum") == 0)
+ return ACL_VACUUM;
+ if (strcmp(privname, "analyze") == 0)
+ return ACL_ANALYZE;
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("unrecognized privilege type \"%s\"", privname)));
***************
*** 1319,1324 ****
--- 1325,1336 ----
return "CREATE";
case ACL_CREATE_TEMP:
return "TEMP";
+ case ACL_TRUNCATE:
+ return "TRUNCATE";
+ case ACL_VACUUM:
+ return "VACUUM";
+ case ACL_ANALYZE:
+ return "ANALYZE";
default:
elog(ERROR, "unrecognized privilege: %d", (int) privilege);
}
***************
*** 1502,1508 ****
* protected in this way. Assume the view rules can take care of
* themselves.
*/
! if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)) &&
IsSystemClass(classForm) &&
classForm->relkind != RELKIND_VIEW &&
!has_rolcatupdate(roleid) &&
--- 1514,1520 ----
* protected in this way. Assume the view rules can take care of
* themselves.
*/
! if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE)) &&
IsSystemClass(classForm) &&
classForm->relkind != RELKIND_VIEW &&
!has_rolcatupdate(roleid) &&
***************
*** 1511,1517 ****
#ifdef ACLDEBUG
elog(DEBUG2, "permission denied for system catalog update");
#endif
! mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE);
}
/*
--- 1523,1529 ----
#ifdef ACLDEBUG
elog(DEBUG2, "permission denied for system catalog update");
#endif
! mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE);
}
/*
Index: src/backend/commands/analyze.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/analyze.c,v
retrieving revision 1.90
diff -c -r1.90 analyze.c
*** src/backend/commands/analyze.c 22 Nov 2005 18:17:08 -0000 1.90
--- src/backend/commands/analyze.c 4 Jan 2006 03:38:02 -0000
***************
*** 112,117 ****
--- 112,118 ----
double totalrows,
totaldeadrows;
HeapTuple *rows;
+ AclResult aclresult;
if (vacstmt->verbose)
elevel = INFO;
***************
*** 146,158 ****
*/
onerel = relation_open(relid, AccessShareLock);
! if (!(pg_class_ownercheck(RelationGetRelid(onerel), GetUserId()) ||
(pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared)))
{
/* No need for a WARNING if we already complained during VACUUM */
if (!vacstmt->vacuum)
ereport(WARNING,
! (errmsg("skipping \"%s\" --- only table or database owner can analyze it",
RelationGetRelationName(onerel))));
relation_close(onerel, AccessShareLock);
return;
--- 147,160 ----
*/
onerel = relation_open(relid, AccessShareLock);
! aclresult = pg_class_aclcheck(RelationGetRelid(onerel), GetUserId(), ACL_ANALYZE);
! if (!((aclresult == ACLCHECK_OK) ||
(pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared)))
{
/* No need for a WARNING if we already complained during VACUUM */
if (!vacstmt->vacuum)
ereport(WARNING,
! (errmsg("skipping \"%s\" --- only user with ANALYZE privilege or database owner can analyze it",
RelationGetRelationName(onerel))));
relation_close(onerel, AccessShareLock);
return;
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.176
diff -c -r1.176 tablecmds.c
*** src/backend/commands/tablecmds.c 22 Nov 2005 18:17:09 -0000 1.176
--- src/backend/commands/tablecmds.c 4 Jan 2006 03:38:02 -0000
***************
*** 544,549 ****
--- 544,550 ----
{
RangeVar *rv = lfirst(cell);
Relation rel;
+ AclResult aclresult;
/* Grab exclusive lock in preparation for truncate */
rel = heap_openrv(rv, AccessExclusiveLock);
***************
*** 556,564 ****
RelationGetRelationName(rel))));
/* Permissions checks */
! if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
! aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
! RelationGetRelationName(rel));
if (!allowSystemTableMods && IsSystemRelation(rel))
ereport(ERROR,
--- 557,565 ----
RelationGetRelationName(rel))));
/* Permissions checks */
! aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), ACL_TRUNCATE);
! if (aclresult != ACLCHECK_OK)
! aclcheck_error(aclresult, ACL_KIND_CLASS, RelationGetRelationName(rel));
if (!allowSystemTableMods && IsSystemRelation(rel))
ereport(ERROR,
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.319
diff -c -r1.319 vacuum.c
*** src/backend/commands/vacuum.c 22 Nov 2005 18:17:09 -0000 1.319
--- src/backend/commands/vacuum.c 4 Jan 2006 03:38:02 -0000
***************
*** 948,953 ****
--- 948,954 ----
LockRelId onerelid;
Oid toast_relid;
bool result;
+ AclResult aclresult;
/* Begin a transaction for vacuuming this relation */
StartTransactionCommand();
***************
*** 989,1008 ****
/*
* Open the class, get an appropriate lock on it, and check permissions.
*
! * We allow the user to vacuum a table if he is superuser, the table
! * owner, or the database owner (but in the latter case, only if it's not
! * a shared relation). pg_class_ownercheck includes the superuser case.
*
* Note we choose to treat permissions failure as a WARNING and keep
* trying to vacuum the rest of the DB --- is this appropriate?
*/
onerel = relation_open(relid, lmode);
! if (!(pg_class_ownercheck(RelationGetRelid(onerel), GetUserId()) ||
(pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared)))
{
ereport(WARNING,
! (errmsg("skipping \"%s\" --- only table or database owner can vacuum it",
RelationGetRelationName(onerel))));
relation_close(onerel, lmode);
StrategyHintVacuum(false);
--- 990,1011 ----
/*
* Open the class, get an appropriate lock on it, and check permissions.
*
! * We allow the user to vacuum a table if he is superuser, has the
! * VACUUM permission on the table, or the database owner (but in the
! * latter case, only if it's not a shared relation).
! * pg_class_aclcheck includes the superuser case.
*
* Note we choose to treat permissions failure as a WARNING and keep
* trying to vacuum the rest of the DB --- is this appropriate?
*/
onerel = relation_open(relid, lmode);
! aclresult = pg_class_aclcheck(RelationGetRelid(onerel), GetUserId(), ACL_VACUUM);
! if (!((aclresult == ACLCHECK_OK) ||
(pg_database_ownercheck(MyDatabaseId, GetUserId()) && !onerel->rd_rel->relisshared)))
{
ereport(WARNING,
! (errmsg("skipping \"%s\" --- only user with VACUUM privilege or database owner can vacuum it",
RelationGetRelationName(onerel))));
relation_close(onerel, lmode);
StrategyHintVacuum(false);
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.521
diff -c -r2.521 gram.y
*** src/backend/parser/gram.y 29 Dec 2005 04:53:18 -0000 2.521
--- src/backend/parser/gram.y 4 Jan 2006 03:38:02 -0000
***************
*** 3300,3305 ****
--- 3300,3306 ----
privilege: SELECT { $$ = pstrdup($1); }
| REFERENCES { $$ = pstrdup($1); }
| CREATE { $$ = pstrdup($1); }
+ | ANALYZE { $$ = pstrdup($1); }
| ColId { $$ = $1; }
;
Index: src/backend/utils/adt/acl.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/acl.c,v
retrieving revision 1.129
diff -c -r1.129 acl.c
*** src/backend/utils/adt/acl.c 18 Nov 2005 02:38:23 -0000 1.129
--- src/backend/utils/adt/acl.c 4 Jan 2006 03:38:02 -0000
***************
*** 287,292 ****
--- 287,301 ----
case ACL_CREATE_TEMP_CHR:
read = ACL_CREATE_TEMP;
break;
+ case ACL_TRUNCATE_CHR:
+ read = ACL_TRUNCATE;
+ break;
+ case ACL_VACUUM_CHR:
+ read = ACL_VACUUM;
+ break;
+ case ACL_ANALYZE_CHR:
+ read = ACL_ANALYZE;
+ break;
default:
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
***************
*** 1334,1339 ****
--- 1343,1354 ----
return ACL_CREATE_TEMP;
if (pg_strcasecmp(priv_type, "TEMPORARY") == 0)
return ACL_CREATE_TEMP;
+ if (pg_strcasecmp(priv_type, "TRUNCATE") == 0)
+ return ACL_TRUNCATE;
+ if (pg_strcasecmp(priv_type, "VACUUM") == 0)
+ return ACL_VACUUM;
+ if (pg_strcasecmp(priv_type, "ANALYZE") == 0)
+ return ACL_ANALYZE;
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
***************
*** 1559,1564 ****
--- 1574,1594 ----
if (pg_strcasecmp(priv_type, "TRIGGER WITH GRANT OPTION") == 0)
return ACL_GRANT_OPTION_FOR(ACL_TRIGGER);
+ if (pg_strcasecmp(priv_type, "TRUNCATE") == 0)
+ return ACL_TRUNCATE;
+ if (pg_strcasecmp(priv_type, "TRUNCATE WITH GRANT OPTION") == 0)
+ return ACL_GRANT_OPTION_FOR(ACL_TRUNCATE);
+
+ if (pg_strcasecmp(priv_type, "VACUUM") == 0)
+ return ACL_VACUUM;
+ if (pg_strcasecmp(priv_type, "VACUUM WITH GRANT OPTION") == 0)
+ return ACL_GRANT_OPTION_FOR(ACL_VACUUM);
+
+ if (pg_strcasecmp(priv_type, "ANALYZE") == 0)
+ return ACL_ANALYZE;
+ if (pg_strcasecmp(priv_type, "ANALYZE WITH GRANT OPTION") == 0)
+ return ACL_GRANT_OPTION_FOR(ACL_ANALYZE);
+
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized privilege type: \"%s\"", priv_type)));
Index: src/bin/pg_dump/dumputils.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/dumputils.c,v
retrieving revision 1.23
diff -c -r1.23 dumputils.c
*** src/bin/pg_dump/dumputils.c 3 Dec 2005 21:06:18 -0000 1.23
--- src/bin/pg_dump/dumputils.c 4 Jan 2006 03:38:02 -0000
***************
*** 580,585 ****
--- 580,588 ----
CONVERT_PRIV('d', "DELETE");
CONVERT_PRIV('x', "REFERENCES");
CONVERT_PRIV('t', "TRIGGER");
+ CONVERT_PRIV('e', "TRUNCATE");
+ CONVERT_PRIV('V', "VACUUM");
+ CONVERT_PRIV('A', "ANALYZE");
}
else
{
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/tab-complete.c,v
retrieving revision 1.143
diff -c -r1.143 tab-complete.c
*** src/bin/psql/tab-complete.c 18 Dec 2005 02:17:16 -0000 1.143
--- src/bin/psql/tab-complete.c 4 Jan 2006 03:38:02 -0000
***************
*** 1343,1349 ****
{
static const char *const list_privileg[] =
{"SELECT", "INSERT", "UPDATE", "DELETE", "RULE", "REFERENCES",
! "TRIGGER", "CREATE", "TEMPORARY", "EXECUTE", "USAGE", "ALL", NULL};
COMPLETE_WITH_LIST(list_privileg);
}
--- 1343,1350 ----
{
static const char *const list_privileg[] =
{"SELECT", "INSERT", "UPDATE", "DELETE", "RULE", "REFERENCES",
! "TRIGGER", "TRUNCATE", "VACUUM", "ANALYZE", "CREATE",
! "TEMPORARY", "EXECUTE", "USAGE", "ALL", NULL};
COMPLETE_WITH_LIST(list_privileg);
}
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.298
diff -c -r1.298 parsenodes.h
*** src/include/nodes/parsenodes.h 7 Dec 2005 15:20:55 -0000 1.298
--- src/include/nodes/parsenodes.h 4 Jan 2006 03:38:02 -0000
***************
*** 47,53 ****
#define ACL_USAGE (1<<8) /* for languages and namespaces */
#define ACL_CREATE (1<<9) /* for namespaces and databases */
#define ACL_CREATE_TEMP (1<<10) /* for databases */
! #define N_ACL_RIGHTS 11 /* 1 plus the last 1<<x */
#define ACL_NO_RIGHTS 0
/* Currently, SELECT ... FOR UPDATE/FOR SHARE requires UPDATE privileges */
#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
--- 47,56 ----
#define ACL_USAGE (1<<8) /* for languages and namespaces */
#define ACL_CREATE (1<<9) /* for namespaces and databases */
#define ACL_CREATE_TEMP (1<<10) /* for databases */
! #define ACL_TRUNCATE (1<<11) /* for relations */
! #define ACL_VACUUM (1<<12) /* for relations */
! #define ACL_ANALYZE (1<<13) /* for relations */
! #define N_ACL_RIGHTS 14 /* 1 plus the last 1<<x */
#define ACL_NO_RIGHTS 0
/* Currently, SELECT ... FOR UPDATE/FOR SHARE requires UPDATE privileges */
#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
Index: src/include/utils/acl.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/acl.h,v
retrieving revision 1.91
diff -c -r1.91 acl.h
*** src/include/utils/acl.h 1 Dec 2005 02:03:01 -0000 1.91
--- src/include/utils/acl.h 4 Jan 2006 03:38:02 -0000
***************
*** 135,148 ****
#define ACL_USAGE_CHR 'U'
#define ACL_CREATE_CHR 'C'
#define ACL_CREATE_TEMP_CHR 'T'
/* string holding all privilege code chars, in order by bitmask position */
! #define ACL_ALL_RIGHTS_STR "arwdRxtXUCT"
/*
* Bitmasks defining "all rights" for each supported object type
*/
! #define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_RULE|ACL_REFERENCES|ACL_TRIGGER)
#define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP)
#define ACL_ALL_RIGHTS_FUNCTION (ACL_EXECUTE)
#define ACL_ALL_RIGHTS_LANGUAGE (ACL_USAGE)
--- 135,151 ----
#define ACL_USAGE_CHR 'U'
#define ACL_CREATE_CHR 'C'
#define ACL_CREATE_TEMP_CHR 'T'
+ #define ACL_TRUNCATE_CHR 'e'
+ #define ACL_VACUUM_CHR 'V'
+ #define ACL_ANALYZE_CHR 'A'
/* string holding all privilege code chars, in order by bitmask position */
! #define ACL_ALL_RIGHTS_STR "arwdRxtXUCTeVA"
/*
* Bitmasks defining "all rights" for each supported object type
*/
! #define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_RULE|ACL_REFERENCES|ACL_TRIGGER|ACL_TRUNCATE|ACL_VACUUM|ACL_ANALYZE)
#define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP)
#define ACL_ALL_RIGHTS_FUNCTION (ACL_EXECUTE)
#define ACL_ALL_RIGHTS_LANGUAGE (ACL_USAGE)
Index: src/test/regress/expected/dependency.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/dependency.out,v
retrieving revision 1.3
diff -c -r1.3 dependency.out
*** src/test/regress/expected/dependency.out 21 Nov 2005 12:49:33 -0000 1.3
--- src/test/regress/expected/dependency.out 4 Jan 2006 03:38:02 -0000
***************
*** 26,32 ****
ERROR: role "regression_user" cannot be dropped because some objects depend on it
DETAIL: access to table deptest
-- now we are OK to drop him
! REVOKE TRIGGER ON deptest FROM regression_user;
DROP USER regression_user;
-- we are OK too if we drop the privileges all at once
REVOKE ALL ON deptest FROM regression_user2;
--- 26,32 ----
ERROR: role "regression_user" cannot be dropped because some objects depend on it
DETAIL: access to table deptest
-- now we are OK to drop him
! REVOKE TRIGGER, TRUNCATE, VACUUM, ANALYZE ON deptest FROM regression_user;
DROP USER regression_user;
-- we are OK too if we drop the privileges all at once
REVOKE ALL ON deptest FROM regression_user2;
***************
*** 69,76 ****
\z deptest1
Access privileges for database "regression"
Schema | Name | Type | Access privileges
! --------+----------+-------+----------------------------------------------------------------------------------------------------------------------------------------
! public | deptest1 | table | {regression_user0=arwdRxt/regression_user0,regression_user1=a*r*w*d*R*x*t*/regression_user0,regression_user2=arwdRxt/regression_user1}
(1 row)
DROP OWNED BY regression_user1;
--- 69,76 ----
\z deptest1
Access privileges for database "regression"
Schema | Name | Type | Access privileges
! --------+----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------
! public | deptest1 | table | {regression_user0=arwdRxteVA/regression_user0,regression_user1=a*r*w*d*R*x*t*e*V*A*/regression_user0,regression_user2=arwdRxteVA/regression_user1}
(1 row)
DROP OWNED BY regression_user1;
***************
*** 78,85 ****
\z deptest1
Access privileges for database "regression"
Schema | Name | Type | Access privileges
! --------+----------+-------+---------------------------------------------
! public | deptest1 | table | {regression_user0=arwdRxt/regression_user0}
(1 row)
-- table was dropped
--- 78,85 ----
\z deptest1
Access privileges for database "regression"
Schema | Name | Type | Access privileges
! --------+----------+-------+------------------------------------------------
! public | deptest1 | table | {regression_user0=arwdRxteVA/regression_user0}
(1 row)
-- table was dropped
Index: src/test/regress/expected/privileges.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/privileges.out,v
retrieving revision 1.32
diff -c -r1.32 privileges.out
*** src/test/regress/expected/privileges.out 15 Aug 2005 02:40:30 -0000 1.32
--- src/test/regress/expected/privileges.out 4 Jan 2006 03:38:02 -0000
***************
*** 67,72 ****
--- 67,73 ----
------+------
(0 rows)
+ TRUNCATE atest1; -- ok
INSERT INTO atest1 VALUES (2, 'two'); -- ok
INSERT INTO atest2 VALUES ('foo', true); -- fail
ERROR: permission denied for relation atest2
***************
*** 85,90 ****
--- 86,98 ----
ERROR: permission denied for relation atest2
DELETE FROM atest2; -- fail
ERROR: permission denied for relation atest2
+ TRUNCATE atest2; -- fail
+ ERROR: permission denied for relation atest2
+ VACUUM atest2; -- fail
+ WARNING: skipping "atest2" --- only user with VACUUM privilege or database owner can vacuum it
+ ANALYZE atest1; -- ok
+ ANALYZE atest2; -- fail
+ WARNING: skipping "atest2" --- only user with ANALYZE privilege or database owner can analyze it
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
ERROR: permission denied for relation atest2
COPY atest2 FROM stdin; -- fail
***************
*** 542,547 ****
--- 550,576 ----
f
(1 row)
+ select has_table_privilege(t1.oid,'truncate')
+ from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
+ select has_table_privilege(t1.oid,'vacuum')
+ from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
+ select has_table_privilege(t1.oid,'analyze')
+ from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege
+ ---------------------
+ f
+ (1 row)
+
-- Grant options
SET SESSION AUTHORIZATION regressuser1;
CREATE TABLE atest4 (a int);
Index: src/test/regress/sql/dependency.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/dependency.sql,v
retrieving revision 1.3
diff -c -r1.3 dependency.sql
*** src/test/regress/sql/dependency.sql 21 Nov 2005 12:49:33 -0000 1.3
--- src/test/regress/sql/dependency.sql 4 Jan 2006 03:38:02 -0000
***************
*** 25,31 ****
DROP USER regression_user;
-- now we are OK to drop him
! REVOKE TRIGGER ON deptest FROM regression_user;
DROP USER regression_user;
-- we are OK too if we drop the privileges all at once
--- 25,31 ----
DROP USER regression_user;
-- now we are OK to drop him
! REVOKE TRIGGER, TRUNCATE, VACUUM, ANALYZE ON deptest FROM regression_user;
DROP USER regression_user;
-- we are OK too if we drop the privileges all at once
Index: src/test/regress/sql/privileges.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/privileges.sql,v
retrieving revision 1.17
diff -c -r1.17 privileges.sql
*** src/test/regress/sql/privileges.sql 15 Aug 2005 02:40:32 -0000 1.17
--- src/test/regress/sql/privileges.sql 4 Jan 2006 03:38:02 -0000
***************
*** 50,55 ****
--- 50,56 ----
SELECT * FROM atest1; -- ok
SELECT * FROM atest2; -- ok
+ TRUNCATE atest1; -- ok
INSERT INTO atest1 VALUES (2, 'two'); -- ok
INSERT INTO atest2 VALUES ('foo', true); -- fail
INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
***************
*** 58,63 ****
--- 59,68 ----
SELECT * FROM atest1 FOR UPDATE; -- ok
SELECT * FROM atest2 FOR UPDATE; -- fail
DELETE FROM atest2; -- fail
+ TRUNCATE atest2; -- fail
+ VACUUM atest2; -- fail
+ ANALYZE atest1; -- ok
+ ANALYZE atest2; -- fail
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
COPY atest2 FROM stdin; -- fail
GRANT ALL ON atest1 TO PUBLIC; -- fail
***************
*** 288,293 ****
--- 293,304 ----
from (select oid from pg_class where relname = 'atest1') as t1;
select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'atest1') as t1;
+ select has_table_privilege(t1.oid,'truncate')
+ from (select oid from pg_class where relname = 'atest1') as t1;
+ select has_table_privilege(t1.oid,'vacuum')
+ from (select oid from pg_class where relname = 'atest1') as t1;
+ select has_table_privilege(t1.oid,'analyze')
+ from (select oid from pg_class where relname = 'atest1') as t1;
-- Grant options
Stephen Frost <sfrost@snowman.net> writes:
The following patch implements individual privileges for TRUNCATE,
VACUUM and ANALYZE. Includes documentation and regression test
updates. Resolves TODO item 'Add a separate TRUNCATE permission'.
At least the 'no one interested has written a patch' argument is gone
now, fire away with other comments/concerns. :)
I have a very serious problem with the idea of inventing individual
privilege bits for every maintenance command in sight. That does not
scale. How will you handle "GRANT ADD COLUMN", or "GRANT ADD COLUMN
as-long-as-its-not-SERIAL-because-I-dont-want-you-creating-sequences",
or "GRANT ALTER TABLE RELIABILITY" as soon as someone writes that patch,
or a dozen other cases that I could name without stopping for breath?
The proposed patch eats three of the five available privilege bits (that
is, available without accepting the distributed cost of enlarging ACL
bitmasks), and you've made no case at all why we should spend that
limited resource in this particular fashion.
regards, tom lane
On Tue, Jan 03, 2006 at 11:32:01PM -0500, Tom Lane wrote:
Stephen Frost <sfrost@snowman.net> writes:
The following patch implements individual privileges for TRUNCATE,
VACUUM and ANALYZE. Includes documentation and regression test
updates. Resolves TODO item 'Add a separate TRUNCATE permission'.At least the 'no one interested has written a patch' argument is gone
now, fire away with other comments/concerns. :)I have a very serious problem with the idea of inventing individual
privilege bits for every maintenance command in sight. That does not
scale. How will you handle "GRANT ADD COLUMN", or "GRANT ADD COLUMN
as-long-as-its-not-SERIAL-because-I-dont-want-you-creating-sequences",
or "GRANT ALTER TABLE RELIABILITY" as soon as someone writes that patch,
or a dozen other cases that I could name without stopping for breath?The proposed patch eats three of the five available privilege bits (that
is, available without accepting the distributed cost of enlarging ACL
bitmasks), and you've made no case at all why we should spend that
limited resource in this particular fashion.
We rely heavily on truncate as delete for large numbers of rows is very
costly. An example, we copy_in batches of rows from several sources through
the day to a "pending work" table, with another process periodically
processing the rows and sweeping them into a history table. The sweep
leaves an empty "pending work" table. Truncate is very efficient for this
pattern.
However it means that all our jobs have to run with more permissions than
they really should have as there is no way to grant "truncate". If giving
truncate its very own permission is too wasteful of permission bits, perhaps
having truncate be the same as "delete" for permissions purposes would work.
Alternatively a separate "whole table operations" permision might cover
truncate and some of the alter type things too. Of course table owner does
this, but that is what I don't want everyone to be require to have.
-dg
--
David Gould daveg@sonic.net
If simplicity worked, the world would be overrun with insects.
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
The following patch implements individual privileges for TRUNCATE,
VACUUM and ANALYZE. Includes documentation and regression test
updates. Resolves TODO item 'Add a separate TRUNCATE permission'.At least the 'no one interested has written a patch' argument is gone
now, fire away with other comments/concerns. :)I have a very serious problem with the idea of inventing individual
privilege bits for every maintenance command in sight. That does not
scale. How will you handle "GRANT ADD COLUMN", or "GRANT ADD COLUMN
as-long-as-its-not-SERIAL-because-I-dont-want-you-creating-sequences",
or "GRANT ALTER TABLE RELIABILITY" as soon as someone writes that patch,
or a dozen other cases that I could name without stopping for breath?
GRANT ADD COLUMN, etc, aren't maintenance commands, they're DDL
statements and as such should be the purview of the owner. TRUNCATE,
VACUUM and ANALYZE are DML commands and are commands a user of
the table would use through the normal course of inserting, updating or
deleteing data in the table.
The proposed patch eats three of the five available privilege bits (that
is, available without accepting the distributed cost of enlarging ACL
bitmasks), and you've made no case at all why we should spend that
limited resource in this particular fashion.
I've shown a specific use-case for this. It's been asked for before by
others. I've shown why these particular ones make sense (while 'ADD
COLUMN', etc, don't). If we come up with more Postgres-specific DML
statements which aren't covered by other grants (which doesn't seem
terribly likely at this point) then we should add those. I could see
making VACUUM and ANALYZE use the same bit (since one implies the other)
but I'm not really a big fan of that and I don't see any other need for
these bits coming down the line anytime soon.
Thanks,
Stephen
* daveg (daveg@sonic.net) wrote:
We rely heavily on truncate as delete for large numbers of rows is very
costly. An example, we copy_in batches of rows from several sources through
the day to a "pending work" table, with another process periodically
processing the rows and sweeping them into a history table. The sweep
leaves an empty "pending work" table. Truncate is very efficient for this
pattern.However it means that all our jobs have to run with more permissions than
they really should have as there is no way to grant "truncate". If giving
truncate its very own permission is too wasteful of permission bits, perhaps
having truncate be the same as "delete" for permissions purposes would work.
Sounds very similar to my use-case, except my users just have to suffer
with delete because I don't want to grant them additional permissions.
Having truncate act off of delete isn't actually an option
unfortunately. This is because truncate skips triggers (probably not an
issue for you, certainly not one for me, but a problem with doing it in
the general case).
I'm not sure about you, but I know that I'd like to be able to do:
TRUNCATE, insert/copy data, ANALYZE without having to give all the other
permissions associated with ownership.
Alternatively a separate "whole table operations" permision might cover
truncate and some of the alter type things too. Of course table owner does
this, but that is what I don't want everyone to be require to have.
I'm not entirely sure if that'd be better or not.. It would involve
changing the structure of the ACLs to have two sets for each relation
and you'd have to sometimes look at one, sometimes at the other, and
possible both in some cases...
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
GRANT ADD COLUMN, etc, aren't maintenance commands, they're DDL
statements and as such should be the purview of the owner. TRUNCATE,
VACUUM and ANALYZE are DML commands and are commands a user of
the table would use through the normal course of inserting, updating or
deleteing data in the table.
I find this reasoning fairly dubious. In particular, it's hard to argue
that there is no DDL component to TRUNCATE when it effectively does an
implicit disable-triggers operation. Another thing setting TRUNCATE
apart from run-of-the-mill DDL operations is that it inherently violates
MVCC rules (by deleting rows that should still be visible to concurrent
transactions).
But my real problem with the approach is that I don't see where it
stops. If you're allowed to do ANALYZE, why not ALTER TABLE SET
STATISTICS? If you're allowed to do TRUNCATE, why not the
recently-discussed ALTER TABLE SET RELIABILITY? And how about CLUSTER?
All of these could be pretty useful for some applications not too far
removed from yours. And there will be someone wanting a bit for
DISABLE/ENABLE TRIGGER coming along right afterwards. Must we implement
a separate nonstandard privilege bit for every operation that someone
comes up and wants a bit for, if they have the necessary cut-and-paste
skill to submit a patch for it?
I'd feel happier about an approach that adds *one* privilege bit
covering a range of operations that we agree to be useful. This will
avoid chewing a disproportionate amount of ACL storage space, and it
will force us to confront the decision about which operations are out
as well as which are in.
One last point: -patches is not the place for this type of discussion.
regards, tom lane
Tom, et al,
Sorry for the longish email; if you're most interested in a change to
the ACL system to allow more privileges then skip to the bottom where
I worked up a change to give us more options without much of a
performance impact (I don't think anyway). Personally, I'd prefer that
to overloading the bits we have (except perhaps for vacuum/analyze).
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
GRANT ADD COLUMN, etc, aren't maintenance commands, they're DDL
statements and as such should be the purview of the owner. TRUNCATE,
VACUUM and ANALYZE are DML commands and are commands a user of
the table would use through the normal course of inserting, updating or
deleteing data in the table.I find this reasoning fairly dubious. In particular, it's hard to argue
that there is no DDL component to TRUNCATE when it effectively does an
implicit disable-triggers operation. Another thing setting TRUNCATE
apart from run-of-the-mill DDL operations is that it inherently violates
MVCC rules (by deleting rows that should still be visible to concurrent
transactions).
Kind of makes one wish you could know what tables were going to be
touched for a given transaction at the start of the transaction. That's
not really here nor there tho. I could see limiting truncate privileges
to tables which don't have on-delete triggers, that doesn't help with
the MVCC problem though and ends up being why we can't just use 'delete'
privileges for it.
Could we base vacuum and analyze rights off of other privileges though?
vacuum allowed if yoou have 'delete' privileges, analyze if you have
'insert', 'update', or 'delete'? And for 'truncate' permissions...
But my real problem with the approach is that I don't see where it
stops. If you're allowed to do ANALYZE, why not ALTER TABLE SET
STATISTICS? If you're allowed to do TRUNCATE, why not the
recently-discussed ALTER TABLE SET RELIABILITY? And how about CLUSTER?
All of these could be pretty useful for some applications not too far
removed from yours. And there will be someone wanting a bit for
DISABLE/ENABLE TRIGGER coming along right afterwards. Must we implement
a separate nonstandard privilege bit for every operation that someone
comes up and wants a bit for, if they have the necessary cut-and-paste
skill to submit a patch for it?
I think analyze is distinct from set statistics. SET STATISTICS, if
used improperly (perhaps by mistake or misunderstanding), could cause
serious havoc on the system as potentially very poor plans are chosen
because the statistics aren't at all correct. I don't see how running
analyze a couple times would have a detrimental effect (except for the
effort of running the analyze itself, but that's really not all that
much and if they want to DoS the box in that way there are other
things they can do). SET STATISTICS is also hopefully something you're
not having to change or do every time you add/remove/update data.
SET RELIABILITY is a more interesting question since it could be used in
a situation similar to why truncate's popular (mass data
loading/reloading). The same is true for disable/enable triggers.
I'd feel happier about an approach that adds *one* privilege bit
covering a range of operations that we agree to be useful. This will
avoid chewing a disproportionate amount of ACL storage space, and it
will force us to confront the decision about which operations are out
as well as which are in.
If we modify VACUUM/ANALYZE to be based off what I suggested above, then
we could add just one 'BYPASS' permission bit which would allow
TRUNCATE right now and then SET RELIABILITY, and DISABLE/ENABLE TRIGGER
later. I'm not a particularly big fan of this though because, while I'd
like to be able to give TRUNCATE permissions I'm not a big fan of SET
RELIABILITY because it would affect PITR backups. I suppose a user
would still have to intentionally do that though and so they'd have only
themselves to blame if the data they loaded wasn't part of the backup.
DISABLE/ENABLE TRIGGER has a similar issue though because that could
probably be used to bypass CHECK and REFERENCES constraints, which I
wouldn't want to allow.
A BYPASS bit would be better than having to give ownership rights
though. We could also look at restructuring the ACL system to be able
to handle more permissions better. As was suggested elsewhere, one
option would be to have a seperate set of ACLs (at least internally)
such that a given set of commands/permissions would be associated
with one set or the other set (and hopefully rarely, both). These could
be divided up by either level or frequency (which I think would actually
result in the same set). Level would be row/table/database, frequency
would be estimation of usage in the real world. This would seperate
SELECT, INSERT, UPDATE, DELETE into one set of permissions, and then
REFERENCES, RULE, TRIGGER, TRUNCATE, SET RELIABILITY, DISABLE/ENABLE
TRIGGER, CLUSTER into the other set. I havn't looked into this very
deeply but I like the basic idea of it better than having a 'catch-all'
permission bit.
Looking into utils/acl.h, grant options are pretty rarely accessed, even
less so than a 'truncate' permission would be, and I could see changing
AclItem to have:
typedef struct AclItem
{
Oid ai_grantee; /* ID that this item grants privs to */
Oid ai_grantor; /* grantor of privs */
AclMode ai_privs; /* privilege bits */
AclMode aig_privs; /* grant option bits */
} AclItem;
This makes AclItem slightly larger but I don't think it would have a
detrimental affect on performance other than that, it seems unlikely
that AclItem is scanned in such a way that the structure size would
adversely affect performance much. There's cacheing issues I suppose
but an extra couple of bytes doesn't seem all that terrible.
This would give us an extra 16 bits to work with though for ACL
privileges. At the moment we're talking about adding perhaps 6, or 4 if
we do the overloading for VACUUM/ANALYZE I suggested, which means still
having 15 or 17 bits left. We could postpone the grant split till we
actually go over 16 ACL bits used but I don't think there's much need
to.
If that's more palatable then I'd be happy to work up the changes to the
ACL system to implement that.
Thanks,
Stephen
Stephen Frost wrote:
I'm not a particularly big fan of this though because, while I'd
like to be able to give TRUNCATE permissions I'm not a big fan of SET
RELIABILITY because it would affect PITR backups.
As far as I have understood the discussion... with WAL archiving turned on,
the whole RELIABILITY changes would be no-ops, no?
Just as the CTAS optimization etc. only skip WAL if WAL archiving is turned
off.
Best Regards,
Michael Paesold
* Michael Paesold (mpaesold@gmx.at) wrote:
Stephen Frost wrote:
I'm not a particularly big fan of this though because, while I'd
like to be able to give TRUNCATE permissions I'm not a big fan of SET
RELIABILITY because it would affect PITR backups.As far as I have understood the discussion... with WAL archiving turned on,
the whole RELIABILITY changes would be no-ops, no?
Just as the CTAS optimization etc. only skip WAL if WAL archiving is turned
off.
Oh, I thought the reliability bit would bypass WAL even with archiving
turned on (which could be fine in some cases, just not all cases :). Of
course, all of this is still up in the air somewhat. :) If it's a noop
in that case then the 'bypass' bit might be alright to have control SET
RELIABILITY. I'd rather have the flexibility to have them be seperately
grantable though.
Thanks,
Stephen
On Thu, 2006-01-05 at 09:41 -0500, Stephen Frost wrote:
* Michael Paesold (mpaesold@gmx.at) wrote:
Stephen Frost wrote:
I'm not a particularly big fan of this though because, while I'd
like to be able to give TRUNCATE permissions I'm not a big fan of SET
RELIABILITY because it would affect PITR backups.As far as I have understood the discussion... with WAL archiving turned on,
the whole RELIABILITY changes would be no-ops, no?
Just as the CTAS optimization etc. only skip WAL if WAL archiving is turned
off.Oh, I thought the reliability bit would bypass WAL even with archiving
turned on (which could be fine in some cases, just not all cases :). Of
It might be better if this was an setting in postgresql.conf requiring a
restart to change, off by default.
I don't like the thought of a table owner or even a super-user being
able to throw away data because they failed to investigate the full
impact of the backup strategy. I.e. Someone missed the memo that backups
were changing from pg_dumps to PITR for database environment H.
--