user.c

PostgreSQL7.4.6 for Linux

			ereport(ERROR,
					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
					 errmsg("user ID must be positive")));
		havesysid = true;
	}
	if (dvalidUntil)
		validUntil = strVal(dvalidUntil->arg);
	if (dpassword)
		password = strVal(dpassword->arg);
	if (dgroupElts)
		groupElts = (List *) dgroupElts->arg;

	/* Check some permissions first */
	if (password)
		CheckPgUserAclNotNull();

	if (!superuser())
		ereport(ERROR,
				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
				 errmsg("must be superuser to create users")));

	if (strcmp(stmt->user, "public") == 0)
		ereport(ERROR,
				(errcode(ERRCODE_RESERVED_NAME),
				 errmsg("user name \"%s\" is reserved",
						stmt->user)));

	/*
	 * Scan the pg_shadow relation to be certain the user or id doesn't
	 * already exist.  Note we secure exclusive lock, because we also need
	 * to be sure of what the next usesysid should be, and we need to
	 * protect our eventual update of the flat password file.
	 */
	pg_shadow_rel = heap_openr(ShadowRelationName, ExclusiveLock);
	pg_shadow_dsc = RelationGetDescr(pg_shadow_rel);

	scan = heap_beginscan(pg_shadow_rel, SnapshotNow, 0, NULL);
	max_id = 99;				/* start auto-assigned ids at 100 */
	while (!user_exists && !sysid_exists &&
		   (tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
	{
		Form_pg_shadow shadow_form = (Form_pg_shadow) GETSTRUCT(tuple);
		int32		this_sysid;

		user_exists = (strcmp(NameStr(shadow_form->usename), stmt->user) == 0);

		this_sysid = shadow_form->usesysid;
		if (havesysid)			/* customized id wanted */
			sysid_exists = (this_sysid == sysid);
		else
		{
			/* pick 1 + max */
			if (this_sysid > max_id)
				max_id = this_sysid;
		}
	}
	heap_endscan(scan);

	if (user_exists)
		ereport(ERROR,
				(errcode(ERRCODE_DUPLICATE_OBJECT),
				 errmsg("user \"%s\" already exists",
						stmt->user)));
	if (sysid_exists)
		ereport(ERROR,
				(errcode(ERRCODE_DUPLICATE_OBJECT),
				 errmsg("user ID %d is already assigned", sysid)));

	/* If no sysid given, use max existing id + 1 */
	if (!havesysid)
		sysid = max_id + 1;

	/*
	 * Build a tuple to insert
	 */
	MemSet(new_record, 0, sizeof(new_record));
	MemSet(new_record_nulls, ' ', sizeof(new_record_nulls));

	new_record[Anum_pg_shadow_usename - 1] =
		DirectFunctionCall1(namein, CStringGetDatum(stmt->user));
	new_record[Anum_pg_shadow_usesysid - 1] = Int32GetDatum(sysid);
	AssertState(BoolIsValid(createdb));
	new_record[Anum_pg_shadow_usecreatedb - 1] = BoolGetDatum(createdb);
	AssertState(BoolIsValid(createuser));
	new_record[Anum_pg_shadow_usesuper - 1] = BoolGetDatum(createuser);
	/* superuser gets catupd right by default */
	new_record[Anum_pg_shadow_usecatupd - 1] = BoolGetDatum(createuser);

	if (password)
	{
		if (!encrypt_password || isMD5(password))
			new_record[Anum_pg_shadow_passwd - 1] =
				DirectFunctionCall1(textin, CStringGetDatum(password));
		else
		{
			if (!EncryptMD5(password, stmt->user, strlen(stmt->user),
							encrypted_password))
				elog(ERROR, "password encryption failed");
			new_record[Anum_pg_shadow_passwd - 1] =
				DirectFunctionCall1(textin, CStringGetDatum(encrypted_password));
		}
	}
	else
		new_record_nulls[Anum_pg_shadow_passwd - 1] = 'n';

	if (validUntil)
		new_record[Anum_pg_shadow_valuntil - 1] =
			DirectFunctionCall1(abstimein, CStringGetDatum(validUntil));
	else
		new_record_nulls[Anum_pg_shadow_valuntil - 1] = 'n';

	new_record_nulls[Anum_pg_shadow_useconfig - 1] = 'n';

	tuple = heap_formtuple(pg_shadow_dsc, new_record, new_record_nulls);

	/*
	 * Insert new record in the pg_shadow table
	 */
	simple_heap_insert(pg_shadow_rel, tuple);

	/* Update indexes */
	CatalogUpdateIndexes(pg_shadow_rel, tuple);

	/*
	 * Add the user to the groups specified. We'll just call the below
	 * AlterGroup for this.
	 */
	foreach(item, groupElts)
	{
		AlterGroupStmt ags;

		ags.name = strVal(lfirst(item));		/* the group name to add
												 * this in */
		ags.action = +1;
		ags.listUsers = makeList1(makeInteger(sysid));
		AlterGroup(&ags, "CREATE USER");
	}

	/*
	 * Now we can clean up; but keep lock until commit (to avoid possible
	 * deadlock when commit code tries to acquire lock).
	 */
	heap_close(pg_shadow_rel, NoLock);

	/*
	 * Set flag to update flat password file at commit.
	 */
	user_file_update_needed = true;
}



/*
 * ALTER USER
 */
void
AlterUser(AlterUserStmt *stmt)
{
	Datum		new_record[Natts_pg_shadow];
	char		new_record_nulls[Natts_pg_shadow];
	char		new_record_repl[Natts_pg_shadow];
	Relation	pg_shadow_rel;
	TupleDesc	pg_shadow_dsc;
	HeapTuple	tuple,
				new_tuple;
	List	   *option;
	char	   *password = NULL;	/* PostgreSQL user password */
	bool		encrypt_password = Password_encryption; /* encrypt password? */
	char		encrypted_password[MD5_PASSWD_LEN + 1];
	int			createdb = -1;	/* Can the user create databases? */
	int			createuser = -1;	/* Can this user create users? */
	char	   *validUntil = NULL;		/* The time the login is valid
										 * until */
	DefElem    *dpassword = NULL;
	DefElem    *dcreatedb = NULL;
	DefElem    *dcreateuser = NULL;
	DefElem    *dvalidUntil = NULL;

	/* Extract options from the statement node tree */
	foreach(option, stmt->options)
	{
		DefElem    *defel = (DefElem *) lfirst(option);

		if (strcmp(defel->defname, "password") == 0 ||
			strcmp(defel->defname, "encryptedPassword") == 0 ||
			strcmp(defel->defname, "unencryptedPassword") == 0)
		{
			if (dpassword)
				ereport(ERROR,
						(errcode(ERRCODE_SYNTAX_ERROR),
						 errmsg("conflicting or redundant options")));
			dpassword = defel;
			if (strcmp(defel->defname, "encryptedPassword") == 0)
				encrypt_password = true;
			else if (strcmp(defel->defname, "unencryptedPassword") == 0)
				encrypt_password = false;
		}
		else if (strcmp(defel->defname, "createdb") == 0)
		{
			if (dcreatedb)
				ereport(ERROR,
						(errcode(ERRCODE_SYNTAX_ERROR),
						 errmsg("conflicting or redundant options")));
			dcreatedb = defel;
		}
		else if (strcmp(defel->defname, "createuser") == 0)
		{
			if (dcreateuser)
				ereport(ERROR,
						(errcode(ERRCODE_SYNTAX_ERROR),
						 errmsg("conflicting or redundant options")));
			dcreateuser = defel;
		}
		else if (strcmp(defel->defname, "validUntil") == 0)
		{
			if (dvalidUntil)
				ereport(ERROR,
						(errcode(ERRCODE_SYNTAX_ERROR),
						 errmsg("conflicting or redundant options")));
			dvalidUntil = defel;
		}
		else
			elog(ERROR, "option \"%s\" not recognized",
				 defel->defname);
	}

	if (dcreatedb)
		createdb = intVal(dcreatedb->arg);
	if (dcreateuser)
		createuser = intVal(dcreateuser->arg);
	if (dvalidUntil)
		validUntil = strVal(dvalidUntil->arg);
	if (dpassword)
		password = strVal(dpassword->arg);

	if (password)
		CheckPgUserAclNotNull();

	/* must be superuser or just want to change your own password */
	if (!superuser() &&
		!(createdb < 0 &&
		  createuser < 0 &&
		  !validUntil &&
		  password &&
		  strcmp(GetUserNameFromId(GetUserId()), stmt->user) == 0))
		ereport(ERROR,
				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
				 errmsg("permission denied")));

	/*
	 * Scan the pg_shadow relation to be certain the user exists. Note we
	 * secure exclusive lock to protect our update of the flat password
	 * file.
	 */
	pg_shadow_rel = heap_openr(ShadowRelationName, ExclusiveLock);
	pg_shadow_dsc = RelationGetDescr(pg_shadow_rel);

	tuple = SearchSysCache(SHADOWNAME,
						   PointerGetDatum(stmt->user),
						   0, 0, 0);
	if (!HeapTupleIsValid(tuple))
		ereport(ERROR,
				(errcode(ERRCODE_UNDEFINED_OBJECT),
				 errmsg("user \"%s\" does not exist", stmt->user)));

	/*
	 * Build an updated tuple, perusing the information just obtained
	 */
	MemSet(new_record, 0, sizeof(new_record));
	MemSet(new_record_nulls, ' ', sizeof(new_record_nulls));
	MemSet(new_record_repl, ' ', sizeof(new_record_repl));

	new_record[Anum_pg_shadow_usename - 1] = DirectFunctionCall1(namein,
											CStringGetDatum(stmt->user));
	new_record_repl[Anum_pg_shadow_usename - 1] = 'r';

	/* createdb */
	if (createdb >= 0)
	{
		new_record[Anum_pg_shadow_usecreatedb - 1] = BoolGetDatum(createdb > 0);
		new_record_repl[Anum_pg_shadow_usecreatedb - 1] = 'r';
	}

	/*
	 * createuser (superuser) and catupd
	 *
	 * XXX It's rather unclear how to handle catupd.  It's probably best to
	 * keep it equal to the superuser status, otherwise you could end up
	 * with a situation where no existing superuser can alter the
	 * catalogs, including pg_shadow!
	 */
	if (createuser >= 0)
	{
		new_record[Anum_pg_shadow_usesuper - 1] = BoolGetDatum(createuser > 0);
		new_record_repl[Anum_pg_shadow_usesuper - 1] = 'r';

		new_record[Anum_pg_shadow_usecatupd - 1] = BoolGetDatum(createuser > 0);
		new_record_repl[Anum_pg_shadow_usecatupd - 1] = 'r';
	}

	/* password */
	if (password)
	{
		if (!encrypt_password || isMD5(password))
			new_record[Anum_pg_shadow_passwd - 1] =
				DirectFunctionCall1(textin, CStringGetDatum(password));
		else
		{
			if (!EncryptMD5(password, stmt->user, strlen(stmt->user),
							encrypted_password))
				elog(ERROR, "password encryption failed");
			new_record[Anum_pg_shadow_passwd - 1] =
				DirectFunctionCall1(textin, CStringGetDatum(encrypted_password));
		}
		new_record_repl[Anum_pg_shadow_passwd - 1] = 'r';
	}

	/* valid until */
	if (validUntil)
	{
		new_record[Anum_pg_shadow_valuntil - 1] =
			DirectFunctionCall1(abstimein, CStringGetDatum(validUntil));
		new_record_repl[Anum_pg_shadow_valuntil - 1] = 'r';
	}

	new_tuple = heap_modifytuple(tuple, pg_shadow_rel, new_record,
								 new_record_nulls, new_record_repl);
	simple_heap_update(pg_shadow_rel, &tuple->t_self, new_tuple);

	/* Update indexes */
	CatalogUpdateIndexes(pg_shadow_rel, new_tuple);

	ReleaseSysCache(tuple);
	heap_freetuple(new_tuple);

	/*
	 * Now we can clean up; but keep lock until commit (to avoid possible
	 * deadlock when commit code tries to acquire lock).
	 */
	heap_close(pg_shadow_rel, NoLock);

	/*
	 * Set flag to update flat password file at commit.
	 */
	user_file_update_needed = true;
}


/*
 * ALTER USER ... SET
 */
void
AlterUserSet(AlterUserSetStmt *stmt)
{
	char	   *valuestr;
	HeapTuple	oldtuple,
				newtuple;
	Relation	rel;
	Datum		repl_val[Natts_pg_shadow];
	char		repl_null[Natts_pg_shadow];
	char		repl_repl[Natts_pg_shadow];
	int			i;

	valuestr = flatten_set_variable_args(stmt->variable, stmt->value);

	/*
	 * RowExclusiveLock is sufficient, because we don't need to update the
	 * flat password file.
	 */
	rel = heap_openr(ShadowRelationName, RowExclusiveLock);
	oldtuple = SearchSysCache(SHADOWNAME,
							  PointerGetDatum(stmt->user),
							  0, 0, 0);
	if (!HeapTupleIsValid(oldtuple))
		ereport(ERROR,
				(errcode(ERRCODE_UNDEFINED_OBJECT),
				 errmsg("user \"%s\" does not exist", stmt->user)));

	if (!(superuser()
	 || ((Form_pg_shadow) GETSTRUCT(oldtuple))->usesysid == GetUserId()))
		ereport(ERROR,
				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
				 errmsg("permission denied")));

	for (i = 0; i < Natts_pg_shadow; i++)
		repl_repl[i] = ' ';

	repl_repl[Anum_pg_shadow_useconfig - 1] = 'r';
	if (strcmp(stmt->variable, "all") == 0 && valuestr == NULL)
	{
		/* RESET ALL */
		repl_null[Anum_pg_shadow_useconfig - 1] = 'n';
	}
	else
	{
		Datum		datum;
		bool		isnull;
		ArrayType  *array;

		repl_null[Anum_pg_shadow_useconfig - 1] = ' ';

		datum = SysCacheGetAttr(SHADOWNAME, oldtuple,
								Anum_pg_shadow_useconfig, &isnull);

		array = isnull ? ((ArrayType *) NULL) : DatumGetArrayTypeP(datum);

		if (valuestr)
			array = GUCArrayAdd(array, stmt->variable, valuestr);
		else
			array = GUCArrayDelete(array, stmt->variable);

		if (array)
			repl_val[Anum_pg_shadow_useconfig - 1] = PointerGetDatum(array);
		else
			repl_null[Anum_pg_shadow_useconfig - 1] = 'n';
	}

	newtuple = heap_modifytuple(oldtuple, rel, repl_val, repl_null, repl_repl);
	simple_heap_update(rel, &oldtuple->t_self, newtuple);

	CatalogUpdateIndexes(rel, newtuple);

	ReleaseSysCache(oldtuple);
	heap_close(rel, RowExclusiveLock);
}



/*
 * DROP USER
 */
void
DropUser(DropUserStmt *stmt)
{
	Relation	pg_shadow_rel;
	TupleDesc	pg_shadow_dsc;
	List	   *item;

	if (!superuser())
		ereport(ERROR,
				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
				 errmsg("must be superuser to drop users")));

	/*
	 * Scan the pg_shadow relation to find the usesysid of the user to be
	 * deleted.  Note we secure exclusive lock, because we need to protect
	 * our update of the flat password file.
	 */
	pg_shadow_rel = heap_openr(ShadowRelationName, ExclusiveLock);
	pg_shadow_dsc = RelationGetDescr(pg_shadow_rel);

	foreach(item, stmt->users)
	{
		const char *user = strVal(lfirst(item));
		HeapTuple	tuple,
					tmp_tuple;
		Relation	pg_rel;
		TupleDesc	pg_dsc;
		ScanKeyData scankey;
		HeapScanDesc scan;
		AclId		usesysid;

		tuple = SearchSysCache(SHADOWNAME,
							   PointerGetDatum(user),
							   0, 0, 0);
		if (!HeapTupleIsValid(tuple))
			ereport(ERROR,
					(errcode(ERRCODE_UNDEFINED_OBJECT),
					 errmsg("user \"%s\" does not exist", user)));

		usesysid = ((Form_pg_shadow) GETSTRUCT(tuple))->usesysid;

		if (usesysid == GetUserId())
			ereport(ERROR,
					(errcode(ERRCODE_OBJECT_IN_USE),
					 errmsg("current user cannot be dropped")));
		if (usesysid == GetSessionUserId())
			ereport(ERROR,
					(errcode(ERRCODE_OBJECT_IN_USE),
					 errmsg("session user cannot be dropped")));

		/*
		 * Check if user still owns a database. If so, error out.
		 *
		 * (It used to be that this function would drop the database
		 * automatically. This is not only very dangerous for people that
		 * don't read the manual, it doesn't seem to be the behaviour one
		 * would expect either.) -- petere 2000/01/14)
		 */
		pg_rel = heap_openr(DatabaseRelationName, AccessShareLock);
		pg_dsc = RelationGetDescr(pg_rel);

		ScanKeyEntryInitialize(&scankey, 0x0,
							   Anum_pg_database_datdba, F_INT4EQ,
							   Int32GetDatum(usesysid));

		scan = heap_beginscan(pg_rel, SnapshotNow, 1, &scankey);

		if ((tmp_tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
		{
			char	   *dbname;

			dbname = NameStr(((Form_pg_database) GETSTRUCT(tmp_tuple))->datname);
			ereport(ERROR,
					(errcode(ERRCODE_OBJECT_IN_USE),
					 errmsg("user \"%s\" cannot be dropped", user),
				   errdetail("The user owns database \"%s\".", dbname)));
		}

		heap_endscan(scan);
		heap_close(pg_rel, AccessShareLock);

		/*
		 * Somehow we'd have to check for tables, views, etc. owned by the
		 * user as well, but those could be spread out over all sorts of
		 * databases which we don't have access to (easily).
		 */

		/*
		 * Remove the user from the pg_shadow table
		 */
		simple_heap_delete(pg_shadow_rel, &tuple->t_self);

		ReleaseSysCache(tuple);

		/*
		 * Remove user from groups
		 *
		 * try calling alter group drop user for every group
		 */
		pg_rel = heap_openr(GroupRelationName, ExclusiveLock);
		pg_dsc = RelationGetDescr(pg_rel);
		scan = heap_beginscan(pg_rel, SnapshotNow, 0, NULL);
		while ((tmp_tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
		{
			AlterGroupStmt ags;

			/* the group name from which to try to drop the user: */
			ags.name = pstrdup(NameStr(((Form_pg_group) GETSTRUCT(tmp_tuple))->groname));
			ags.action = -1;
			ags.listUsers = makeList1(makeInteger(usesysid));
			AlterGroup(&ags, "DROP USER");
		}
		heap_endscan(scan);
		heap_close(pg_rel, ExclusiveLock);

		/*
		 * Advance command counter so that later iterations of this loop
		 * will see the changes already made.  This is essential if, for
		 * example, we are trying to drop two users who are members of the
		 * same group --- the AlterGroup for the second user had better
		 * see the tuple updated from the first one.
		 */
		CommandCounterIncrement();
	}

	/*
	 * Now we can clean up; but keep lock until commit (to avoid possible
	 * deadlock when commit code tries to acquire lock).
	 */
	heap_close(pg_shadow_rel, NoLock);

	/*
	 * Set flag to update flat password file at commit.
	 */
	user_file_update_needed = true;
}


/*
 * Rename user
 */
void
RenameUser(const char *oldname, const char *newname)
{
	HeapTuple	tup;
	Relation	rel;

	/* ExclusiveLock because we need to update the password file */
	rel = heap_openr(ShadowRelationName, ExclusiveLock);

	tup = SearchSysCacheCopy(SHADOWNAME,
							 CStringGetDatum(oldname),
							 0, 0, 0);