Re: Admin functions contrib - Mailing list pgsql-patches

I talked to Tom about this today.  First, I want to apologize for
running you around in circles in this.  I don't think we are giving it
the attention it needs because of our schedule.  I also think the
functionality is drifting into the "new features" territory and this is
also part of the delay you are seeing.

I think you did a great thing by breaking the patch into two parts:  one
for logging, and the other for log reading and other stuff.  The logging
part is already in the patch queue.  As for the function below, I first
think the security issue brough up about them wasn't a valid concern
because as I stated someone could just load the plperl server-side
language and do anything to the OS.

In fact this might be the best solution for you.  Instead of trying to
code read/write/rename/unlink and other functions into the backend as
hardcoded, why not just have pgadmin load plperlu and as the super-user
you have access to that functionality, and much more, especially with
the new plperl in 7.5.  In fact, your goal of modifying the
postgresql.conf file is much more natural in perl than in the API you
supplied, and probably more reliable.

So, I suggest we get the logging code into the backend, and you can code
anything you want pgadmin to do in plperlu, and Win32 supports plperlu
too.  The big advantage is that you can improve the plperlu functions
with every release of pgadmin.

---------------------------------------------------------------------------

Andreas Pflug wrote:
> These files add administrative functions to pgsql 7.5. All are used by
> pgAdmin3 or will be used in the near future if available. This is meant
> as contrib module, whilst IMHO all these functions should be integrated
> into the backend.
>
> Included are functions to
>
> log file access
> These where previously posted together with the logger subprocess patch
> and might get committed to the backend code.
>
> misc.
> send SIGHUP to postmaster
>
> generic file access functions
> These are more restrictive than the previously posted: Write access is
> necessary for files to rename and unlink, and paths are restricted to
> PGDATA and the logdir.
>
> size functions
> These are 7.5 replacements for dbsize.
>
> Regards,
> Andreas

> subdir = contrib/admin
> top_builddir = ../..
> include $(top_builddir)/src/Makefile.global
>
> MODULE_big = admin
> DATA_built = admin.sql
> DOCS = README.admin
> OBJS = size.o genfile.o misc.o
> include $(top_srcdir)/contrib/contrib-global.mk

> /* **********************************
>  * Administrative functions
>  * ********************************* */
>
> /* database object size functions (admin.c) */
>
> CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
>     AS 'MODULE_PATHNAME', 'pg_tablespace_size'
>     LANGUAGE C STABLE STRICT;
>
> CREATE FUNCTION pg_database_size(oid) RETURNS bigint
>     AS 'MODULE_PATHNAME', 'pg_database_size'
>     LANGUAGE C STABLE STRICT;
>
> CREATE FUNCTION pg_relation_size(oid) RETURNS bigint
>     AS 'MODULE_PATHNAME', 'pg_relation_size'
>     LANGUAGE C STABLE STRICT;
>
> CREATE FUNCTION pg_size_pretty(bigint) RETURNS text
>     AS 'MODULE_PATHNAME', 'pg_size_pretty'
>     LANGUAGE C STABLE STRICT;
>
>
> /* generic file access functions (genfile.c) */
>
> CREATE FUNCTION pg_file_stat(text) RETURNS record
>    AS 'MODULE_PATHNAME', 'pg_file_stat'
>     LANGUAGE C VOLATILE STRICT;
>
> CREATE FUNCTION pg_file_length(text) RETURNS bigint
>    AS 'SELECT len FROM pg_file_stat($1) AS s(len int8, c timestamp, a timestamp, m timestamp, i bool)'
>     LANGUAGE SQL VOLATILE STRICT;
>
> CREATE FUNCTION pg_file_read(text, bigint, bigint) RETURNS text
>    AS 'MODULE_PATHNAME', 'pg_file_read'
>     LANGUAGE C VOLATILE STRICT;
>
> CREATE FUNCTION pg_file_write(text, text, bool) RETURNS bigint
>    AS 'MODULE_PATHNAME', 'pg_file_write'
>     LANGUAGE C VOLATILE STRICT;
>
> CREATE FUNCTION pg_file_rename(text, text, text) RETURNS bool
>    AS 'MODULE_PATHNAME', 'pg_file_rename'
>     LANGUAGE C VOLATILE STRICT;
>
> CREATE FUNCTION pg_file_unlink(text) RETURNS bool
>    AS 'MODULE_PATHNAME', 'pg_file_unlink'
>     LANGUAGE C VOLATILE STRICT;
>
> CREATE FUNCTION pg_file_rename(text, text) RETURNS bool
>    AS 'SELECT pg_file_rename($1, $2, NULL); '
>     LANGUAGE SQL VOLATILE STRICT;
>
> CREATE FUNCTION pg_dir_ls(text, bool) RETURNS setof text
>    AS 'MODULE_PATHNAME', 'pg_dir_ls'
>     LANGUAGE C VOLATILE STRICT;
>
>
> /* Miscellaneous functions (misc.c) */
>
> CREATE FUNCTION pg_reload_conf() RETURNS int4
>    AS 'MODULE_PATHNAME', 'pg_reload_conf'
>     LANGUAGE C STABLE STRICT;
>
> CREATE FUNCTION pg_logfile_rotate() RETURNS bool
>    AS 'MODULE_PATHNAME', 'pg_logfile_rotate'
>     LANGUAGE C STABLE STRICT;
>
> CREATE FUNCTION pg_logdir_ls() RETURNS setof record
>    AS 'MODULE_PATHNAME', 'pg_logdir_ls'
>     LANGUAGE C VOLATILE STRICT;
>
> CREATE VIEW pg_logdir_ls AS
>     SELECT *
>     FROM pg_logdir_ls() AS A
>     (filetime timestamp, pid int4, filename text);




> Misc functions for administrative purposes
>
> size.c:
> -----------------------------
> int8 pg_tablespace_size(oid)
> int8 pg_database_size(oid)
> int8 pg_relation_size(oid)
> text pg_size_pretty(int8)
>
>
> genfile.c (superuser only):
> -----------------------------
> record pg_file_stat(fname text)
> int8 pg_file_length(fname text)
> text pg_file_read(fname text, offs int8, len int8)
> int8 pg_file_write(fname text, data text, append bool)
> bool pg_file_rename(oldname text, newname text)
> bool pg_file_rename(oldname text, newname text, archivname text)
> bool pg_file_unlink(fname text)
> setof text pg_dir_ls(dirname text, bool fullpath)
>
>
> misc.c (superuser only):
> -----------------------------
> int4 pg_reload_conf()
> bool pg_logfile_rotate()
> setof record pg_logdir_ls()
>
> VIEW pg_logdir_ls(filetime timestamp, pid int4, filename text)

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

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