From ISPMan

This IS useful and PostgreSQL even has PAM integration and perl bindings(DBD-Pg).

Thoughts on how to implement this will be planned and detailed on this wiki page.

[edit] What will ISPMan need to do

• Create the/a database - should it be only one?
• Set the permissions on the created database(s) - Tightened to the ISPman user and/or domain so we can use PAM.
• Configurable limit on the number of databases a user/domain can create - can we limit this on postgres? If not, database creation should be done by ISPMan only.
• Configurable default limit on database size for every user and/or domain

This page is currently under development by

PedroAlgarvio

This information maybe incomplete or wrong so please use this information at your own risk. Thank you.

Feel free to make suggestions or edit this page.

[edit] PostgreSQL

In order for postgres to use PAM to authenticate users one must create:

auth        required  pam_ldap.so
account     required  pam_ldap.so

And change postgres to use PAM. I'm not trusting anyone and requiring all users wether trough sockets or tcp to authenticate trough ldap.

local   all         all                             pam postgresql
host    all         all         10.1.0.0/24         pam postgresql

[edit] Roles

How would one handle roles?

Create a superuser role like domain.tld that will be used to create new roles and new databases? This probably won't be needed if we add another admin user into ISPMan, like the one we set for cyrus on ispman.conf.

Then a user from that domain when trying to create a new database make's ISPman first check if he can have more databases and if he can, delegate that creation to the correct user.

[edit] ADMIN SQL Queries

[edit] Create new role

CREATE ROLE "username@domain.tld" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
 

[edit] Create new database

[edit] For databases named by the username

CREATE DATABASE "username@domain.tld" OWNER "username@domain.tld";
 

[edit] For databases named diferently then the username

Create database:

CREATE DATABASE "DATABASE_NAME" OWNER "username@domain.tld";
 

[edit] ISPMan Agent Library

To comply with database support in ISPMan v1.3, a database specific library file needs to be provided (e.g. database.Postgres.lib). This needs to implement the following perl functions:

• add_database
• modify_database
• delete_database
• rename_database (optional)

Each function is passed the $params hash, from the ISPMan process.

Example $params hash (pulled from MySQL integration)
$VAR1 = {
          'ispmanDBType' => 'MySQL',
          'ispmanDBHost' => 'elwood'
          'ispmanDBUser' => 'test_master_abc',
          'ispmanDBPass' => '*5C44098C97D2ECDA5CAF6589ACEDAF8E85ABB6B7',
          'ispmanDBAccessFilter' => '%.example.com',
          'ispmanDBName' => 'test_master_abc',
          'ispmanDBPrivilege' => [
                                   'SELECT',
                                   'CREATE',
                                   'INSERT',
                                   'ALTER',
                                   'REFERENCES',
                                   'UPDATE',
                                   'INDEX',
                                   'DELETE',
                                   'DROP'
                                 ],
        };

Notes:

• ispmanDBPrivilege consists of arbitrary privilege options, which can be defined via confVar dbAccessPermissions
• ispmanDBPass contains the (hashed) password as entered in the UI form. This is stored in plain text if not database specific encrypted in DBMan.pm

The following gathers the necessary actions for each function:

[edit] add_database

[edit] modify_database

[edit] delete_database

[edit] rename_database

[edit] Possible Perl Implementation

Do NOTE that I know almost nothing about perl programing ;)

#!/usr/bin/env perl
 
# SQL Statement:
#   CREATE ROLE "user@domain.tld" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
#   CREATE DATABASE "DATABASE_NAME" OWNER "username@domain.tld";
#
# Postgres default port -> 5432
 
BEGIN {
    use FindBin;
    unshift @INC,
        ( $FindBin::Bin, "$FindBin::Bin/../lib", "$FindBin::Bin/../conf" );
    use Getopt::Std;
    getopt( 'hpu', \%opts );
    unless ($opts{'h'} && $opts{'u'} && $ARGV[0]) {
        $0 =~ s/.*\///;
        print "Usage: $0 -h host -p port -u username db_name\n";
        print "Example: $FindBin::Script -h localhost -u user_domain_tld db_name\n";
        print "Example: $FindBin::Script -h localhost -u user\@domain.tld db_name\n";
        exit;
    }
}
 
 
# set default port if none is provided
$port = $opts{'p'} || '5432';
$host = $opts{'h'};
$user = $opts{'u'};
$admin = $ISPMan::Config->{'psqlAdminUsername'};
$pass = $ISPMan::Config->{'psqlAdminPass'};
 
use DBI;
$dbh = DBI->connect("dbi:Pg:host=$host;port=$port;", "$admin", "$pass", { Autocommit >= 0 });
$dbh->do("CREATE ROLE \"$user\" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN");
$dbh->do("CREATE DATABASE \"$ARGV[0]\" OWNER \"$user\"");
$rc = $dbh->disconnect;