PostgreSQL Source Code  git master
user.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * user.c
4  * Commands for manipulating roles (formerly called users).
5  *
6  * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group
7  * Portions Copyright (c) 1994, Regents of the University of California
8  *
9  * src/backend/commands/user.c
10  *
11  *-------------------------------------------------------------------------
12  */
13 #include "postgres.h"
14 
15 #include "access/genam.h"
16 #include "access/htup_details.h"
17 #include "access/table.h"
18 #include "access/xact.h"
19 #include "catalog/binary_upgrade.h"
20 #include "catalog/catalog.h"
21 #include "catalog/dependency.h"
22 #include "catalog/indexing.h"
23 #include "catalog/objectaccess.h"
25 #include "catalog/pg_authid.h"
26 #include "catalog/pg_database.h"
28 #include "commands/comment.h"
29 #include "commands/dbcommands.h"
30 #include "commands/seclabel.h"
31 #include "commands/user.h"
32 #include "libpq/crypt.h"
33 #include "miscadmin.h"
34 #include "storage/lmgr.h"
35 #include "utils/acl.h"
36 #include "utils/builtins.h"
37 #include "utils/fmgroids.h"
38 #include "utils/syscache.h"
39 #include "utils/timestamp.h"
40 
41 /* Potentially set by pg_upgrade_support functions */
43 
44 
45 /* GUC parameter */
47 
48 /* Hook to check passwords in CreateRole() and AlterRole() */
50 
51 static void AddRoleMems(const char *rolename, Oid roleid,
52  List *memberSpecs, List *memberIds,
53  Oid grantorId, bool admin_opt);
54 static void DelRoleMems(const char *rolename, Oid roleid,
55  List *memberSpecs, List *memberIds,
56  bool admin_opt);
57 
58 
59 /* Check if current user has createrole privileges */
60 static bool
62 {
64 }
65 
66 
67 /*
68  * CREATE ROLE
69  */
70 Oid
72 {
73  Relation pg_authid_rel;
74  TupleDesc pg_authid_dsc;
75  HeapTuple tuple;
76  Datum new_record[Natts_pg_authid];
77  bool new_record_nulls[Natts_pg_authid];
78  Oid roleid;
79  ListCell *item;
81  char *password = NULL; /* user password */
82  bool issuper = false; /* Make the user a superuser? */
83  bool inherit = true; /* Auto inherit privileges? */
84  bool createrole = false; /* Can this user create roles? */
85  bool createdb = false; /* Can the user create databases? */
86  bool canlogin = false; /* Can this user login? */
87  bool isreplication = false; /* Is this a replication role? */
88  bool bypassrls = false; /* Is this a row security enabled role? */
89  int connlimit = -1; /* maximum connections allowed */
90  List *addroleto = NIL; /* roles to make this a member of */
91  List *rolemembers = NIL; /* roles to be members of this role */
92  List *adminmembers = NIL; /* roles to be admins of this role */
93  char *validUntil = NULL; /* time the login is valid until */
94  Datum validUntil_datum; /* same, as timestamptz Datum */
95  bool validUntil_null;
96  DefElem *dpassword = NULL;
97  DefElem *dissuper = NULL;
98  DefElem *dinherit = NULL;
99  DefElem *dcreaterole = NULL;
100  DefElem *dcreatedb = NULL;
101  DefElem *dcanlogin = NULL;
102  DefElem *disreplication = NULL;
103  DefElem *dconnlimit = NULL;
104  DefElem *daddroleto = NULL;
105  DefElem *drolemembers = NULL;
106  DefElem *dadminmembers = NULL;
107  DefElem *dvalidUntil = NULL;
108  DefElem *dbypassRLS = NULL;
109 
110  /* The defaults can vary depending on the original statement type */
111  switch (stmt->stmt_type)
112  {
113  case ROLESTMT_ROLE:
114  break;
115  case ROLESTMT_USER:
116  canlogin = true;
117  /* may eventually want inherit to default to false here */
118  break;
119  case ROLESTMT_GROUP:
120  break;
121  }
122 
123  /* Extract options from the statement node tree */
124  foreach(option, stmt->options)
125  {
126  DefElem *defel = (DefElem *) lfirst(option);
127 
128  if (strcmp(defel->defname, "password") == 0)
129  {
130  if (dpassword)
131  ereport(ERROR,
132  (errcode(ERRCODE_SYNTAX_ERROR),
133  errmsg("conflicting or redundant options"),
134  parser_errposition(pstate, defel->location)));
135  dpassword = defel;
136  }
137  else if (strcmp(defel->defname, "sysid") == 0)
138  {
139  ereport(NOTICE,
140  (errmsg("SYSID can no longer be specified")));
141  }
142  else if (strcmp(defel->defname, "superuser") == 0)
143  {
144  if (dissuper)
145  ereport(ERROR,
146  (errcode(ERRCODE_SYNTAX_ERROR),
147  errmsg("conflicting or redundant options"),
148  parser_errposition(pstate, defel->location)));
149  dissuper = defel;
150  }
151  else if (strcmp(defel->defname, "inherit") == 0)
152  {
153  if (dinherit)
154  ereport(ERROR,
155  (errcode(ERRCODE_SYNTAX_ERROR),
156  errmsg("conflicting or redundant options"),
157  parser_errposition(pstate, defel->location)));
158  dinherit = defel;
159  }
160  else if (strcmp(defel->defname, "createrole") == 0)
161  {
162  if (dcreaterole)
163  ereport(ERROR,
164  (errcode(ERRCODE_SYNTAX_ERROR),
165  errmsg("conflicting or redundant options"),
166  parser_errposition(pstate, defel->location)));
167  dcreaterole = defel;
168  }
169  else if (strcmp(defel->defname, "createdb") == 0)
170  {
171  if (dcreatedb)
172  ereport(ERROR,
173  (errcode(ERRCODE_SYNTAX_ERROR),
174  errmsg("conflicting or redundant options"),
175  parser_errposition(pstate, defel->location)));
176  dcreatedb = defel;
177  }
178  else if (strcmp(defel->defname, "canlogin") == 0)
179  {
180  if (dcanlogin)
181  ereport(ERROR,
182  (errcode(ERRCODE_SYNTAX_ERROR),
183  errmsg("conflicting or redundant options"),
184  parser_errposition(pstate, defel->location)));
185  dcanlogin = defel;
186  }
187  else if (strcmp(defel->defname, "isreplication") == 0)
188  {
189  if (disreplication)
190  ereport(ERROR,
191  (errcode(ERRCODE_SYNTAX_ERROR),
192  errmsg("conflicting or redundant options"),
193  parser_errposition(pstate, defel->location)));
194  disreplication = defel;
195  }
196  else if (strcmp(defel->defname, "connectionlimit") == 0)
197  {
198  if (dconnlimit)
199  ereport(ERROR,
200  (errcode(ERRCODE_SYNTAX_ERROR),
201  errmsg("conflicting or redundant options"),
202  parser_errposition(pstate, defel->location)));
203  dconnlimit = defel;
204  }
205  else if (strcmp(defel->defname, "addroleto") == 0)
206  {
207  if (daddroleto)
208  ereport(ERROR,
209  (errcode(ERRCODE_SYNTAX_ERROR),
210  errmsg("conflicting or redundant options"),
211  parser_errposition(pstate, defel->location)));
212  daddroleto = defel;
213  }
214  else if (strcmp(defel->defname, "rolemembers") == 0)
215  {
216  if (drolemembers)
217  ereport(ERROR,
218  (errcode(ERRCODE_SYNTAX_ERROR),
219  errmsg("conflicting or redundant options"),
220  parser_errposition(pstate, defel->location)));
221  drolemembers = defel;
222  }
223  else if (strcmp(defel->defname, "adminmembers") == 0)
224  {
225  if (dadminmembers)
226  ereport(ERROR,
227  (errcode(ERRCODE_SYNTAX_ERROR),
228  errmsg("conflicting or redundant options"),
229  parser_errposition(pstate, defel->location)));
230  dadminmembers = defel;
231  }
232  else if (strcmp(defel->defname, "validUntil") == 0)
233  {
234  if (dvalidUntil)
235  ereport(ERROR,
236  (errcode(ERRCODE_SYNTAX_ERROR),
237  errmsg("conflicting or redundant options"),
238  parser_errposition(pstate, defel->location)));
239  dvalidUntil = defel;
240  }
241  else if (strcmp(defel->defname, "bypassrls") == 0)
242  {
243  if (dbypassRLS)
244  ereport(ERROR,
245  (errcode(ERRCODE_SYNTAX_ERROR),
246  errmsg("conflicting or redundant options"),
247  parser_errposition(pstate, defel->location)));
248  dbypassRLS = defel;
249  }
250  else
251  elog(ERROR, "option \"%s\" not recognized",
252  defel->defname);
253  }
254 
255  if (dpassword && dpassword->arg)
256  password = strVal(dpassword->arg);
257  if (dissuper)
258  issuper = intVal(dissuper->arg) != 0;
259  if (dinherit)
260  inherit = intVal(dinherit->arg) != 0;
261  if (dcreaterole)
262  createrole = intVal(dcreaterole->arg) != 0;
263  if (dcreatedb)
264  createdb = intVal(dcreatedb->arg) != 0;
265  if (dcanlogin)
266  canlogin = intVal(dcanlogin->arg) != 0;
267  if (disreplication)
268  isreplication = intVal(disreplication->arg) != 0;
269  if (dconnlimit)
270  {
271  connlimit = intVal(dconnlimit->arg);
272  if (connlimit < -1)
273  ereport(ERROR,
274  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
275  errmsg("invalid connection limit: %d", connlimit)));
276  }
277  if (daddroleto)
278  addroleto = (List *) daddroleto->arg;
279  if (drolemembers)
280  rolemembers = (List *) drolemembers->arg;
281  if (dadminmembers)
282  adminmembers = (List *) dadminmembers->arg;
283  if (dvalidUntil)
284  validUntil = strVal(dvalidUntil->arg);
285  if (dbypassRLS)
286  bypassrls = intVal(dbypassRLS->arg) != 0;
287 
288  /* Check some permissions first */
289  if (issuper)
290  {
291  if (!superuser())
292  ereport(ERROR,
293  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
294  errmsg("must be superuser to create superusers")));
295  }
296  else if (isreplication)
297  {
298  if (!superuser())
299  ereport(ERROR,
300  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
301  errmsg("must be superuser to create replication users")));
302  }
303  else if (bypassrls)
304  {
305  if (!superuser())
306  ereport(ERROR,
307  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
308  errmsg("must be superuser to create bypassrls users")));
309  }
310  else
311  {
313  ereport(ERROR,
314  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
315  errmsg("permission denied to create role")));
316  }
317 
318  /*
319  * Check that the user is not trying to create a role in the reserved
320  * "pg_" namespace.
321  */
322  if (IsReservedName(stmt->role))
323  ereport(ERROR,
324  (errcode(ERRCODE_RESERVED_NAME),
325  errmsg("role name \"%s\" is reserved",
326  stmt->role),
327  errdetail("Role names starting with \"pg_\" are reserved.")));
328 
329  /*
330  * If built with appropriate switch, whine when regression-testing
331  * conventions for role names are violated.
332  */
333 #ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
334  if (strncmp(stmt->role, "regress_", 8) != 0)
335  elog(WARNING, "roles created by regression test cases should have names starting with \"regress_\"");
336 #endif
337 
338  /*
339  * Check the pg_authid relation to be certain the role doesn't already
340  * exist.
341  */
342  pg_authid_rel = table_open(AuthIdRelationId, RowExclusiveLock);
343  pg_authid_dsc = RelationGetDescr(pg_authid_rel);
344 
345  if (OidIsValid(get_role_oid(stmt->role, true)))
346  ereport(ERROR,
348  errmsg("role \"%s\" already exists",
349  stmt->role)));
350 
351  /* Convert validuntil to internal form */
352  if (validUntil)
353  {
354  validUntil_datum = DirectFunctionCall3(timestamptz_in,
355  CStringGetDatum(validUntil),
357  Int32GetDatum(-1));
358  validUntil_null = false;
359  }
360  else
361  {
362  validUntil_datum = (Datum) 0;
363  validUntil_null = true;
364  }
365 
366  /*
367  * Call the password checking hook if there is one defined
368  */
369  if (check_password_hook && password)
370  (*check_password_hook) (stmt->role,
371  password,
372  get_password_type(password),
373  validUntil_datum,
374  validUntil_null);
375 
376  /*
377  * Build a tuple to insert
378  */
379  MemSet(new_record, 0, sizeof(new_record));
380  MemSet(new_record_nulls, false, sizeof(new_record_nulls));
381 
382  new_record[Anum_pg_authid_rolname - 1] =
384 
385  new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(issuper);
386  new_record[Anum_pg_authid_rolinherit - 1] = BoolGetDatum(inherit);
387  new_record[Anum_pg_authid_rolcreaterole - 1] = BoolGetDatum(createrole);
388  new_record[Anum_pg_authid_rolcreatedb - 1] = BoolGetDatum(createdb);
389  new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
390  new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
391  new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
392 
393  if (password)
394  {
395  char *shadow_pass;
396  char *logdetail;
397 
398  /*
399  * Don't allow an empty password. Libpq treats an empty password the
400  * same as no password at all, and won't even try to authenticate. But
401  * other clients might, so allowing it would be confusing. By clearing
402  * the password when an empty string is specified, the account is
403  * consistently locked for all clients.
404  *
405  * Note that this only covers passwords stored in the database itself.
406  * There are also checks in the authentication code, to forbid an
407  * empty password from being used with authentication methods that
408  * fetch the password from an external system, like LDAP or PAM.
409  */
410  if (password[0] == '\0' ||
411  plain_crypt_verify(stmt->role, password, "", &logdetail) == STATUS_OK)
412  {
413  ereport(NOTICE,
414  (errmsg("empty string is not a valid password, clearing password")));
415  new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
416  }
417  else
418  {
419  /* Encrypt the password to the requested format. */
420  shadow_pass = encrypt_password(Password_encryption, stmt->role,
421  password);
422  new_record[Anum_pg_authid_rolpassword - 1] =
423  CStringGetTextDatum(shadow_pass);
424  }
425  }
426  else
427  new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
428 
429  new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
430  new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
431 
432  new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(bypassrls);
433 
434  /*
435  * pg_largeobject_metadata contains pg_authid.oid's, so we use the
436  * binary-upgrade override.
437  */
438  if (IsBinaryUpgrade)
439  {
441  ereport(ERROR,
442  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
443  errmsg("pg_authid OID value not set when in binary upgrade mode")));
444 
447  }
448  else
449  {
450  roleid = GetNewOidWithIndex(pg_authid_rel, AuthIdOidIndexId,
451  Anum_pg_authid_oid);
452  }
453 
454  new_record[Anum_pg_authid_oid - 1] = ObjectIdGetDatum(roleid);
455 
456  tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls);
457 
458  /*
459  * Insert new record in the pg_authid table
460  */
461  CatalogTupleInsert(pg_authid_rel, tuple);
462 
463  /*
464  * Advance command counter so we can see new record; else tests in
465  * AddRoleMems may fail.
466  */
467  if (addroleto || adminmembers || rolemembers)
469 
470  /*
471  * Add the new role to the specified existing roles.
472  */
473  if (addroleto)
474  {
475  RoleSpec *thisrole = makeNode(RoleSpec);
476  List *thisrole_list = list_make1(thisrole);
477  List *thisrole_oidlist = list_make1_oid(roleid);
478 
479  thisrole->roletype = ROLESPEC_CSTRING;
480  thisrole->rolename = stmt->role;
481  thisrole->location = -1;
482 
483  foreach(item, addroleto)
484  {
485  RoleSpec *oldrole = lfirst(item);
486  HeapTuple oldroletup = get_rolespec_tuple(oldrole);
487  Form_pg_authid oldroleform = (Form_pg_authid) GETSTRUCT(oldroletup);
488  Oid oldroleid = oldroleform->oid;
489  char *oldrolename = NameStr(oldroleform->rolname);
490 
491  AddRoleMems(oldrolename, oldroleid,
492  thisrole_list,
493  thisrole_oidlist,
494  GetUserId(), false);
495 
496  ReleaseSysCache(oldroletup);
497  }
498  }
499 
500  /*
501  * Add the specified members to this new role. adminmembers get the admin
502  * option, rolemembers don't.
503  */
504  AddRoleMems(stmt->role, roleid,
505  adminmembers, roleSpecsToIds(adminmembers),
506  GetUserId(), true);
507  AddRoleMems(stmt->role, roleid,
508  rolemembers, roleSpecsToIds(rolemembers),
509  GetUserId(), false);
510 
511  /* Post creation hook for new role */
512  InvokeObjectPostCreateHook(AuthIdRelationId, roleid, 0);
513 
514  /*
515  * Close pg_authid, but keep lock till commit.
516  */
517  table_close(pg_authid_rel, NoLock);
518 
519  return roleid;
520 }
521 
522 
523 /*
524  * ALTER ROLE
525  *
526  * Note: the rolemembers option accepted here is intended to support the
527  * backwards-compatible ALTER GROUP syntax. Although it will work to say
528  * "ALTER ROLE role ROLE rolenames", we don't document it.
529  */
530 Oid
532 {
533  Datum new_record[Natts_pg_authid];
534  bool new_record_nulls[Natts_pg_authid];
535  bool new_record_repl[Natts_pg_authid];
536  Relation pg_authid_rel;
537  TupleDesc pg_authid_dsc;
538  HeapTuple tuple,
539  new_tuple;
540  Form_pg_authid authform;
541  ListCell *option;
542  char *rolename = NULL;
543  char *password = NULL; /* user password */
544  int issuper = -1; /* Make the user a superuser? */
545  int inherit = -1; /* Auto inherit privileges? */
546  int createrole = -1; /* Can this user create roles? */
547  int createdb = -1; /* Can the user create databases? */
548  int canlogin = -1; /* Can this user login? */
549  int isreplication = -1; /* Is this a replication role? */
550  int connlimit = -1; /* maximum connections allowed */
551  List *rolemembers = NIL; /* roles to be added/removed */
552  char *validUntil = NULL; /* time the login is valid until */
553  Datum validUntil_datum; /* same, as timestamptz Datum */
554  bool validUntil_null;
555  int bypassrls = -1;
556  DefElem *dpassword = NULL;
557  DefElem *dissuper = NULL;
558  DefElem *dinherit = NULL;
559  DefElem *dcreaterole = NULL;
560  DefElem *dcreatedb = NULL;
561  DefElem *dcanlogin = NULL;
562  DefElem *disreplication = NULL;
563  DefElem *dconnlimit = NULL;
564  DefElem *drolemembers = NULL;
565  DefElem *dvalidUntil = NULL;
566  DefElem *dbypassRLS = NULL;
567  Oid roleid;
568 
570  "Cannot alter reserved roles.");
571 
572  /* Extract options from the statement node tree */
573  foreach(option, stmt->options)
574  {
575  DefElem *defel = (DefElem *) lfirst(option);
576 
577  if (strcmp(defel->defname, "password") == 0)
578  {
579  if (dpassword)
580  ereport(ERROR,
581  (errcode(ERRCODE_SYNTAX_ERROR),
582  errmsg("conflicting or redundant options")));
583  dpassword = defel;
584  }
585  else if (strcmp(defel->defname, "superuser") == 0)
586  {
587  if (dissuper)
588  ereport(ERROR,
589  (errcode(ERRCODE_SYNTAX_ERROR),
590  errmsg("conflicting or redundant options")));
591  dissuper = defel;
592  }
593  else if (strcmp(defel->defname, "inherit") == 0)
594  {
595  if (dinherit)
596  ereport(ERROR,
597  (errcode(ERRCODE_SYNTAX_ERROR),
598  errmsg("conflicting or redundant options")));
599  dinherit = defel;
600  }
601  else if (strcmp(defel->defname, "createrole") == 0)
602  {
603  if (dcreaterole)
604  ereport(ERROR,
605  (errcode(ERRCODE_SYNTAX_ERROR),
606  errmsg("conflicting or redundant options")));
607  dcreaterole = defel;
608  }
609  else if (strcmp(defel->defname, "createdb") == 0)
610  {
611  if (dcreatedb)
612  ereport(ERROR,
613  (errcode(ERRCODE_SYNTAX_ERROR),
614  errmsg("conflicting or redundant options")));
615  dcreatedb = defel;
616  }
617  else if (strcmp(defel->defname, "canlogin") == 0)
618  {
619  if (dcanlogin)
620  ereport(ERROR,
621  (errcode(ERRCODE_SYNTAX_ERROR),
622  errmsg("conflicting or redundant options")));
623  dcanlogin = defel;
624  }
625  else if (strcmp(defel->defname, "isreplication") == 0)
626  {
627  if (disreplication)
628  ereport(ERROR,
629  (errcode(ERRCODE_SYNTAX_ERROR),
630  errmsg("conflicting or redundant options")));
631  disreplication = defel;
632  }
633  else if (strcmp(defel->defname, "connectionlimit") == 0)
634  {
635  if (dconnlimit)
636  ereport(ERROR,
637  (errcode(ERRCODE_SYNTAX_ERROR),
638  errmsg("conflicting or redundant options")));
639  dconnlimit = defel;
640  }
641  else if (strcmp(defel->defname, "rolemembers") == 0 &&
642  stmt->action != 0)
643  {
644  if (drolemembers)
645  ereport(ERROR,
646  (errcode(ERRCODE_SYNTAX_ERROR),
647  errmsg("conflicting or redundant options")));
648  drolemembers = defel;
649  }
650  else if (strcmp(defel->defname, "validUntil") == 0)
651  {
652  if (dvalidUntil)
653  ereport(ERROR,
654  (errcode(ERRCODE_SYNTAX_ERROR),
655  errmsg("conflicting or redundant options")));
656  dvalidUntil = defel;
657  }
658  else if (strcmp(defel->defname, "bypassrls") == 0)
659  {
660  if (dbypassRLS)
661  ereport(ERROR,
662  (errcode(ERRCODE_SYNTAX_ERROR),
663  errmsg("conflicting or redundant options")));
664  dbypassRLS = defel;
665  }
666  else
667  elog(ERROR, "option \"%s\" not recognized",
668  defel->defname);
669  }
670 
671  if (dpassword && dpassword->arg)
672  password = strVal(dpassword->arg);
673  if (dissuper)
674  issuper = intVal(dissuper->arg);
675  if (dinherit)
676  inherit = intVal(dinherit->arg);
677  if (dcreaterole)
678  createrole = intVal(dcreaterole->arg);
679  if (dcreatedb)
680  createdb = intVal(dcreatedb->arg);
681  if (dcanlogin)
682  canlogin = intVal(dcanlogin->arg);
683  if (disreplication)
684  isreplication = intVal(disreplication->arg);
685  if (dconnlimit)
686  {
687  connlimit = intVal(dconnlimit->arg);
688  if (connlimit < -1)
689  ereport(ERROR,
690  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
691  errmsg("invalid connection limit: %d", connlimit)));
692  }
693  if (drolemembers)
694  rolemembers = (List *) drolemembers->arg;
695  if (dvalidUntil)
696  validUntil = strVal(dvalidUntil->arg);
697  if (dbypassRLS)
698  bypassrls = intVal(dbypassRLS->arg);
699 
700  /*
701  * Scan the pg_authid relation to be certain the user exists.
702  */
703  pg_authid_rel = table_open(AuthIdRelationId, RowExclusiveLock);
704  pg_authid_dsc = RelationGetDescr(pg_authid_rel);
705 
706  tuple = get_rolespec_tuple(stmt->role);
707  authform = (Form_pg_authid) GETSTRUCT(tuple);
708  rolename = pstrdup(NameStr(authform->rolname));
709  roleid = authform->oid;
710 
711  /*
712  * To mess with a superuser or replication role in any way you gotta be
713  * superuser. We also insist on superuser to change the BYPASSRLS
714  * property. Otherwise, if you don't have createrole, you're only allowed
715  * to change your own password.
716  */
717  if (authform->rolsuper || issuper >= 0)
718  {
719  if (!superuser())
720  ereport(ERROR,
721  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
722  errmsg("must be superuser to alter superuser roles or change superuser attribute")));
723  }
724  else if (authform->rolreplication || isreplication >= 0)
725  {
726  if (!superuser())
727  ereport(ERROR,
728  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
729  errmsg("must be superuser to alter replication roles or change replication attribute")));
730  }
731  else if (bypassrls >= 0)
732  {
733  if (!superuser())
734  ereport(ERROR,
735  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
736  errmsg("must be superuser to change bypassrls attribute")));
737  }
738  else if (!have_createrole_privilege())
739  {
740  /* We already checked issuper, isreplication, and bypassrls */
741  if (!(inherit < 0 &&
742  createrole < 0 &&
743  createdb < 0 &&
744  canlogin < 0 &&
745  !dconnlimit &&
746  !rolemembers &&
747  !validUntil &&
748  dpassword &&
749  roleid == GetUserId()))
750  ereport(ERROR,
751  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
752  errmsg("permission denied")));
753  }
754 
755  /* Convert validuntil to internal form */
756  if (validUntil)
757  {
758  validUntil_datum = DirectFunctionCall3(timestamptz_in,
759  CStringGetDatum(validUntil),
761  Int32GetDatum(-1));
762  validUntil_null = false;
763  }
764  else
765  {
766  /* fetch existing setting in case hook needs it */
767  validUntil_datum = SysCacheGetAttr(AUTHNAME, tuple,
768  Anum_pg_authid_rolvaliduntil,
769  &validUntil_null);
770  }
771 
772  /*
773  * Call the password checking hook if there is one defined
774  */
775  if (check_password_hook && password)
776  (*check_password_hook) (rolename,
777  password,
778  get_password_type(password),
779  validUntil_datum,
780  validUntil_null);
781 
782  /*
783  * Build an updated tuple, perusing the information just obtained
784  */
785  MemSet(new_record, 0, sizeof(new_record));
786  MemSet(new_record_nulls, false, sizeof(new_record_nulls));
787  MemSet(new_record_repl, false, sizeof(new_record_repl));
788 
789  /*
790  * issuper/createrole/etc
791  */
792  if (issuper >= 0)
793  {
794  new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(issuper > 0);
795  new_record_repl[Anum_pg_authid_rolsuper - 1] = true;
796  }
797 
798  if (inherit >= 0)
799  {
800  new_record[Anum_pg_authid_rolinherit - 1] = BoolGetDatum(inherit > 0);
801  new_record_repl[Anum_pg_authid_rolinherit - 1] = true;
802  }
803 
804  if (createrole >= 0)
805  {
806  new_record[Anum_pg_authid_rolcreaterole - 1] = BoolGetDatum(createrole > 0);
807  new_record_repl[Anum_pg_authid_rolcreaterole - 1] = true;
808  }
809 
810  if (createdb >= 0)
811  {
812  new_record[Anum_pg_authid_rolcreatedb - 1] = BoolGetDatum(createdb > 0);
813  new_record_repl[Anum_pg_authid_rolcreatedb - 1] = true;
814  }
815 
816  if (canlogin >= 0)
817  {
818  new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin > 0);
819  new_record_repl[Anum_pg_authid_rolcanlogin - 1] = true;
820  }
821 
822  if (isreplication >= 0)
823  {
824  new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication > 0);
825  new_record_repl[Anum_pg_authid_rolreplication - 1] = true;
826  }
827 
828  if (dconnlimit)
829  {
830  new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
831  new_record_repl[Anum_pg_authid_rolconnlimit - 1] = true;
832  }
833 
834  /* password */
835  if (password)
836  {
837  char *shadow_pass;
838  char *logdetail;
839 
840  /* Like in CREATE USER, don't allow an empty password. */
841  if (password[0] == '\0' ||
842  plain_crypt_verify(rolename, password, "", &logdetail) == STATUS_OK)
843  {
844  ereport(NOTICE,
845  (errmsg("empty string is not a valid password, clearing password")));
846  new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
847  }
848  else
849  {
850  /* Encrypt the password to the requested format. */
851  shadow_pass = encrypt_password(Password_encryption, rolename,
852  password);
853  new_record[Anum_pg_authid_rolpassword - 1] =
854  CStringGetTextDatum(shadow_pass);
855  }
856  new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
857  }
858 
859  /* unset password */
860  if (dpassword && dpassword->arg == NULL)
861  {
862  new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
863  new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
864  }
865 
866  /* valid until */
867  new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
868  new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
869  new_record_repl[Anum_pg_authid_rolvaliduntil - 1] = true;
870 
871  if (bypassrls >= 0)
872  {
873  new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(bypassrls > 0);
874  new_record_repl[Anum_pg_authid_rolbypassrls - 1] = true;
875  }
876 
877  new_tuple = heap_modify_tuple(tuple, pg_authid_dsc, new_record,
878  new_record_nulls, new_record_repl);
879  CatalogTupleUpdate(pg_authid_rel, &tuple->t_self, new_tuple);
880 
881  InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
882 
883  ReleaseSysCache(tuple);
884  heap_freetuple(new_tuple);
885 
886  /*
887  * Advance command counter so we can see new record; else tests in
888  * AddRoleMems may fail.
889  */
890  if (rolemembers)
892 
893  if (stmt->action == +1) /* add members to role */
894  AddRoleMems(rolename, roleid,
895  rolemembers, roleSpecsToIds(rolemembers),
896  GetUserId(), false);
897  else if (stmt->action == -1) /* drop members from role */
898  DelRoleMems(rolename, roleid,
899  rolemembers, roleSpecsToIds(rolemembers),
900  false);
901 
902  /*
903  * Close pg_authid, but keep lock till commit.
904  */
905  table_close(pg_authid_rel, NoLock);
906 
907  return roleid;
908 }
909 
910 
911 /*
912  * ALTER ROLE ... SET
913  */
914 Oid
916 {
917  HeapTuple roletuple;
918  Form_pg_authid roleform;
919  Oid databaseid = InvalidOid;
920  Oid roleid = InvalidOid;
921 
922  if (stmt->role)
923  {
925  "Cannot alter reserved roles.");
926 
927  roletuple = get_rolespec_tuple(stmt->role);
928  roleform = (Form_pg_authid) GETSTRUCT(roletuple);
929  roleid = roleform->oid;
930 
931  /*
932  * Obtain a lock on the role and make sure it didn't go away in the
933  * meantime.
934  */
935  shdepLockAndCheckObject(AuthIdRelationId, roleid);
936 
937  /*
938  * To mess with a superuser you gotta be superuser; else you need
939  * createrole, or just want to change your own settings
940  */
941  if (roleform->rolsuper)
942  {
943  if (!superuser())
944  ereport(ERROR,
945  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
946  errmsg("must be superuser to alter superusers")));
947  }
948  else
949  {
950  if (!have_createrole_privilege() && roleid != GetUserId())
951  ereport(ERROR,
952  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
953  errmsg("permission denied")));
954  }
955 
956  ReleaseSysCache(roletuple);
957  }
958 
959  /* look up and lock the database, if specified */
960  if (stmt->database != NULL)
961  {
962  databaseid = get_database_oid(stmt->database, false);
963  shdepLockAndCheckObject(DatabaseRelationId, databaseid);
964 
965  if (!stmt->role)
966  {
967  /*
968  * If no role is specified, then this is effectively the same as
969  * ALTER DATABASE ... SET, so use the same permission check.
970  */
971  if (!pg_database_ownercheck(databaseid, GetUserId()))
973  stmt->database);
974  }
975  }
976 
977  if (!stmt->role && !stmt->database)
978  {
979  /* Must be superuser to alter settings globally. */
980  if (!superuser())
981  ereport(ERROR,
982  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
983  errmsg("must be superuser to alter settings globally")));
984  }
985 
986  AlterSetting(databaseid, roleid, stmt->setstmt);
987 
988  return roleid;
989 }
990 
991 
992 /*
993  * DROP ROLE
994  */
995 void
997 {
998  Relation pg_authid_rel,
999  pg_auth_members_rel;
1000  ListCell *item;
1001 
1003  ereport(ERROR,
1004  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1005  errmsg("permission denied to drop role")));
1006 
1007  /*
1008  * Scan the pg_authid relation to find the Oid of the role(s) to be
1009  * deleted.
1010  */
1011  pg_authid_rel = table_open(AuthIdRelationId, RowExclusiveLock);
1012  pg_auth_members_rel = table_open(AuthMemRelationId, RowExclusiveLock);
1013 
1014  foreach(item, stmt->roles)
1015  {
1016  RoleSpec *rolspec = lfirst(item);
1017  char *role;
1018  HeapTuple tuple,
1019  tmp_tuple;
1020  Form_pg_authid roleform;
1021  ScanKeyData scankey;
1022  char *detail;
1023  char *detail_log;
1024  SysScanDesc sscan;
1025  Oid roleid;
1026 
1027  if (rolspec->roletype != ROLESPEC_CSTRING)
1028  ereport(ERROR,
1029  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1030  errmsg("cannot use special role specifier in DROP ROLE")));
1031  role = rolspec->rolename;
1032 
1033  tuple = SearchSysCache1(AUTHNAME, PointerGetDatum(role));
1034  if (!HeapTupleIsValid(tuple))
1035  {
1036  if (!stmt->missing_ok)
1037  {
1038  ereport(ERROR,
1039  (errcode(ERRCODE_UNDEFINED_OBJECT),
1040  errmsg("role \"%s\" does not exist", role)));
1041  }
1042  else
1043  {
1044  ereport(NOTICE,
1045  (errmsg("role \"%s\" does not exist, skipping",
1046  role)));
1047  }
1048 
1049  continue;
1050  }
1051 
1052  roleform = (Form_pg_authid) GETSTRUCT(tuple);
1053  roleid = roleform->oid;
1054 
1055  if (roleid == GetUserId())
1056  ereport(ERROR,
1057  (errcode(ERRCODE_OBJECT_IN_USE),
1058  errmsg("current user cannot be dropped")));
1059  if (roleid == GetOuterUserId())
1060  ereport(ERROR,
1061  (errcode(ERRCODE_OBJECT_IN_USE),
1062  errmsg("current user cannot be dropped")));
1063  if (roleid == GetSessionUserId())
1064  ereport(ERROR,
1065  (errcode(ERRCODE_OBJECT_IN_USE),
1066  errmsg("session user cannot be dropped")));
1067 
1068  /*
1069  * For safety's sake, we allow createrole holders to drop ordinary
1070  * roles but not superuser roles. This is mainly to avoid the
1071  * scenario where you accidentally drop the last superuser.
1072  */
1073  if (roleform->rolsuper && !superuser())
1074  ereport(ERROR,
1075  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1076  errmsg("must be superuser to drop superusers")));
1077 
1078  /* DROP hook for the role being removed */
1079  InvokeObjectDropHook(AuthIdRelationId, roleid, 0);
1080 
1081  /*
1082  * Lock the role, so nobody can add dependencies to her while we drop
1083  * her. We keep the lock until the end of transaction.
1084  */
1085  LockSharedObject(AuthIdRelationId, roleid, 0, AccessExclusiveLock);
1086 
1087  /* Check for pg_shdepend entries depending on this role */
1088  if (checkSharedDependencies(AuthIdRelationId, roleid,
1089  &detail, &detail_log))
1090  ereport(ERROR,
1091  (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST),
1092  errmsg("role \"%s\" cannot be dropped because some objects depend on it",
1093  role),
1094  errdetail_internal("%s", detail),
1095  errdetail_log("%s", detail_log)));
1096 
1097  /*
1098  * Remove the role from the pg_authid table
1099  */
1100  CatalogTupleDelete(pg_authid_rel, &tuple->t_self);
1101 
1102  ReleaseSysCache(tuple);
1103 
1104  /*
1105  * Remove role from the pg_auth_members table. We have to remove all
1106  * tuples that show it as either a role or a member.
1107  *
1108  * XXX what about grantor entries? Maybe we should do one heap scan.
1109  */
1110  ScanKeyInit(&scankey,
1111  Anum_pg_auth_members_roleid,
1112  BTEqualStrategyNumber, F_OIDEQ,
1113  ObjectIdGetDatum(roleid));
1114 
1115  sscan = systable_beginscan(pg_auth_members_rel, AuthMemRoleMemIndexId,
1116  true, NULL, 1, &scankey);
1117 
1118  while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
1119  {
1120  CatalogTupleDelete(pg_auth_members_rel, &tmp_tuple->t_self);
1121  }
1122 
1123  systable_endscan(sscan);
1124 
1125  ScanKeyInit(&scankey,
1126  Anum_pg_auth_members_member,
1127  BTEqualStrategyNumber, F_OIDEQ,
1128  ObjectIdGetDatum(roleid));
1129 
1130  sscan = systable_beginscan(pg_auth_members_rel, AuthMemMemRoleIndexId,
1131  true, NULL, 1, &scankey);
1132 
1133  while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
1134  {
1135  CatalogTupleDelete(pg_auth_members_rel, &tmp_tuple->t_self);
1136  }
1137 
1138  systable_endscan(sscan);
1139 
1140  /*
1141  * Remove any comments or security labels on this role.
1142  */
1143  DeleteSharedComments(roleid, AuthIdRelationId);
1144  DeleteSharedSecurityLabel(roleid, AuthIdRelationId);
1145 
1146  /*
1147  * Remove settings for this role.
1148  */
1149  DropSetting(InvalidOid, roleid);
1150 
1151  /*
1152  * Advance command counter so that later iterations of this loop will
1153  * see the changes already made. This is essential if, for example,
1154  * we are trying to drop both a role and one of its direct members ---
1155  * we'll get an error if we try to delete the linking pg_auth_members
1156  * tuple twice. (We do not need a CCI between the two delete loops
1157  * above, because it's not allowed for a role to directly contain
1158  * itself.)
1159  */
1161  }
1162 
1163  /*
1164  * Now we can clean up; but keep locks until commit.
1165  */
1166  table_close(pg_auth_members_rel, NoLock);
1167  table_close(pg_authid_rel, NoLock);
1168 }
1169 
1170 /*
1171  * Rename role
1172  */
1174 RenameRole(const char *oldname, const char *newname)
1175 {
1176  HeapTuple oldtuple,
1177  newtuple;
1178  TupleDesc dsc;
1179  Relation rel;
1180  Datum datum;
1181  bool isnull;
1182  Datum repl_val[Natts_pg_authid];
1183  bool repl_null[Natts_pg_authid];
1184  bool repl_repl[Natts_pg_authid];
1185  int i;
1186  Oid roleid;
1187  ObjectAddress address;
1188  Form_pg_authid authform;
1189 
1190  rel = table_open(AuthIdRelationId, RowExclusiveLock);
1191  dsc = RelationGetDescr(rel);
1192 
1193  oldtuple = SearchSysCache1(AUTHNAME, CStringGetDatum(oldname));
1194  if (!HeapTupleIsValid(oldtuple))
1195  ereport(ERROR,
1196  (errcode(ERRCODE_UNDEFINED_OBJECT),
1197  errmsg("role \"%s\" does not exist", oldname)));
1198 
1199  /*
1200  * XXX Client applications probably store the session user somewhere, so
1201  * renaming it could cause confusion. On the other hand, there may not be
1202  * an actual problem besides a little confusion, so think about this and
1203  * decide. Same for SET ROLE ... we don't restrict renaming the current
1204  * effective userid, though.
1205  */
1206 
1207  authform = (Form_pg_authid) GETSTRUCT(oldtuple);
1208  roleid = authform->oid;
1209 
1210  if (roleid == GetSessionUserId())
1211  ereport(ERROR,
1212  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1213  errmsg("session user cannot be renamed")));
1214  if (roleid == GetOuterUserId())
1215  ereport(ERROR,
1216  (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1217  errmsg("current user cannot be renamed")));
1218 
1219  /*
1220  * Check that the user is not trying to rename a system role and not
1221  * trying to rename a role into the reserved "pg_" namespace.
1222  */
1223  if (IsReservedName(NameStr(authform->rolname)))
1224  ereport(ERROR,
1225  (errcode(ERRCODE_RESERVED_NAME),
1226  errmsg("role name \"%s\" is reserved",
1227  NameStr(authform->rolname)),
1228  errdetail("Role names starting with \"pg_\" are reserved.")));
1229 
1230  if (IsReservedName(newname))
1231  ereport(ERROR,
1232  (errcode(ERRCODE_RESERVED_NAME),
1233  errmsg("role name \"%s\" is reserved",
1234  newname),
1235  errdetail("Role names starting with \"pg_\" are reserved.")));
1236 
1237  /*
1238  * If built with appropriate switch, whine when regression-testing
1239  * conventions for role names are violated.
1240  */
1241 #ifdef ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS
1242  if (strncmp(newname, "regress_", 8) != 0)
1243  elog(WARNING, "roles created by regression test cases should have names starting with \"regress_\"");
1244 #endif
1245 
1246  /* make sure the new name doesn't exist */
1248  ereport(ERROR,
1250  errmsg("role \"%s\" already exists", newname)));
1251 
1252  /*
1253  * createrole is enough privilege unless you want to mess with a superuser
1254  */
1255  if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper)
1256  {
1257  if (!superuser())
1258  ereport(ERROR,
1259  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1260  errmsg("must be superuser to rename superusers")));
1261  }
1262  else
1263  {
1265  ereport(ERROR,
1266  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1267  errmsg("permission denied to rename role")));
1268  }
1269 
1270  /* OK, construct the modified tuple */
1271  for (i = 0; i < Natts_pg_authid; i++)
1272  repl_repl[i] = false;
1273 
1274  repl_repl[Anum_pg_authid_rolname - 1] = true;
1275  repl_val[Anum_pg_authid_rolname - 1] = DirectFunctionCall1(namein,
1276  CStringGetDatum(newname));
1277  repl_null[Anum_pg_authid_rolname - 1] = false;
1278 
1279  datum = heap_getattr(oldtuple, Anum_pg_authid_rolpassword, dsc, &isnull);
1280 
1281  if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
1282  {
1283  /* MD5 uses the username as salt, so just clear it on a rename */
1284  repl_repl[Anum_pg_authid_rolpassword - 1] = true;
1285  repl_null[Anum_pg_authid_rolpassword - 1] = true;
1286 
1287  ereport(NOTICE,
1288  (errmsg("MD5 password cleared because of role rename")));
1289  }
1290 
1291  newtuple = heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_repl);
1292  CatalogTupleUpdate(rel, &oldtuple->t_self, newtuple);
1293 
1294  InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
1295 
1296  ObjectAddressSet(address, AuthIdRelationId, roleid);
1297 
1298  ReleaseSysCache(oldtuple);
1299 
1300  /*
1301  * Close pg_authid, but keep lock till commit.
1302  */
1303  table_close(rel, NoLock);
1304 
1305  return address;
1306 }
1307 
1308 /*
1309  * GrantRoleStmt
1310  *
1311  * Grant/Revoke roles to/from roles
1312  */
1313 void
1315 {
1316  Relation pg_authid_rel;
1317  Oid grantor;
1318  List *grantee_ids;
1319  ListCell *item;
1320 
1321  if (stmt->grantor)
1322  grantor = get_rolespec_oid(stmt->grantor, false);
1323  else
1324  grantor = GetUserId();
1325 
1326  grantee_ids = roleSpecsToIds(stmt->grantee_roles);
1327 
1328  /* AccessShareLock is enough since we aren't modifying pg_authid */
1329  pg_authid_rel = table_open(AuthIdRelationId, AccessShareLock);
1330 
1331  /*
1332  * Step through all of the granted roles and add/remove entries for the
1333  * grantees, or, if admin_opt is set, then just add/remove the admin
1334  * option.
1335  *
1336  * Note: Permissions checking is done by AddRoleMems/DelRoleMems
1337  */
1338  foreach(item, stmt->granted_roles)
1339  {
1340  AccessPriv *priv = (AccessPriv *) lfirst(item);
1341  char *rolename = priv->priv_name;
1342  Oid roleid;
1343 
1344  /* Must reject priv(columns) and ALL PRIVILEGES(columns) */
1345  if (rolename == NULL || priv->cols != NIL)
1346  ereport(ERROR,
1347  (errcode(ERRCODE_INVALID_GRANT_OPERATION),
1348  errmsg("column names cannot be included in GRANT/REVOKE ROLE")));
1349 
1350  roleid = get_role_oid(rolename, false);
1351  if (stmt->is_grant)
1352  AddRoleMems(rolename, roleid,
1353  stmt->grantee_roles, grantee_ids,
1354  grantor, stmt->admin_opt);
1355  else
1356  DelRoleMems(rolename, roleid,
1357  stmt->grantee_roles, grantee_ids,
1358  stmt->admin_opt);
1359  }
1360 
1361  /*
1362  * Close pg_authid, but keep lock till commit.
1363  */
1364  table_close(pg_authid_rel, NoLock);
1365 }
1366 
1367 /*
1368  * DropOwnedObjects
1369  *
1370  * Drop the objects owned by a given list of roles.
1371  */
1372 void
1374 {
1375  List *role_ids = roleSpecsToIds(stmt->roles);
1376  ListCell *cell;
1377 
1378  /* Check privileges */
1379  foreach(cell, role_ids)
1380  {
1381  Oid roleid = lfirst_oid(cell);
1382 
1383  if (!has_privs_of_role(GetUserId(), roleid))
1384  ereport(ERROR,
1385  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1386  errmsg("permission denied to drop objects")));
1387  }
1388 
1389  /* Ok, do it */
1390  shdepDropOwned(role_ids, stmt->behavior);
1391 }
1392 
1393 /*
1394  * ReassignOwnedObjects
1395  *
1396  * Give the objects owned by a given list of roles away to another user.
1397  */
1398 void
1400 {
1401  List *role_ids = roleSpecsToIds(stmt->roles);
1402  ListCell *cell;
1403  Oid newrole;
1404 
1405  /* Check privileges */
1406  foreach(cell, role_ids)
1407  {
1408  Oid roleid = lfirst_oid(cell);
1409 
1410  if (!has_privs_of_role(GetUserId(), roleid))
1411  ereport(ERROR,
1412  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1413  errmsg("permission denied to reassign objects")));
1414  }
1415 
1416  /* Must have privileges on the receiving side too */
1417  newrole = get_rolespec_oid(stmt->newrole, false);
1418 
1419  if (!has_privs_of_role(GetUserId(), newrole))
1420  ereport(ERROR,
1421  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1422  errmsg("permission denied to reassign objects")));
1423 
1424  /* Ok, do it */
1425  shdepReassignOwned(role_ids, newrole);
1426 }
1427 
1428 /*
1429  * roleSpecsToIds
1430  *
1431  * Given a list of RoleSpecs, generate a list of role OIDs in the same order.
1432  *
1433  * ROLESPEC_PUBLIC is not allowed.
1434  */
1435 List *
1436 roleSpecsToIds(List *memberNames)
1437 {
1438  List *result = NIL;
1439  ListCell *l;
1440 
1441  foreach(l, memberNames)
1442  {
1443  RoleSpec *rolespec = lfirst_node(RoleSpec, l);
1444  Oid roleid;
1445 
1446  roleid = get_rolespec_oid(rolespec, false);
1447  result = lappend_oid(result, roleid);
1448  }
1449  return result;
1450 }
1451 
1452 /*
1453  * AddRoleMems -- Add given members to the specified role
1454  *
1455  * rolename: name of role to add to (used only for error messages)
1456  * roleid: OID of role to add to
1457  * memberSpecs: list of RoleSpec of roles to add (used only for error messages)
1458  * memberIds: OIDs of roles to add
1459  * grantorId: who is granting the membership
1460  * admin_opt: granting admin option?
1461  */
1462 static void
1463 AddRoleMems(const char *rolename, Oid roleid,
1464  List *memberSpecs, List *memberIds,
1465  Oid grantorId, bool admin_opt)
1466 {
1467  Relation pg_authmem_rel;
1468  TupleDesc pg_authmem_dsc;
1469  ListCell *specitem;
1470  ListCell *iditem;
1471 
1472  Assert(list_length(memberSpecs) == list_length(memberIds));
1473 
1474  /* Skip permission check if nothing to do */
1475  if (!memberIds)
1476  return;
1477 
1478  /*
1479  * Check permissions: must have createrole or admin option on the role to
1480  * be changed. To mess with a superuser role, you gotta be superuser.
1481  */
1482  if (superuser_arg(roleid))
1483  {
1484  if (!superuser())
1485  ereport(ERROR,
1486  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1487  errmsg("must be superuser to alter superusers")));
1488  }
1489  else
1490  {
1491  if (!have_createrole_privilege() &&
1492  !is_admin_of_role(grantorId, roleid))
1493  ereport(ERROR,
1494  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1495  errmsg("must have admin option on role \"%s\"",
1496  rolename)));
1497  }
1498 
1499  /*
1500  * The role membership grantor of record has little significance at
1501  * present. Nonetheless, inasmuch as users might look to it for a crude
1502  * audit trail, let only superusers impute the grant to a third party.
1503  *
1504  * Before lifting this restriction, give the member == role case of
1505  * is_admin_of_role() a fresh look. Ensure that the current role cannot
1506  * use an explicit grantor specification to take advantage of the session
1507  * user's self-admin right.
1508  */
1509  if (grantorId != GetUserId() && !superuser())
1510  ereport(ERROR,
1511  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1512  errmsg("must be superuser to set grantor")));
1513 
1514  pg_authmem_rel = table_open(AuthMemRelationId, RowExclusiveLock);
1515  pg_authmem_dsc = RelationGetDescr(pg_authmem_rel);
1516 
1517  forboth(specitem, memberSpecs, iditem, memberIds)
1518  {
1519  RoleSpec *memberRole = lfirst_node(RoleSpec, specitem);
1520  Oid memberid = lfirst_oid(iditem);
1521  HeapTuple authmem_tuple;
1522  HeapTuple tuple;
1523  Datum new_record[Natts_pg_auth_members];
1524  bool new_record_nulls[Natts_pg_auth_members];
1525  bool new_record_repl[Natts_pg_auth_members];
1526 
1527  /*
1528  * Refuse creation of membership loops, including the trivial case
1529  * where a role is made a member of itself. We do this by checking to
1530  * see if the target role is already a member of the proposed member
1531  * role. We have to ignore possible superuserness, however, else we
1532  * could never grant membership in a superuser-privileged role.
1533  */
1534  if (is_member_of_role_nosuper(roleid, memberid))
1535  ereport(ERROR,
1536  (errcode(ERRCODE_INVALID_GRANT_OPERATION),
1537  errmsg("role \"%s\" is a member of role \"%s\"",
1538  rolename, get_rolespec_name(memberRole))));
1539 
1540  /*
1541  * Check if entry for this role/member already exists; if so, give
1542  * warning unless we are adding admin option.
1543  */
1544  authmem_tuple = SearchSysCache2(AUTHMEMROLEMEM,
1545  ObjectIdGetDatum(roleid),
1546  ObjectIdGetDatum(memberid));
1547  if (HeapTupleIsValid(authmem_tuple) &&
1548  (!admin_opt ||
1549  ((Form_pg_auth_members) GETSTRUCT(authmem_tuple))->admin_option))
1550  {
1551  ereport(NOTICE,
1552  (errmsg("role \"%s\" is already a member of role \"%s\"",
1553  get_rolespec_name(memberRole), rolename)));
1554  ReleaseSysCache(authmem_tuple);
1555  continue;
1556  }
1557 
1558  /* Build a tuple to insert or update */
1559  MemSet(new_record, 0, sizeof(new_record));
1560  MemSet(new_record_nulls, false, sizeof(new_record_nulls));
1561  MemSet(new_record_repl, false, sizeof(new_record_repl));
1562 
1563  new_record[Anum_pg_auth_members_roleid - 1] = ObjectIdGetDatum(roleid);
1564  new_record[Anum_pg_auth_members_member - 1] = ObjectIdGetDatum(memberid);
1565  new_record[Anum_pg_auth_members_grantor - 1] = ObjectIdGetDatum(grantorId);
1566  new_record[Anum_pg_auth_members_admin_option - 1] = BoolGetDatum(admin_opt);
1567 
1568  if (HeapTupleIsValid(authmem_tuple))
1569  {
1570  new_record_repl[Anum_pg_auth_members_grantor - 1] = true;
1571  new_record_repl[Anum_pg_auth_members_admin_option - 1] = true;
1572  tuple = heap_modify_tuple(authmem_tuple, pg_authmem_dsc,
1573  new_record,
1574  new_record_nulls, new_record_repl);
1575  CatalogTupleUpdate(pg_authmem_rel, &tuple->t_self, tuple);
1576  ReleaseSysCache(authmem_tuple);
1577  }
1578  else
1579  {
1580  tuple = heap_form_tuple(pg_authmem_dsc,
1581  new_record, new_record_nulls);
1582  CatalogTupleInsert(pg_authmem_rel, tuple);
1583  }
1584 
1585  /* CCI after each change, in case there are duplicates in list */
1587  }
1588 
1589  /*
1590  * Close pg_authmem, but keep lock till commit.
1591  */
1592  table_close(pg_authmem_rel, NoLock);
1593 }
1594 
1595 /*
1596  * DelRoleMems -- Remove given members from the specified role
1597  *
1598  * rolename: name of role to del from (used only for error messages)
1599  * roleid: OID of role to del from
1600  * memberSpecs: list of RoleSpec of roles to del (used only for error messages)
1601  * memberIds: OIDs of roles to del
1602  * admin_opt: remove admin option only?
1603  */
1604 static void
1605 DelRoleMems(const char *rolename, Oid roleid,
1606  List *memberSpecs, List *memberIds,
1607  bool admin_opt)
1608 {
1609  Relation pg_authmem_rel;
1610  TupleDesc pg_authmem_dsc;
1611  ListCell *specitem;
1612  ListCell *iditem;
1613 
1614  Assert(list_length(memberSpecs) == list_length(memberIds));
1615 
1616  /* Skip permission check if nothing to do */
1617  if (!memberIds)
1618  return;
1619 
1620  /*
1621  * Check permissions: must have createrole or admin option on the role to
1622  * be changed. To mess with a superuser role, you gotta be superuser.
1623  */
1624  if (superuser_arg(roleid))
1625  {
1626  if (!superuser())
1627  ereport(ERROR,
1628  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1629  errmsg("must be superuser to alter superusers")));
1630  }
1631  else
1632  {
1633  if (!have_createrole_privilege() &&
1634  !is_admin_of_role(GetUserId(), roleid))
1635  ereport(ERROR,
1636  (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1637  errmsg("must have admin option on role \"%s\"",
1638  rolename)));
1639  }
1640 
1641  pg_authmem_rel = table_open(AuthMemRelationId, RowExclusiveLock);
1642  pg_authmem_dsc = RelationGetDescr(pg_authmem_rel);
1643 
1644  forboth(specitem, memberSpecs, iditem, memberIds)
1645  {
1646  RoleSpec *memberRole = lfirst(specitem);
1647  Oid memberid = lfirst_oid(iditem);
1648  HeapTuple authmem_tuple;
1649 
1650  /*
1651  * Find entry for this role/member
1652  */
1653  authmem_tuple = SearchSysCache2(AUTHMEMROLEMEM,
1654  ObjectIdGetDatum(roleid),
1655  ObjectIdGetDatum(memberid));
1656  if (!HeapTupleIsValid(authmem_tuple))
1657  {
1658  ereport(WARNING,
1659  (errmsg("role \"%s\" is not a member of role \"%s\"",
1660  get_rolespec_name(memberRole), rolename)));
1661  continue;
1662  }
1663 
1664  if (!admin_opt)
1665  {
1666  /* Remove the entry altogether */
1667  CatalogTupleDelete(pg_authmem_rel, &authmem_tuple->t_self);
1668  }
1669  else
1670  {
1671  /* Just turn off the admin option */
1672  HeapTuple tuple;
1673  Datum new_record[Natts_pg_auth_members];
1674  bool new_record_nulls[Natts_pg_auth_members];
1675  bool new_record_repl[Natts_pg_auth_members];
1676 
1677  /* Build a tuple to update with */
1678  MemSet(new_record, 0, sizeof(new_record));
1679  MemSet(new_record_nulls, false, sizeof(new_record_nulls));
1680  MemSet(new_record_repl, false, sizeof(new_record_repl));
1681 
1682  new_record[Anum_pg_auth_members_admin_option - 1] = BoolGetDatum(false);
1683  new_record_repl[Anum_pg_auth_members_admin_option - 1] = true;
1684 
1685  tuple = heap_modify_tuple(authmem_tuple, pg_authmem_dsc,
1686  new_record,
1687  new_record_nulls, new_record_repl);
1688  CatalogTupleUpdate(pg_authmem_rel, &tuple->t_self, tuple);
1689  }
1690 
1691  ReleaseSysCache(authmem_tuple);
1692 
1693  /* CCI after each change, in case there are duplicates in list */
1695  }
1696 
1697  /*
1698  * Close pg_authmem, but keep lock till commit.
1699  */
1700  table_close(pg_authmem_rel, NoLock);
1701 }
bool has_createrole_privilege(Oid roleid)
Definition: aclchk.c:5312
#define NIL
Definition: pg_list.h:65
Oid GetNewOidWithIndex(Relation relation, Oid indexId, AttrNumber oidcolumn)
Definition: catalog.c:315
RoleSpec * role
Definition: parsenodes.h:2515
void shdepDropOwned(List *roleids, DropBehavior behavior)
Definition: pg_shdepend.c:1285
Datum namein(PG_FUNCTION_ARGS)
Definition: name.c:48
void table_close(Relation relation, LOCKMODE lockmode)
Definition: table.c:167
#define forboth(cell1, list1, cell2, list2)
Definition: pg_list.h:434
void systable_endscan(SysScanDesc sysscan)
Definition: genam.c:569
#define GETSTRUCT(TUP)
Definition: htup_details.h:655
Oid binary_upgrade_next_pg_authid_oid
Definition: user.c:42
#define InvokeObjectPostCreateHook(classId, objectId, subId)
Definition: objectaccess.h:151
#define RelationGetDescr(relation)
Definition: rel.h:483
Oid GetUserId(void)
Definition: miscinit.c:476
int Password_encryption
Definition: user.c:46
#define PointerGetDatum(X)
Definition: postgres.h:556
RoleStmtType stmt_type
Definition: parsenodes.h:2507
char * pstrdup(const char *in)
Definition: mcxt.c:1187
void DropOwnedObjects(DropOwnedStmt *stmt)
Definition: user.c:1373
bool has_privs_of_role(Oid member, Oid role)
Definition: acl.c:4892
void AlterSetting(Oid databaseid, Oid roleid, VariableSetStmt *setstmt)
#define InvokeObjectDropHook(classId, objectId, subId)
Definition: objectaccess.h:160
char * encrypt_password(PasswordType target_type, const char *role, const char *password)
Definition: crypt.c:114
bool is_admin_of_role(Oid member, Oid role)
Definition: acl.c:4974
#define AccessShareLock
Definition: lockdefs.h:36
int plain_crypt_verify(const char *role, const char *shadow_pass, const char *client_pass, char **logdetail)
Definition: crypt.c:222
#define strVal(v)
Definition: value.h:54
int errcode(int sqlerrcode)
Definition: elog.c:691
bool superuser(void)
Definition: superuser.c:46
#define MemSet(start, val, len)
Definition: c.h:1004
List * granted_roles
Definition: parsenodes.h:1977
void CatalogTupleDelete(Relation heapRel, ItemPointer tid)
Definition: indexing.c:350
RoleSpec * role
Definition: parsenodes.h:2523
HeapTuple heap_form_tuple(TupleDesc tupleDescriptor, Datum *values, bool *isnull)
Definition: heaptuple.c:1020
List * roles
Definition: parsenodes.h:2531
List * cols
Definition: parsenodes.h:1962
#define DirectFunctionCall1(func, arg1)
Definition: fmgr.h:626
void heap_freetuple(HeapTuple htup)
Definition: heaptuple.c:1338
unsigned int Oid
Definition: postgres_ext.h:31
bool IsReservedName(const char *name)
Definition: catalog.c:210
List * lappend_oid(List *list, Oid datum)
Definition: list.c:357
#define OidIsValid(objectId)
Definition: c.h:706
void(* check_password_hook_type)(const char *username, const char *shadow_pass, PasswordType password_type, Datum validuntil_time, bool validuntil_null)
Definition: user.h:23
Oid GetSessionUserId(void)
Definition: miscinit.c:510
SysScanDesc systable_beginscan(Relation heapRelation, Oid indexId, bool indexOK, Snapshot snapshot, int nkeys, ScanKey key)
Definition: genam.c:357
bool IsBinaryUpgrade
Definition: globals.c:110
Oid CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
Definition: user.c:71
int errdetail_internal(const char *fmt,...)
Definition: elog.c:1062
Oid get_role_oid(const char *rolname, bool missing_ok)
Definition: acl.c:5175
#define AuthMemMemRoleIndexId
#define list_make1(x1)
Definition: pg_list.h:206
Oid grantor
FormData_pg_authid * Form_pg_authid
Definition: pg_authid.h:56
void aclcheck_error(AclResult aclerr, ObjectType objtype, const char *objectname)
Definition: aclchk.c:3294
HeapTuple systable_getnext(SysScanDesc sysscan)
Definition: genam.c:476
#define SearchSysCacheExists1(cacheId, key1)
Definition: syscache.h:183
Oid GetOuterUserId(void)
Definition: miscinit.c:487
#define ObjectIdGetDatum(X)
Definition: postgres.h:507
#define ERROR
Definition: elog.h:43
void GrantRole(GrantRoleStmt *stmt)
Definition: user.c:1314
static void AddRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, Oid grantorId, bool admin_opt)
Definition: user.c:1463
void shdepLockAndCheckObject(Oid classId, Oid objectId)
Definition: pg_shdepend.c:1109
ItemPointerData t_self
Definition: htup.h:65
#define lfirst_node(type, lc)
Definition: pg_list.h:172
#define NoLock
Definition: lockdefs.h:34
void shdepReassignOwned(List *roleids, Oid newrole)
Definition: pg_shdepend.c:1439
int location
Definition: parsenodes.h:736
#define RowExclusiveLock
Definition: lockdefs.h:38
int errdetail(const char *fmt,...)
Definition: elog.c:1035
#define CStringGetDatum(X)
Definition: postgres.h:578
int errdetail_log(const char *fmt,...)
Definition: elog.c:1083
static char * password
Definition: streamutil.c:53
DropBehavior behavior
Definition: parsenodes.h:3460
#define STATUS_OK
Definition: c.h:1166
#define InvokeObjectPostAlterHook(classId, objectId, subId)
Definition: objectaccess.h:175
RoleSpec * grantor
Definition: parsenodes.h:1981
bool pg_database_ownercheck(Oid db_oid, Oid roleid)
Definition: aclchk.c:5105
bool superuser_arg(Oid roleid)
Definition: superuser.c:56
#define DirectFunctionCall3(func, arg1, arg2, arg3)
Definition: fmgr.h:630
Node * arg
Definition: parsenodes.h:734
char * get_rolespec_name(const RoleSpec *role)
Definition: acl.c:5294
int location
Definition: parsenodes.h:331
#define WARNING
Definition: elog.h:40
Oid AlterRole(AlterRoleStmt *stmt)
Definition: user.c:531
#define heap_getattr(tup, attnum, tupleDesc, isnull)
Definition: htup_details.h:762
HeapTuple SearchSysCache1(int cacheId, Datum key1)
Definition: syscache.c:1115
#define TextDatumGetCString(d)
Definition: builtins.h:87
uintptr_t Datum
Definition: postgres.h:367
void CommandCounterIncrement(void)
Definition: xact.c:1021
void check_rolespec_name(const RoleSpec *role, const char *detail_msg)
Definition: acl.c:5316
void ReleaseSysCache(HeapTuple tuple)
Definition: syscache.c:1163
RoleSpecType roletype
Definition: parsenodes.h:329
Oid AlterRoleSet(AlterRoleSetStmt *stmt)
Definition: user.c:915
Datum SysCacheGetAttr(int cacheId, HeapTuple tup, AttrNumber attributeNumber, bool *isNull)
Definition: syscache.c:1376
#define list_make1_oid(x1)
Definition: pg_list.h:228
Oid get_rolespec_oid(const RoleSpec *role, bool missing_ok)
Definition: acl.c:5209
FormData_pg_auth_members * Form_pg_auth_members
void LockSharedObject(Oid classid, Oid objid, uint16 objsubid, LOCKMODE lockmode)
Definition: lmgr.c:1017
VariableSetStmt * setstmt
Definition: parsenodes.h:2525
bool admin_option
#define BoolGetDatum(X)
Definition: postgres.h:402
Oid get_database_oid(const char *dbname, bool missing_ok)
Definition: dbcommands.c:2108
#define InvalidOid
Definition: postgres_ext.h:36
ObjectAddress RenameRole(const char *oldname, const char *newname)
Definition: user.c:1174
#define ereport(elevel,...)
Definition: elog.h:155
#define NOTICE
Definition: elog.h:37
List * roleSpecsToIds(List *memberNames)
Definition: user.c:1436
#define makeNode(_type_)
Definition: nodes.h:575
#define HeapTupleIsValid(tuple)
Definition: htup.h:78
#define Assert(condition)
Definition: c.h:800
#define lfirst(lc)
Definition: pg_list.h:169
void DeleteSharedComments(Oid oid, Oid classoid)
Definition: comment.c:373
List * options
Definition: parsenodes.h:2516
HeapTuple get_rolespec_tuple(const RoleSpec *role)
Definition: acl.c:5248
void CatalogTupleUpdate(Relation heapRel, ItemPointer otid, HeapTuple tup)
Definition: indexing.c:301
static int list_length(const List *l)
Definition: pg_list.h:149
int parser_errposition(ParseState *pstate, int location)
Definition: parse_node.c:110
RoleSpec * newrole
Definition: parsenodes.h:3470
HeapTuple SearchSysCache2(int cacheId, Datum key1, Datum key2)
Definition: syscache.c:1126
char * rolename
Definition: parsenodes.h:330
#define AuthIdOidIndexId
Definition: pg_authid.h:65
#define ObjectAddressSet(addr, class_id, object_id)
Definition: objectaddress.h:40
void DeleteSharedSecurityLabel(Oid objectId, Oid classId)
Definition: seclabel.c:488
#define AccessExclusiveLock
Definition: lockdefs.h:45
#define Int32GetDatum(X)
Definition: postgres.h:479
bool is_member_of_role_nosuper(Oid member, Oid role)
Definition: acl.c:4954
Datum timestamptz_in(PG_FUNCTION_ARGS)
Definition: timestamp.c:401
#define intVal(v)
Definition: value.h:52
Oid createdb(ParseState *pstate, const CreatedbStmt *stmt)
Definition: dbcommands.c:100
int errmsg(const char *fmt,...)
Definition: elog.c:902
PasswordType get_password_type(const char *shadow_pass)
Definition: crypt.c:89
#define elog(elevel,...)
Definition: elog.h:228
void ReassignOwnedObjects(ReassignOwnedStmt *stmt)
Definition: user.c:1399
static bool have_createrole_privilege(void)
Definition: user.c:61
int i
#define NameStr(name)
Definition: c.h:677
void ScanKeyInit(ScanKey entry, AttrNumber attributeNumber, StrategyNumber strategy, RegProcedure procedure, Datum argument)
Definition: scankey.c:76
#define CStringGetTextDatum(s)
Definition: builtins.h:86
void DropRole(DropRoleStmt *stmt)
Definition: user.c:996
char * defname
Definition: parsenodes.h:733
List * grantee_roles
Definition: parsenodes.h:1978
bool checkSharedDependencies(Oid classId, Oid objectId, char **detail_msg, char **detail_log_msg)
Definition: pg_shdepend.c:577
Relation table_open(Oid relationId, LOCKMODE lockmode)
Definition: table.c:39
HeapTuple heap_modify_tuple(HeapTuple tuple, TupleDesc tupleDesc, Datum *replValues, bool *replIsnull, bool *doReplace)
Definition: heaptuple.c:1113
check_password_hook_type check_password_hook
Definition: user.c:49
void DropSetting(Oid databaseid, Oid roleid)
#define ERRCODE_DUPLICATE_OBJECT
Definition: streamutil.c:32
Definition: pg_list.h:50
#define AuthMemRoleMemIndexId
void CatalogTupleInsert(Relation heapRel, HeapTuple tup)
Definition: indexing.c:221
#define BTEqualStrategyNumber
Definition: stratnum.h:31
char * priv_name
Definition: parsenodes.h:1961
#define lfirst_oid(lc)
Definition: pg_list.h:171
static void DelRoleMems(const char *rolename, Oid roleid, List *memberSpecs, List *memberIds, bool admin_opt)
Definition: user.c:1605