PostgreSqlPsqlPlugin

The plugin allows a TWiki user to execute a SQL command on a PostgreSQL database by typing the command in a textarea. The command is executed through a forked process communication with the psql system call. The standard output and standard error IO are displayed on top of the input textarea.

The plugin makes no session tracking - each command submission executes a new psql command. Therefor not all psql commands can be used, for e.g. the connect command.

Syntax Rules

The plugin synthax is:

%POSTGRESQLPSQL{host="host" [ port="port" ] database="database" user="user"}%

The host is the postgresql server, the port is the database server port (default 5432), the database is the name of the database to connect to, and the user is the login name for the database connection.

The Plugin checks if the logged-in TWiki user is allowed to make a database connection. The user must be listed in the TWiki configuration hash $TWiki::cfg{Plugins}{PostgreSqlPsqlPlugin}{Acl} of the local TWiki configuration file lib/LocalSite.cfg:

# ACL's for the PostgreSqlPsql plugin
$TWiki::cfg{Plugins}{PostgreSqlPsqlPlugin}{Acl} = "FirstnameLastname, ...";

If this is the case, the plugin makes the psql database connection as user user. Note that just using "FirstnameLastname" will not work since the "..." is significant. The password is taken from the configuration file .pgpass in the home directory /%POSTGRESQLPSQL_HOME%/FirstnameLastname/ which must contain a line as:

*:*:*:*:password

This file must be readable for the Apache UID.

Security Considerations

The plugin executes a system call, but forks the cgi process and executes the Perl exec command in the child process, so to by-pass the Bash interpreter, while reading the taint command arguments.

We untaint any regular expression! It is up to you to check if this is enough secure. If not, you should untaint only special regular expressions in the cgi module code.

Be aware that only those TWiki users can execute the plugin, which are allowed to run the psql command on your database! You can controle the topic access that initialises the plugin with the TWiki ACL's:

    *  Set ALLOWTOPICVIEW = FirstnameLastname 

The plugin should be used with the Secure Socket Layer SSL.

Screenshots

Get the psql help on a sql command:

Make a sql select statement:

Plugin Settings

• One line description, is shown in the TextFormattingRules topic:
  • Set SHORTDESCRIPTION = PSQL command CGI plugin for postgreSQL database

• Debug plugin: (See output in data/debug.txt)
  • Set DEBUG = 0

• Set default postgresql port number:
  • Set POSTGRESQLPSQL_PORT= 5432

• Set the home directory:
  • Set POSTGRESQLPSQL_HOME= /home

• Set the width of the psql input textarea:
  • Set POSTGRESQLPSQL_WIDTH = 100

• Set the height of the psql imput textarea:
  • Set POSTGRESQLPSQL_HEIGHT= 10

Plugin Installation Instructions

• Download the ZIP file from the Plugin web (see below)
• Unzip PostgreSqlPsqlPlugin.zip in your twiki installation directory. Content:

  File:	Description:
  data/TWiki/PostgreSqlPsqlPlugin.txt	Plugin topic
  data/TWiki/PostgreSqlPsqlPlugin.txt,v	Plugin topic repository
  lib/TWiki/Plugins/PostgreSqlPsqlPlugin.pm	Plugin Perl module
  lib/TWiki/Plugins/PostgreSqlPsqlPluginPlugin/PostgreSqlPsql.pm	Plugin main Perl CGI module
  lib/TWiki/Plugins/PostgreSqlPsqlPluginPlugin/psql.sh	Bash script to call the psql command

• The psql.sh Bash script must be executable for the Apache UID.
• Make sure you have the psql package installed on the server.
• Define the ACL's hash $TWiki::cfg{Plugins}{PostgreSqlPsqlPlugin}{Acl} in the TWiki locale configuration file lib/LocalSite.cfg.
• Define in the user home directory /%POSTGRESQLPSQL_HOME% the .pgpass file (as descripted above).
• Configure the Plugin:
  • TWiki 4.0 and up: Run the configure script to enable the Plugin
  • Change the Plugin settings as needed

Plugin Info

Plugin Author:	TWiki:Main.RalfSchaer
Copyright:	© 2006, TWiki:Main.RalfSchaer
License:	GPL ( GNU General Public License)
Plugin Version:	1 Nov 2006 (V1.000)
Change History:
1 Nov 2006:	Initial version
TWiki Dependency:	$TWiki::Plugins::VERSION 1.1
CPAN Dependencies:	none
Other Dependencies:	psql package (installed on server), fork
Perl Version:	5
Benchmarks:	GoodStyle nn%, FormattedSearch nn%, PostgreSqlPsqlPlugin nn%
Plugin Home:	http://TWiki.org/cgi-bin/view/Plugins/PostgreSqlPsqlPlugin
Feedback:	http://TWiki.org/cgi-bin/view/Plugins/PostgreSqlPsqlPluginDev
Appraisal:	http://TWiki.org/cgi-bin/view/Plugins/PostgreSqlPsqlPluginAppraisal

Related Topics: TWikiPlugins, DeveloperDocumentationCategory, AdminDocumentationCategory, TWikiPreferences

-- TWiki:Main.RalfSchaer - 1 Nov 2006