1
/*-------------------------------------------------------------------------
4
* Commands for manipulating users and groups.
6
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
7
* Portions Copyright (c) 1994, Regents of the University of California
9
* $PostgreSQL: pgsql/src/backend/commands/user.c,v 1.147 2004-12-31 21:59:42 pgsql Exp $
11
*-------------------------------------------------------------------------
20
#include "access/heapam.h"
21
#include "catalog/catname.h"
22
#include "catalog/indexing.h"
23
#include "catalog/pg_database.h"
24
#include "catalog/pg_group.h"
25
#include "catalog/pg_shadow.h"
26
#include "catalog/pg_type.h"
27
#include "commands/user.h"
28
#include "libpq/crypt.h"
29
#include "miscadmin.h"
30
#include "storage/fd.h"
31
#include "storage/pmsignal.h"
32
#include "utils/acl.h"
33
#include "utils/array.h"
34
#include "utils/builtins.h"
35
#include "utils/fmgroids.h"
36
#include "utils/guc.h"
37
#include "utils/lsyscache.h"
38
#include "utils/syscache.h"
41
#define PWD_FILE "pg_pwd"
42
#define USER_GROUP_FILE "pg_group"
45
extern bool Password_encryption;
48
* The need-to-update-files flags are a pair of SubTransactionIds that show
49
* what level of the subtransaction tree requested the update. To register
50
* an update, the subtransaction saves its own SubTransactionId in the flag,
51
* unless the value was already set to a valid SubTransactionId (which implies
52
* that it or a parent level has already requested the same). If it aborts
53
* and the value is its SubTransactionId, it resets the flag to
54
* InvalidSubTransactionId. If it commits, it changes the value to its
55
* parent's SubTransactionId. This way the value is propagated up to the
56
* top-level transaction, which will update the files if a valid
57
* SubTransactionId is detected.
59
static SubTransactionId user_file_update_subid = InvalidSubTransactionId;
60
static SubTransactionId group_file_update_subid = InvalidSubTransactionId;
62
#define user_file_update_needed() \
64
if (user_file_update_subid == InvalidSubTransactionId) \
65
user_file_update_subid = GetCurrentSubTransactionId(); \
68
#define group_file_update_needed() \
70
if (group_file_update_subid == InvalidSubTransactionId) \
71
group_file_update_subid = GetCurrentSubTransactionId(); \
75
static void CheckPgUserAclNotNull(void);
76
static void UpdateGroupMembership(Relation group_rel, HeapTuple group_tuple,
78
static IdList *IdListToArray(List *members);
79
static List *IdArrayToList(IdList *oldarray);
85
* Outputs string in quotes, with double-quotes duplicated.
86
* We could use quote_ident(), but that expects a TEXT argument.
89
fputs_quote(char *str, FILE *fp)
104
* group_getfilename --- get full pathname of group file
106
* Note that result string is palloc'd, and should be freed by the caller.
109
group_getfilename(void)
114
bufsize = strlen(DataDir) + strlen("/global/") +
115
strlen(USER_GROUP_FILE) + 1;
116
pfnam = (char *) palloc(bufsize);
117
snprintf(pfnam, bufsize, "%s/global/%s", DataDir, USER_GROUP_FILE);
124
* Get full pathname of password file.
126
* Note that result string is palloc'd, and should be freed by the caller.
129
user_getfilename(void)
134
bufsize = strlen(DataDir) + strlen("/global/") +
135
strlen(PWD_FILE) + 1;
136
pfnam = (char *) palloc(bufsize);
137
snprintf(pfnam, bufsize, "%s/global/%s", DataDir, PWD_FILE);
144
* write_group_file: update the flat group file
147
write_group_file(Relation grel)
156
TupleDesc dsc = RelationGetDescr(grel);
159
* Create a temporary filename to be renamed later. This prevents the
160
* backend from clobbering the pg_group file while the postmaster
161
* might be reading from it.
163
filename = group_getfilename();
164
bufsize = strlen(filename) + 12;
165
tempname = (char *) palloc(bufsize);
166
snprintf(tempname, bufsize, "%s.%d", filename, MyProcPid);
168
oumask = umask((mode_t) 077);
169
fp = AllocateFile(tempname, "w");
173
(errcode_for_file_access(),
174
errmsg("could not write to temporary file \"%s\": %m", tempname)));
177
* Read pg_group and write the file. Note we use SnapshotSelf to
178
* ensure we see all effects of current transaction. (Perhaps could
179
* do a CommandCounterIncrement beforehand, instead?)
181
scan = heap_beginscan(grel, SnapshotSelf, 0, NULL);
182
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
194
bool first_user = true;
196
datum = heap_getattr(tuple, Anum_pg_group_groname, dsc, &isnull);
197
/* ignore NULL groupnames --- shouldn't happen */
200
groname = NameStr(*DatumGetName(datum));
203
* Check for invalid characters in the group name.
205
i = strcspn(groname, "\n");
206
if (groname[i] != '\0')
209
(errmsg("invalid group name \"%s\"", groname)));
213
grolist_datum = heap_getattr(tuple, Anum_pg_group_grolist, dsc, &isnull);
214
/* Ignore NULL group lists */
218
/* be sure the IdList is not toasted */
219
grolist_p = DatumGetIdListP(grolist_datum);
222
num = IDLIST_NUM(grolist_p);
223
aidp = IDLIST_DAT(grolist_p);
224
for (i = 0; i < num; ++i)
226
tuple = SearchSysCache(SHADOWSYSID,
227
PointerGetDatum(aidp[i]),
229
if (HeapTupleIsValid(tuple))
231
usename = NameStr(((Form_pg_shadow) GETSTRUCT(tuple))->usename);
234
* Check for illegal characters in the user name.
236
j = strcspn(usename, "\n");
237
if (usename[j] != '\0')
240
(errmsg("invalid user name \"%s\"", usename)));
245
* File format is: "dbname" "user1" "user2" "user3"
249
fputs_quote(groname, fp);
256
fputs_quote(usename, fp);
258
ReleaseSysCache(tuple);
263
/* if IdList was toasted, free detoasted copy */
264
if ((Pointer) grolist_p != DatumGetPointer(grolist_datum))
271
(errcode_for_file_access(),
272
errmsg("could not write to temporary file \"%s\": %m",
276
* Rename the temp file to its final name, deleting the old pg_pwd. We
277
* expect that rename(2) is an atomic action.
279
if (rename(tempname, filename))
281
(errcode_for_file_access(),
282
errmsg("could not rename file \"%s\" to \"%s\": %m",
283
tempname, filename)));
291
* write_user_file: update the flat password file
294
write_user_file(Relation urel)
303
TupleDesc dsc = RelationGetDescr(urel);
306
* Create a temporary filename to be renamed later. This prevents the
307
* backend from clobbering the pg_pwd file while the postmaster might
308
* be reading from it.
310
filename = user_getfilename();
311
bufsize = strlen(filename) + 12;
312
tempname = (char *) palloc(bufsize);
313
snprintf(tempname, bufsize, "%s.%d", filename, MyProcPid);
315
oumask = umask((mode_t) 077);
316
fp = AllocateFile(tempname, "w");
320
(errcode_for_file_access(),
321
errmsg("could not write to temporary file \"%s\": %m", tempname)));
324
* Read pg_shadow and write the file. Note we use SnapshotSelf to
325
* ensure we see all effects of current transaction. (Perhaps could
326
* do a CommandCounterIncrement beforehand, instead?)
328
scan = heap_beginscan(urel, SnapshotSelf, 0, NULL);
329
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
338
datum = heap_getattr(tuple, Anum_pg_shadow_usename, dsc, &isnull);
339
/* ignore NULL usernames (shouldn't happen) */
342
usename = NameStr(*DatumGetName(datum));
344
datum = heap_getattr(tuple, Anum_pg_shadow_passwd, dsc, &isnull);
347
* It can be argued that people having a null password shouldn't
348
* be allowed to connect under password authentication, because
349
* they need to have a password set up first. If you think
350
* assuming an empty password in that case is better, change this
351
* logic to look something like the code for valuntil.
356
passwd = DatumGetCString(DirectFunctionCall1(textout, datum));
358
datum = heap_getattr(tuple, Anum_pg_shadow_valuntil, dsc, &isnull);
360
valuntil = pstrdup("");
362
valuntil = DatumGetCString(DirectFunctionCall1(abstimeout, datum));
365
* Check for illegal characters in the username and password.
367
i = strcspn(usename, "\n");
368
if (usename[i] != '\0')
371
(errmsg("invalid user name \"%s\"", usename)));
374
i = strcspn(passwd, "\n");
375
if (passwd[i] != '\0')
378
(errmsg("invalid user password \"%s\"", passwd)));
383
* The extra columns we emit here are not really necessary. To
384
* remove them, the parser in backend/libpq/crypt.c would need to
387
fputs_quote(usename, fp);
389
fputs_quote(passwd, fp);
391
fputs_quote(valuntil, fp);
401
(errcode_for_file_access(),
402
errmsg("could not write to temporary file \"%s\": %m",
406
* Rename the temp file to its final name, deleting the old pg_pwd. We
407
* expect that rename(2) is an atomic action.
409
if (rename(tempname, filename))
411
(errcode_for_file_access(),
412
errmsg("could not rename file \"%s\" to \"%s\": %m",
413
tempname, filename)));
421
* This trigger is fired whenever someone modifies pg_shadow or pg_group
422
* via general-purpose INSERT/UPDATE/DELETE commands.
424
* XXX should probably have two separate triggers.
427
update_pg_pwd_and_pg_group(PG_FUNCTION_ARGS)
429
user_file_update_needed();
430
group_file_update_needed();
432
return PointerGetDatum(NULL);
437
* This routine is called during transaction commit or abort.
439
* On commit, if we've written pg_shadow or pg_group during the current
440
* transaction, update the flat files and signal the postmaster.
442
* On abort, just reset the static flags so we don't try to do it on the
443
* next successful commit.
445
* NB: this should be the last step before actual transaction commit.
446
* If any error aborts the transaction after we run this code, the postmaster
447
* will still have received and cached the changed data; so minimize the
448
* window for such problems.
451
AtEOXact_UpdatePasswordFile(bool isCommit)
453
Relation urel = NULL;
454
Relation grel = NULL;
456
if (user_file_update_subid == InvalidSubTransactionId &&
457
group_file_update_subid == InvalidSubTransactionId)
462
user_file_update_subid = InvalidSubTransactionId;
463
group_file_update_subid = InvalidSubTransactionId;
468
* We use ExclusiveLock to ensure that only one backend writes the
469
* flat file(s) at a time. That's sufficient because it's okay to
470
* allow plain reads of the tables in parallel. There is some chance
471
* of a deadlock here (if we were triggered by a user update of
472
* pg_shadow or pg_group, which likely won't have gotten a strong
473
* enough lock), so get the locks we need before writing anything.
475
if (user_file_update_subid != InvalidSubTransactionId)
476
urel = heap_openr(ShadowRelationName, ExclusiveLock);
477
if (group_file_update_subid != InvalidSubTransactionId)
478
grel = heap_openr(GroupRelationName, ExclusiveLock);
480
/* Okay to write the files */
481
if (user_file_update_subid != InvalidSubTransactionId)
483
user_file_update_subid = InvalidSubTransactionId;
484
write_user_file(urel);
485
heap_close(urel, NoLock);
488
if (group_file_update_subid != InvalidSubTransactionId)
490
group_file_update_subid = InvalidSubTransactionId;
491
write_group_file(grel);
492
heap_close(grel, NoLock);
496
* Signal the postmaster to reload its password & group-file cache.
498
SendPostmasterSignal(PMSIGNAL_PASSWORD_CHANGE);
502
* AtEOSubXact_UpdatePasswordFile
504
* Called at subtransaction end, this routine resets or updates the
505
* need-to-update-files flags.
508
AtEOSubXact_UpdatePasswordFile(bool isCommit,
509
SubTransactionId mySubid,
510
SubTransactionId parentSubid)
514
if (user_file_update_subid == mySubid)
515
user_file_update_subid = parentSubid;
517
if (group_file_update_subid == mySubid)
518
group_file_update_subid = parentSubid;
522
if (user_file_update_subid == mySubid)
523
user_file_update_subid = InvalidSubTransactionId;
525
if (group_file_update_subid == mySubid)
526
group_file_update_subid = InvalidSubTransactionId;
534
CreateUser(CreateUserStmt *stmt)
536
Relation pg_shadow_rel;
537
TupleDesc pg_shadow_dsc;
540
Datum new_record[Natts_pg_shadow];
541
char new_record_nulls[Natts_pg_shadow];
542
bool user_exists = false,
543
sysid_exists = false,
548
char *password = NULL; /* PostgreSQL user password */
549
bool encrypt_password = Password_encryption; /* encrypt password? */
550
char encrypted_password[MD5_PASSWD_LEN + 1];
551
int sysid = 0; /* PgSQL system id (valid if havesysid) */
552
bool createdb = false; /* Can the user create databases? */
553
bool createuser = false; /* Can this user create users? */
554
List *groupElts = NIL; /* The groups the user is a member of */
555
char *validUntil = NULL; /* The time the login is valid
557
DefElem *dpassword = NULL;
558
DefElem *dsysid = NULL;
559
DefElem *dcreatedb = NULL;
560
DefElem *dcreateuser = NULL;
561
DefElem *dgroupElts = NULL;
562
DefElem *dvalidUntil = NULL;
564
/* Extract options from the statement node tree */
565
foreach(option, stmt->options)
567
DefElem *defel = (DefElem *) lfirst(option);
569
if (strcmp(defel->defname, "password") == 0 ||
570
strcmp(defel->defname, "encryptedPassword") == 0 ||
571
strcmp(defel->defname, "unencryptedPassword") == 0)
575
(errcode(ERRCODE_SYNTAX_ERROR),
576
errmsg("conflicting or redundant options")));
578
if (strcmp(defel->defname, "encryptedPassword") == 0)
579
encrypt_password = true;
580
else if (strcmp(defel->defname, "unencryptedPassword") == 0)
581
encrypt_password = false;
583
else if (strcmp(defel->defname, "sysid") == 0)
587
(errcode(ERRCODE_SYNTAX_ERROR),
588
errmsg("conflicting or redundant options")));
591
else if (strcmp(defel->defname, "createdb") == 0)
595
(errcode(ERRCODE_SYNTAX_ERROR),
596
errmsg("conflicting or redundant options")));
599
else if (strcmp(defel->defname, "createuser") == 0)
603
(errcode(ERRCODE_SYNTAX_ERROR),
604
errmsg("conflicting or redundant options")));
607
else if (strcmp(defel->defname, "groupElts") == 0)
611
(errcode(ERRCODE_SYNTAX_ERROR),
612
errmsg("conflicting or redundant options")));
615
else if (strcmp(defel->defname, "validUntil") == 0)
619
(errcode(ERRCODE_SYNTAX_ERROR),
620
errmsg("conflicting or redundant options")));
624
elog(ERROR, "option \"%s\" not recognized",
629
createdb = intVal(dcreatedb->arg) != 0;
631
createuser = intVal(dcreateuser->arg) != 0;
634
sysid = intVal(dsysid->arg);
637
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
638
errmsg("user ID must be positive")));
642
validUntil = strVal(dvalidUntil->arg);
644
password = strVal(dpassword->arg);
646
groupElts = (List *) dgroupElts->arg;
648
/* Check some permissions first */
650
CheckPgUserAclNotNull();
654
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
655
errmsg("must be superuser to create users")));
657
if (strcmp(stmt->user, "public") == 0)
659
(errcode(ERRCODE_RESERVED_NAME),
660
errmsg("user name \"%s\" is reserved",
664
* Scan the pg_shadow relation to be certain the user or id doesn't
665
* already exist. Note we secure exclusive lock, because we also need
666
* to be sure of what the next usesysid should be, and we need to
667
* protect our eventual update of the flat password file.
669
pg_shadow_rel = heap_openr(ShadowRelationName, ExclusiveLock);
670
pg_shadow_dsc = RelationGetDescr(pg_shadow_rel);
672
scan = heap_beginscan(pg_shadow_rel, SnapshotNow, 0, NULL);
673
max_id = 99; /* start auto-assigned ids at 100 */
674
while (!user_exists && !sysid_exists &&
675
(tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
677
Form_pg_shadow shadow_form = (Form_pg_shadow) GETSTRUCT(tuple);
680
user_exists = (strcmp(NameStr(shadow_form->usename), stmt->user) == 0);
682
this_sysid = shadow_form->usesysid;
683
if (havesysid) /* customized id wanted */
684
sysid_exists = (this_sysid == sysid);
688
if (this_sysid > max_id)
696
(errcode(ERRCODE_DUPLICATE_OBJECT),
697
errmsg("user \"%s\" already exists",
701
(errcode(ERRCODE_DUPLICATE_OBJECT),
702
errmsg("user ID %d is already assigned", sysid)));
704
/* If no sysid given, use max existing id + 1 */
709
* Build a tuple to insert
711
MemSet(new_record, 0, sizeof(new_record));
712
MemSet(new_record_nulls, ' ', sizeof(new_record_nulls));
714
new_record[Anum_pg_shadow_usename - 1] =
715
DirectFunctionCall1(namein, CStringGetDatum(stmt->user));
716
new_record[Anum_pg_shadow_usesysid - 1] = Int32GetDatum(sysid);
717
AssertState(BoolIsValid(createdb));
718
new_record[Anum_pg_shadow_usecreatedb - 1] = BoolGetDatum(createdb);
719
AssertState(BoolIsValid(createuser));
720
new_record[Anum_pg_shadow_usesuper - 1] = BoolGetDatum(createuser);
721
/* superuser gets catupd right by default */
722
new_record[Anum_pg_shadow_usecatupd - 1] = BoolGetDatum(createuser);
726
if (!encrypt_password || isMD5(password))
727
new_record[Anum_pg_shadow_passwd - 1] =
728
DirectFunctionCall1(textin, CStringGetDatum(password));
731
if (!EncryptMD5(password, stmt->user, strlen(stmt->user),
733
elog(ERROR, "password encryption failed");
734
new_record[Anum_pg_shadow_passwd - 1] =
735
DirectFunctionCall1(textin, CStringGetDatum(encrypted_password));
739
new_record_nulls[Anum_pg_shadow_passwd - 1] = 'n';
742
new_record[Anum_pg_shadow_valuntil - 1] =
743
DirectFunctionCall1(abstimein, CStringGetDatum(validUntil));
745
new_record_nulls[Anum_pg_shadow_valuntil - 1] = 'n';
747
new_record_nulls[Anum_pg_shadow_useconfig - 1] = 'n';
749
tuple = heap_formtuple(pg_shadow_dsc, new_record, new_record_nulls);
752
* Insert new record in the pg_shadow table
754
simple_heap_insert(pg_shadow_rel, tuple);
757
CatalogUpdateIndexes(pg_shadow_rel, tuple);
760
* Add the user to the groups specified. We'll just call the below
761
* AlterGroup for this.
763
foreach(item, groupElts)
767
ags.name = strVal(lfirst(item)); /* the group name to add
770
ags.listUsers = list_make1(makeInteger(sysid));
771
AlterGroup(&ags, "CREATE USER");
775
* Now we can clean up; but keep lock until commit (to avoid possible
776
* deadlock when commit code tries to acquire lock).
778
heap_close(pg_shadow_rel, NoLock);
781
* Set flag to update flat password file at commit.
783
user_file_update_needed();
792
AlterUser(AlterUserStmt *stmt)
794
Datum new_record[Natts_pg_shadow];
795
char new_record_nulls[Natts_pg_shadow];
796
char new_record_repl[Natts_pg_shadow];
797
Relation pg_shadow_rel;
798
TupleDesc pg_shadow_dsc;
802
char *password = NULL; /* PostgreSQL user password */
803
bool encrypt_password = Password_encryption; /* encrypt password? */
804
char encrypted_password[MD5_PASSWD_LEN + 1];
805
int createdb = -1; /* Can the user create databases? */
806
int createuser = -1; /* Can this user create users? */
807
char *validUntil = NULL; /* The time the login is valid
809
DefElem *dpassword = NULL;
810
DefElem *dcreatedb = NULL;
811
DefElem *dcreateuser = NULL;
812
DefElem *dvalidUntil = NULL;
814
/* Extract options from the statement node tree */
815
foreach(option, stmt->options)
817
DefElem *defel = (DefElem *) lfirst(option);
819
if (strcmp(defel->defname, "password") == 0 ||
820
strcmp(defel->defname, "encryptedPassword") == 0 ||
821
strcmp(defel->defname, "unencryptedPassword") == 0)
825
(errcode(ERRCODE_SYNTAX_ERROR),
826
errmsg("conflicting or redundant options")));
828
if (strcmp(defel->defname, "encryptedPassword") == 0)
829
encrypt_password = true;
830
else if (strcmp(defel->defname, "unencryptedPassword") == 0)
831
encrypt_password = false;
833
else if (strcmp(defel->defname, "createdb") == 0)
837
(errcode(ERRCODE_SYNTAX_ERROR),
838
errmsg("conflicting or redundant options")));
841
else if (strcmp(defel->defname, "createuser") == 0)
845
(errcode(ERRCODE_SYNTAX_ERROR),
846
errmsg("conflicting or redundant options")));
849
else if (strcmp(defel->defname, "validUntil") == 0)
853
(errcode(ERRCODE_SYNTAX_ERROR),
854
errmsg("conflicting or redundant options")));
858
elog(ERROR, "option \"%s\" not recognized",
863
createdb = intVal(dcreatedb->arg);
865
createuser = intVal(dcreateuser->arg);
867
validUntil = strVal(dvalidUntil->arg);
869
password = strVal(dpassword->arg);
872
CheckPgUserAclNotNull();
874
/* must be superuser or just want to change your own password */
880
strcmp(GetUserNameFromId(GetUserId()), stmt->user) == 0))
882
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
883
errmsg("permission denied")));
886
* Scan the pg_shadow relation to be certain the user exists. Note we
887
* secure exclusive lock to protect our update of the flat password
890
pg_shadow_rel = heap_openr(ShadowRelationName, ExclusiveLock);
891
pg_shadow_dsc = RelationGetDescr(pg_shadow_rel);
893
tuple = SearchSysCache(SHADOWNAME,
894
PointerGetDatum(stmt->user),
896
if (!HeapTupleIsValid(tuple))
898
(errcode(ERRCODE_UNDEFINED_OBJECT),
899
errmsg("user \"%s\" does not exist", stmt->user)));
902
* Build an updated tuple, perusing the information just obtained
904
MemSet(new_record, 0, sizeof(new_record));
905
MemSet(new_record_nulls, ' ', sizeof(new_record_nulls));
906
MemSet(new_record_repl, ' ', sizeof(new_record_repl));
908
new_record[Anum_pg_shadow_usename - 1] = DirectFunctionCall1(namein,
909
CStringGetDatum(stmt->user));
910
new_record_repl[Anum_pg_shadow_usename - 1] = 'r';
915
new_record[Anum_pg_shadow_usecreatedb - 1] = BoolGetDatum(createdb > 0);
916
new_record_repl[Anum_pg_shadow_usecreatedb - 1] = 'r';
920
* createuser (superuser) and catupd
922
* XXX It's rather unclear how to handle catupd. It's probably best to
923
* keep it equal to the superuser status, otherwise you could end up
924
* with a situation where no existing superuser can alter the
925
* catalogs, including pg_shadow!
929
new_record[Anum_pg_shadow_usesuper - 1] = BoolGetDatum(createuser > 0);
930
new_record_repl[Anum_pg_shadow_usesuper - 1] = 'r';
932
new_record[Anum_pg_shadow_usecatupd - 1] = BoolGetDatum(createuser > 0);
933
new_record_repl[Anum_pg_shadow_usecatupd - 1] = 'r';
939
if (!encrypt_password || isMD5(password))
940
new_record[Anum_pg_shadow_passwd - 1] =
941
DirectFunctionCall1(textin, CStringGetDatum(password));
944
if (!EncryptMD5(password, stmt->user, strlen(stmt->user),
946
elog(ERROR, "password encryption failed");
947
new_record[Anum_pg_shadow_passwd - 1] =
948
DirectFunctionCall1(textin, CStringGetDatum(encrypted_password));
950
new_record_repl[Anum_pg_shadow_passwd - 1] = 'r';
956
new_record[Anum_pg_shadow_valuntil - 1] =
957
DirectFunctionCall1(abstimein, CStringGetDatum(validUntil));
958
new_record_repl[Anum_pg_shadow_valuntil - 1] = 'r';
961
new_tuple = heap_modifytuple(tuple, pg_shadow_rel, new_record,
962
new_record_nulls, new_record_repl);
963
simple_heap_update(pg_shadow_rel, &tuple->t_self, new_tuple);
966
CatalogUpdateIndexes(pg_shadow_rel, new_tuple);
968
ReleaseSysCache(tuple);
969
heap_freetuple(new_tuple);
972
* Now we can clean up; but keep lock until commit (to avoid possible
973
* deadlock when commit code tries to acquire lock).
975
heap_close(pg_shadow_rel, NoLock);
978
* Set flag to update flat password file at commit.
980
user_file_update_needed();
988
AlterUserSet(AlterUserSetStmt *stmt)
994
Datum repl_val[Natts_pg_shadow];
995
char repl_null[Natts_pg_shadow];
996
char repl_repl[Natts_pg_shadow];
999
valuestr = flatten_set_variable_args(stmt->variable, stmt->value);
1002
* RowExclusiveLock is sufficient, because we don't need to update the
1003
* flat password file.
1005
rel = heap_openr(ShadowRelationName, RowExclusiveLock);
1006
oldtuple = SearchSysCache(SHADOWNAME,
1007
PointerGetDatum(stmt->user),
1009
if (!HeapTupleIsValid(oldtuple))
1011
(errcode(ERRCODE_UNDEFINED_OBJECT),
1012
errmsg("user \"%s\" does not exist", stmt->user)));
1014
if (!(superuser() ||
1015
((Form_pg_shadow) GETSTRUCT(oldtuple))->usesysid == GetUserId()))
1017
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1018
errmsg("permission denied")));
1020
for (i = 0; i < Natts_pg_shadow; i++)
1023
repl_repl[Anum_pg_shadow_useconfig - 1] = 'r';
1024
if (strcmp(stmt->variable, "all") == 0 && valuestr == NULL)
1027
repl_null[Anum_pg_shadow_useconfig - 1] = 'n';
1035
repl_null[Anum_pg_shadow_useconfig - 1] = ' ';
1037
datum = SysCacheGetAttr(SHADOWNAME, oldtuple,
1038
Anum_pg_shadow_useconfig, &isnull);
1040
array = isnull ? NULL : DatumGetArrayTypeP(datum);
1043
array = GUCArrayAdd(array, stmt->variable, valuestr);
1045
array = GUCArrayDelete(array, stmt->variable);
1048
repl_val[Anum_pg_shadow_useconfig - 1] = PointerGetDatum(array);
1050
repl_null[Anum_pg_shadow_useconfig - 1] = 'n';
1053
newtuple = heap_modifytuple(oldtuple, rel, repl_val, repl_null, repl_repl);
1054
simple_heap_update(rel, &oldtuple->t_self, newtuple);
1056
CatalogUpdateIndexes(rel, newtuple);
1058
ReleaseSysCache(oldtuple);
1059
heap_close(rel, RowExclusiveLock);
1068
DropUser(DropUserStmt *stmt)
1070
Relation pg_shadow_rel;
1071
TupleDesc pg_shadow_dsc;
1076
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1077
errmsg("must be superuser to drop users")));
1080
* Scan the pg_shadow relation to find the usesysid of the user to be
1081
* deleted. Note we secure exclusive lock, because we need to protect
1082
* our update of the flat password file.
1084
pg_shadow_rel = heap_openr(ShadowRelationName, ExclusiveLock);
1085
pg_shadow_dsc = RelationGetDescr(pg_shadow_rel);
1087
foreach(item, stmt->users)
1089
const char *user = strVal(lfirst(item));
1094
ScanKeyData scankey;
1098
tuple = SearchSysCache(SHADOWNAME,
1099
PointerGetDatum(user),
1101
if (!HeapTupleIsValid(tuple))
1103
(errcode(ERRCODE_UNDEFINED_OBJECT),
1104
errmsg("user \"%s\" does not exist", user)));
1106
usesysid = ((Form_pg_shadow) GETSTRUCT(tuple))->usesysid;
1108
if (usesysid == GetUserId())
1110
(errcode(ERRCODE_OBJECT_IN_USE),
1111
errmsg("current user cannot be dropped")));
1112
if (usesysid == GetSessionUserId())
1114
(errcode(ERRCODE_OBJECT_IN_USE),
1115
errmsg("session user cannot be dropped")));
1118
* Check if user still owns a database. If so, error out.
1120
* (It used to be that this function would drop the database
1121
* automatically. This is not only very dangerous for people that
1122
* don't read the manual, it doesn't seem to be the behaviour one
1123
* would expect either.) -- petere 2000/01/14)
1125
pg_rel = heap_openr(DatabaseRelationName, AccessShareLock);
1126
pg_dsc = RelationGetDescr(pg_rel);
1128
ScanKeyInit(&scankey,
1129
Anum_pg_database_datdba,
1130
BTEqualStrategyNumber, F_INT4EQ,
1131
Int32GetDatum(usesysid));
1133
scan = heap_beginscan(pg_rel, SnapshotNow, 1, &scankey);
1135
if ((tmp_tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
1139
dbname = NameStr(((Form_pg_database) GETSTRUCT(tmp_tuple))->datname);
1141
(errcode(ERRCODE_OBJECT_IN_USE),
1142
errmsg("user \"%s\" cannot be dropped", user),
1143
errdetail("The user owns database \"%s\".", dbname)));
1147
heap_close(pg_rel, AccessShareLock);
1150
* Somehow we'd have to check for tables, views, etc. owned by the
1151
* user as well, but those could be spread out over all sorts of
1152
* databases which we don't have access to (easily).
1156
* Remove the user from the pg_shadow table
1158
simple_heap_delete(pg_shadow_rel, &tuple->t_self);
1160
ReleaseSysCache(tuple);
1163
* Remove user from groups
1165
* try calling alter group drop user for every group
1167
pg_rel = heap_openr(GroupRelationName, ExclusiveLock);
1168
pg_dsc = RelationGetDescr(pg_rel);
1169
scan = heap_beginscan(pg_rel, SnapshotNow, 0, NULL);
1170
while ((tmp_tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
1174
/* the group name from which to try to drop the user: */
1175
ags.name = pstrdup(NameStr(((Form_pg_group) GETSTRUCT(tmp_tuple))->groname));
1177
ags.listUsers = list_make1(makeInteger(usesysid));
1178
AlterGroup(&ags, "DROP USER");
1181
heap_close(pg_rel, ExclusiveLock);
1184
* Advance command counter so that later iterations of this loop
1185
* will see the changes already made. This is essential if, for
1186
* example, we are trying to drop two users who are members of the
1187
* same group --- the AlterGroup for the second user had better
1188
* see the tuple updated from the first one.
1190
CommandCounterIncrement();
1194
* Now we can clean up; but keep lock until commit (to avoid possible
1195
* deadlock when commit code tries to acquire lock).
1197
heap_close(pg_shadow_rel, NoLock);
1200
* Set flag to update flat password file at commit.
1202
user_file_update_needed();
1210
RenameUser(const char *oldname, const char *newname)
1218
Datum repl_val[Natts_pg_shadow];
1219
char repl_null[Natts_pg_shadow];
1220
char repl_repl[Natts_pg_shadow];
1223
/* ExclusiveLock because we need to update the password file */
1224
rel = heap_openr(ShadowRelationName, ExclusiveLock);
1225
dsc = RelationGetDescr(rel);
1227
oldtuple = SearchSysCache(SHADOWNAME,
1228
CStringGetDatum(oldname),
1230
if (!HeapTupleIsValid(oldtuple))
1232
(errcode(ERRCODE_UNDEFINED_OBJECT),
1233
errmsg("user \"%s\" does not exist", oldname)));
1236
* XXX Client applications probably store the session user somewhere,
1237
* so renaming it could cause confusion. On the other hand, there may
1238
* not be an actual problem besides a little confusion, so think about
1241
if (((Form_pg_shadow) GETSTRUCT(oldtuple))->usesysid == GetSessionUserId())
1243
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1244
errmsg("session user may not be renamed")));
1246
/* make sure the new name doesn't exist */
1247
if (SearchSysCacheExists(SHADOWNAME,
1248
CStringGetDatum(newname),
1251
(errcode(ERRCODE_DUPLICATE_OBJECT),
1252
errmsg("user \"%s\" already exists", newname)));
1254
/* must be superuser */
1257
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1258
errmsg("must be superuser to rename users")));
1260
for (i = 0; i < Natts_pg_shadow; i++)
1263
repl_repl[Anum_pg_shadow_usename - 1] = 'r';
1264
repl_val[Anum_pg_shadow_usename - 1] = DirectFunctionCall1(namein,
1265
CStringGetDatum(newname));
1266
repl_null[Anum_pg_shadow_usename - 1] = ' ';
1268
datum = heap_getattr(oldtuple, Anum_pg_shadow_passwd, dsc, &isnull);
1270
if (!isnull && isMD5(DatumGetCString(DirectFunctionCall1(textout, datum))))
1272
/* MD5 uses the username as salt, so just clear it on a rename */
1273
repl_repl[Anum_pg_shadow_passwd - 1] = 'r';
1274
repl_null[Anum_pg_shadow_passwd - 1] = 'n';
1277
(errmsg("MD5 password cleared because of user rename")));
1280
newtuple = heap_modifytuple(oldtuple, rel, repl_val, repl_null, repl_repl);
1281
simple_heap_update(rel, &oldtuple->t_self, newtuple);
1283
CatalogUpdateIndexes(rel, newtuple);
1285
ReleaseSysCache(oldtuple);
1286
heap_close(rel, NoLock);
1288
user_file_update_needed();
1293
* CheckPgUserAclNotNull
1295
* check to see if there is an ACL on pg_shadow
1298
CheckPgUserAclNotNull(void)
1302
htup = SearchSysCache(RELOID,
1303
ObjectIdGetDatum(RelOid_pg_shadow),
1305
if (!HeapTupleIsValid(htup)) /* should not happen, we hope */
1306
elog(ERROR, "cache lookup failed for relation %u", RelOid_pg_shadow);
1308
if (heap_attisnull(htup, Anum_pg_class_relacl))
1310
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1311
errmsg("before using passwords you must revoke privileges on %s",
1312
ShadowRelationName),
1313
errdetail("This restriction is to prevent unprivileged users from reading the passwords."),
1314
errhint("Try REVOKE ALL ON \"%s\" FROM PUBLIC.",
1315
ShadowRelationName)));
1317
ReleaseSysCache(htup);
1326
CreateGroup(CreateGroupStmt *stmt)
1328
Relation pg_group_rel;
1331
TupleDesc pg_group_dsc;
1332
bool group_exists = false,
1333
sysid_exists = false,
1336
Datum new_record[Natts_pg_group];
1337
char new_record_nulls[Natts_pg_group];
1340
List *newlist = NIL;
1343
List *userElts = NIL;
1344
DefElem *dsysid = NULL;
1345
DefElem *duserElts = NULL;
1347
foreach(option, stmt->options)
1349
DefElem *defel = (DefElem *) lfirst(option);
1351
if (strcmp(defel->defname, "sysid") == 0)
1355
(errcode(ERRCODE_SYNTAX_ERROR),
1356
errmsg("conflicting or redundant options")));
1359
else if (strcmp(defel->defname, "userElts") == 0)
1363
(errcode(ERRCODE_SYNTAX_ERROR),
1364
errmsg("conflicting or redundant options")));
1368
elog(ERROR, "option \"%s\" not recognized",
1374
sysid = intVal(dsysid->arg);
1377
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1378
errmsg("group ID must be positive")));
1383
userElts = (List *) duserElts->arg;
1386
* Make sure the user can do this.
1390
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1391
errmsg("must be superuser to create groups")));
1393
if (strcmp(stmt->name, "public") == 0)
1395
(errcode(ERRCODE_RESERVED_NAME),
1396
errmsg("group name \"%s\" is reserved",
1400
* Scan the pg_group relation to be certain the group or id doesn't
1401
* already exist. Note we secure exclusive lock, because we also need
1402
* to be sure of what the next grosysid should be, and we need to
1403
* protect our eventual update of the flat group file.
1405
pg_group_rel = heap_openr(GroupRelationName, ExclusiveLock);
1406
pg_group_dsc = RelationGetDescr(pg_group_rel);
1408
scan = heap_beginscan(pg_group_rel, SnapshotNow, 0, NULL);
1409
max_id = 99; /* start auto-assigned ids at 100 */
1410
while (!group_exists && !sysid_exists &&
1411
(tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
1413
Form_pg_group group_form = (Form_pg_group) GETSTRUCT(tuple);
1416
group_exists = (strcmp(NameStr(group_form->groname), stmt->name) == 0);
1418
this_sysid = group_form->grosysid;
1419
if (havesysid) /* customized id wanted */
1420
sysid_exists = (this_sysid == sysid);
1424
if (this_sysid > max_id)
1425
max_id = this_sysid;
1432
(errcode(ERRCODE_DUPLICATE_OBJECT),
1433
errmsg("group \"%s\" already exists",
1437
(errcode(ERRCODE_DUPLICATE_OBJECT),
1438
errmsg("group ID %d is already assigned", sysid)));
1440
/* If no sysid given, use max existing id + 1 */
1445
* Translate the given user names to ids
1447
foreach(item, userElts)
1449
const char *groupuser = strVal(lfirst(item));
1450
int32 userid = get_usesysid(groupuser);
1452
if (!list_member_int(newlist, userid))
1453
newlist = lappend_int(newlist, userid);
1456
/* build an array to insert */
1458
grolist = IdListToArray(newlist);
1463
* Form a tuple to insert
1465
new_record[Anum_pg_group_groname - 1] =
1466
DirectFunctionCall1(namein, CStringGetDatum(stmt->name));
1467
new_record[Anum_pg_group_grosysid - 1] = Int32GetDatum(sysid);
1468
new_record[Anum_pg_group_grolist - 1] = PointerGetDatum(grolist);
1470
new_record_nulls[Anum_pg_group_groname - 1] = ' ';
1471
new_record_nulls[Anum_pg_group_grosysid - 1] = ' ';
1472
new_record_nulls[Anum_pg_group_grolist - 1] = grolist ? ' ' : 'n';
1474
tuple = heap_formtuple(pg_group_dsc, new_record, new_record_nulls);
1477
* Insert a new record in the pg_group table
1479
simple_heap_insert(pg_group_rel, tuple);
1481
/* Update indexes */
1482
CatalogUpdateIndexes(pg_group_rel, tuple);
1485
* Now we can clean up; but keep lock until commit (to avoid possible
1486
* deadlock when commit code tries to acquire lock).
1488
heap_close(pg_group_rel, NoLock);
1491
* Set flag to update flat group file at commit.
1493
group_file_update_needed();
1501
AlterGroup(AlterGroupStmt *stmt, const char *tag)
1503
Relation pg_group_rel;
1504
TupleDesc pg_group_dsc;
1505
HeapTuple group_tuple;
1513
* Make sure the user can do this.
1517
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1518
errmsg("must be superuser to alter groups")));
1521
* Secure exclusive lock to protect our update of the flat group file.
1523
pg_group_rel = heap_openr(GroupRelationName, ExclusiveLock);
1524
pg_group_dsc = RelationGetDescr(pg_group_rel);
1527
* Fetch existing tuple for group.
1529
group_tuple = SearchSysCache(GRONAME,
1530
PointerGetDatum(stmt->name),
1532
if (!HeapTupleIsValid(group_tuple))
1534
(errcode(ERRCODE_UNDEFINED_OBJECT),
1535
errmsg("group \"%s\" does not exist", stmt->name)));
1537
/* Fetch old group membership. */
1538
datum = heap_getattr(group_tuple, Anum_pg_group_grolist,
1539
pg_group_dsc, &null);
1540
oldarray = null ? NULL : DatumGetIdListP(datum);
1542
/* initialize list with old array contents */
1543
newlist = IdArrayToList(oldarray);
1546
* Now decide what to do.
1548
AssertState(stmt->action == +1 || stmt->action == -1);
1550
if (stmt->action == +1) /* add users, might also be invoked by
1554
* convert the to be added usernames to sysids and add them to the
1557
foreach(item, stmt->listUsers)
1561
if (strcmp(tag, "ALTER GROUP") == 0)
1563
/* Get the uid of the proposed user to add. */
1564
sysid = get_usesysid(strVal(lfirst(item)));
1566
else if (strcmp(tag, "CREATE USER") == 0)
1569
* in this case we already know the uid and it wouldn't be
1570
* in the cache anyway yet
1572
sysid = intVal(lfirst(item));
1576
elog(ERROR, "unexpected tag: \"%s\"", tag);
1577
sysid = 0; /* keep compiler quiet */
1580
if (!list_member_int(newlist, sysid))
1581
newlist = lappend_int(newlist, sysid);
1585
UpdateGroupMembership(pg_group_rel, group_tuple, newlist);
1586
} /* endif alter group add user */
1588
else if (stmt->action == -1) /* drop users from group */
1590
bool is_dropuser = strcmp(tag, "DROP USER") == 0;
1596
(errcode(ERRCODE_WARNING),
1597
errmsg("group \"%s\" does not have any members",
1603
* convert the to be dropped usernames to sysids and remove
1604
* them from the list
1606
foreach(item, stmt->listUsers)
1612
/* Get the uid of the proposed user to drop. */
1613
sysid = get_usesysid(strVal(lfirst(item)));
1617
/* for dropuser we already know the uid */
1618
sysid = intVal(lfirst(item));
1620
if (list_member_int(newlist, sysid))
1621
newlist = list_delete_int(newlist, sysid);
1622
else if (!is_dropuser)
1624
(errcode(ERRCODE_WARNING),
1625
errmsg("user \"%s\" is not in group \"%s\"",
1626
strVal(lfirst(item)), stmt->name)));
1630
UpdateGroupMembership(pg_group_rel, group_tuple, newlist);
1631
} /* endif group not null */
1632
} /* endif alter group drop user */
1634
ReleaseSysCache(group_tuple);
1637
* Now we can clean up; but keep lock until commit (to avoid possible
1638
* deadlock when commit code tries to acquire lock).
1640
heap_close(pg_group_rel, NoLock);
1643
* Set flag to update flat group file at commit.
1645
group_file_update_needed();
1649
* Subroutine for AlterGroup: given a pg_group tuple and a desired new
1650
* membership (expressed as an integer list), form and write an updated tuple.
1651
* The pg_group relation must be open and locked already.
1654
UpdateGroupMembership(Relation group_rel, HeapTuple group_tuple,
1658
Datum new_record[Natts_pg_group];
1659
char new_record_nulls[Natts_pg_group];
1660
char new_record_repl[Natts_pg_group];
1663
newarray = IdListToArray(members);
1666
* Form an updated tuple with the new array and write it back.
1668
MemSet(new_record, 0, sizeof(new_record));
1669
MemSet(new_record_nulls, ' ', sizeof(new_record_nulls));
1670
MemSet(new_record_repl, ' ', sizeof(new_record_repl));
1672
new_record[Anum_pg_group_grolist - 1] = PointerGetDatum(newarray);
1673
new_record_repl[Anum_pg_group_grolist - 1] = 'r';
1675
tuple = heap_modifytuple(group_tuple, group_rel,
1676
new_record, new_record_nulls, new_record_repl);
1678
simple_heap_update(group_rel, &group_tuple->t_self, tuple);
1680
/* Update indexes */
1681
CatalogUpdateIndexes(group_rel, tuple);
1686
* Convert an integer list of sysids to an array.
1689
IdListToArray(List *members)
1691
int nmembers = list_length(members);
1696
newarray = palloc(ARR_OVERHEAD(1) + nmembers * sizeof(int32));
1697
newarray->size = ARR_OVERHEAD(1) + nmembers * sizeof(int32);
1698
newarray->flags = 0;
1699
newarray->elemtype = INT4OID;
1700
ARR_NDIM(newarray) = 1; /* one dimensional array */
1701
ARR_LBOUND(newarray)[0] = 1; /* axis starts at one */
1702
ARR_DIMS(newarray)[0] = nmembers; /* axis is this long */
1704
foreach(item, members)
1705
((int *) ARR_DATA_PTR(newarray))[i++] = lfirst_int(item);
1711
* Convert an array of sysids to an integer list.
1714
IdArrayToList(IdList *oldarray)
1716
List *newlist = NIL;
1720
if (oldarray == NULL)
1723
Assert(ARR_NDIM(oldarray) == 1);
1724
Assert(ARR_ELEMTYPE(oldarray) == INT4OID);
1726
hibound = ARR_DIMS(oldarray)[0];
1728
for (i = 0; i < hibound; i++)
1732
sysid = ((int32 *) ARR_DATA_PTR(oldarray))[i];
1733
/* filter out any duplicates --- probably a waste of time */
1734
if (!list_member_int(newlist, sysid))
1735
newlist = lappend_int(newlist, sysid);
1746
DropGroup(DropGroupStmt *stmt)
1748
Relation pg_group_rel;
1752
* Make sure the user can do this.
1756
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1757
errmsg("must be superuser to drop groups")));
1760
* Secure exclusive lock to protect our update of the flat group file.
1762
pg_group_rel = heap_openr(GroupRelationName, ExclusiveLock);
1764
/* Find and delete the group. */
1766
tuple = SearchSysCacheCopy(GRONAME,
1767
PointerGetDatum(stmt->name),
1769
if (!HeapTupleIsValid(tuple))
1771
(errcode(ERRCODE_UNDEFINED_OBJECT),
1772
errmsg("group \"%s\" does not exist", stmt->name)));
1774
simple_heap_delete(pg_group_rel, &tuple->t_self);
1777
* Now we can clean up; but keep lock until commit (to avoid possible
1778
* deadlock when commit code tries to acquire lock).
1780
heap_close(pg_group_rel, NoLock);
1783
* Set flag to update flat group file at commit.
1785
group_file_update_needed();
1793
RenameGroup(const char *oldname, const char *newname)
1798
/* ExclusiveLock because we need to update the flat group file */
1799
rel = heap_openr(GroupRelationName, ExclusiveLock);
1801
tup = SearchSysCacheCopy(GRONAME,
1802
CStringGetDatum(oldname),
1804
if (!HeapTupleIsValid(tup))
1806
(errcode(ERRCODE_UNDEFINED_OBJECT),
1807
errmsg("group \"%s\" does not exist", oldname)));
1809
/* make sure the new name doesn't exist */
1810
if (SearchSysCacheExists(GRONAME,
1811
CStringGetDatum(newname),
1814
(errcode(ERRCODE_DUPLICATE_OBJECT),
1815
errmsg("group \"%s\" already exists", newname)));
1817
/* must be superuser */
1820
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1821
errmsg("must be superuser to rename groups")));
1824
namestrcpy(&(((Form_pg_group) GETSTRUCT(tup))->groname), newname);
1825
simple_heap_update(rel, &tup->t_self, tup);
1826
CatalogUpdateIndexes(rel, tup);
1828
heap_close(rel, NoLock);
1829
heap_freetuple(tup);
1831
group_file_update_needed();