>From: Tom Lane <tgl@sss.pgh.pa.us>
>I think a reasonable answer to this is to restrict VACUUM on any
>table to be allowed only to the table owner and Postgres superuser.
>Does anyone have an objection or better idea?
In the dim and distant past I produced a patch that put vacuum
into the list of things that you could GRANT on a per-table
basis. I don't know what effort it would take to rework that
for current or if it would be worth it.
I think your suggestion above would be perfect if you never
need to allow anyone else to vacuum a table.
I'va attached the old patch below.
Keith.
*** backend/parser/gram.y.orig Thu May 22 18:16:19 1997
--- backend/parser/gram.y Thu May 22 18:18:22 1997
***************
*** 646,656 **** privileges: ALL PRIVILEGES {
! $$ = aclmakepriv("rwaR",0); } | ALL {
! $$ = aclmakepriv("rwaR",0); } | operation_commalist { $$ = $1;
--- 646,656 ---- privileges: ALL PRIVILEGES {
! $$ = aclmakepriv("rwaRv",0); } | ALL {
! $$ = aclmakepriv("rwaRv",0); } | operation_commalist { $$ = $1;
***************
*** 681,686 ****
--- 681,689 ---- } | RULE { $$ = ACL_MODE_RU_CHR;
+ }
+ | VACUUM {
+ $$ = ACL_MODE_VA_CHR; } ;
*** backend/tcop/aclchk.c.orig Thu May 22 18:18:43 1997
--- backend/tcop/aclchk.c Thu May 22 18:25:07 1997
***************
*** 32,37 ****
--- 32,38 ---- #include "catalog/pg_aggregate.h" #include "catalog/pg_proc.h" #include "catalog/pg_user.h"
+ #include "catalog/pg_database.h" #include "utils/syscache.h" #include "parser/catalog_utils.h" #include "fmgr.h"
***************
*** 62,68 **** "No error.", "Permission denied.", "Table does not exist.",
! "Must be table owner." }; #ifdef ACLDEBUG_TRACE
--- 63,70 ---- "No error.", "Permission denied.", "Table does not exist.",
! "Must be table owner.",
! "Must be database dba." }; #ifdef ACLDEBUG_TRACE
***************
*** 514,525 ****
--- 516,537 ---- NAMEDATALEN, value); owner_id = ((TypeTupleForm) GETSTRUCT(htp))->typowner; break;
+ case DATABASEN:
+ if (!HeapTupleIsValid(htp))
+ elog(WARN, "pg_ownercheck: database \"%-.*s\" not found",
+ NAMEDATALEN, value);
+ owner_id = ((Form_pg_database) GETSTRUCT(htp))->datdba;
+ break; default: elog(WARN, "pg_ownercheck: invalid cache id: %d", cacheid); break; }
+ #ifdef ACLDEBUG_TRACE
+ elog(DEBUG, "pg_ownercheck: user = %d owner = %d", user_id, owner_id);
+ #endif
+ return(user_id == owner_id); }
*** backend/tcop/utility.c.orig Thu May 22 18:25:23 1997
--- backend/tcop/utility.c Thu May 22 18:33:04 1997
***************
*** 614,625 ****
--- 614,644 ---- break; case T_VacuumStmt:
+ {
+ int aclcheck_result;
+ char* dbasename;
+ VacuumStmt *stmt = (VacuumStmt *)parsetree;
+
+ #ifndef NO_SECURITY
+ relname = stmt->vacrel;
+ if (relname != NULL) {
+ aclcheck_result = pg_aclcheck(relname, userName, ACL_VA);
+ if(aclcheck_result != ACLCHECK_OK)
+ elog(WARN, "%s: %s", relname, aclcheck_error_strings[aclcheck_result]);
+ } else { /* Need to check we own the DB for a full vacuum */
+ dbasename = GetDatabaseName();
+ if (!pg_ownercheck(userName, dbasename, DATABASEN))
+ elog(WARN, "%s: %s", dbasename, aclcheck_error_strings[ACLCHECK_NOT_DBA]);
+ }
+ #endif
+ commandTag = "VACUUM"; CHECK_IF_ABORTED(); vacuum( ((VacuumStmt *) parsetree)->vacrel,
((VacuumStmt*) parsetree)->verbose, ((VacuumStmt *) parsetree)->analyze, ((VacuumStmt *)
parsetree)->va_spec);
+ } break; case T_ExplainStmt:
*** backend/utils/adt/acl.c.orig Sat May 10 21:25:18 1997
--- backend/utils/adt/acl.c Sun May 11 22:27:58 1997
***************
*** 66,72 **** /* * aclparse * Consumes and parses an ACL specification of the form:
! * [group|user] [A-Za-z0-9]*[+-=][rwaR]* * from string 's', ignoring any leading white space or white
space * between the optional id type keyword (group|user) and the actual * ACL specification.
--- 66,72 ---- /* * aclparse * Consumes and parses an ACL specification of the form:
! * [group|user] [A-Za-z0-9]*[+-=][rwaRv]* * from string 's', ignoring any leading white space or white
space * between the optional id type keyword (group|user) and the actual * ACL specification.
***************
*** 123,128 ****
--- 123,129 ---- case ACL_MODE_RD_CHR: aip->ai_mode |= ACL_RD; break; case ACL_MODE_WR_CHR: aip->ai_mode |=
ACL_WR;break; case ACL_MODE_RU_CHR: aip->ai_mode |= ACL_RU; break;
+ case ACL_MODE_VA_CHR: aip->ai_mode |= ACL_VA; break; default: elog(WARN, "aclparse: mode flags must use
\"%s\"", ACL_MODE_STR); }
***************
*** 517,524 **** int i; int l;
! Assert(strlen(old_privlist)<5);
! priv = malloc(5); /* at most "rwaR" */; if (old_privlist == NULL || old_privlist[0] == '\0') { priv[0] =
new_priv;
--- 518,525 ---- int i; int l;
! Assert(strlen(old_privlist)<(N_ACL_MODES+1));
! priv = malloc(N_ACL_MODES+1); /* at most N_ACL_MODES */; if (old_privlist == NULL || old_privlist[0] ==
'\0'){ priv[0] = new_priv;
***************
*** 530,536 **** l = strlen(old_privlist);
! if (l == 4) { /* can't add any more privileges */ return priv; }
--- 531,537 ---- l = strlen(old_privlist);
! if (l == N_ACL_MODES) { /* can't add any more privileges */ return priv; }
*** backend/utils/cache/syscache.c.orig Sun May 11 12:59:32 1997
--- backend/utils/cache/syscache.c Sun May 11 20:23:39 1997
***************
*** 45,50 ****
--- 45,51 ---- #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" #include "catalog/pg_class.h"
+ #include "catalog/pg_database.h" #include "catalog/pg_type.h" #include "catalog/pg_rewrite.h" #include
"catalog/pg_aggregate.h"
***************
*** 315,320 ****
--- 316,330 ---- 0, 0 }, sizeof(FormData_pg_opclass),
+ NULL,
+ (ScanFunc) NULL },
+ { DatabaseRelationName, /* DATABASEN */
+ 1,
+ { Anum_pg_database_datname,
+ 0,
+ 0,
+ 0 },
+ sizeof(FormData_pg_database), NULL, (ScanFunc) NULL } };
*** include/utils/acl.h.orig Thu May 22 18:33:30 1997
--- include/utils/acl.h Thu May 22 18:43:16 1997
***************
*** 52,58 **** #define ACL_RD (1<<1) /* read */ #define ACL_WR (1<<2) /* write
(append/delete/replace)*/ #define ACL_RU (1<<3) /* place rules */
! #define N_ACL_MODES 4 #define ACL_MODECHG_ADD 1 #define ACL_MODECHG_DEL 2
--- 52,59 ---- #define ACL_RD (1<<1) /* read */ #define ACL_WR (1<<2) /* write
(append/delete/replace)*/ #define ACL_RU (1<<3) /* place rules */
! #define ACL_VA (1<<4) /* vacuum */
! #define N_ACL_MODES 5 #define ACL_MODECHG_ADD 1 #define ACL_MODECHG_DEL 2
***************
*** 61,67 **** /* change this line if you want to set the default acl permission */ #define ACL_WORLD_DEFAULT
(ACL_RD)/* #define ACL_WORLD_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) */
! #define ACL_OWNER_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) /* * AclItem
--- 62,68 ---- /* change this line if you want to set the default acl permission */ #define ACL_WORLD_DEFAULT
(ACL_RD)/* #define ACL_WORLD_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU) */
! #define ACL_OWNER_DEFAULT (ACL_RD|ACL_WR|ACL_AP|ACL_RU|ACL_VA) /* * AclItem
***************
*** 105,121 **** #define ACL_MODECHG_ADD_CHR '+' #define ACL_MODECHG_DEL_CHR '-' #define
ACL_MODECHG_EQL_CHR '='
! #define ACL_MODE_STR "arwR" /* list of valid characters */ #define ACL_MODE_AP_CHR 'a' #define
ACL_MODE_RD_CHR 'r' #define ACL_MODE_WR_CHR 'w' #define ACL_MODE_RU_CHR 'R' /* result
codesfor pg_aclcheck */ #define ACLCHECK_OK 0 #define ACLCHECK_NO_PRIV 1 #define
ACLCHECK_NO_CLASS 2 #define ACLCHECK_NOT_OWNER 3 /* warning messages. set these in aclchk.c. */ extern
char*aclcheck_error_strings[];
--- 106,124 ---- #define ACL_MODECHG_ADD_CHR '+' #define ACL_MODECHG_DEL_CHR '-' #define
ACL_MODECHG_EQL_CHR '='
! #define ACL_MODE_STR "arwRv" /* list of valid characters */ #define ACL_MODE_AP_CHR 'a'
#define ACL_MODE_RD_CHR 'r' #define ACL_MODE_WR_CHR 'w' #define ACL_MODE_RU_CHR 'R'
+ #define ACL_MODE_VA_CHR 'v' /* If you add a mode don't forget to change N_ACL_MODES */ /* result codes for
pg_aclcheck*/ #define ACLCHECK_OK 0 #define ACLCHECK_NO_PRIV 1 #define ACLCHECK_NO_CLASS
2#define ACLCHECK_NOT_OWNER 3
+ #define ACLCHECK_NOT_DBA 4 /* warning messages. set these in aclchk.c. */ extern char
*aclcheck_error_strings[];
*** include/utils/syscache.h.orig Sun May 11 13:19:12 1997
--- include/utils/syscache.h Sun May 11 13:20:42 1997
***************
*** 59,64 ****
--- 59,65 ---- #define REWRITENAME 25 #define PROSRC 26 #define CLADEFTYPE 27
+ #define DATABASEN 28 /* ---------------- * struct cachedesc: information needed for a call to
InitSysCache()
*** bin/psql/psqlHelp.h.orig Thu May 22 18:43:41 1997
--- bin/psql/psqlHelp.h Fri May 23 15:32:48 1997
***************
*** 136,142 **** "fetch [forward|backward] [<number>|all] [in <cursorname>];"}, { "grant", "grant access
controlto a user or group",
! "grant <privilege[,privilege,...]> on <rel1>[,...<reln>] to \n[public | group <group> | <username>]\n\t
privilegeis {ALL | SELECT | INSERT | UPDATE | DELETE | RULE}"}, { "insert", "insert tuples", "insert into
<class_name>[(<attr1>...<attrN>)]\n\t[values (<expr1>...<exprN>); |\n\tselect <expr1>,...<exprN> [from <from_clause>]
[where<qual>];"},
--- 136,142 ---- "fetch [forward|backward] [<number>|all] [in <cursorname>];"}, { "grant", "grant access
controlto a user or group",
! "grant <privilege[,privilege,...]> on <rel1>[,...<reln>] to \n[public | group <group> | <username>]\n\t
privilegeis {ALL | SELECT | INSERT | UPDATE | DELETE | RULE | VACUUM}"}, { "insert", "insert tuples",
"insertinto <class_name> [(<attr1>...<attrN>)]\n\t[values (<expr1>...<exprN>); |\n\tselect <expr1>,...<exprN> [from
<from_clause>][where <qual>];"},
***************
*** 157,163 **** "reset {DateStyle | GEQO}"}, { "revoke", "revoke access control from a user or group",
! "revoke <privilege[,privilege,...]> on <rel1>[,...<reln>] from \n[public | group <group> | <username>]\n\t
privilegeis {ALL | SELECT | INSERT | UPDATE | DELETE | RULE}"}, { "rollback", "abort a transaction",
"rollback[transaction|work]"},
--- 157,163 ---- "reset {DateStyle | GEQO}"}, { "revoke", "revoke access control from a user or group",
! "revoke <privilege[,privilege,...]> on <rel1>[,...<reln>] from \n[public | group <group> | <username>]\n\t
privilegeis {ALL | SELECT | INSERT | UPDATE | DELETE | RULE | VACUUM}"}, { "rollback", "abort a transaction",
"rollback [transaction|work]"},
*** man/grant.l.orig Mon May 12 08:18:55 1997
--- man/grant.l Mon May 12 08:20:15 1997
***************
*** 10,16 **** \fBon\fR <rel1>[,...<reln>] \fBto\fR [\fBpublic\fR | group <group> | <username>]
! \fBprivilege\fR is {\fBALL\fR | \fBSELECT\fR | \fBINSERT\fR | \fBUPDATE\fR | \fBDELETE\fR | \fBRULE\fR} .fi .SH
DESCRIPTION.PP
--- 10,16 ---- \fBon\fR <rel1>[,...<reln>] \fBto\fR [\fBpublic\fR | group <group> | <username>]
! \fBprivilege\fR is {\fBALL\fR | \fBSELECT\fR | \fBINSERT\fR | \fBUPDATE\fR | \fBDELETE\fR | \fBRULE\fR |
\fBVACUUM\R}.fi .SH DESCRIPTION .PP
*** man/revoke.l.orig Mon May 12 08:19:14 1997
--- man/revoke.l Mon May 12 08:20:42 1997
***************
*** 10,16 **** \fBon\fR <rel1>[,...<reln>] \fBfrom\fR [\fBpublic\fR | group <group> | <username>]
! \fBprivilege\fR is {\fBALL\fR | \fBSELECT\fR | \fBINSERT\fR | \fBUPDATE\fR | \fBDELETE\fR | \fBRULE\fR} .fi .SH
DESCRIPTION.PP
--- 10,16 ---- \fBon\fR <rel1>[,...<reln>] \fBfrom\fR [\fBpublic\fR | group <group> | <username>]
! \fBprivilege\fR is {\fBALL\fR | \fBSELECT\fR | \fBINSERT\fR | \fBUPDATE\fR | \fBDELETE\fR | \fBRULE\fR |
\fBVACUUM\R}.fi .SH DESCRIPTION .PP
*** man/vacuum.l.orig Thu May 22 18:47:54 1997
--- man/vacuum.l Thu May 22 18:56:00 1997
***************
*** 1,7 **** .\" This is -*-nroff-*- .\" XXX standard disclaimer belongs here.... .\" $Header:
/usr/local/cvsroot/postgres95/src/man/vacuum.l,v1.4 1997/05/13 04:41:54 momjian Exp $
! .TH VACUUM SQL 11/05/95 PostgreSQL PostgreSQL .SH NAME vacuum \(em vacuum a database .SH SYNOPSIS
--- 1,7 ---- .\" This is -*-nroff-*- .\" XXX standard disclaimer belongs here.... .\" $Header:
/usr/local/cvsroot/postgres95/src/man/vacuum.l,v1.4 1997/05/13 04:41:54 momjian Exp $
! .TH VACUUM SQL 22/05/97 PostgreSQL PostgreSQL .SH NAME vacuum \(em vacuum a database .SH SYNOPSIS
***************
*** 40,44 **** .PP The purge(l) command can be used to control the archive retention characteristics of a given table.
.SH"SEE ALSO"
! purge(l).
--- 40,53 ---- .PP The purge(l) command can be used to control the archive retention characteristics of a given table.
+ .PP
+ Only the database owner (DBA) is allowed to vacuum the whole database.
+ Table vacuum rights may be granted to or revoked from individual users
+ with the
+ .BR grant(l)
+ and
+ .BR revoke(l)
+ commands.
+ .SH "SEE ALSO"
! purge(l) grant(l) revoke(l).
