granicus.if.org Git - postgresql/blob - src/backend/commands/user.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * user.c--
   4  *        use pg_exec_query to create a new user in the catalog
   5  *
   6  * Copyright (c) 1994, Regents of the University of California
   7  *
   8  *
   9  *
  10  *-------------------------------------------------------------------------
  11  */
  12 #include <stdio.h>                              /* for sprintf() */
  13 #include <string.h>
  14 #include <sys/types.h>
  15 #include <sys/stat.h>
  16 #include <fcntl.h>
  17 #include <unistd.h>
  18
  19 #include <postgres.h>
  20
  21 #include <miscadmin.h>
  22 #include <catalog/catname.h>
  23 #ifdef MB
  24 #include <catalog/pg_database_mb.h>
  25 #else
  26 #include <catalog/pg_database.h>
  27 #endif
  28 #include <catalog/pg_shadow.h>
  29 #include <libpq/crypt.h>
  30 #include <access/heapam.h>
  31 #include <access/xact.h>
  32 #include <storage/bufmgr.h>
  33 #include <storage/lmgr.h>
  34 #include <tcop/tcopprot.h>
  35 #include <utils/acl.h>
  36 #include <utils/rel.h>
  37 #include <utils/syscache.h>
  38 #include <commands/user.h>
  39
  40 static void CheckPgUserAclNotNull(void);
  41
  42 /*---------------------------------------------------------------------
  43  * UpdatePgPwdFile
  44  *
  45  * copy the modified contents of pg_shadow to a file used by the postmaster
  46  * for user authentication.  The file is stored as $PGDATA/pg_pwd.
  47  *---------------------------------------------------------------------
  48  */
  49 static
  50 void
  51 UpdatePgPwdFile(char *sql)
  52 {
  53
  54         char       *filename;
  55         char       *tempname;
  56
  57         /*
  58          * Create a temporary filename to be renamed later.  This prevents the
  59          * backend from clobbering the pg_pwd file while the postmaster might
  60          * be reading from it.
  61          */
  62         filename = crypt_getpwdfilename();
  63         tempname = (char *) malloc(strlen(filename) + 12);
  64         sprintf(tempname, "%s.%d", filename, MyProcPid);
  65
  66         /*
  67          * Copy the contents of pg_shadow to the pg_pwd ASCII file using a the
  68          * SEPCHAR character as the delimiter between fields.  Then rename the
  69          * file to its final name.
  70          */
  71         sprintf(sql, "copy %s to '%s' using delimiters %s", ShadowRelationName, tempname, CRYPT_PWD_FILE_SEPCHAR);
  72         pg_exec_query(sql);
  73         rename(tempname, filename);
  74         free((void *) tempname);
  75
  76         /*
  77          * Create a flag file the postmaster will detect the next time it
  78          * tries to authenticate a user.  The postmaster will know to reload
  79          * the pg_pwd file contents.
  80          */
  81         filename = crypt_getpwdreloadfilename();
  82         creat(filename, S_IRUSR | S_IWUSR);
  83 }
  84
  85 /*---------------------------------------------------------------------
  86  * DefineUser
  87  *
  88  * Add the user to the pg_shadow relation, and if specified make sure the
  89  * user is specified in the desired groups of defined in pg_group.
  90  *---------------------------------------------------------------------
  91  */
  92 void
  93 DefineUser(CreateUserStmt *stmt)
  94 {
  95
  96         char       *pg_shadow;
  97         Relation        pg_shadow_rel;
  98         TupleDesc       pg_shadow_dsc;
  99         HeapScanDesc scan;
 100         HeapTuple       tuple;
 101         Datum           datum;
 102         Buffer          buffer;
 103         char            sql[512];
 104         char       *sql_end;
 105         bool            exists = false,
 106                                 n,
 107                                 inblock;
 108         int                     max_id = -1;
 109
 110         if (stmt->password)
 111                 CheckPgUserAclNotNull();
 112         if (!(inblock = IsTransactionBlock()))
 113                 BeginTransactionBlock();
 114
 115         /*
 116          * Make sure the user attempting to create a user can insert into the
 117          * pg_shadow relation.
 118          */
 119         pg_shadow = GetPgUserName();
 120         if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK)
 121         {
 122                 UserAbortTransactionBlock();
 123                 elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
 124                          pg_shadow, ShadowRelationName);
 125                 return;
 126         }
 127
 128         /*
 129          * Scan the pg_shadow relation to be certain the user doesn't already
 130          * exist.
 131          */
 132         pg_shadow_rel = heap_openr(ShadowRelationName);
 133         pg_shadow_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
 134
 135         /*
 136          * Secure a write lock on pg_shadow so we can be sure of what the next
 137          * usesysid should be.
 138          */
 139         RelationSetLockForWrite(pg_shadow_rel);
 140
 141         scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
 142         while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer)))
 143         {
 144                 datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_shadow_dsc, &n);
 145
 146                 if (!exists && !strncmp((char *) datum, stmt->user, strlen(stmt->user)))
 147                         exists = true;
 148
 149                 datum = heap_getattr(tuple, Anum_pg_shadow_usesysid, pg_shadow_dsc, &n);
 150                 if ((int) datum > max_id)
 151                         max_id = (int) datum;
 152
 153                 ReleaseBuffer(buffer);
 154         }
 155         heap_endscan(scan);
 156
 157         if (exists)
 158         {
 159                 RelationUnsetLockForWrite(pg_shadow_rel);
 160                 heap_close(pg_shadow_rel);
 161                 UserAbortTransactionBlock();
 162                 elog(ERROR, "defineUser: user \"%s\" has already been created", stmt->user);
 163                 return;
 164         }
 165
 166         /*
 167          * Build the insert statment to be executed.
 168          */
 169         sprintf(sql, "insert into %s(usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd,passwd", ShadowRelationName);
 170 /*      if (stmt->password)
 171         strcat(sql, ",passwd"); -- removed so that insert empty string when no password */
 172         if (stmt->validUntil)
 173                 strcat(sql, ",valuntil");
 174
 175         sql_end = sql + strlen(sql);
 176         sprintf(sql_end, ") values('%s',%d", stmt->user, max_id + 1);
 177         if (stmt->createdb && *stmt->createdb)
 178                 strcat(sql_end, ",'t','t'");
 179         else
 180                 strcat(sql_end, ",'f','t'");
 181         if (stmt->createuser && *stmt->createuser)
 182                 strcat(sql_end, ",'t','t'");
 183         else
 184                 strcat(sql_end, ",'f','t'");
 185         sql_end += strlen(sql_end);
 186         if (stmt->password)
 187         {
 188                 sprintf(sql_end, ",'%s'", stmt->password);
 189                 sql_end += strlen(sql_end);
 190         }
 191         else
 192         {
 193                 strcpy(sql_end, ",''");
 194                 sql_end += strlen(sql_end);
 195         }
 196         if (stmt->validUntil)
 197         {
 198                 sprintf(sql_end, ",'%s'", stmt->validUntil);
 199                 sql_end += strlen(sql_end);
 200         }
 201         strcat(sql_end, ")");
 202
 203         pg_exec_query(sql);
 204
 205         /*
 206          * Add the stuff here for groups.
 207          */
 208
 209         UpdatePgPwdFile(sql);
 210
 211         /*
 212          * This goes after the UpdatePgPwdFile to be certain that two backends
 213          * to not attempt to write to the pg_pwd file at the same time.
 214          */
 215         RelationUnsetLockForWrite(pg_shadow_rel);
 216         heap_close(pg_shadow_rel);
 217
 218         if (IsTransactionBlock() && !inblock)
 219                 EndTransactionBlock();
 220 }
 221
 222
 223 extern void
 224 AlterUser(AlterUserStmt *stmt)
 225 {
 226
 227         char       *pg_shadow;
 228         Relation        pg_shadow_rel;
 229         TupleDesc       pg_shadow_dsc;
 230         HeapScanDesc scan;
 231         HeapTuple       tuple;
 232         Datum           datum;
 233         Buffer          buffer;
 234         char            sql[512];
 235         char       *sql_end;
 236         bool            exists = false,
 237                                 n,
 238                                 inblock;
 239
 240         if (stmt->password)
 241                 CheckPgUserAclNotNull();
 242         if (!(inblock = IsTransactionBlock()))
 243                 BeginTransactionBlock();
 244
 245         /*
 246          * Make sure the user attempting to create a user can insert into the
 247          * pg_shadow relation.
 248          */
 249         pg_shadow = GetPgUserName();
 250         if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
 251         {
 252                 UserAbortTransactionBlock();
 253                 elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
 254                          pg_shadow, ShadowRelationName);
 255                 return;
 256         }
 257
 258         /*
 259          * Scan the pg_shadow relation to be certain the user exists.
 260          */
 261         pg_shadow_rel = heap_openr(ShadowRelationName);
 262         pg_shadow_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
 263
 264         /*
 265          * Secure a write lock on pg_shadow so we can be sure that when the
 266          * dump of the pg_pwd file is done, there is not another backend doing
 267          * the same.
 268          */
 269         RelationSetLockForWrite(pg_shadow_rel);
 270
 271         scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
 272         while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer)))
 273         {
 274                 datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_shadow_dsc, &n);
 275
 276                 if (!strncmp((char *) datum, stmt->user, strlen(stmt->user)))
 277                 {
 278                         exists = true;
 279                         ReleaseBuffer(buffer);
 280                         break;
 281                 }
 282         }
 283         heap_endscan(scan);
 284
 285         if (!exists)
 286         {
 287                 RelationUnsetLockForWrite(pg_shadow_rel);
 288                 heap_close(pg_shadow_rel);
 289                 UserAbortTransactionBlock();
 290                 elog(ERROR, "alterUser: user \"%s\" does not exist", stmt->user);
 291                 return;
 292         }
 293
 294         /*
 295          * Create the update statement to modify the user.
 296          */
 297         sprintf(sql, "update %s set", ShadowRelationName);
 298         sql_end = sql;
 299         if (stmt->password)
 300         {
 301                 sql_end += strlen(sql_end);
 302                 sprintf(sql_end, " passwd = '%s'", stmt->password);
 303         }
 304         if (stmt->createdb)
 305         {
 306                 if (sql_end != sql)
 307                         strcat(sql_end, ",");
 308                 sql_end += strlen(sql_end);
 309                 if (*stmt->createdb)
 310                         strcat(sql_end, " usecreatedb = 't'");
 311                 else
 312                         strcat(sql_end, " usecreatedb = 'f'");
 313         }
 314         if (stmt->createuser)
 315         {
 316                 if (sql_end != sql)
 317                         strcat(sql_end, ",");
 318                 sql_end += strlen(sql_end);
 319                 if (*stmt->createuser)
 320                         strcat(sql_end, " usesuper = 't'");
 321                 else
 322                         strcat(sql_end, " usesuper = 'f'");
 323         }
 324         if (stmt->validUntil)
 325         {
 326                 if (sql_end != sql)
 327                         strcat(sql_end, ",");
 328                 sql_end += strlen(sql_end);
 329                 sprintf(sql_end, " valuntil = '%s'", stmt->validUntil);
 330         }
 331         if (sql_end != sql)
 332         {
 333                 sql_end += strlen(sql_end);
 334                 sprintf(sql_end, " where usename = '%s'", stmt->user);
 335                 pg_exec_query(sql);
 336         }
 337
 338         /* do the pg_group stuff here */
 339
 340         UpdatePgPwdFile(sql);
 341
 342         RelationUnsetLockForWrite(pg_shadow_rel);
 343         heap_close(pg_shadow_rel);
 344
 345         if (IsTransactionBlock() && !inblock)
 346                 EndTransactionBlock();
 347 }
 348
 349
 350 extern void
 351 RemoveUser(char *user)
 352 {
 353
 354         char       *pg_shadow;
 355         Relation        pg_shadow_rel,
 356                                 pg_rel;
 357         TupleDesc       pg_dsc;
 358         HeapScanDesc scan;
 359         HeapTuple       tuple;
 360         Datum           datum;
 361         Buffer          buffer;
 362         char            sql[512];
 363         bool            n,
 364                                 inblock;
 365         int                     usesysid = -1,
 366                                 ndbase = 0;
 367         char      **dbase = NULL;
 368
 369         if (!(inblock = IsTransactionBlock()))
 370                 BeginTransactionBlock();
 371
 372         /*
 373          * Make sure the user attempting to create a user can delete from the
 374          * pg_shadow relation.
 375          */
 376         pg_shadow = GetPgUserName();
 377         if (pg_aclcheck(ShadowRelationName, pg_shadow, ACL_RD | ACL_WR) != ACLCHECK_OK)
 378         {
 379                 UserAbortTransactionBlock();
 380                 elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
 381                          pg_shadow, ShadowRelationName);
 382                 return;
 383         }
 384
 385         /*
 386          * Perform a scan of the pg_shadow relation to find the usesysid of
 387          * the user to be deleted.      If it is not found, then return a warning
 388          * message.
 389          */
 390         pg_shadow_rel = heap_openr(ShadowRelationName);
 391         pg_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
 392
 393         /*
 394          * Secure a write lock on pg_shadow so we can be sure that when the
 395          * dump of the pg_pwd file is done, there is not another backend doing
 396          * the same.
 397          */
 398         RelationSetLockForWrite(pg_shadow_rel);
 399
 400         scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
 401         while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer)))
 402         {
 403                 datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_dsc, &n);
 404
 405                 if (!strncmp((char *) datum, user, strlen(user)))
 406                 {
 407                         usesysid = (int) heap_getattr(tuple, Anum_pg_shadow_usesysid, pg_dsc, &n);
 408                         ReleaseBuffer(buffer);
 409                         break;
 410                 }
 411                 ReleaseBuffer(buffer);
 412         }
 413         heap_endscan(scan);
 414
 415         if (usesysid == -1)
 416         {
 417                 RelationUnsetLockForWrite(pg_shadow_rel);
 418                 heap_close(pg_shadow_rel);
 419                 UserAbortTransactionBlock();
 420                 elog(ERROR, "removeUser: user \"%s\" does not exist", user);
 421                 return;
 422         }
 423
 424         /*
 425          * Perform a scan of the pg_database relation to find the databases
 426          * owned by usesysid.  Then drop them.
 427          */
 428         pg_rel = heap_openr(DatabaseRelationName);
 429         pg_dsc = RelationGetTupleDescriptor(pg_rel);
 430
 431         scan = heap_beginscan(pg_rel, false, false, 0, NULL);
 432         while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer)))
 433         {
 434                 datum = heap_getattr(tuple, Anum_pg_database_datdba, pg_dsc, &n);
 435
 436                 if ((int) datum == usesysid)
 437                 {
 438                         datum = heap_getattr(tuple, Anum_pg_database_datname, pg_dsc, &n);
 439                         if (memcmp((void *) datum, "template1", 9))
 440                         {
 441                                 dbase = (char **) realloc((void *) dbase, sizeof(char *) * (ndbase + 1));
 442                                 dbase[ndbase] = (char *) malloc(NAMEDATALEN + 1);
 443                                 memcpy((void *) dbase[ndbase], (void *) datum, NAMEDATALEN);
 444                                 dbase[ndbase++][NAMEDATALEN] = '\0';
 445                         }
 446                 }
 447                 ReleaseBuffer(buffer);
 448         }
 449         heap_endscan(scan);
 450         heap_close(pg_rel);
 451
 452         while (ndbase--)
 453         {
 454                 elog(NOTICE, "Dropping database %s", dbase[ndbase]);
 455                 sprintf(sql, "drop database %s", dbase[ndbase]);
 456                 free((void *) dbase[ndbase]);
 457                 pg_exec_query(sql);
 458         }
 459         if (dbase)
 460                 free((void *) dbase);
 461
 462         /*
 463          * Since pg_shadow is global over all databases, one of two things
 464          * must be done to insure complete consistency.  First, pg_shadow
 465          * could be made non-global. This would elminate the code above for
 466          * deleting database and would require the addition of code to delete
 467          * tables, views, etc owned by the user.
 468          *
 469          * The second option would be to create a means of deleting tables, view,
 470          * etc. owned by the user from other databases.  pg_shadow is global and
 471          * so this must be done at some point.
 472          *
 473          * Let us not forget that the user should be removed from the pg_groups
 474          * also.
 475          *
 476          * Todd A. Brandys 11/18/1997
 477          *
 478          */
 479
 480         /*
 481          * Remove the user from the pg_shadow table
 482          */
 483         sprintf(sql, "delete from %s where usename = '%s'", ShadowRelationName, user);
 484         pg_exec_query(sql);
 485
 486         UpdatePgPwdFile(sql);
 487
 488         RelationUnsetLockForWrite(pg_shadow_rel);
 489         heap_close(pg_shadow_rel);
 490
 491         if (IsTransactionBlock() && !inblock)
 492                 EndTransactionBlock();
 493 }
 494
 495 /*
 496  * CheckPgUserAclNotNull
 497  *
 498  * check to see if there is an ACL on pg_shadow
 499  */
 500 static void
 501 CheckPgUserAclNotNull()
 502 {
 503         HeapTuple       htp;
 504
 505         htp = SearchSysCacheTuple(RELNAME, PointerGetDatum(ShadowRelationName),
 506                                                           0, 0, 0);
 507         if (!HeapTupleIsValid(htp))
 508         {
 509                 elog(ERROR, "IsPgUserAclNull: class \"%s\" not found",
 510                          ShadowRelationName);
 511         }
 512
 513         if (heap_attisnull(htp, Anum_pg_class_relacl))
 514         {
 515                 elog(NOTICE, "To use passwords, you have to revoke permissions on pg_shadow");
 516                 elog(NOTICE, "so normal users can not read the passwords.");
 517                 elog(ERROR, "Try 'REVOKE ALL ON pg_shadow FROM PUBLIC'");
 518         }
 519
 520         return;
 521 }