Re: [HACKERS] VACUUM as a denial-of-service attack - Mailing list pgsql-hackers

>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).