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_shadow.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 <utils/syscache.h>
34 #include <commands/user.h>
36 static void CheckPgUserAclNotNull(void);
38 /*---------------------------------------------------------------------
41 * copy the modified contents of pg_shadow to a file used by the postmaster
42 * for user authentication. The file is stored as $PGDATA/pg_pwd.
43 *---------------------------------------------------------------------
46 void UpdatePgPwdFile(char* sql) {
51 /* Create a temporary filename to be renamed later. This prevents the
52 * backend from clobbering the pg_pwd file while the postmaster might be
55 filename = crypt_getpwdfilename();
56 tempname = (char*)malloc(strlen(filename) + 12);
57 sprintf(tempname, "%s.%d", filename, MyProcPid);
59 /* Copy the contents of pg_shadow to the pg_pwd ASCII file using a the SEPCHAR
60 * character as the delimiter between fields. Then rename the file to its
63 sprintf(sql, "copy %s to '%s' using delimiters %s", ShadowRelationName, tempname, CRYPT_PWD_FILE_SEPCHAR);
64 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
65 rename(tempname, filename);
66 free((void*)tempname);
68 /* Create a flag file the postmaster will detect the next time it tries to
69 * authenticate a user. The postmaster will know to reload the pg_pwd file
72 filename = crypt_getpwdreloadfilename();
73 creat(filename, S_IRUSR | S_IWUSR);
76 /*---------------------------------------------------------------------
79 * Add the user to the pg_shadow relation, and if specified make sure the
80 * user is specified in the desired groups of defined in pg_group.
81 *---------------------------------------------------------------------
83 void DefineUser(CreateUserStmt *stmt) {
86 Relation pg_shadow_rel;
87 TupleDesc pg_shadow_dsc;
100 CheckPgUserAclNotNull();
101 if (!(inblock = IsTransactionBlock()))
102 BeginTransactionBlock();
104 /* Make sure the user attempting to create a user can insert into the pg_shadow
107 pg_user = GetPgUserName();
108 if (pg_aclcheck(ShadowRelationName, pg_user, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK) {
109 UserAbortTransactionBlock();
110 elog(ERROR, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
111 pg_user, ShadowRelationName);
115 /* Scan the pg_shadow relation to be certain the user doesn't already exist.
117 pg_shadow_rel = heap_openr(ShadowRelationName);
118 pg_shadow_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
119 /* Secure a write lock on pg_shadow so we can be sure of what the next usesysid
122 RelationSetLockForWrite(pg_shadow_rel);
124 scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
125 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
126 datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_shadow_dsc, &n);
128 if (!exists && !strncmp((char*)datum, stmt->user, strlen(stmt->user)))
131 datum = heap_getattr(tuple, Anum_pg_shadow_usesysid, pg_shadow_dsc, &n);
132 if ((int)datum > max_id)
135 ReleaseBuffer(buffer);
140 RelationUnsetLockForWrite(pg_shadow_rel);
141 heap_close(pg_shadow_rel);
142 UserAbortTransactionBlock();
143 elog(ERROR, "defineUser: user \"%s\" has already been created", stmt->user);
147 /* Build the insert statment to be executed.
149 sprintf(sql, "insert into %s(usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd,passwd", ShadowRelationName);
150 /* if (stmt->password)
151 strcat(sql, ",passwd"); -- removed so that insert empty string when no password */
152 if (stmt->validUntil)
153 strcat(sql, ",valuntil");
155 sql_end = sql + strlen(sql);
156 sprintf(sql_end, ") values('%s',%d", stmt->user, max_id + 1);
157 if (stmt->createdb && *stmt->createdb)
158 strcat(sql_end, ",'t','t'");
160 strcat(sql_end, ",'f','t'");
161 if (stmt->createuser && *stmt->createuser)
162 strcat(sql_end, ",'t','t'");
164 strcat(sql_end, ",'f','t'");
165 sql_end += strlen(sql_end);
166 if (stmt->password) {
167 sprintf(sql_end, ",'%s'", stmt->password);
168 sql_end += strlen(sql_end);
170 strcpy(sql_end, ",''");
171 sql_end += strlen(sql_end);
173 if (stmt->validUntil) {
174 sprintf(sql_end, ",'%s'", stmt->validUntil);
175 sql_end += strlen(sql_end);
177 strcat(sql_end, ")");
179 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
181 /* Add the stuff here for groups.
184 UpdatePgPwdFile(sql);
186 /* This goes after the UpdatePgPwdFile to be certain that two backends to not
187 * attempt to write to the pg_pwd file at the same time.
189 RelationUnsetLockForWrite(pg_shadow_rel);
190 heap_close(pg_shadow_rel);
192 if (IsTransactionBlock() && !inblock)
193 EndTransactionBlock();
197 extern void AlterUser(AlterUserStmt *stmt) {
200 Relation pg_shadow_rel;
201 TupleDesc pg_shadow_dsc;
213 CheckPgUserAclNotNull();
214 if (!(inblock = IsTransactionBlock()))
215 BeginTransactionBlock();
217 /* Make sure the user attempting to create a user can insert into the pg_shadow
220 pg_user = GetPgUserName();
221 if (pg_aclcheck(ShadowRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
222 UserAbortTransactionBlock();
223 elog(ERROR, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
224 pg_user, ShadowRelationName);
228 /* Scan the pg_shadow relation to be certain the user exists.
230 pg_shadow_rel = heap_openr(ShadowRelationName);
231 pg_shadow_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
232 /* Secure a write lock on pg_shadow so we can be sure that when the dump of
233 * the pg_pwd file is done, there is not another backend doing the same.
235 RelationSetLockForWrite(pg_shadow_rel);
237 scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
238 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
239 datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_shadow_dsc, &n);
241 if (!strncmp((char*)datum, stmt->user, strlen(stmt->user))) {
243 ReleaseBuffer(buffer);
250 RelationUnsetLockForWrite(pg_shadow_rel);
251 heap_close(pg_shadow_rel);
252 UserAbortTransactionBlock();
253 elog(ERROR, "alterUser: user \"%s\" does not exist", stmt->user);
257 /* Create the update statement to modify the user.
259 sprintf(sql, "update %s set", ShadowRelationName);
261 if (stmt->password) {
262 sql_end += strlen(sql_end);
263 sprintf(sql_end, " passwd = '%s'", stmt->password);
265 if (stmt->createdb) {
267 strcat(sql_end, ",");
268 sql_end += strlen(sql_end);
270 strcat(sql_end, " usecreatedb = 't'");
272 strcat(sql_end, " usecreatedb = 'f'");
274 if (stmt->createuser) {
276 strcat(sql_end, ",");
277 sql_end += strlen(sql_end);
278 if (*stmt->createuser)
279 strcat(sql_end, " usesuper = 't'");
281 strcat(sql_end, " usesuper = 'f'");
283 if (stmt->validUntil) {
285 strcat(sql_end, ",");
286 sql_end += strlen(sql_end);
287 sprintf(sql_end, " valuntil = '%s'", stmt->validUntil);
289 if (sql_end != sql) {
290 sql_end += strlen(sql_end);
291 sprintf(sql_end, " where usename = '%s'", stmt->user);
292 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
295 /* do the pg_group stuff here */
297 UpdatePgPwdFile(sql);
299 RelationUnsetLockForWrite(pg_shadow_rel);
300 heap_close(pg_shadow_rel);
302 if (IsTransactionBlock() && !inblock)
303 EndTransactionBlock();
307 extern void RemoveUser(char* user) {
310 Relation pg_shadow_rel,
324 if (!(inblock = IsTransactionBlock()))
325 BeginTransactionBlock();
327 /* Make sure the user attempting to create a user can delete from the pg_shadow
330 pg_user = GetPgUserName();
331 if (pg_aclcheck(ShadowRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
332 UserAbortTransactionBlock();
333 elog(ERROR, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
334 pg_user, ShadowRelationName);
338 /* Perform a scan of the pg_shadow relation to find the usesysid of the user to
339 * be deleted. If it is not found, then return a warning message.
341 pg_shadow_rel = heap_openr(ShadowRelationName);
342 pg_dsc = RelationGetTupleDescriptor(pg_shadow_rel);
343 /* Secure a write lock on pg_shadow so we can be sure that when the dump of
344 * the pg_pwd file is done, there is not another backend doing the same.
346 RelationSetLockForWrite(pg_shadow_rel);
348 scan = heap_beginscan(pg_shadow_rel, false, false, 0, NULL);
349 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
350 datum = heap_getattr(tuple, Anum_pg_shadow_usename, pg_dsc, &n);
352 if (!strncmp((char*)datum, user, strlen(user))) {
353 usesysid = (int)heap_getattr(tuple, Anum_pg_shadow_usesysid, pg_dsc, &n);
354 ReleaseBuffer(buffer);
357 ReleaseBuffer(buffer);
361 if (usesysid == -1) {
362 RelationUnsetLockForWrite(pg_shadow_rel);
363 heap_close(pg_shadow_rel);
364 UserAbortTransactionBlock();
365 elog(ERROR, "removeUser: user \"%s\" does not exist", user);
369 /* Perform a scan of the pg_database relation to find the databases owned by
370 * usesysid. Then drop them.
372 pg_rel = heap_openr(DatabaseRelationName);
373 pg_dsc = RelationGetTupleDescriptor(pg_rel);
375 scan = heap_beginscan(pg_rel, false, false, 0, NULL);
376 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
377 datum = heap_getattr(tuple, Anum_pg_database_datdba, pg_dsc, &n);
379 if ((int)datum == usesysid) {
380 datum = heap_getattr(tuple, Anum_pg_database_datname, pg_dsc, &n);
381 if (memcmp((void*)datum, "template1", 9)) {
382 dbase = (char**)realloc((void*)dbase, sizeof(char*) * (ndbase + 1));
383 dbase[ndbase] = (char*)malloc(NAMEDATALEN + 1);
384 memcpy((void*)dbase[ndbase], (void*)datum, NAMEDATALEN);
385 dbase[ndbase++][NAMEDATALEN] = '\0';
388 ReleaseBuffer(buffer);
394 elog(NOTICE, "Dropping database %s", dbase[ndbase]);
395 sprintf(sql, "drop database %s", dbase[ndbase]);
396 free((void*)dbase[ndbase]);
397 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
402 /* Since pg_shadow is global over all databases, one of two things must be done
403 * to insure complete consistency. First, pg_shadow could be made non-global.
404 * This would elminate the code above for deleting database and would require
405 * the addition of code to delete tables, views, etc owned by the user.
407 * The second option would be to create a means of deleting tables, view,
408 * etc. owned by the user from other databases. Pg_user is global and so
409 * this must be done at some point.
411 * Let us not forget that the user should be removed from the pg_groups also.
413 * Todd A. Brandys 11/18/1997
417 /* Remove the user from the pg_shadow table
419 sprintf(sql, "delete from %s where usename = '%s'", ShadowRelationName, user);
420 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
422 UpdatePgPwdFile(sql);
424 RelationUnsetLockForWrite(pg_shadow_rel);
425 heap_close(pg_shadow_rel);
427 if (IsTransactionBlock() && !inblock)
428 EndTransactionBlock();
432 * CheckPgUserAclNotNull
434 * check to see if there is an ACL on pg_shadow
436 static void CheckPgUserAclNotNull()
440 htp = SearchSysCacheTuple(RELNAME, PointerGetDatum(ShadowRelationName),
442 if (!HeapTupleIsValid(htp))
444 elog(ERROR, "IsPgUserAclNull: class \"%s\" not found",
448 if (heap_attisnull(htp, Anum_pg_class_relacl))
450 elog(NOTICE, "To use passwords, you have to revoke permissions on pg_shadow");
451 elog(NOTICE, "so normal users can not read the passwords.");
452 elog(ERROR, "Try 'REVOKE ALL ON pg_shadow FROM PUBLIC'");