1 /*-------------------------------------------------------------------------
4 * use pg_exec_query to create a new user in the catalog
6 * Copyright (c) 1994, Regents of the University of California
10 *-------------------------------------------------------------------------
12 #include <stdio.h> /* for sprintf() */
14 #include <sys/types.h>
21 #include <miscadmin.h>
22 #include <catalog/catname.h>
23 #include <catalog/pg_database.h>
24 #include <catalog/pg_user.h>
25 #include <libpq/crypt.h>
26 #include <access/heapam.h>
27 #include <access/xact.h>
28 #include <storage/bufmgr.h>
29 #include <storage/lmgr.h>
30 #include <tcop/tcopprot.h>
31 #include <utils/acl.h>
32 #include <utils/rel.h>
33 #include <commands/user.h>
35 /*---------------------------------------------------------------------
38 * copy the modified contents of pg_user to a file used by the postmaster
39 * for user authentication. The file is stored as $PGDATA/pg_pwd.
40 *---------------------------------------------------------------------
43 void UpdatePgPwdFile(char* sql) {
48 /* Create a temporary filename to be renamed later. This prevents the
49 * backend from clobbering the pg_pwd file while the postmaster might be
52 filename = crypt_getpwdfilename();
53 tempname = (char*)malloc(strlen(filename) + 12);
54 sprintf(tempname, "%s.%d", filename, MyProcPid);
56 /* Copy the contents of pg_user to the pg_pwd ASCII file using a the SEPCHAR
57 * character as the delimiter between fields. Then rename the file to its
60 sprintf(sql, "copy %s to '%s' using delimiters %s", UserRelationName, tempname, CRYPT_PWD_FILE_SEPCHAR);
61 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
62 rename(tempname, filename);
63 free((void*)tempname);
65 /* Create a flag file the postmaster will detect the next time it tries to
66 * authenticate a user. The postmaster will know to reload the pg_pwd file
69 filename = crypt_getpwdreloadfilename();
70 creat(filename, S_IRUSR | S_IWUSR);
73 /*---------------------------------------------------------------------
76 * Add the user to the pg_user relation, and if specified make sure the
77 * user is specified in the desired groups of defined in pg_group.
78 *---------------------------------------------------------------------
80 void DefineUser(CreateUserStmt *stmt) {
84 TupleDesc pg_user_dsc;
96 if (!(inblock = IsTransactionBlock()))
97 BeginTransactionBlock();
99 /* Make sure the user attempting to create a user can insert into the pg_user
102 pg_user = GetPgUserName();
103 if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK) {
104 UserAbortTransactionBlock();
105 elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
106 pg_user, UserRelationName);
110 /* Scan the pg_user relation to be certain the user doesn't already exist.
112 pg_user_rel = heap_openr(UserRelationName);
113 pg_user_dsc = RelationGetTupleDescriptor(pg_user_rel);
114 /* Secure a write lock on pg_user so we can be sure of what the next usesysid
117 RelationSetLockForWrite(pg_user_rel);
119 scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
120 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
121 datum = heap_getattr(tuple, Anum_pg_user_usename, pg_user_dsc, &n);
123 if (!exists && !strncmp((char*)datum, stmt->user, strlen(stmt->user)))
126 datum = heap_getattr(tuple, Anum_pg_user_usesysid, pg_user_dsc, &n);
127 if ((int)datum > max_id)
130 ReleaseBuffer(buffer);
135 RelationUnsetLockForWrite(pg_user_rel);
136 heap_close(pg_user_rel);
137 UserAbortTransactionBlock();
138 elog(ERROR, "defineUser: user \"%s\" has already been created", stmt->user);
142 /* Build the insert statment to be executed.
144 sprintf(sql, "insert into %s(usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd,passwd", UserRelationName);
145 /* if (stmt->password)
146 strcat(sql, ",passwd"); -- removed so that insert empty string when no password */
147 if (stmt->validUntil)
148 strcat(sql, ",valuntil");
150 sql_end = sql + strlen(sql);
151 sprintf(sql_end, ") values('%s',%d", stmt->user, max_id + 1);
152 if (stmt->createdb && *stmt->createdb)
153 strcat(sql_end, ",'t','t'");
155 strcat(sql_end, ",'f','t'");
156 if (stmt->createuser && *stmt->createuser)
157 strcat(sql_end, ",'t','t'");
159 strcat(sql_end, ",'f','t'");
160 sql_end += strlen(sql_end);
161 if (stmt->password) {
162 sprintf(sql_end, ",'%s'", stmt->password);
163 sql_end += strlen(sql_end);
165 strcpy(sql_end, ",''");
166 sql_end += strlen(sql_end);
168 if (stmt->validUntil) {
169 sprintf(sql_end, ",'%s'", stmt->validUntil);
170 sql_end += strlen(sql_end);
172 strcat(sql_end, ")");
174 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
176 /* Add the stuff here for groups.
179 UpdatePgPwdFile(sql);
181 /* This goes after the UpdatePgPwdFile to be certain that two backends to not
182 * attempt to write to the pg_pwd file at the same time.
184 RelationUnsetLockForWrite(pg_user_rel);
185 heap_close(pg_user_rel);
187 if (IsTransactionBlock() && !inblock)
188 EndTransactionBlock();
192 extern void AlterUser(AlterUserStmt *stmt) {
195 Relation pg_user_rel;
196 TupleDesc pg_user_dsc;
207 if (!(inblock = IsTransactionBlock()))
208 BeginTransactionBlock();
210 /* Make sure the user attempting to create a user can insert into the pg_user
213 pg_user = GetPgUserName();
214 if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
215 UserAbortTransactionBlock();
216 elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
217 pg_user, UserRelationName);
221 /* Scan the pg_user relation to be certain the user exists.
223 pg_user_rel = heap_openr(UserRelationName);
224 pg_user_dsc = RelationGetTupleDescriptor(pg_user_rel);
225 /* Secure a write lock on pg_user so we can be sure that when the dump of
226 * the pg_pwd file is done, there is not another backend doing the same.
228 RelationSetLockForWrite(pg_user_rel);
230 scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
231 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
232 datum = heap_getattr(tuple, Anum_pg_user_usename, pg_user_dsc, &n);
234 if (!strncmp((char*)datum, stmt->user, strlen(stmt->user))) {
236 ReleaseBuffer(buffer);
243 RelationUnsetLockForWrite(pg_user_rel);
244 heap_close(pg_user_rel);
245 UserAbortTransactionBlock();
246 elog(ERROR, "alterUser: user \"%s\" does not exist", stmt->user);
250 /* Create the update statement to modify the user.
252 sprintf(sql, "update %s set", UserRelationName);
254 if (stmt->password) {
255 sql_end += strlen(sql_end);
256 sprintf(sql_end, " passwd = '%s'", stmt->password);
258 if (stmt->createdb) {
260 strcat(sql_end, ",");
261 sql_end += strlen(sql_end);
263 strcat(sql_end, " usecreatedb = 't'");
265 strcat(sql_end, " usecreatedb = 'f'");
267 if (stmt->createuser) {
269 strcat(sql_end, ",");
270 sql_end += strlen(sql_end);
271 if (*stmt->createuser)
272 strcat(sql_end, " usesuper = 't'");
274 strcat(sql_end, " usesuper = 'f'");
276 if (stmt->validUntil) {
278 strcat(sql_end, ",");
279 sql_end += strlen(sql_end);
280 sprintf(sql_end, " valuntil = '%s'", stmt->validUntil);
282 if (sql_end != sql) {
283 sql_end += strlen(sql_end);
284 sprintf(sql_end, " where usename = '%s'", stmt->user);
285 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
288 /* do the pg_group stuff here */
290 UpdatePgPwdFile(sql);
292 RelationUnsetLockForWrite(pg_user_rel);
293 heap_close(pg_user_rel);
295 if (IsTransactionBlock() && !inblock)
296 EndTransactionBlock();
300 extern void RemoveUser(char* user) {
303 Relation pg_user_rel,
317 if (!(inblock = IsTransactionBlock()))
318 BeginTransactionBlock();
320 /* Make sure the user attempting to create a user can delete from the pg_user
323 pg_user = GetPgUserName();
324 if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
325 UserAbortTransactionBlock();
326 elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
327 pg_user, UserRelationName);
331 /* Perform a scan of the pg_user relation to find the usesysid of the user to
332 * be deleted. If it is not found, then return a warning message.
334 pg_user_rel = heap_openr(UserRelationName);
335 pg_dsc = RelationGetTupleDescriptor(pg_user_rel);
336 /* Secure a write lock on pg_user so we can be sure that when the dump of
337 * the pg_pwd file is done, there is not another backend doing the same.
339 RelationSetLockForWrite(pg_user_rel);
341 scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
342 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
343 datum = heap_getattr(tuple, Anum_pg_user_usename, pg_dsc, &n);
345 if (!strncmp((char*)datum, user, strlen(user))) {
346 usesysid = (int)heap_getattr(tuple, Anum_pg_user_usesysid, pg_dsc, &n);
347 ReleaseBuffer(buffer);
350 ReleaseBuffer(buffer);
354 if (usesysid == -1) {
355 RelationUnsetLockForWrite(pg_user_rel);
356 heap_close(pg_user_rel);
357 UserAbortTransactionBlock();
358 elog(ERROR, "removeUser: user \"%s\" does not exist", user);
362 /* Perform a scan of the pg_database relation to find the databases owned by
363 * usesysid. Then drop them.
365 pg_rel = heap_openr(DatabaseRelationName);
366 pg_dsc = RelationGetTupleDescriptor(pg_rel);
368 scan = heap_beginscan(pg_rel, false, false, 0, NULL);
369 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
370 datum = heap_getattr(tuple, Anum_pg_database_datdba, pg_dsc, &n);
372 if ((int)datum == usesysid) {
373 datum = heap_getattr(tuple, Anum_pg_database_datname, pg_dsc, &n);
374 if (memcmp((void*)datum, "template1", 9)) {
375 dbase = (char**)realloc((void*)dbase, sizeof(char*) * (ndbase + 1));
376 dbase[ndbase] = (char*)malloc(NAMEDATALEN + 1);
377 memcpy((void*)dbase[ndbase], (void*)datum, NAMEDATALEN);
378 dbase[ndbase++][NAMEDATALEN] = '\0';
381 ReleaseBuffer(buffer);
387 elog(NOTICE, "Dropping database %s", dbase[ndbase]);
388 sprintf(sql, "drop database %s", dbase[ndbase]);
389 free((void*)dbase[ndbase]);
390 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
395 /* Since pg_user is global over all databases, one of two things must be done
396 * to insure complete consistency. First, pg_user could be made non-global.
397 * This would elminate the code above for deleting database and would require
398 * the addition of code to delete tables, views, etc owned by the user.
400 * The second option would be to create a means of deleting tables, view,
401 * etc. owned by the user from other databases. Pg_user is global and so
402 * this must be done at some point.
404 * Let us not forget that the user should be removed from the pg_groups also.
406 * Todd A. Brandys 11/18/1997
410 /* Remove the user from the pg_user table
412 sprintf(sql, "delete from %s where usename = '%s'", UserRelationName, user);
413 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
415 UpdatePgPwdFile(sql);
417 RelationUnsetLockForWrite(pg_user_rel);
418 heap_close(pg_user_rel);
420 if (IsTransactionBlock() && !inblock)
421 EndTransactionBlock();