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() */
17 #include <miscadmin.h>
18 #include <catalog/catname.h>
19 #include <catalog/pg_database.h>
20 #include <catalog/pg_user.h>
21 #include <libpq/crypt.h>
22 #include <access/heapam.h>
23 #include <access/xact.h>
24 #include <storage/bufmgr.h>
25 #include <storage/lmgr.h>
26 #include <tcop/tcopprot.h>
27 #include <utils/acl.h>
28 #include <utils/palloc.h>
29 #include <utils/rel.h>
30 #include <commands/user.h>
32 /*---------------------------------------------------------------------
35 * copy the modified contents of pg_user to a file used by the postmaster
36 * for user authentication. The file is stored as $PGDATA/pg_pwd.
37 *---------------------------------------------------------------------
40 void UpdatePgPwdFile(char* sql) {
44 filename = crypt_getpwdfilename();
45 sprintf(sql, "copy %s to '%s' using delimiters '#'", UserRelationName, filename);
46 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
49 /*---------------------------------------------------------------------
52 * Add the user to the pg_user relation, and if specified make sure the
53 * user is specified in the desired groups of defined in pg_group.
54 *---------------------------------------------------------------------
56 void DefineUser(CreateUserStmt *stmt) {
60 TupleDesc pg_user_dsc;
72 if (!(inblock = IsTransactionBlock()))
73 BeginTransactionBlock();
75 /* Make sure the user attempting to create a user can insert into the pg_user
78 pg_user = GetPgUserName();
79 if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR | ACL_AP) != ACLCHECK_OK) {
80 UserAbortTransactionBlock();
81 elog(WARN, "defineUser: user \"%s\" does not have SELECT and INSERT privilege for \"%s\"",
82 pg_user, UserRelationName);
86 /* Scan the pg_user relation to be certain the user doesn't already exist.
88 pg_user_rel = heap_openr(UserRelationName);
89 pg_user_dsc = RelationGetTupleDescriptor(pg_user_rel);
90 /* Secure a write lock on pg_user so we can be sure of what the next usesysid
93 RelationSetLockForWrite(pg_user_rel);
95 scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
96 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
97 datum = heap_getattr(tuple, buffer, Anum_pg_user_usename, pg_user_dsc, &n);
99 if (!exists && !strncmp((char*)datum, stmt->user, strlen(stmt->user)))
102 datum = heap_getattr(tuple, buffer, Anum_pg_user_usesysid, pg_user_dsc, &n);
103 if ((int)datum > max_id)
106 ReleaseBuffer(buffer);
111 RelationUnsetLockForWrite(pg_user_rel);
112 heap_close(pg_user_rel);
113 UserAbortTransactionBlock();
114 elog(WARN, "defineUser: user \"%s\" has already been created", stmt->user);
118 /* Build the insert statment to be executed.
120 sprintf(sql, "insert into %s(usename,usesysid,usecreatedb,usetrace,usesuper,usecatupd,passwd", UserRelationName);
121 /* if (stmt->password)
122 strcat(sql, ",passwd"); -- removed so that insert empty string when no password */
123 if (stmt->validUntil)
124 strcat(sql, ",valuntil");
126 sql_end = sql + strlen(sql);
127 sprintf(sql_end, ") values('%s',%d", stmt->user, max_id + 1);
128 if (stmt->createdb && *stmt->createdb)
129 strcat(sql_end, ",'t','t'");
131 strcat(sql_end, ",'f','t'");
132 if (stmt->createuser && *stmt->createuser)
133 strcat(sql_end, ",'t','t'");
135 strcat(sql_end, ",'f','t'");
136 sql_end += strlen(sql_end);
137 if (stmt->password) {
138 sprintf(sql_end, ",'%s'", stmt->password);
139 sql_end += strlen(sql_end);
141 strcpy(sql_end, ",''");
142 sql_end += strlen(sql_end);
144 if (stmt->validUntil) {
145 sprintf(sql_end, ",'%s'", stmt->validUntil);
146 sql_end += strlen(sql_end);
148 strcat(sql_end, ")");
150 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
152 /* Add the stuff here for groups.
155 UpdatePgPwdFile(sql);
157 /* This goes after the UpdatePgPwdFile to be certain that two backends to not
158 * attempt to write to the pg_pwd file at the same time.
160 RelationUnsetLockForWrite(pg_user_rel);
161 heap_close(pg_user_rel);
163 if (IsTransactionBlock() && !inblock)
164 EndTransactionBlock();
168 extern void AlterUser(AlterUserStmt *stmt) {
171 Relation pg_user_rel;
172 TupleDesc pg_user_dsc;
183 if (!(inblock = IsTransactionBlock()))
184 BeginTransactionBlock();
186 /* Make sure the user attempting to create a user can insert into the pg_user
189 pg_user = GetPgUserName();
190 if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
191 UserAbortTransactionBlock();
192 elog(WARN, "alterUser: user \"%s\" does not have SELECT and UPDATE privilege for \"%s\"",
193 pg_user, UserRelationName);
197 /* Scan the pg_user relation to be certain the user exists.
199 pg_user_rel = heap_openr(UserRelationName);
200 pg_user_dsc = RelationGetTupleDescriptor(pg_user_rel);
201 /* Secure a write lock on pg_user so we can be sure that when the dump of
202 * the pg_pwd file is done, there is not another backend doing the same.
204 RelationSetLockForWrite(pg_user_rel);
206 scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
207 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
208 datum = heap_getattr(tuple, buffer, Anum_pg_user_usename, pg_user_dsc, &n);
210 if (!strncmp((char*)datum, stmt->user, strlen(stmt->user))) {
212 ReleaseBuffer(buffer);
219 RelationUnsetLockForWrite(pg_user_rel);
220 heap_close(pg_user_rel);
221 UserAbortTransactionBlock();
222 elog(WARN, "alterUser: user \"%s\" does not exist", stmt->user);
226 /* Create the update statement to modify the user.
228 sprintf(sql, "update %s set", UserRelationName);
230 if (stmt->password) {
231 sql_end += strlen(sql_end);
232 sprintf(sql_end, " passwd = '%s'", stmt->password);
234 if (stmt->createdb) {
236 strcat(sql_end, ",");
237 sql_end += strlen(sql_end);
239 strcat(sql_end, " usecreatedb = 't'");
241 strcat(sql_end, " usecreatedb = 'f'");
243 if (stmt->createuser) {
245 strcat(sql_end, ",");
246 sql_end += strlen(sql_end);
247 if (*stmt->createuser)
248 strcat(sql_end, " usesuper = 't'");
250 strcat(sql_end, " usesuper = 'f'");
252 if (stmt->validUntil) {
254 strcat(sql_end, ",");
255 sql_end += strlen(sql_end);
256 sprintf(sql_end, " valuntil = '%s'", stmt->validUntil);
258 if (sql_end != sql) {
259 sql_end += strlen(sql_end);
260 sprintf(sql_end, " where usename = '%s'", stmt->user);
261 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
264 /* do the pg_group stuff here */
266 UpdatePgPwdFile(sql);
268 RelationUnsetLockForWrite(pg_user_rel);
269 heap_close(pg_user_rel);
271 if (IsTransactionBlock() && !inblock)
272 EndTransactionBlock();
276 extern void RemoveUser(char* user) {
279 Relation pg_user_rel,
293 if (!(inblock = IsTransactionBlock()))
294 BeginTransactionBlock();
296 /* Make sure the user attempting to create a user can delete from the pg_user
299 pg_user = GetPgUserName();
300 if (pg_aclcheck(UserRelationName, pg_user, ACL_RD | ACL_WR) != ACLCHECK_OK) {
301 UserAbortTransactionBlock();
302 elog(WARN, "removeUser: user \"%s\" does not have SELECT and DELETE privilege for \"%s\"",
303 pg_user, UserRelationName);
307 /* Perform a scan of the pg_user relation to find the usesysid of the user to
308 * be deleted. If it is not found, then return a warning message.
310 pg_user_rel = heap_openr(UserRelationName);
311 pg_dsc = RelationGetTupleDescriptor(pg_user_rel);
312 /* Secure a write lock on pg_user so we can be sure that when the dump of
313 * the pg_pwd file is done, there is not another backend doing the same.
315 RelationSetLockForWrite(pg_user_rel);
317 scan = heap_beginscan(pg_user_rel, false, false, 0, NULL);
318 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
319 datum = heap_getattr(tuple, buffer, Anum_pg_user_usename, pg_dsc, &n);
321 if (!strncmp((char*)datum, user, strlen(user))) {
322 usesysid = (int)heap_getattr(tuple, buffer, Anum_pg_user_usesysid, pg_dsc, &n);
323 ReleaseBuffer(buffer);
326 ReleaseBuffer(buffer);
330 if (usesysid == -1) {
331 RelationUnsetLockForWrite(pg_user_rel);
332 heap_close(pg_user_rel);
333 UserAbortTransactionBlock();
334 elog(WARN, "removeUser: user \"%s\" does not exist", user);
338 /* Perform a scan of the pg_database relation to find the databases owned by
339 * usesysid. Then drop them.
341 pg_rel = heap_openr(DatabaseRelationName);
342 pg_dsc = RelationGetTupleDescriptor(pg_rel);
344 scan = heap_beginscan(pg_rel, false, false, 0, NULL);
345 while (HeapTupleIsValid(tuple = heap_getnext(scan, 0, &buffer))) {
346 datum = heap_getattr(tuple, buffer, Anum_pg_database_datdba, pg_dsc, &n);
348 if ((int)datum == usesysid) {
349 datum = heap_getattr(tuple, buffer, Anum_pg_database_datname, pg_dsc, &n);
350 if (memcmp((void*)datum, "template1", 9)) {
351 dbase = (char**)repalloc((void*)dbase, sizeof(char*) * (ndbase + 1));
352 dbase[ndbase] = (char*)palloc(NAMEDATALEN + 1);
353 memcpy((void*)dbase[ndbase], (void*)datum, NAMEDATALEN);
354 dbase[ndbase++][NAMEDATALEN] = '\0';
357 ReleaseBuffer(buffer);
363 elog(NOTICE, "Dropping database %s", dbase[ndbase]);
364 sprintf(sql, "drop database %s", dbase[ndbase]);
365 pfree((void*)dbase[ndbase]);
366 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
371 /* Since pg_user is global over all databases, one of two things must be done
372 * to insure complete consistency. First, pg_user could be made non-global.
373 * This would elminate the code above for deleting database and would require
374 * the addition of code to delete tables, views, etc owned by the user.
376 * The second option would be to create a means of deleting tables, view,
377 * etc. owned by the user from other databases. Pg_user is global and so
378 * this must be done at some point.
380 * Let us not forget that the user should be removed from the pg_groups also.
382 * Todd A. Brandys 11/18/1997
386 /* Remove the user from the pg_user table
388 sprintf(sql, "delete from %s where usename = '%s'", UserRelationName, user);
389 pg_exec_query(sql, (char**)NULL, (Oid*)NULL, 0);
391 UpdatePgPwdFile(sql);
393 RelationUnsetLockForWrite(pg_user_rel);
394 heap_close(pg_user_rel);
396 if (IsTransactionBlock() && !inblock)
397 EndTransactionBlock();