PostgreSqlIntegration

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.

Contents

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

File: /etc/pam.d/postgresql 
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.

File: /var/lib/postgresql/data/pg_hba.conf 
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


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

          

          

            [edit]
          

          
Create new database

          

            [edit]
          

          
For databases named by the username

          

          
    
            
  1. 
              
    
                CREATE DATABASE "username@domain.tld" OWNER "username@domain.tld";
              
    
            
    2. 
            
  2. 
              
    
                 
              
    
            
    3. 
          

          

          

            [edit]
          

          
For databases named diferently then the username

          
Create database:

          

          
    
            
  1. 
              
    
                CREATE DATABASE "DATABASE_NAME" OWNER "username@domain.tld";
              
    
            
    2. 
            
  2. 
              
    
                 
              
    
            
    3. 
          

          

          

            [edit]
          

          
Possible ISPMan CLI tool

          
A first CLI aproach on this subject.

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

          

            

              File: /opt/ispman/bin/ispman.psql.createDatabase
            

            

              
                

                  

                  
    
                    
  1. 
                      
    
                        #!/usr/bin/env perl
                      
    
                    
    2. 
                    
  2. 
                      
    
                         
                      
    
                    
    3. 
                    
  3. 
                      
    
                        # SQL Statement:
                      
    
                    
    4. 
                    
  4. 
                      
    
                        # CREATE ROLE "user@domain.tld" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
                      
    
                    
    5. 
                    
  5. 
                      
    
                        # CREATE DATABASE "DATABASE_NAME" OWNER "username@domain.tld";
                      
    
                    
    6. 
                    
  6. 
                      
    
                        #
                      
    
                    
    7. 
                    
  7. 
                      
    
                        # Postgres default port -> 5432
                      
    
                    
    8. 
                    
  8. 
                      
    
                         
                      
    
                    
    9. 
                    
  9. 
                      
    
                        BEGIN {
                      
    
                    
    10. 
                    
  10. 
                      
    
                        use FindBin;
                      
    
                    
    11. 
                    
  11. 
                      
    
                        unshift @INC,
                      
    
                    
    12. 
                    
  12. 
                      
    
                        ( $FindBin::Bin, "$FindBin::Bin/../lib", "$FindBin::Bin/../conf" );
                      
    
                    
    13. 
                    
  13. 
                      
    
                        use Getopt::Std;
                      
    
                    
    14. 
                    
  14. 
                      
    
                        getopt( 'hpu', \%opts );
                      
    
                    
    15. 
                    
  15. 
                      
    
                        unless ($opts{'h'} && $opts{'u'} && $ARGV[0]) {
                      
    
                    
    16. 
                    
  16. 
                      
    
                        $0 =~ s/.*\///;
                      
    
                    
    17. 
                    
  17. 
                      
    
                        print "Usage: $0 -h host -p port -u username db_name\n";
                      
    
                    
    18. 
                    
  18. 
                      
    
                        print "Example: $FindBin::Script -h localhost -u user_domain_tld db_name\n";
                      
    
                    
    19. 
                    
  19. 
                      
    
                        print "Example: $FindBin::Script -h localhost -u user\@domain.tld db_name\n";
                      
    
                    
    20. 
                    
  20. 
                      
    
                        exit;
                      
    
                    
    21. 
                    
  21. 
                      
    
                        }
                      
    
                    
    22. 
                    
  22. 
                      
    
                        }
                      
    
                    
    23. 
                    
  23. 
                      
    
                         
                      
    
                    
    24. 
                    
  24. 
                      
    
                         
                      
    
                    
    25. 
                    
  25. 
                      
    
                        # set default port if none is provided
                      
    
                    
    26. 
                    
  26. 
                      
    
                        $port = $opts{'p'} || '5432';
                      
    
                    
    27. 
                    
  27. 
                      
    
                        $host = $opts{'h'};
                      
    
                    
    28. 
                    
  28. 
                      
    
                        $user = $opts{'u'};
                      
    
                    
    29. 
                    
  29. 
                      
    
                        $admin = $ISPMan::Config->{'psqlAdminUsername'};
                      
    
                    
    30. 
                    
  30. 
                      
    
                        $pass = $ISPMan::Config->{'psqlAdminPass'};
                      
    
                    
    31. 
                    
  31. 
                      
    
                         
                      
    
                    
    32. 
                    
  32. 
                      
    
                        use DBI;
                      
    
                    
    33. 
                    
  33. 
                      
    
                        $dbh = DBI->connect("dbi:Pg:host=$host;port=$port;", "$admin", "$pass", { Autocommit >= 0 });
                      
    
                    
    34. 
                    
  34. 
                      
    
                        $dbh->do("CREATE ROLE \"$user\" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN");
                      
    
                    
    35. 
                    
  35. 
                      
    
                        $dbh->do("CREATE DATABASE \"$ARGV[0]\" OWNER \"$user\"");
                      
    
                    
    36. 
                    
  36. 
                      
    
                        $rc = $dbh->disconnect;
                      
    
                    
    37. 
                    
  37. 
                      
    
                         
                      
    
                    
    38. 
                  

                  

                

              		
            

          

          

            Retrieved from "http://wiki.ispman.net/index.php/PostgreSqlIntegration"
          

          
          

        

      

    

    

      
      
      
      
      
      
      

      

        
Toolbox

        

          
        

      

      

        
Support The Bounties: