Re: [HACKERS] For review: Server instrumentation patch - Mailing list pgsql-patches

Dave Page wrote:
> > The list isn't complete. pgadmin uses these three functions
> > for logfile
> > tracking:
> >
> > - pg_logdir_ls to list logfiles
> > - pg_file_length to check for changes of the current logfile
> > - pg_file_read to retrieve a logfile
>
> Yes you're right, I didn't check thoroughly (in my defence, the coffee
> machine broke this morning). Anyhoo, pg_file_stat is used by
> pg_file_length, so that would be required as well.
>
> None of those allow any modification of the filesystem, so do not suffer
> the potential security issues that Tom was concerned about, so hopefully
> there is no problem with them going in?

OK, I have modified the patch to include these functions:

    pg_reload_conf()
    pg_file_stat()
    pg_file_read()
    pg_file_length()
    pg_dir_ls()
    pg_logfile_rotate()
    pg_logdir_ls()

These can only be run by the super-user, and can only access files
inside PGDATA, or in the logdir directory if that is in a different
place from PGDATA.

The only part I didn't like about the patch is the stat display:

    test=> select pg_file_stat('postgresql.conf');
                                    pg_file_stat
    -----------------------------------------------------------------------------

     (12287,"2005-08-11 00:06:30","2005-08-11 00:06:43","2005-08-11 00:06:30",f)
    (1 row)

Shouldn't this return multiple labeled columns rather than an array?

The patch is attached and genfile.c goes in utils/adt.

--
  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/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.276
diff -c -c -r1.276 func.sgml
*** doc/src/sgml/func.sgml    2 Aug 2005 16:11:56 -0000    1.276
--- doc/src/sgml/func.sgml    11 Aug 2005 04:21:37 -0000
***************
*** 9061,9066 ****
--- 9061,9069 ----
     <indexterm zone="functions-admin">
      <primary>pg_cancel_backend</primary>
     </indexterm>
+    <indexterm zone="functions-admin">
+     <primary>pg_reload_conf</primary>
+    </indexterm>

     <indexterm zone="functions-admin">
      <primary>signal</primary>
***************
*** 9068,9074 ****
     </indexterm>

     <para>
!     The function shown in <xref
      linkend="functions-admin-signal-table"> sends control signals to
      other server processes.  Use of this function is restricted
      to superusers.
--- 9071,9077 ----
     </indexterm>

     <para>
!     The functions shown in <xref
      linkend="functions-admin-signal-table"> sends control signals to
      other server processes.  Use of this function is restricted
      to superusers.
***************
*** 9090,9110 ****
         <entry><type>int</type></entry>
         <entry>Cancel a backend's current query</entry>
        </row>
       </tbody>
      </tgroup>
     </table>

     <para>
!     This function returns 1 if successful, 0 if not successful.
      The process ID (<literal>pid</literal>) of an active backend can be found
      from the <structfield>procpid</structfield> column in the
      <structname>pg_stat_activity</structname> view, or by listing the <command>postgres</command>
      processes on the server with <application>ps</>.
     </para>
!
!    <indexterm zone="functions-admin">
!     <primary>pg_start_backup</primary>
!    </indexterm>

     <indexterm zone="functions-admin">
      <primary>pg_stop_backup</primary>
--- 9093,9121 ----
         <entry><type>int</type></entry>
         <entry>Cancel a backend's current query</entry>
        </row>
+       <row>
+        <entry>
+         <literal><function>pg_reload_conf</function>()</literal>
+         </entry>
+        <entry><type>int</type></entry>
+        <entry>Triggers the server processes to reload configuration files</entry>
+       </row>
       </tbody>
      </tgroup>
     </table>

     <para>
!     These functions return 1 if successful, 0 if not successful.
      The process ID (<literal>pid</literal>) of an active backend can be found
      from the <structfield>procpid</structfield> column in the
      <structname>pg_stat_activity</structname> view, or by listing the <command>postgres</command>
      processes on the server with <application>ps</>.
     </para>
!    <para>
!     <function>pg_reload_conf</> sends a SIGHUP event to the
!     postmaster, and thus triggers a reload of the configuration files
!     in all backend processes.
!    </para>

     <indexterm zone="functions-admin">
      <primary>pg_stop_backup</primary>
***************
*** 9309,9314 ****
--- 9320,9457 ----
      appropriate.
     </para>

+    <para>
+     The functions shown in <xref
+     linkend="functions-admin-genfile"> provide native file access to
+     files on the machine hosting the server. They are restricted to
+     the cluster directory or the logfile directory.
+     Use of these functions is restricted to superusers.
+    </para>
+
+    <table id="functions-admin-genfile">
+     <title>Generic File Access Functions</title>
+     <tgroup cols="3">
+      <thead>
+       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+       </row>
+      </thead>
+
+      <tbody>
+       <row>
+        <entry>
+         <literal><function>pg_file_stat</function>(<parameter>filename_text</parameter>)</literal>
+         </entry>
+        <entry><type>record</type></entry>
+        <entry>Retrieves file stats</entry>
+       </row>
+       <row>
+        <entry>
+         <literal><function>pg_file_length</function>(<parameter>filename_text</parameter>)</literal>
+         </entry>
+        <entry><type>int8</type></entry>
+        <entry>Returns the file length</entry>
+       </row>
+       <row>
+        <entry>
+         <literal><function>pg_file_read</function>(<parameter>filename_text</parameter>,
+         <parameter>offset_int8</parameter>,<parameter>length_int8</parameter>)</literal>
+         </entry>
+        <entry><type>text</type></entry>
+        <entry>Returns the contents of a text file</entry>
+       </row>
+       <row>
+        <entry>
+
<literal><function>pg_dir_ls</function>(<parameter>dirname_text</parameter>,<parameter>fullpath_bool</parameter>)</literal>
+         </entry>
+        <entry><type>setof text</type></entry>
+        <entry>Returns the file length</entry>
+       </row>
+      </tbody>
+     </tgroup>
+    </table>
+
+    <indexterm zone="functions-admin">
+     <primary>pg_file_stat</primary>
+    </indexterm>
+
+    <para>
+     <function>pg_file_stat()</> returns a record that contains the
+     length, creation timestamp, last accessed timestamp, last modified
+     timestamp and and a flag indicating a directory.
+    </para>
+
+    <indexterm zone="functions-admin">
+     <primary>pg_file_length</primary>
+    </indexterm>
+    <para>
+     <function>pg_file_length()</> returns the length of the given file.
+    </para>
+
+    <indexterm zone="functions-admin">
+     <primary>pg_file_read</primary>
+    </indexterm>
+    <para>
+     <function>pg_file_read()</> returns a part of a textfile, starting
+     at the offset giving length bytes.
+
+    <para>
+     <function>pg_dir_ls</> lists all filenames in the named directory.
+    </para>
+
+    <para>
+     The functions shown in <xref
+     linkend="functions-admin-logfile"> allow access to the server
+     logfile, if the stderr log output is redirected.
+     Use of these functions is restricted to superusers.
+    </para>
+
+    <table id="functions-admin-logfile">
+     <title>Backend Logfile Functions</title>
+     <tgroup cols="3">
+      <thead>
+       <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+       </row>
+      </thead>
+
+      <tbody>
+       <row>
+        <entry>
+         <literal><function>pg_logfile_rotate</function>()</literal>
+         </entry>
+        <entry><type>int</type></entry>
+        <entry>Trigger logfile rotation</entry>
+       </row>
+       <row>
+        <entry>
+         <literal><function>pg_logdir_ls</function>()</literal>
+         </entry>
+        <entry><type>setof record</type></entry>
+        <entry>lists all logfiles in the pg_log subdirectory</entry>
+       </row>
+      </tbody>
+     </tgroup>
+    </table>
+
+    <indexterm zone="functions-admin">
+     <primary>pg_logfile_rotate</primary>
+    </indexterm>
+    <para>
+     <function>pg_logfile_rotate</> issues a logfile rotation trigger,
+     which forces the server to close the current logfile and open a
+     fresh one.
+    </para>
+
+    <indexterm zone="functions-admin">
+     <primary>pg_logdir_ls</primary>
+    </indexterm>
+    <para>
+     <function>pg_logdir_ls</> lists all files in the pg_log
+     subdirectory. For each log file, a record consisting of a
+     timestamp of its creation time and a complete path to the file is
+     returned. For convenience, the view pg_logdir_ls wraps the
+     function.
+    </para>
+
    </sect1>
  </chapter>

Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.18
diff -c -c -r1.18 system_views.sql
*** src/backend/catalog/system_views.sql    31 Jul 2005 17:19:17 -0000    1.18
--- src/backend/catalog/system_views.sql    11 Aug 2005 04:21:37 -0000
***************
*** 331,333 ****
--- 331,338 ----
                      pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
              pg_stat_get_db_blocks_hit(D.oid) AS blks_hit
      FROM pg_database D;
+
+ CREATE VIEW pg_logdir_ls AS
+     SELECT *
+     FROM pg_logdir_ls() AS A
+     (filetime timestamp, filename text);
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.461
diff -c -c -r1.461 postmaster.c
*** src/backend/postmaster/postmaster.c    29 Jul 2005 19:30:04 -0000    1.461
--- src/backend/postmaster/postmaster.c    11 Aug 2005 04:21:44 -0000
***************
*** 3393,3398 ****
--- 3393,3403 ----
          }
      }

+     if (CheckPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE) && SysLoggerPID != 0)
+     {
+         kill(SysLoggerPID, SIGUSR1);
+     }
+
      PG_SETMASK(&UnBlockSig);

      errno = save_errno;
Index: src/backend/postmaster/syslogger.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/syslogger.c,v
retrieving revision 1.18
diff -c -c -r1.18 syslogger.c
*** src/backend/postmaster/syslogger.c    21 Jul 2005 18:06:12 -0000    1.18
--- src/backend/postmaster/syslogger.c    11 Aug 2005 04:21:45 -0000
***************
*** 101,106 ****
--- 101,107 ----
   * Flags set by interrupt handlers for later service in the main loop.
   */
  static volatile sig_atomic_t got_SIGHUP = false;
+ static volatile sig_atomic_t rotation_requested = false;


  /* Local subroutines */
***************
*** 117,122 ****
--- 118,124 ----
  static char *logfile_getname(pg_time_t timestamp);
  static void set_next_rotation_time(void);
  static void sigHupHandler(SIGNAL_ARGS);
+ static void sigUsr1Handler(SIGNAL_ARGS);


  /*
***************
*** 200,206 ****
      pqsignal(SIGQUIT, SIG_IGN);
      pqsignal(SIGALRM, SIG_IGN);
      pqsignal(SIGPIPE, SIG_IGN);
!     pqsignal(SIGUSR1, SIG_IGN);
      pqsignal(SIGUSR2, SIG_IGN);

      /*
--- 202,208 ----
      pqsignal(SIGQUIT, SIG_IGN);
      pqsignal(SIGALRM, SIG_IGN);
      pqsignal(SIGPIPE, SIG_IGN);
!     pqsignal(SIGUSR1, sigUsr1Handler);  /* request log rotation */
      pqsignal(SIGUSR2, SIG_IGN);

      /*
***************
*** 235,241 ****
      /* main worker loop */
      for (;;)
      {
-         bool        rotation_requested = false;
          bool        time_based_rotation = false;

  #ifndef WIN32
--- 237,242 ----
***************
*** 726,731 ****
--- 727,734 ----
      char       *filename;
      FILE       *fh;

+     rotation_requested = false;
+
      /*
       * When doing a time-based rotation, invent the new logfile name based
       * on the planned rotation time, not current time, to avoid "slippage"
***************
*** 876,878 ****
--- 879,888 ----
  {
      got_SIGHUP = true;
  }
+
+ /* SIGUSR1: set flag to rotate logfile */
+ static void
+ sigUsr1Handler(SIGNAL_ARGS)
+ {
+     rotation_requested = true;
+ }
Index: src/backend/utils/adt/Makefile
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/Makefile,v
retrieving revision 1.58
diff -c -c -r1.58 Makefile
*** src/backend/utils/adt/Makefile    29 Jul 2005 14:46:57 -0000    1.58
--- src/backend/utils/adt/Makefile    11 Aug 2005 04:21:45 -0000
***************
*** 24,30 ****
      tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
      network.o mac.o inet_net_ntop.o inet_net_pton.o \
      ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
!     ascii.o quote.o pgstatfuncs.o encode.o dbsize.o

  like.o: like.c like_match.c

--- 24,30 ----
      tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
      network.o mac.o inet_net_ntop.o inet_net_pton.o \
      ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
!     ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o

  like.o: like.c like_match.c

Index: src/backend/utils/adt/misc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/misc.c,v
retrieving revision 1.45
diff -c -c -r1.45 misc.c
*** src/backend/utils/adt/misc.c    4 Jul 2005 04:51:50 -0000    1.45
--- src/backend/utils/adt/misc.c    11 Aug 2005 04:21:45 -0000
***************
*** 17,34 ****
--- 17,46 ----
  #include <sys/file.h>
  #include <signal.h>
  #include <dirent.h>
+ #include <time.h>
+ #include <sys/time.h>

  #include "commands/dbcommands.h"
  #include "miscadmin.h"
  #include "storage/procarray.h"
+ #include "storage/pmsignal.h"
  #include "storage/fd.h"
  #include "utils/builtins.h"
+ #include "utils/elog.h"
+ #include "utils/datetime.h"
  #include "funcapi.h"
  #include "catalog/pg_type.h"
  #include "catalog/pg_tablespace.h"
+ #include "postmaster/syslogger.h"

  #define atooid(x)  ((Oid) strtoul((x), NULL, 10))

+ typedef struct
+ {
+     char *location;
+     DIR *dirdesc;
+ } directory_fctx;
+

  /*
   * Check if data is Null
***************
*** 107,112 ****
--- 119,273 ----
      PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0), SIGINT));
  }

+
+ Datum
+ pg_reload_conf(PG_FUNCTION_ARGS)
+ {
+     if (!superuser())
+         ereport(ERROR,
+                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                  (errmsg("only superuser can signal the postmaster"))));
+
+     if (kill(PostmasterPid, SIGHUP))
+     {
+         ereport(WARNING,
+                 (errmsg("failed to send signal to postmaster: %m")));
+
+         PG_RETURN_INT32(0);
+     }
+
+     PG_RETURN_INT32(1);
+ }
+
+
+ /*
+  * Rotate log file
+  */
+ Datum
+ pg_logfile_rotate(PG_FUNCTION_ARGS)
+ {
+     if (!superuser())
+         ereport(ERROR,
+                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                  (errmsg("only superuser can rotate log files"))));
+
+     if (!Redirect_stderr)
+     {
+         ereport(NOTICE,
+                 (errcode(ERRCODE_WARNING),
+                  errmsg("no logfile configured; rotation not supported")));
+         PG_RETURN_INT32(0);
+     }
+
+     SendPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE);
+
+     PG_RETURN_INT32(0);
+ }
+
+ /*
+  * scan log directory for log files
+  */
+ Datum pg_logdir_ls(PG_FUNCTION_ARGS)
+ {
+     FuncCallContext *funcctx;
+     struct dirent *de;
+     directory_fctx *fctx;
+
+     if (!superuser())
+         ereport(ERROR,
+                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                  (errmsg("only superuser can list the log directory"))));
+
+     if (memcmp(Log_filename, "postgresql-%Y-%m-%d_%H%M%S.log", 30) != 0)
+         ereport(ERROR,
+                 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                  (errmsg("the log_filename parameter must equal 'postgresql-%%Y-%%m-%%d_%%H%%M%%S.log'"))));
+
+     if (SRF_IS_FIRSTCALL())
+     {
+         MemoryContext oldcontext;
+         TupleDesc tupdesc;
+
+         funcctx = SRF_FIRSTCALL_INIT();
+         oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+         fctx = palloc(sizeof(directory_fctx));
+         if (is_absolute_path(Log_directory))
+             fctx->location = Log_directory;
+         else
+         {
+             fctx->location = palloc(strlen(DataDir) + strlen(Log_directory) + 2);
+             sprintf(fctx->location, "%s/%s", DataDir, Log_directory);
+         }
+         tupdesc = CreateTemplateTupleDesc(2, false);
+         TupleDescInitEntry(tupdesc, (AttrNumber) 1, "starttime",
+                            TIMESTAMPOID, -1, 0);
+         TupleDescInitEntry(tupdesc, (AttrNumber) 2, "filename",
+                            TEXTOID, -1, 0);
+
+         funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc);
+
+         fctx->dirdesc = AllocateDir(fctx->location);
+
+         if (!fctx->dirdesc)
+             ereport(ERROR,
+                     (errcode_for_file_access(),
+                      errmsg("%s is not browsable: %m", fctx->location)));
+
+         funcctx->user_fctx = fctx;
+         MemoryContextSwitchTo(oldcontext);
+     }
+
+     funcctx = SRF_PERCALL_SETUP();
+     fctx = (directory_fctx*) funcctx->user_fctx;
+
+     if (!fctx->dirdesc)  /* not a readable directory  */
+         SRF_RETURN_DONE(funcctx);
+
+     while ((de = ReadDir(fctx->dirdesc, fctx->location)) != NULL)
+     {
+         char    *values[2];
+         HeapTuple tuple;
+         char     *field[MAXDATEFIELDS];
+         char    lowstr[MAXDATELEN + 1];
+         int        dtype;
+         int        nf, ftype[MAXDATEFIELDS];
+         fsec_t    fsec;
+         int        tz = 0;
+         struct    pg_tm date;
+
+         /*
+          * Default format:
+          *        postgresql-YYYY-MM-DD_HHMMSS.log
+          */
+         if (strlen(de->d_name) != 32 || memcmp(de->d_name, "postgresql-", 11) ||
+             de->d_name[21] != '_' || strcmp(de->d_name + 28, ".log"))
+               continue;
+
+         de->d_name[17] = '\0';
+         values[0] = de->d_name + 11;       /* timestamp */
+
+         values[1] = palloc(strlen(fctx->location) + strlen(de->d_name) + 2);
+         sprintf(values[1], "%s/%s", fctx->location, de->d_name);
+
+         /* parse and decode expected timestamp */
+         if (ParseDateTime(values[0], lowstr, sizeof(lowstr), field, ftype, MAXDATEFIELDS, &nf))
+             continue;
+
+         if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz))
+             continue;
+
+         /* Seems the format fits the expected format; feed it into the tuple */
+
+         tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
+
+         SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+     }
+
+     FreeDir(fctx->dirdesc);
+     SRF_RETURN_DONE(funcctx);
+ }
+
  #ifdef NOT_USED

  /* Disabled in 8.0 due to reliability concerns; FIXME someday */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.380
diff -c -c -r1.380 pg_proc.h
*** src/include/catalog/pg_proc.h    2 Aug 2005 16:11:57 -0000    1.380
--- src/include/catalog/pg_proc.h    11 Aug 2005 04:21:54 -0000
***************
*** 3049,3054 ****
--- 3049,3073 ----
  DATA(insert OID = 2173 ( pg_stop_backup            PGNSP PGUID 12 f f t f v 0 25 "" _null_ _null_ _null_
pg_stop_backup- _null_ )); 
  DESCR("Finish taking an online backup");

+ DATA(insert OID = 2621 ( pg_reload_conf         PGNSP PGUID 12 f f t f v 0 23 "" _null_ _null_ _null_ pg_reload_conf
-_null_ )); 
+ DESCR("Reload configuration files");
+
+ DATA(insert OID = 2622 ( pg_logfile_rotate        PGNSP PGUID 12 f f t f v 0 23 "" _null_ _null_ _null_
pg_logfile_rotate- _null_ )); 
+ DESCR("rotate log file");
+ DATA(insert OID = 2623 ( pg_logdir_ls            PGNSP PGUID 12 f f t t v 0 2249 "" _null_ _null_ _null_ pg_logdir_ls
-_null_ )); 
+ DESCR("list all available log files");
+
+
+ DATA(insert OID = 2624 ( pg_file_stat        PGNSP PGUID 12 f f t f v 1 2249 "25" _null_ _null_ _null_ pg_file_stat -
_null_)); 
+ DESCR("retrieve file stats");
+ DATA(insert OID = 2625 ( pg_file_length        PGNSP PGUID 14 f f t f v 1 20 "25" _null_ _null_ _null_ "SELECT len
FROMpg_file_stat($1) AS s(len int8, c timestamp, a timestamp, m timestamp, i bool)" - _null_ )); 
+ DESCR("returns length of a file");
+ DATA(insert OID = 2626 ( pg_file_read        PGNSP PGUID 12 f f t f v 3 25 "25 20 20" _null_ _null_ _null_
pg_file_read- _null_ )); 
+ DESCR("reads text from a file");
+ DATA(insert OID = 2627 ( pg_dir_ls            PGNSP PGUID 12 f f t t v 2 25 "25 16" _null_ _null_ _null_ pg_dir_ls -
_null_)); 
+ DESCR("list all file in a directory");
+
+
  /* Aggregates (moved here from pg_aggregate for 7.3) */

  DATA(insert OID = 2100 (  avg                PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_
aggregate_dummy- _null_ )); 
Index: src/include/storage/pmsignal.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/storage/pmsignal.h,v
retrieving revision 1.12
diff -c -c -r1.12 pmsignal.h
*** src/include/storage/pmsignal.h    28 Jun 2005 19:51:25 -0000    1.12
--- src/include/storage/pmsignal.h    11 Aug 2005 04:21:54 -0000
***************
*** 25,30 ****
--- 25,31 ----
      PMSIGNAL_PASSWORD_CHANGE,    /* pg_auth file has changed */
      PMSIGNAL_WAKEN_CHILDREN,    /* send a SIGUSR1 signal to all backends */
      PMSIGNAL_WAKEN_ARCHIVER,    /* send a NOTIFY signal to xlog archiver */
+     PMSIGNAL_ROTATE_LOGFILE,    /* send SIGUSR1 to syslogger to rotate logfile */

      NUM_PMSIGNALS                /* Must be last value of enum! */
  } PMSignalReason;
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.262
diff -c -c -r1.262 builtins.h
*** src/include/utils/builtins.h    29 Jul 2005 14:47:04 -0000    1.262
--- src/include/utils/builtins.h    11 Aug 2005 04:21:54 -0000
***************
*** 374,385 ****
--- 374,393 ----
  extern Datum pg_complete_relation_size_name(PG_FUNCTION_ARGS);
  extern Datum pg_size_pretty(PG_FUNCTION_ARGS);

+ /* genfile.c */
+ extern Datum pg_file_stat(PG_FUNCTION_ARGS);
+ extern Datum pg_file_read(PG_FUNCTION_ARGS);
+ extern Datum pg_dir_ls(PG_FUNCTION_ARGS);
+
  /* misc.c */
  extern Datum nullvalue(PG_FUNCTION_ARGS);
  extern Datum nonnullvalue(PG_FUNCTION_ARGS);
  extern Datum current_database(PG_FUNCTION_ARGS);
  extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+ extern Datum pg_reload_conf(PG_FUNCTION_ARGS);
  extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
+ extern Datum pg_logfile_rotate(PG_FUNCTION_ARGS);
+ extern Datum pg_logdir_ls(PG_FUNCTION_ARGS);

  /* not_in.c */
  extern Datum int4notin(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.106
diff -c -c -r1.106 rules.out
*** src/test/regress/expected/rules.out    31 Jul 2005 17:19:22 -0000    1.106
--- src/test/regress/expected/rules.out    11 Aug 2005 04:21:56 -0000
***************
*** 1280,1285 ****
--- 1280,1286 ----
   pg_group                 | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT
pg_auth_members.memberFROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid
WHERE(NOT pg_authid.rolcanlogin); 
   pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname
AS"tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN
pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace
tON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); 
   pg_locks                 | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid,
l.objid,l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database"
oid,relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint,
"transaction"xid, pid integer, "mode" text, "granted" boolean); 
+  pg_logdir_ls             | SELECT a.filetime, a.filename FROM pg_logdir_ls() a(filetime timestamp without time zone,
filenametext); 
   pg_prepared_xacts        | SELECT p."transaction", p.gid, p."prepared", u.rolname AS "owner", d.datname AS
"database"FROM ((pg_prepared_xact() p("transaction" xid, gid text, "prepared" timestamp with time zone, ownerid oid,
dbidoid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); 
   pg_roles                 | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit,
pg_authid.rolcreaterole,pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit,
'********'::textAS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid; 
   pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)
ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid =
c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); 
***************
*** 1320,1326 ****
   shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit,shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE
(shoe.slcolor= shoelace.sl_color)))); 
   street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ##
r.thepath);
   toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
! (44 rows)

  SELECT tablename, rulename, definition FROM pg_rules
      ORDER BY tablename, rulename;
--- 1321,1327 ----
   shoelace_obsolete        | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit,shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE
(shoe.slcolor= shoelace.sl_color)))); 
   street                   | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ##
r.thepath);
   toyemp                   | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
! (45 rows)

  SELECT tablename, rulename, definition FROM pg_rules
      ORDER BY tablename, rulename;
/*-------------------------------------------------------------------------
 *
 * genfile.c
 *
 *
 * Copyright (c) 2004, PostgreSQL Global Development Group
 *
 * Author: Andreas Pflug <pgadmin@pse-consulting.de>
 *
 * IDENTIFICATION
 *      $PostgreSQL: $
 *
 *-------------------------------------------------------------------------
 */
#include "postgres.h"

#include <sys/file.h>
#include <sys/stat.h>
#include <unistd.h>
#include <dirent.h>

#include "utils/builtins.h"
#include "miscadmin.h"
#include "storage/fd.h"
#include "catalog/pg_type.h"
#include "funcapi.h"

extern  char *Log_directory;

typedef struct
{
    char    *location;
    DIR        *dirdesc;
} directory_fctx;

/*
 * Return an absolute path. Argument may be absolute or
 * relative to the DataDir.
 */
static char *check_and_make_absolute(text *arg)
{
    char *filename;
    int filename_len = VARSIZE(arg) - VARHDRSZ;
    int datadir_len = strlen(DataDir);

    filename = palloc(filename_len + 1);
    memcpy(filename, VARDATA(arg), filename_len);
    filename[filename_len] = '\0';

    canonicalize_path(filename);
    filename_len = strlen(filename);    /* recompute */

    /*
     *    Prevent reference to the parent directory.
     *    "..a.." is a valid file name though.
     */
    if (strcmp(filename, "..") == 0 ||                            /* beginning */
        strncmp(filename, "../", 3) == 0 ||                        /* beginning */
        strcmp(filename, "/..") == 0 ||                            /* beginning */
        strncmp(filename, "../", 3) == 0 ||                        /* beginning */
        strstr(filename, "/../") != NULL ||                        /* middle */
        strncmp(filename + filename_len - 3, "/..", 3) == 0)    /* end */
            ereport(ERROR,
                  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                   (errmsg("Reference to a parent directory (\"..\") not allowed"))));

    if (is_absolute_path(filename))
    {
        /* The log directory might be outside our datadir, but allow it */
        if ((strncmp(filename, Log_directory, strlen(Log_directory)) == 0 &&
             (filename[strlen(Log_directory)] == '/' ||
              filename[strlen(Log_directory)] == '\0')) ||
            (strncmp(filename, DataDir, datadir_len) == 0 &&
             (filename[datadir_len] == '/' ||
              filename[datadir_len] == '\0')))
            return filename;

        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 (errmsg("Absolute path not allowed"))));
        return NULL;
    }
    else
    {
        char *absname = palloc(datadir_len + filename_len + 2);
        sprintf(absname, "%s/%s", DataDir, filename);
        pfree(filename);
        return absname;
    }
}


Datum pg_file_read(PG_FUNCTION_ARGS)
{
    size_t        bytes_to_read = PG_GETARG_INT64(2);
    int64        seek_offset = PG_GETARG_INT64(1);
    char         *buf = 0;
    size_t        nbytes;
    FILE        *file;
    char        *filename;

    if (!superuser())
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 (errmsg("only superuser may access generic file functions"))));

    filename = check_and_make_absolute(PG_GETARG_TEXT_P(0));

    if ((file = AllocateFile(filename, PG_BINARY_R)) == NULL)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not open file %s for reading: %m", filename)));
        PG_RETURN_NULL();
    }

    if (fseeko(file, (off_t)seek_offset,
        (seek_offset >= 0) ? SEEK_SET : SEEK_END) != 0)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not seek in file %s: %m", filename)));
        PG_RETURN_NULL();
    }

    buf = palloc(bytes_to_read + VARHDRSZ);

    nbytes = fread(VARDATA(buf), bytes_to_read, 1, file);
    FreeFile(file);

    if (nbytes < 0)
    {
        ereport(ERROR,
                (errcode_for_file_access(),
                 errmsg("could not read file %s: %m", filename)));
        PG_RETURN_NULL();
    }
    VARATT_SIZEP(buf) = nbytes + VARHDRSZ;

    pfree(filename);
    PG_RETURN_TEXT_P(buf);
}


Datum pg_file_stat(PG_FUNCTION_ARGS)
{
    AttInMetadata *attinmeta;
    char        *filename = check_and_make_absolute(PG_GETARG_TEXT_P(0));
    struct stat fst;
    char        lenbuf[30], cbuf[30], abuf[30], mbuf[30], dirbuf[2];
    char        *values[5] = {lenbuf, cbuf, abuf, mbuf, dirbuf};
    pg_time_t    timestamp;
    HeapTuple    tuple;
    TupleDesc    tupdesc = CreateTemplateTupleDesc(5, false);

    if (!superuser())
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 (errmsg("only superuser may access generic file functions"))));

    TupleDescInitEntry(tupdesc, (AttrNumber) 1, "length", INT8OID, -1, 0);
    TupleDescInitEntry(tupdesc, (AttrNumber) 2, "ctime", TIMESTAMPOID, -1, 0);
    TupleDescInitEntry(tupdesc, (AttrNumber) 3, "atime", TIMESTAMPOID, -1, 0);
    TupleDescInitEntry(tupdesc, (AttrNumber) 4, "mtime", TIMESTAMPOID, -1, 0);
    TupleDescInitEntry(tupdesc, (AttrNumber) 5, "isdir", BOOLOID, -1, 0);
    attinmeta = TupleDescGetAttInMetadata(tupdesc);

    if (stat(filename, &fst) < 0)
    {
        ereport(WARNING,
                (errcode_for_file_access(),
                 errmsg("could not stat file %s: %m", filename)));
        MemSet(values, 0, sizeof(char *) * 5);
        tuple = BuildTupleFromCStrings(attinmeta, values);
    }
    else
    {
        snprintf(lenbuf, 30, INT64_FORMAT, (int64)fst.st_size);

        timestamp = fst.st_ctime;
        pg_strftime(cbuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        timestamp = fst.st_atime;
        pg_strftime(abuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        timestamp = fst.st_mtime;
        pg_strftime(mbuf, 30, "%F %T", pg_localtime(×tamp, global_timezone));

        if (fst.st_mode & S_IFDIR)
            strcpy(dirbuf, "t");
        else
            strcpy(dirbuf, "f");

        tuple = BuildTupleFromCStrings(attinmeta, values);
    }

    pfree(filename);
    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}


Datum pg_dir_ls(PG_FUNCTION_ARGS)
{
    FuncCallContext *funcctx;
    struct dirent *de;
    directory_fctx *fctx;

    if (!superuser())
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 (errmsg("only superuser may access generic file functions"))));

    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext oldcontext;

        funcctx = SRF_FIRSTCALL_INIT();
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

        fctx = palloc(sizeof(directory_fctx));
        fctx->location = check_and_make_absolute(PG_GETARG_TEXT_P(0));

        fctx->dirdesc = AllocateDir(fctx->location);

        if (!fctx->dirdesc)
            ereport(ERROR,
                    (errcode_for_file_access(),
                     errmsg("%s is not browsable: %m", fctx->location)));

        if (PG_ARGISNULL(1) || !PG_GETARG_BOOL(1))
        {
            pfree(fctx->location);
            fctx->location = NULL;
        }
        funcctx->user_fctx = fctx;
        MemoryContextSwitchTo(oldcontext);
    }

    funcctx = SRF_PERCALL_SETUP();
    fctx = (directory_fctx*) funcctx->user_fctx;

    if (!fctx->dirdesc)  /* not a readable directory  */
        SRF_RETURN_DONE(funcctx);

    while ((de = ReadDir(fctx->dirdesc, fctx->location)) != NULL)
    {
        char        *name;
        text        *result;
        int            len;

        if (!strcmp(de->d_name, ".") || !strcmp(de->d_name, ".."))
            continue;

        if (fctx->location)
        {
            char *path = palloc(strlen(fctx->location) + strlen(de->d_name) + 2);
            sprintf(path, "%s/%s", fctx->location, de->d_name);
            name = path;
        }
        else
            name = de->d_name;

        len = strlen(name);
        result = palloc(len + VARHDRSZ);
        VARATT_SIZEP(result) = len + VARHDRSZ;
        memcpy(VARDATA(result), name, len);

        SRF_RETURN_NEXT(funcctx, PointerGetDatum(result));
    }

    FreeDir(fctx->dirdesc);
    SRF_RETURN_DONE(funcctx);
}