source/passdb/pdb_pgsql.c

   1 /*
   2  * PostgresSQL password backend for samba
   3  * Copyright (C) Hamish Friedlander 2003
   4  * Copyright (C) Jelmer Vernooij 2004
   5  *
   6  * This program is free software; you can redistribute it and/or modify it under
   7  * the terms of the GNU General Public License as published by the Free
   8  * Software Foundation; either version 2 of the License, or (at your option)
   9  * any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful, but WITHOUT
  12  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
  13  * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
  14  * more details.
  15  *
  16  * You should have received a copy of the GNU General Public License along with
  17  * this program; if not, write to the Free Software Foundation, Inc., 675
  18  * Mass Ave, Cambridge, MA 02139, USA.
  19  */
  20
  21 #include "includes.h"
  22 #include <libpq-fe.h>
  23
  24 #define CONFIG_HOST_DEFAULT                             "localhost"
  25 #define CONFIG_USER_DEFAULT                             "samba"
  26 #define CONFIG_PASS_DEFAULT                             ""
  27 #define CONFIG_PORT_DEFAULT                             "5432"
  28 #define CONFIG_DB_DEFAULT                               "samba"
  29
  30 /* handles for doing db transactions */
  31 typedef struct pdb_pgsql_data {
  32   PGconn     *handle ;
  33   PGresult   *pwent  ;
  34   long        currow ;
  35
  36   const char *location ;
  37 } pdb_pgsql_data ;
  38
  39 #define SET_DATA(data,methods) { \
  40         if(!methods){ \
  41                 DEBUG(0, ("invalid methods!\n")); \
  42                         return NT_STATUS_INVALID_PARAMETER; \
  43         } \
  44         data = (struct pdb_pgsql_data *)methods->private_data; \
  45                 if(!data || !(data->handle)){ \
  46                         DEBUG(0, ("invalid handle!\n")); \
  47                                 return NT_STATUS_INVALID_HANDLE; \
  48                 } \
  49 }
  50
  51 #define SET_DATA_QUIET(data,methods) { \
  52         if(!methods){ \
  53                 DEBUG(0, ("invalid methods!\n")); \
  54                         return ; \
  55         } \
  56         data = (struct pdb_pgsql_data *)methods->private_data; \
  57                 if(!data || !(data->handle)){ \
  58                         DEBUG(0, ("invalid handle!\n")); \
  59                                 return ; \
  60                 } \
  61 }
  62
  63
  64 #define config_value( data, name, default_value ) \
  65   lp_parm_const_string( GLOBAL_SECTION_SNUM, (data)->location, name, default_value )
  66
  67 static long PQgetlong( PGresult *r, long row, long col )
  68 {
  69   if ( PQgetisnull( r, row, col ) ) return 0 ;
  70
  71   return atol( PQgetvalue( r, row,  col ) ) ;
  72 }
  73
  74 static NTSTATUS row_to_sam_account ( PGresult *r, long row, SAM_ACCOUNT *u )
  75 {
  76   pstring temp ;
  77   DOM_SID sid ;
  78
  79   if ( row >= PQntuples( r ) ) return NT_STATUS_INVALID_PARAMETER ;
  80
  81   pdb_set_logon_time           ( u, PQgetlong ( r, row,  0 ), PDB_SET ) ;
  82   pdb_set_logoff_time          ( u, PQgetlong ( r, row,  1 ), PDB_SET ) ;
  83   pdb_set_kickoff_time         ( u, PQgetlong ( r, row,  2 ), PDB_SET ) ;
  84   pdb_set_pass_last_set_time   ( u, PQgetlong ( r, row,  3 ), PDB_SET ) ;
  85   pdb_set_pass_can_change_time ( u, PQgetlong ( r, row,  4 ), PDB_SET ) ;
  86   pdb_set_pass_must_change_time( u, PQgetlong ( r, row,  5 ), PDB_SET ) ;
  87   pdb_set_username             ( u, PQgetvalue( r, row,  6 ), PDB_SET ) ;
  88   pdb_set_domain               ( u, PQgetvalue( r, row,  7 ), PDB_SET ) ;
  89   pdb_set_nt_username          ( u, PQgetvalue( r, row,  8 ), PDB_SET ) ;
  90   pdb_set_fullname             ( u, PQgetvalue( r, row,  9 ), PDB_SET ) ;
  91   pdb_set_homedir              ( u, PQgetvalue( r, row, 10 ), PDB_SET ) ;
  92   pdb_set_dir_drive            ( u, PQgetvalue( r, row, 11 ), PDB_SET ) ;
  93   pdb_set_logon_script         ( u, PQgetvalue( r, row, 12 ), PDB_SET ) ;
  94   pdb_set_profile_path         ( u, PQgetvalue( r, row, 13 ), PDB_SET ) ;
  95   pdb_set_acct_desc            ( u, PQgetvalue( r, row, 14 ), PDB_SET ) ;
  96   pdb_set_workstations         ( u, PQgetvalue( r, row, 15 ), PDB_SET ) ;
  97   pdb_set_unknown_str          ( u, PQgetvalue( r, row, 16 ), PDB_SET ) ;
  98   pdb_set_munged_dial          ( u, PQgetvalue( r, row, 17 ), PDB_SET ) ;
  99
 100   pdb_set_acct_ctrl            ( u, PQgetlong ( r, row, 23 ), PDB_SET ) ;
 101   pdb_set_logon_divs           ( u, PQgetlong ( r, row, 24 ), PDB_SET ) ;
 102   pdb_set_hours_len            ( u, PQgetlong ( r, row, 25 ), PDB_SET ) ;
 103   pdb_set_bad_password_count    ( u, PQgetlong (r, row, 26 ), PDB_SET ) ;
 104   pdb_set_logon_count            ( u, PQgetlong ( r, row, 27 ), PDB_SET ) ;
 105   pdb_set_unknown_6            ( u, PQgetlong ( r, row, 28 ), PDB_SET ) ;
 106
 107   if ( !PQgetisnull( r, row, 18 ) ) {
 108     string_to_sid( &sid, PQgetvalue( r, row, 18 ) ) ;
 109     pdb_set_user_sid ( u, &sid, PDB_SET ) ;
 110   }
 111
 112   if ( !PQgetisnull( r, row, 19 ) ) {
 113     string_to_sid( &sid, PQgetvalue( r, row, 19 ) ) ;
 114     pdb_set_group_sid( u, &sid, PDB_SET ) ;
 115   }
 116
 117   if ( pdb_gethexpwd( PQgetvalue( r, row, 20 ), temp ), PDB_SET ) pdb_set_lanman_passwd( u, temp, PDB_SET ) ;
 118   if ( pdb_gethexpwd( PQgetvalue( r, row, 21 ), temp ), PDB_SET ) pdb_set_nt_passwd    ( u, temp, PDB_SET ) ;
 119
 120   /* Only use plaintext password storage when lanman and nt are NOT used */
 121   if ( PQgetisnull( r, row, 20 ) || PQgetisnull( r, row, 21 ) ) pdb_set_plaintext_passwd( u, PQgetvalue( r, row, 22 ) ) ;
 122
 123   return NT_STATUS_OK ;
 124 }
 125
 126 static NTSTATUS pgsqlsam_setsampwent(struct pdb_methods *methods, BOOL update, uint16 acb_mask)
 127 {
 128   struct pdb_pgsql_data *data ;
 129   char *query ;
 130   NTSTATUS retval ;
 131
 132   SET_DATA( data, methods ) ;
 133
 134   query = sql_account_query_select(NULL, data->location, update, SQL_SEARCH_NONE, NULL);
 135
 136   /* Do it */
 137   DEBUG( 5, ("Executing query %s\n", query) ) ;
 138   data->pwent  = PQexec( data->handle, query ) ;
 139   data->currow = 0 ;
 140
 141   /* Result? */
 142   if ( data->pwent == NULL )
 143   {
 144     DEBUG( 0, ("Error executing %s, %s\n", query, PQerrorMessage( data->handle ) ) ) ;
 145     retval = NT_STATUS_UNSUCCESSFUL ;
 146   }
 147   else if ( PQresultStatus( data->pwent ) != PGRES_TUPLES_OK )
 148   {
 149     DEBUG( 0, ("Error executing %s, %s\n", query, PQresultErrorMessage( data->pwent ) ) ) ;
 150     retval = NT_STATUS_UNSUCCESSFUL ;
 151   }
 152   else
 153   {
 154     DEBUG( 5, ("pgsqlsam_setsampwent succeeded(%d results)!\n", PQntuples(data->pwent)) ) ;
 155     retval = NT_STATUS_OK ;
 156   }
 157
 158   talloc_free(query);
 159   return retval ;
 160 }
 161
 162 /***************************************************************
 163   End enumeration of the passwd list.
 164  ****************************************************************/
 165
 166 static void pgsqlsam_endsampwent(struct pdb_methods *methods)
 167 {
 168   struct pdb_pgsql_data *data ;
 169
 170   SET_DATA_QUIET( data, methods ) ;
 171
 172   if (data->pwent != NULL)
 173   {
 174     PQclear( data->pwent ) ;
 175   }
 176
 177   data->pwent  = NULL ;
 178   data->currow = 0 ;
 179
 180   DEBUG( 5, ("pgsql_endsampwent called\n") ) ;
 181 }
 182
 183 /*****************************************************************
 184   Get one SAM_ACCOUNT from the list (next in line)
 185  *****************************************************************/
 186
 187 static NTSTATUS pgsqlsam_getsampwent( struct pdb_methods *methods, SAM_ACCOUNT *user )
 188 {
 189   struct pdb_pgsql_data *data;
 190   NTSTATUS retval ;
 191
 192   SET_DATA( data, methods ) ;
 193
 194   if ( data->pwent == NULL )
 195   {
 196     DEBUG( 0, ("invalid pwent\n") ) ;
 197     return NT_STATUS_INVALID_PARAMETER ;
 198   }
 199
 200   retval = row_to_sam_account( data->pwent, data->currow, user ) ;
 201   data->currow++ ;
 202
 203   return retval ;
 204 }
 205
 206 static NTSTATUS pgsqlsam_select_by_field ( struct pdb_methods *methods, SAM_ACCOUNT *user, enum sql_search_field field, const char *sname )
 207 {
 208   struct pdb_pgsql_data *data ;
 209
 210   char *esc ;
 211   char *query ;
 212
 213   PGresult *result ;
 214   NTSTATUS retval ;
 215
 216   SET_DATA(data, methods);
 217
 218   if ( user == NULL )
 219   {
 220     DEBUG( 0, ("pdb_getsampwnam: SAM_ACCOUNT is NULL.\n") ) ;
 221     return NT_STATUS_INVALID_PARAMETER;
 222   }
 223
 224   DEBUG( 5, ("pgsqlsam_select_by_field: getting data where %d = %s(nonescaped)\n", field, sname) ) ;
 225
 226   /* Escape sname */
 227   esc = talloc_array(NULL, char, strlen(sname) * 2 + 1);
 228   if ( !esc )
 229   {
 230     DEBUG(0, ("Can't allocate memory to store escaped name\n"));
 231     return NT_STATUS_NO_MEMORY;
 232   }
 233
 234   //tmp_sname = smb_xstrdup(sname);
 235   PQescapeString( esc, sname, strlen(sname) ) ;
 236
 237   query = sql_account_query_select(NULL, data->location, True, field, esc);
 238
 239   /* Do it */
 240   DEBUG( 5, ("Executing query %s\n", query) ) ;
 241   result = PQexec( data->handle, query ) ;
 242
 243   /* Result? */
 244   if ( result == NULL )
 245   {
 246     DEBUG( 0, ("Error executing %s, %s\n", query, PQerrorMessage( data->handle ) ) ) ;
 247     retval = NT_STATUS_UNSUCCESSFUL ;
 248   }
 249   else if ( PQresultStatus( result ) != PGRES_TUPLES_OK )
 250   {
 251     DEBUG( 0, ("Error executing %s, %s\n", query, PQresultErrorMessage( result ) ) ) ;
 252     retval = NT_STATUS_UNSUCCESSFUL ;
 253   }
 254   else
 255   {
 256     retval = row_to_sam_account( result, 0, user ) ;
 257   }
 258
 259   talloc_free( esc   ) ;
 260   talloc_free( query ) ;
 261
 262   PQclear( result ) ;
 263
 264   return retval ;
 265 }
 266
 267 /******************************************************************
 268   Lookup a name in the SAM database
 269  ******************************************************************/
 270
 271 static NTSTATUS pgsqlsam_getsampwnam ( struct pdb_methods *methods, SAM_ACCOUNT *user, const char *sname )
 272 {
 273   struct pdb_pgsql_data *data;
 274
 275   SET_DATA(data, methods);
 276
 277   if ( !sname )
 278   {
 279     DEBUG( 0, ("invalid name specified") ) ;
 280     return NT_STATUS_INVALID_PARAMETER;
 281   }
 282
 283   return pgsqlsam_select_by_field( methods, user, SQL_SEARCH_USER_NAME, sname ) ;
 284 }
 285
 286
 287 /***************************************************************************
 288   Search by sid
 289  **************************************************************************/
 290
 291 static NTSTATUS pgsqlsam_getsampwsid ( struct pdb_methods *methods, SAM_ACCOUNT *user, const DOM_SID *sid )
 292 {
 293   struct pdb_pgsql_data *data;
 294   fstring sid_str;
 295
 296   SET_DATA( data, methods ) ;
 297
 298   sid_to_string( sid_str, sid ) ;
 299
 300   return pgsqlsam_select_by_field( methods, user, SQL_SEARCH_USER_SID, sid_str ) ;
 301 }
 302
 303 /***************************************************************************
 304   Delete a SAM_ACCOUNT
 305  ****************************************************************************/
 306
 307 static NTSTATUS pgsqlsam_delete_sam_account( struct pdb_methods *methods, SAM_ACCOUNT *sam_pass )
 308 {
 309   struct pdb_pgsql_data *data ;
 310
 311   const char *sname = pdb_get_username( sam_pass ) ;
 312   char *esc ;
 313   char *query ;
 314
 315   PGresult *result ;
 316   NTSTATUS retval ;
 317
 318   SET_DATA(data, methods);
 319
 320   if ( !sname )
 321   {
 322     DEBUG( 0, ("invalid name specified\n") ) ;
 323     return NT_STATUS_INVALID_PARAMETER ;
 324   }
 325
 326   /* Escape sname */
 327   esc = talloc_array(NULL, char, strlen(sname) * 2 + 1);
 328   if ( !esc )
 329   {
 330     DEBUG(0, ("Can't allocate memory to store escaped name\n"));
 331     return NT_STATUS_NO_MEMORY;
 332   }
 333
 334   PQescapeString( esc, sname, strlen(sname) ) ;
 335
 336   query = sql_account_query_delete(NULL, data->location, esc);
 337
 338   /* Do it */
 339   result = PQexec( data->handle, query ) ;
 340
 341   if ( result == NULL )
 342   {
 343     DEBUG( 0, ("Error executing %s, %s\n", query, PQerrorMessage( data->handle ) ) ) ;
 344     retval = NT_STATUS_UNSUCCESSFUL ;
 345   }
 346   else if ( PQresultStatus( result ) != PGRES_COMMAND_OK )
 347   {
 348     DEBUG( 0, ("Error executing %s, %s\n", query, PQresultErrorMessage( result ) ) ) ;
 349     retval = NT_STATUS_UNSUCCESSFUL ;
 350   }
 351   else
 352   {
 353     DEBUG( 5, ("User '%s' deleted\n", sname) ) ;
 354     retval = NT_STATUS_OK ;
 355   }
 356
 357   talloc_free( esc ) ;
 358   talloc_free( query ) ;
 359
 360   return retval ;
 361 }
 362
 363 static NTSTATUS pgsqlsam_replace_sam_account( struct pdb_methods *methods, const SAM_ACCOUNT *newpwd, char isupdate )
 364 {
 365   struct pdb_pgsql_data *data ;
 366   char *query;
 367   PGresult *result ;
 368
 369   if ( !methods )
 370   {
 371     DEBUG( 0, ("invalid methods!\n") ) ;
 372     return NT_STATUS_INVALID_PARAMETER ;
 373   }
 374
 375   data = (struct pdb_pgsql_data *) methods->private_data ;
 376
 377   if ( data == NULL || data->handle == NULL )
 378   {
 379     DEBUG( 0, ("invalid handle!\n") ) ;
 380     return NT_STATUS_INVALID_HANDLE ;
 381   }
 382
 383   query = sql_account_query_update(NULL, data->location, newpwd, isupdate);
 384
 385   result = PQexec( data->handle, query ) ;
 386
 387
 388   /* Execute the query */
 389   if ( result == NULL )
 390   {
 391     DEBUG( 0, ("Error executing %s, %s\n", query, PQerrorMessage( data->handle ) ) ) ;
 392     return NT_STATUS_INVALID_PARAMETER;
 393   }
 394   else if ( PQresultStatus( result ) != PGRES_COMMAND_OK )
 395   {
 396     DEBUG( 0, ("Error executing %s, %s\n", query, PQresultErrorMessage( result ) ) ) ;
 397     return NT_STATUS_INVALID_PARAMETER;
 398   }
 399   talloc_free(query);
 400
 401   return NT_STATUS_OK;
 402 }
 403
 404 static NTSTATUS pgsqlsam_add_sam_account ( struct pdb_methods *methods, SAM_ACCOUNT *newpwd )
 405 {
 406   return pgsqlsam_replace_sam_account( methods, newpwd, 0 ) ;
 407 }
 408
 409 static NTSTATUS pgsqlsam_update_sam_account ( struct pdb_methods *methods, SAM_ACCOUNT *newpwd )
 410 {
 411   return pgsqlsam_replace_sam_account( methods, newpwd, 1 ) ;
 412 }
 413
 414 static NTSTATUS pgsqlsam_init ( struct pdb_context *pdb_context, struct pdb_methods **pdb_method, const char *location )
 415 {
 416   NTSTATUS nt_status ;
 417   struct pdb_pgsql_data *data ;
 418
 419   if ( !pdb_context )
 420   {
 421     DEBUG( 0, ("invalid pdb_methods specified\n") ) ;
 422     return NT_STATUS_UNSUCCESSFUL;
 423   }
 424
 425   if (!NT_STATUS_IS_OK
 426     (nt_status = make_pdb_methods(pdb_context->mem_ctx, pdb_method))) {
 427     return nt_status;
 428   }
 429
 430   (*pdb_method)->name               = "pgsqlsam" ;
 431
 432   (*pdb_method)->setsampwent        = pgsqlsam_setsampwent ;
 433   (*pdb_method)->endsampwent        = pgsqlsam_endsampwent ;
 434   (*pdb_method)->getsampwent        = pgsqlsam_getsampwent ;
 435   (*pdb_method)->getsampwnam        = pgsqlsam_getsampwnam ;
 436   (*pdb_method)->getsampwsid        = pgsqlsam_getsampwsid ;
 437   (*pdb_method)->add_sam_account    = pgsqlsam_add_sam_account ;
 438   (*pdb_method)->update_sam_account = pgsqlsam_update_sam_account ;
 439   (*pdb_method)->delete_sam_account = pgsqlsam_delete_sam_account ;
 440
 441   data = talloc( pdb_context->mem_ctx, struct pdb_pgsql_data ) ;
 442   (*pdb_method)->private_data = data ;
 443   data->handle = NULL ;
 444   data->pwent  = NULL ;
 445
 446   if ( !location )
 447   {
 448     DEBUG( 0, ("No identifier specified. Check the Samba HOWTO Collection for details\n") ) ;
 449     return NT_STATUS_INVALID_PARAMETER;
 450   }
 451
 452   data->location = smb_xstrdup( location ) ;
 453
 454   if(!sql_account_config_valid(data->location)) {
 455           return NT_STATUS_INVALID_PARAMETER;
 456   }
 457
 458   DEBUG
 459   (
 460     1,
 461     (
 462       "Connecting to database server, host: %s, user: %s, password: XXXXXX, database: %s, port: %s\n",
 463       config_value( data, "pgsql host"    , CONFIG_HOST_DEFAULT ),
 464       config_value( data, "pgsql user"    , CONFIG_USER_DEFAULT ),
 465       config_value( data, "pgsql database", CONFIG_DB_DEFAULT   ),
 466       config_value( data, "pgsql port"    , CONFIG_PORT_DEFAULT )
 467     )
 468   ) ;
 469
 470   /* Do the pgsql initialization */
 471   data->handle = PQsetdbLogin(
 472                                 config_value( data, "pgsql host"    , CONFIG_HOST_DEFAULT ),
 473                                 config_value( data, "pgsql port"    , CONFIG_PORT_DEFAULT ),
 474                                 NULL,
 475                                 NULL,
 476                                 config_value( data, "pgsql database", CONFIG_DB_DEFAULT   ),
 477                                 config_value( data, "pgsql user"    , CONFIG_USER_DEFAULT ),
 478                                 config_value( data, "pgsql password", CONFIG_PASS_DEFAULT )
 479                              ) ;
 480
 481   if ( PQstatus( data->handle ) != CONNECTION_OK )
 482   {
 483     DEBUG( 0, ("Failed to connect to pgsql database: error: %s\n", PQerrorMessage( data->handle )) ) ;
 484     return NT_STATUS_UNSUCCESSFUL;
 485   }
 486
 487   DEBUG( 5, ("Connected to pgsql database\n") ) ;
 488   return NT_STATUS_OK;
 489 }
 490
 491 NTSTATUS pdb_pgsql_init(void)
 492 {
 493   return smb_register_passdb( PASSDB_INTERFACE_VERSION, "pgsql", pgsqlsam_init ) ;
 494 }