* Stephen Frost ([EMAIL PROTECTED]) wrote:
> * Tom Lane ([EMAIL PROTECTED]) wrote:
> > Stephen Frost <[EMAIL PROTECTED]> writes:
> > > Tom, if you're watching, are you working on this? I can probably spend
> > > some time today on it, if that'd be helpful.
> >
> > I am not; I was hoping you'd deal with SET ROLE. Is it really much
> > different from SET SESSION AUTHORIZATION?
>
> Here's a much better version of the SET ROLE work. I'm reasonably happy
> with it. The only parts I don't like are that I had to do some ugly
> things in gram.y to avoid making NONE reserved, and I can't seem to see
> how to avoid having ROLE be reserved (I understand it was reserved in
> SQL99 but not in SQL2003...).
Updated yet again, fixing a bug in the prior one that caused it to not
work properly, and some additional things:
Added a 'has_role' function that's basically is_member_of_role for the
masses. Updated information_schema to use has_role for permissions
checks in addition to the straight '=' owner-check. Also fixed up
enabled_roles and applicable_roles views. This depends somewhat on part
of my other patch where I modified is_member_of_role to always return
true for superuser(). If that doesn't end up being done then we'll need
to add some explicit superuser() checks in the SetCurrentRoleId() logic.
Thanks,
Stephen
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.114
diff -c -r1.114 aclchk.c
*** src/backend/catalog/aclchk.c 28 Jun 2005 19:51:21 -0000 1.114
--- src/backend/catalog/aclchk.c 3 Jul 2005 18:30:18 -0000
***************
*** 76,88 ****
* GRANT or REVOKE, we pretend he is the object owner. This ensures
that
* all granted privileges appear to flow from the object owner, and there
* are never multiple "original sources" of a privilege.
*/
static Oid
select_grantor(Oid ownerId)
{
Oid grantorId;
! grantorId = GetUserId();
/* fast path if no difference */
if (grantorId == ownerId)
--- 76,93 ----
* GRANT or REVOKE, we pretend he is the object owner. This ensures
that
* all granted privileges appear to flow from the object owner, and there
* are never multiple "original sources" of a privilege.
+ *
+ * NOTE: SQL spec defines that, unless 'GRANTED BY' is set, the grantor
+ * is the CURRENT_USER if it is set and the CURRENT_ROLE otherwise.
+ * CURRENT_USER is always set for us, so we use that. SQL spec does not
+ * show a way which CURRENT_USER could become unset.
*/
static Oid
select_grantor(Oid ownerId)
{
Oid grantorId;
! grantorId = GetCurrentUserId();
/* fast path if no difference */
if (grantorId == ownerId)
Index: src/backend/catalog/information_schema.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/information_schema.sql,v
retrieving revision 1.29
diff -c -r1.29 information_schema.sql
*** src/backend/catalog/information_schema.sql 28 Jun 2005 05:08:52 -0000
1.29
--- src/backend/catalog/information_schema.sql 3 Jul 2005 18:30:18 -0000
***************
*** 216,222 ****
FROM ((pg_auth_members m join pg_authid a ON (m.roleid = a.oid))
join pg_authid b ON (m.member = b.oid))
! WHERE b.rolname = current_user;
GRANT SELECT ON applicable_roles TO PUBLIC;
--- 216,222 ----
FROM ((pg_auth_members m join pg_authid a ON (m.roleid = a.oid))
join pg_authid b ON (m.member = b.oid))
! WHERE has_role(current_user,b.rolname);
GRANT SELECT ON applicable_roles TO PUBLIC;
***************
*** 239,245 ****
pg_user u
WHERE rs.oid = con.connamespace
AND u.usesysid = coalesce(c.relowner, t.typowner)
! AND u.usename = current_user
AND con.contype = 'c';
GRANT SELECT ON check_constraints TO PUBLIC;
--- 239,245 ----
pg_user u
WHERE rs.oid = con.connamespace
AND u.usesysid = coalesce(c.relowner, t.typowner)
! AND (u.usename = current_user or
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename))
AND con.contype = 'c';
GRANT SELECT ON check_constraints TO PUBLIC;
***************
*** 271,277 ****
AND c.relkind IN ('r', 'v')
AND a.attnum > 0
AND NOT a.attisdropped
! AND u.usename = current_user;
GRANT SELECT ON column_domain_usage TO PUBLIC;
--- 271,277 ----
AND c.relkind IN ('r', 'v')
AND a.attnum > 0
AND NOT a.attisdropped
! AND (u.usename = current_user or
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON column_domain_usage TO PUBLIC;
***************
*** 316,322 ****
--- 316,324 ----
AND aclcontains(c.relacl,
makeaclitem(grantee.oid, u_grantor.oid, pr.type,
false))
AND (u_grantor.rolname = current_user
+ OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u_grantor.rolname)
OR grantee.name = current_user
+ OR has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),grantee.name)
OR grantee.name = 'PUBLIC');
GRANT SELECT ON column_privileges TO PUBLIC;
***************
*** 346,352 ****
AND u.usesysid = coalesce(bt.typowner, t.typowner)
AND nc.oid = c.relnamespace
AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
! AND u.usename = current_user;
GRANT SELECT ON column_udt_usage TO PUBLIC;
--- 348,354 ----
AND u.usesysid = coalesce(bt.typowner, t.typowner)
AND nc.oid = c.relnamespace
AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
! AND (u.usename = current_user or
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON column_udt_usage TO PUBLIC;
***************
*** 457,462 ****
--- 459,465 ----
AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
AND (u.usename = current_user
+ OR has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename)
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
***************
*** 513,519 ****
) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
pg_user u
! WHERE x.tblowner = u.usesysid AND u.usename = current_user;
GRANT SELECT ON constraint_column_usage TO PUBLIC;
--- 516,522 ----
) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
pg_user u
! WHERE x.tblowner = u.usesysid AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON constraint_column_usage TO PUBLIC;
***************
*** 539,545 ****
AND ( (c.contype = 'f' AND c.confrelid = r.oid)
OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
AND r.relkind = 'r'
! AND r.relowner = u.usesysid AND u.usename = current_user;
GRANT SELECT ON constraint_table_usage TO PUBLIC;
--- 542,548 ----
AND ( (c.contype = 'f' AND c.confrelid = r.oid)
OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
AND r.relkind = 'r'
! AND r.relowner = u.usesysid AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON constraint_table_usage TO PUBLIC;
***************
*** 567,573 ****
WHERE rs.oid = con.connamespace
AND n.oid = t.typnamespace
AND u.usesysid = t.typowner
! AND u.usename = current_user
AND t.oid = con.contypid;
GRANT SELECT ON domain_constraints TO PUBLIC;
--- 570,576 ----
WHERE rs.oid = con.connamespace
AND n.oid = t.typnamespace
AND u.usesysid = t.typowner
! AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename))
AND t.oid = con.contypid;
GRANT SELECT ON domain_constraints TO PUBLIC;
***************
*** 595,601 ****
AND bt.typnamespace = nbt.oid
AND t.typtype = 'd'
AND bt.typowner = u.usesysid
! AND u.usename = current_user;
GRANT SELECT ON domain_udt_usage TO PUBLIC;
--- 598,604 ----
AND bt.typnamespace = nbt.oid
AND t.typtype = 'd'
AND bt.typowner = u.usesysid
! AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON domain_udt_usage TO PUBLIC;
***************
*** 692,700 ****
CREATE VIEW enabled_roles AS
SELECT CAST(a.rolname AS sql_identifier) AS role_name
! FROM ((pg_auth_members m join pg_authid a ON (m.roleid = a.oid))
! join pg_authid b ON (m.member = b.oid))
! WHERE b.rolname = current_user;
GRANT SELECT ON enabled_roles TO PUBLIC;
--- 695,702 ----
CREATE VIEW enabled_roles AS
SELECT CAST(a.rolname AS sql_identifier) AS role_name
! FROM pg_authid a
! WHERE has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),a.rolname);
GRANT SELECT ON enabled_roles TO PUBLIC;
***************
*** 726,732 ****
AND c.contype IN ('p', 'u', 'f')
AND r.relkind = 'r'
AND r.relowner = u.usesysid
! AND u.usename = current_user) AS ss
WHERE ss.oid = a.attrelid
AND a.attnum = (ss.x).x
AND NOT a.attisdropped;
--- 728,734 ----
AND c.contype IN ('p', 'u', 'f')
AND r.relkind = 'r'
AND r.relowner = u.usesysid
! AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename))) AS ss
WHERE ss.oid = a.attrelid
AND a.attnum = (ss.x).x
AND NOT a.attisdropped;
***************
*** 788,794 ****
FROM pg_namespace n, pg_proc p, pg_user u
WHERE n.oid = p.pronamespace
AND p.proowner = u.usesysid
! AND (u.usename = current_user OR
has_function_privilege(p.oid, 'EXECUTE'))) AS ss
WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
--- 790,796 ----
FROM pg_namespace n, pg_proc p, pg_user u
WHERE n.oid = p.pronamespace
AND p.proowner = u.usesysid
! AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename) OR
has_function_privilege(p.oid, 'EXECUTE'))) AS ss
WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
***************
*** 843,849 ****
WHERE c.relkind = 'r'
AND con.contype = 'f'
AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
! AND u.usename = current_user;
GRANT SELECT ON referential_constraints TO PUBLIC;
--- 845,851 ----
WHERE c.relkind = 'r'
AND con.contype = 'f'
AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
! AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON referential_constraints TO PUBLIC;
***************
*** 1015,1021 ****
--- 1017,1025 ----
AND aclcontains(p.proacl,
makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE',
false))
AND (u_grantor.rolname = current_user
+ OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u_grantor.rolname)
OR grantee.name = current_user
+ OR has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),grantee.name)
OR grantee.name = 'PUBLIC');
GRANT SELECT ON routine_privileges TO PUBLIC;
***************
*** 1099,1105 ****
WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner =
u.usesysid
AND p.prorettype = t.oid AND t.typnamespace = nt.oid
! AND (u.usename = current_user OR has_function_privilege(p.oid,
'EXECUTE'));
GRANT SELECT ON routines TO PUBLIC;
--- 1103,1109 ----
WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner =
u.usesysid
AND p.prorettype = t.oid AND t.typnamespace = nt.oid
! AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename) OR
has_function_privilege(p.oid, 'EXECUTE'));
GRANT SELECT ON routines TO PUBLIC;
***************
*** 1118,1124 ****
CAST(null AS sql_identifier) AS default_character_set_name,
CAST(null AS character_data) AS sql_path
FROM pg_namespace n, pg_user u
! WHERE n.nspowner = u.usesysid AND u.usename = current_user;
GRANT SELECT ON schemata TO PUBLIC;
--- 1122,1128 ----
CAST(null AS sql_identifier) AS default_character_set_name,
CAST(null AS character_data) AS sql_path
FROM pg_namespace n, pg_user u
! WHERE n.nspowner = u.usesysid AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON schemata TO PUBLIC;
***************
*** 1321,1327 ****
WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
AND c.conrelid = r.oid AND r.relowner = u.usesysid
AND r.relkind = 'r'
! AND u.usename = current_user;
-- FIMXE: Not-null constraints are missing here.
--- 1325,1331 ----
WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
AND c.conrelid = r.oid AND r.relowner = u.usesysid
AND r.relkind = 'r'
! AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
-- FIMXE: Not-null constraints are missing here.
***************
*** 1367,1373 ****
--- 1371,1379 ----
AND aclcontains(c.relacl,
makeaclitem(grantee.oid, u_grantor.oid, pr.type,
false))
AND (u_grantor.rolname = current_user
+ OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u_grantor.rolname)
OR grantee.name = current_user
+ OR has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),grantee.name)
OR grantee.name = 'PUBLIC');
GRANT SELECT ON table_privileges TO PUBLIC;
***************
*** 1402,1407 ****
--- 1408,1414 ----
WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
AND c.relkind IN ('r', 'v')
AND (u.usename = current_user
+ OR has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename)
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
***************
*** 1472,1478 ****
AND c.relowner = u.usesysid
AND t.tgtype & em.num <> 0
AND NOT t.tgisconstraint
! AND u.usename = current_user;
GRANT SELECT ON triggers TO PUBLIC;
--- 1479,1485 ----
AND c.relowner = u.usesysid
AND t.tgtype & em.num <> 0
AND NOT t.tgisconstraint
! AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON triggers TO PUBLIC;
***************
*** 1541,1547 ****
AND t.relkind IN ('r', 'v')
AND t.oid = a.attrelid
AND dt.refobjsubid = a.attnum
! AND t.relowner = u.usesysid AND u.usename = current_user;
GRANT SELECT ON view_column_usage TO PUBLIC;
--- 1548,1554 ----
AND t.relkind IN ('r', 'v')
AND t.oid = a.attrelid
AND dt.refobjsubid = a.attnum
! AND t.relowner = u.usesysid AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON view_column_usage TO PUBLIC;
***************
*** 1577,1583 ****
AND dt.refobjid = t.oid
AND t.relnamespace = nt.oid
AND t.relkind IN ('r', 'v')
! AND t.relowner = u.usesysid AND u.usename = current_user;
GRANT SELECT ON view_table_usage TO PUBLIC;
--- 1584,1590 ----
AND dt.refobjid = t.oid
AND t.relnamespace = nt.oid
AND t.relkind IN ('r', 'v')
! AND t.relowner = u.usesysid AND (u.usename = current_user OR
has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename));
GRANT SELECT ON view_table_usage TO PUBLIC;
***************
*** 1606,1611 ****
--- 1613,1619 ----
WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
AND c.relkind = 'v'
AND (u.usename = current_user
+ OR has_role(COALESCE(CURRENT_ROLE,CURRENT_USER),u.usename)
OR has_table_privilege(c.oid, 'SELECT')
OR has_table_privilege(c.oid, 'INSERT')
OR has_table_privilege(c.oid, 'UPDATE')
Index: src/backend/commands/user.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/user.c,v
retrieving revision 1.155
diff -c -r1.155 user.c
*** src/backend/commands/user.c 29 Jun 2005 20:34:13 -0000 1.155
--- src/backend/commands/user.c 3 Jul 2005 18:30:18 -0000
***************
*** 227,233 ****
errmsg("permission denied to create
role")));
}
! if (strcmp(stmt->role, "public") == 0)
ereport(ERROR,
(errcode(ERRCODE_RESERVED_NAME),
errmsg("role name \"%s\" is reserved",
--- 227,233 ----
errmsg("permission denied to create
role")));
}
! if (strcasecmp(stmt->role, "public") == 0 || strcasecmp(stmt->role,
"none") == 0)
ereport(ERROR,
(errcode(ERRCODE_RESERVED_NAME),
errmsg("role name \"%s\" is reserved",
***************
*** 759,772 ****
roleid = HeapTupleGetOid(tuple);
! if (roleid == GetUserId())
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("current role cannot be
dropped")));
if (roleid == GetSessionUserId())
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
! errmsg("session role cannot be
dropped")));
/*
* For safety's sake, we allow createrole holders to drop
ordinary
--- 759,776 ----
roleid = HeapTupleGetOid(tuple);
! if (roleid == GetCurrentRoleId())
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("current role cannot be
dropped")));
+ if (roleid == GetCurrentUserId())
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg("current user cannot be
dropped")));
if (roleid == GetSessionUserId())
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
! errmsg("session user cannot be
dropped")));
/*
* For safety's sake, we allow createrole holders to drop
ordinary
***************
*** 903,908 ****
--- 907,918 ----
int i;
Oid roleid;
+ if (strcasecmp(newname, "public") == 0 || strcasecmp(newname, "none")
== 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved",
+ newname)));
+
/* ExclusiveLock because we need to update the flat auth file */
rel = heap_open(AuthIdRelationId, ExclusiveLock);
dsc = RelationGetDescr(rel);
***************
*** 919,933 ****
* XXX Client applications probably store the session user somewhere,
* so renaming it could cause confusion. On the other hand, there may
* not be an actual problem besides a little confusion, so think about
! * this and decide.
*/
roleid = HeapTupleGetOid(oldtuple);
if (roleid == GetSessionUserId())
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("session role may not be renamed")));
/* make sure the new name doesn't exist */
if (SearchSysCacheExists(AUTHNAME,
--- 929,953 ----
* XXX Client applications probably store the session user somewhere,
* so renaming it could cause confusion. On the other hand, there may
* not be an actual problem besides a little confusion, so think about
! * this and decide. Same for CurrentRole/CurrentUser?
*/
roleid = HeapTupleGetOid(oldtuple);
+ if (roleid == GetCurrentRoleId())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("current role may not be renamed")));
+
+ if (roleid == GetCurrentUserId())
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("current user may not be renamed")));
+
if (roleid == GetSessionUserId())
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("session user may not be renamed")));
/* make sure the new name doesn't exist */
if (SearchSysCacheExists(AUTHNAME,
***************
*** 1003,1012 ****
List *grantee_ids;
ListCell *item;
if (stmt->grantor)
grantor = get_roleid_checked(stmt->grantor);
else
! grantor = GetUserId();
grantee_ids = roleNamesToIds(stmt->grantee_roles);
--- 1023,1040 ----
List *grantee_ids;
ListCell *item;
+ /*
+ * Per SQL spec, we use the 'GRANTED BY' role if one
+ * is given, otherwise we use CURRENT_USER if set,
+ * and CURRENT_ROLE if CURRENT_USER is not set.
+ * For us, CURRENT_USER may never be *not* set.
+ * (SQL spec also doesn't provide a way for it
+ * to ever become unset...)
+ */
if (stmt->grantor)
grantor = get_roleid_checked(stmt->grantor);
else
! grantor = GetCurrentUserId();
grantee_ids = roleNamesToIds(stmt->grantee_roles);
Index: src/backend/commands/variable.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/variable.c,v
retrieving revision 1.109
diff -c -r1.109 variable.c
*** src/backend/commands/variable.c 28 Jun 2005 05:08:55 -0000 1.109
--- src/backend/commands/variable.c 3 Jul 2005 18:30:18 -0000
***************
*** 564,569 ****
--- 564,682 ----
/*
+ * SET ROLE
+ *
+ * When resetting session auth after an error, we can't expect to do catalog
+ * lookups. Hence, the stored form of the value must provide a numeric oid
+ * that can be re-used directly. We store the string in the form of
+ * NAMEDATALEN 'x's, followed by T or F to indicate superuserness, followed
+ * by the numeric oid, followed by a comma, followed by the role name.
+ * This cannot be confused with a plain role name because of the NAMEDATALEN
+ * limit on names, so we can tell whether we're being passed an initial
+ * role name or a saved/restored value.
+ */
+ extern char *role_string; /* in guc.c */
+
+ const char *
+ assign_role(const char *value, bool doit, GucSource source)
+ {
+ Oid roleid = InvalidOid;
+ bool is_superuser = false;
+ const char *actual_rolename = value;
+ char *result;
+
+ if (strspn(value, "x") == NAMEDATALEN &&
+ (value[NAMEDATALEN] == 'T' || value[NAMEDATALEN] == 'F'))
+ {
+ /* might be a saved userid string */
+ Oid savedoid;
+ char *endptr;
+
+ savedoid = (Oid) strtoul(value + NAMEDATALEN + 1, &endptr, 10);
+
+ if (endptr != value + NAMEDATALEN + 1 && *endptr == ',')
+ {
+ /* syntactically valid, so break out the data */
+ roleid = savedoid;
+ is_superuser = (value[NAMEDATALEN] == 'T');
+ actual_rolename = endptr + 1;
+ }
+ }
+
+ if (roleid == InvalidOid && strncmp(actual_rolename,"NONE",4) != 0)
+ {
+ /* not a saved ID, so look it up */
+ HeapTuple roleTup;
+
+ if (!IsTransactionState())
+ {
+ /*
+ * Can't do catalog lookups, so fail. The upshot of
this is
+ * that role cannot be set in * postgresql.conf, which
seems
+ * like a good thing anyway.
+ */
+ return NULL;
+ }
+
+ roleTup = SearchSysCache(AUTHNAME,
+
PointerGetDatum(value),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(roleTup))
+ {
+ if (source >= PGC_S_INTERACTIVE)
+ ereport(ERROR,
+
(errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("role \"%s\" does not
exist", value)));
+ return NULL;
+ }
+
+ roleid = HeapTupleGetOid(roleTup);
+ is_superuser = ((Form_pg_authid) GETSTRUCT(roleTup))->rolsuper;
+
+ ReleaseSysCache(roleTup);
+ }
+
+ if (doit)
+ SetCurrentRoleId(roleid);
+
+ result = (char *) malloc(NAMEDATALEN + 32 + strlen(actual_rolename));
+ if (!result)
+ return NULL;
+
+ memset(result, 'x', NAMEDATALEN);
+
+ sprintf(result + NAMEDATALEN, "%c%u,%s",
+ is_superuser ? 'T' : 'F',
+ roleid,
+ actual_rolename);
+
+ return result;
+ }
+
+ const char *
+ show_role(void)
+ {
+ /*
+ * Extract the role name from the stored string; see
+ * assign_role
+ */
+ const char *value = role_string;
+ Oid savedoid;
+ char *endptr;
+
+ if (!value || !value[0])
+ return "NONE";
+
+ Assert(strspn(value, "x") == NAMEDATALEN &&
+ (value[NAMEDATALEN] == 'T' || value[NAMEDATALEN] == 'F'));
+
+ savedoid = (Oid) strtoul(value + NAMEDATALEN + 1, &endptr, 10);
+
+ Assert(endptr != value + NAMEDATALEN + 1 && *endptr == ',');
+
+ return endptr + 1;
+ }
+ /*
* SET SESSION AUTHORIZATION
*
* When resetting session auth after an error, we can't expect to do catalog
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.501
diff -c -r2.501 gram.y
*** src/backend/parser/gram.y 29 Jun 2005 20:34:13 -0000 2.501
--- src/backend/parser/gram.y 3 Jul 2005 18:30:19 -0000
***************
*** 396,402 ****
SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
! SYSID SYSTEM_P
TABLE TABLESPACE TEMP TEMPLATE TEMPORARY THEN TIME TIMESTAMP
TO TOAST TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
--- 396,402 ----
SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
! SYSID SYSTEM_P SYSTEM_USER
TABLE TABLESPACE TEMP TEMPLATE TEMPORARY THEN TIME TIMESTAMP
TO TOAST TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
***************
*** 1004,1009 ****
--- 1004,1019 ----
n->args =
list_make1(makeStringConst($2, NULL));
$$ = n;
}
+ | ROLE ColId_or_Sconst
+ {
+ VariableSetStmt *n =
makeNode(VariableSetStmt);
+ n->name = "role";
+ if (strncasecmp($2,"NONE",4) == 0)
+ n->args = NIL;
+ else
+ n->args =
list_make1(makeStringConst($2, NULL));
+ $$ = n;
+ }
| SESSION AUTHORIZATION ColId_or_Sconst
{
VariableSetStmt *n =
makeNode(VariableSetStmt);
***************
*** 1156,1161 ****
--- 1166,1177 ----
n->name = "transaction_isolation";
$$ = (Node *) n;
}
+ | SHOW ROLE
+ {
+ VariableShowStmt *n =
makeNode(VariableShowStmt);
+ n->name = "role";
+ $$ = (Node *) n;
+ }
| SHOW SESSION AUTHORIZATION
{
VariableShowStmt *n =
makeNode(VariableShowStmt);
***************
*** 7067,7073 ****
| CURRENT_ROLE
{
FuncCall *n = makeNode(FuncCall);
! n->funcname =
SystemFuncName("current_user");
n->args = NIL;
n->agg_star = FALSE;
n->agg_distinct = FALSE;
--- 7083,7089 ----
| CURRENT_ROLE
{
FuncCall *n = makeNode(FuncCall);
! n->funcname =
SystemFuncName("current_role");
n->args = NIL;
n->agg_star = FALSE;
n->agg_distinct = FALSE;
***************
*** 7091,7096 ****
--- 7107,7121 ----
n->agg_distinct = FALSE;
$$ = (Node *)n;
}
+ | SYSTEM_USER
+ {
+ FuncCall *n = makeNode(FuncCall);
+ n->funcname =
SystemFuncName("system_user");
+ n->args = NIL;
+ n->agg_star = FALSE;
+ n->agg_distinct = FALSE;
+ $$ = (Node *)n;
+ }
| USER
{
FuncCall *n = makeNode(FuncCall);
***************
*** 8055,8061 ****
| RESTRICT
| RETURNS
| REVOKE
- | ROLE
| ROLLBACK
| ROWS
| RULE
--- 8080,8085 ----
***************
*** 8265,8270 ****
--- 8289,8295 ----
| SESSION_USER
| SOME
| SYMMETRIC
+ | SYSTEM_USER
| TABLE
| THEN
| TO
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.162
diff -c -r1.162 keywords.c
*** src/backend/parser/keywords.c 29 Jun 2005 20:34:14 -0000 1.162
--- src/backend/parser/keywords.c 3 Jul 2005 18:30:19 -0000
***************
*** 313,318 ****
--- 313,319 ----
{"symmetric", SYMMETRIC},
{"sysid", SYSID},
{"system", SYSTEM_P},
+ {"system_user", SYSTEM_USER},
{"table", TABLE},
{"tablespace", TABLESPACE},
{"temp", TEMP},
Index: src/backend/utils/adt/name.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/name.c,v
retrieving revision 1.55
diff -c -r1.55 name.c
*** src/backend/utils/adt/name.c 31 Dec 2004 22:01:22 -0000 1.55
--- src/backend/utils/adt/name.c 3 Jul 2005 18:30:19 -0000
***************
*** 21,30 ****
--- 21,32 ----
#include "postgres.h"
#include "catalog/namespace.h"
+ #include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
#include "libpq/pqformat.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
+ #include "utils/acl.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
***************
*** 316,327 ****
/*
! * SQL-functions CURRENT_USER, SESSION_USER
*/
Datum
current_user(PG_FUNCTION_ARGS)
{
! PG_RETURN_DATUM(DirectFunctionCall1(namein,
CStringGetDatum(GetUserNameFromId(GetUserId()))));
}
Datum
--- 318,340 ----
/*
! * SQL-functions CURRENT_ROLE, CURRENT_USER, SESSION_USER, SYSTEM_USER
*/
Datum
+ current_role(PG_FUNCTION_ARGS)
+ {
+ Oid roleid = GetCurrentRoleId();
+
+ if (roleid == InvalidOid)
+ PG_RETURN_NULL();
+
+ PG_RETURN_DATUM(DirectFunctionCall1(namein,
CStringGetDatum(GetUserNameFromId(roleid))));
+ }
+
+ Datum
current_user(PG_FUNCTION_ARGS)
{
! PG_RETURN_DATUM(DirectFunctionCall1(namein,
CStringGetDatum(GetUserNameFromId(GetCurrentUserId()))));
}
Datum
***************
*** 330,335 ****
--- 343,376 ----
PG_RETURN_DATUM(DirectFunctionCall1(namein,
CStringGetDatum(GetUserNameFromId(GetSessionUserId()))));
}
+ Datum
+ system_user(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_DATUM(DirectFunctionCall1(namein,
CStringGetDatum(GetUserNameFromId(BOOTSTRAP_SUPERUSERID))));
+ }
+
+ /*
+ * Useful to determine if a given member is part of a given role.
+ */
+ Datum
+ has_role(PG_FUNCTION_ARGS)
+ {
+ Name memname = PG_GETARG_NAME(0);
+ Name rolename = PG_GETARG_NAME(1);
+ Oid memid;
+ Oid roleid;
+
+ if (!memid || !roleid)
+ PG_RETURN_BOOL(false);
+
+ memid = get_roleid_checked(NameStr(*memname));
+ roleid = get_roleid_checked(NameStr(*rolename));
+
+ if (is_member_of_role(memid, roleid))
+ PG_RETURN_BOOL(true);
+
+ PG_RETURN_BOOL(false);
+ }
/*
* SQL-functions CURRENT_SCHEMA, CURRENT_SCHEMAS
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.144
diff -c -r1.144 miscinit.c
*** src/backend/utils/init/miscinit.c 28 Jun 2005 22:16:45 -0000 1.144
--- src/backend/utils/init/miscinit.c 3 Jul 2005 18:30:19 -0000
***************
*** 35,40 ****
--- 35,41 ----
#include "storage/fd.h"
#include "storage/ipc.h"
#include "storage/pg_shmem.h"
+ #include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
***************
*** 264,269 ****
--- 265,271 ----
static Oid AuthenticatedUserId = InvalidOid;
static Oid SessionUserId = InvalidOid;
static Oid CurrentUserId = InvalidOid;
+ static Oid CurrentRoleId = InvalidOid;
static bool AuthenticatedUserIsSuperuser = false;
***************
*** 273,288 ****
Oid
GetUserId(void)
{
AssertState(OidIsValid(CurrentUserId));
return CurrentUserId;
}
void
! SetUserId(Oid roleid)
{
! AssertArg(OidIsValid(roleid));
! CurrentUserId = roleid;
}
--- 275,328 ----
Oid
GetUserId(void)
{
+ /*
+ * The SQL spec claims CurrentUserId does not have
+ * to always be valid, but it is for us. The spec
+ * doesn't appear to say where it could possibly be
+ * unset.
+ */
+ AssertState(OidIsValid(CurrentUserId));
+
+ /*
+ * CurrentRoleId is the top of the authorization 'stack'.
+ */
+ if (OidIsValid(CurrentRoleId))
+ return CurrentRoleId;
+
+ /*
+ * CurrentUserId is below CurrentRoleId on the stack and
+ * so is only seen if CurrentRoleId is not set.
+ * The SQL spec requires that CurrentRoleId or CurrentUserId
+ * be valid at any given time.
+ */
+ return CurrentUserId;
+ }
+
+ Oid
+ GetCurrentRoleId(void)
+ {
+ return CurrentRoleId;
+ }
+
+ Oid
+ GetCurrentUserId(void)
+ {
AssertState(OidIsValid(CurrentUserId));
return CurrentUserId;
}
void
! SetRoleId(Oid roleid)
{
! CurrentRoleId = roleid;
! }
!
! void
! SetUserId(Oid userid)
! {
! AssertArg(OidIsValid(userid));
! CurrentUserId = userid;
}
***************
*** 298,310 ****
void
! SetSessionUserId(Oid roleid)
{
! AssertArg(OidIsValid(roleid));
! SessionUserId = roleid;
/* Current user defaults to session user. */
if (!OidIsValid(CurrentUserId))
! CurrentUserId = roleid;
}
--- 338,353 ----
void
! SetSessionUserId(Oid userid)
{
! AssertArg(OidIsValid(userid));
! SessionUserId = userid;
/* Current user defaults to session user. */
if (!OidIsValid(CurrentUserId))
! CurrentUserId = userid;
!
! /* Per SQL-spec, current role defaults to invalid */
! CurrentRoleId = InvalidOid;
}
***************
*** 416,421 ****
--- 459,490 ----
PGC_INTERNAL, PGC_S_OVERRIDE);
}
+ /*
+ * Change Role ID while running
+ *
+ * The current user must be in the role to which they want to change.
+ */
+ void
+ SetCurrentRoleId(Oid roleid)
+ {
+ /*
+ * May be called during startup to set to NONE, this doesn't
+ * need a permission check anyway.
+ */
+ if (roleid == InvalidOid)
+ return SetRoleId(roleid);
+
+ /* Must have authenticated already, else can't make permission check */
+ AssertState(OidIsValid(AuthenticatedUserId));
+
+ if (!is_member_of_role(GetCurrentUserId(),roleid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to set current role")));
+
+ SetRoleId(roleid);
+ }
+
/*
* Get user name from user oid
Index: src/backend/utils/misc/check_guc
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/check_guc,v
retrieving revision 1.8
diff -c -r1.8 check_guc
*** src/backend/utils/misc/check_guc 27 Jun 2003 19:08:38 -0000 1.8
--- src/backend/utils/misc/check_guc 3 Jul 2005 18:30:19 -0000
***************
*** 18,24 ****
## can be ignored
INTENTIONALLY_NOT_INCLUDED="autocommit debug_deadlocks exit_on_error \
is_superuser lc_collate lc_ctype lc_messages lc_monetary lc_numeric lc_time \
! pre_auth_delay seed server_encoding server_version session_authorization \
trace_lock_oidmin trace_lock_table trace_locks trace_lwlocks trace_notify \
trace_userlocks transaction_isolation transaction_read_only \
zero_damaged_pages"
--- 18,24 ----
## can be ignored
INTENTIONALLY_NOT_INCLUDED="autocommit debug_deadlocks exit_on_error \
is_superuser lc_collate lc_ctype lc_messages lc_monetary lc_numeric lc_time \
! pre_auth_delay role seed server_encoding server_version session_authorization
\
trace_lock_oidmin trace_lock_table trace_locks trace_lwlocks trace_notify \
trace_userlocks transaction_isolation transaction_read_only \
zero_damaged_pages"
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.271
diff -c -r1.271 guc.c
*** src/backend/utils/misc/guc.c 28 Jun 2005 05:09:02 -0000 1.271
--- src/backend/utils/misc/guc.c 3 Jul 2005 18:30:19 -0000
***************
*** 195,200 ****
--- 195,203 ----
/* should be static, but commands/variable.c needs to get at it */
char *session_authorization_string;
+ /* should be static, but commands/variable.c needs to get at it */
+ char *role_string;
+
/*
* Displayable names for context types (enum GucContext)
***************
*** 1751,1756 ****
--- 1754,1770 ----
},
{
+ /* Not for general use --- used by SET ROLE */
+ {"role", PGC_USERSET, UNGROUPED,
+ gettext_noop("Sets the current role."),
+ NULL,
+ GUC_REPORT | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL |
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ },
+ &role_string,
+ "NONE", assign_role, show_role
+ },
+
+ {
/* Not for general use --- used by SET SESSION AUTHORIZATION */
{"session_authorization", PGC_USERSET, UNGROUPED,
gettext_noop("Sets the session user name."),
Index: src/include/miscadmin.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/miscadmin.h,v
retrieving revision 1.176
diff -c -r1.176 miscadmin.h
*** src/include/miscadmin.h 28 Jun 2005 05:09:04 -0000 1.176
--- src/include/miscadmin.h 3 Jul 2005 18:30:19 -0000
***************
*** 229,238 ****
--- 229,292 ----
extern void SetDatabasePath(const char *path);
extern char *GetUserNameFromId(Oid roleid);
+ /*
+ * GetUserId will return the authorization identifier on the top
+ * of the authorization 'stack'. That is to say, the CURRENT_ROLE
+ * if it is set, the CURRENT_USER if CURRENT_ROLE is not set.
+ */
extern Oid GetUserId(void);
+
+ /*
+ * Returns the CURRENT_ROLE Oid, this may not be set! Use GetUserId()
+ * if you need something which is always expected to be valid
+ */
+ extern Oid GetCurrentRoleId(void);
+
+ /*
+ * Sets the CURRENT_ROLE authorization identifier, this is the top
+ * of the 'stack' and will be used until it is reset to InvalidOid.
+ * Does not do authorization checks, use SetCurrentRoleId() instead.
+ * (Which can be done with SET ROLE NONE;)
+ */
+ extern void SetRoleId(Oid roleid);
+
+ /*
+ * Returns the CURRENT_USER Oid, SQL spec claims this may not always
+ * be set but doesn't identify a case where it could be unset, this
+ * should *not* be used directly for authorization generally, use
+ * GetUserId() which understands the authorization 'stack'.
+ * This is used by GRANT operations for the grantor, per SQL spec.
+ */
+ extern Oid GetCurrentUserId(void);
+
+ /*
+ * Sets the CURRENT_USER authorization identifier, this should
+ * generally be able to be done only by the superuser.
+ */
extern void SetUserId(Oid roleid);
+
+ /*
+ * Returns the SESSION_USER Oid, SQL spec requires this always be
+ * set, however it should not be used directly for authorization,
+ * use GetUserId() instead
+ */
extern Oid GetSessionUserId(void);
+
+ /*
+ * Sets the CURRENT_ROLE authorization identifier, this is the top
+ * of the 'stack' and will be used until it is reset to InvalidOid
+ * Does authorization checks.
+ * (Which can be done with SET ROLE NONE;)
+ */
+ extern void SetCurrentRoleId(Oid roleid);
+
+ /*
+ * Sets the SESSION_USER authorization identifier, this should
+ * generally be able to be done only by the superuser.
+ * Does authorization checks.
+ */
extern void SetSessionUserId(Oid roleid);
+
extern void InitializeSessionUserId(const char *rolename);
extern void InitializeSessionUserIdStandalone(void);
extern void SetSessionAuthorization(Oid roleid, bool is_superuser);
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.373
diff -c -r1.373 pg_proc.h
*** src/include/catalog/pg_proc.h 1 Jul 2005 19:19:03 -0000 1.373
--- src/include/catalog/pg_proc.h 3 Jul 2005 18:30:20 -0000
***************
*** 1003,1012 ****
--- 1003,1018 ----
DATA(insert OID = 743 ( text_ge PGNSP PGUID 12 f f t f i 2
16 "25 25" _null_ _null_ _null_ text_ge - _null_ ));
DESCR("greater-than-or-equal");
+ DATA(insert OID = 1136 ( current_role PGNSP PGUID 12 f f t f s 0
19 "" _null_ _null_ _null_ current_role - _null_ ));
+ DESCR("current role name");
DATA(insert OID = 745 ( current_user PGNSP PGUID 12 f f t f s 0 19 ""
_null_ _null_ _null_ current_user - _null_ ));
DESCR("current user name");
DATA(insert OID = 746 ( session_user PGNSP PGUID 12 f f t f s 0 19 ""
_null_ _null_ _null_ session_user - _null_ ));
DESCR("session user name");
+ DATA(insert OID = 1137 ( system_user PGNSP PGUID 12 f f t f s 0 19 ""
_null_ _null_ _null_ system_user - _null_ ));
+ DESCR("system user name");
+ DATA(insert OID = 1175 ( has_role PGNSP PGUID 12 f f t f s 2
16 "19 19" _null_ _null_ _null_ has_role - _null_ ));
+ DESCR("is member a member of role");
DATA(insert OID = 744 ( array_eq PGNSP PGUID 12 f f t f i 2
16 "2277 2277" _null_ _null_ _null_ array_eq - _null_ ));
DESCR("array equal");
Index: src/include/commands/variable.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/commands/variable.h,v
retrieving revision 1.25
diff -c -r1.25 variable.h
*** src/include/commands/variable.h 31 Dec 2004 22:03:28 -0000 1.25
--- src/include/commands/variable.h 3 Jul 2005 18:30:20 -0000
***************
*** 26,31 ****
--- 26,34 ----
extern const char *show_random_seed(void);
extern const char *assign_client_encoding(const char *value,
bool doit, GucSource source);
+ extern const char *assign_role(const char *value,
+ bool doit, GucSource
source);
+ extern const char *show_role(void);
extern const char *assign_session_authorization(const char *value,
bool doit, GucSource
source);
extern const char *show_session_authorization(void);
Index: src/include/utils/builtins.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.258
diff -c -r1.258 builtins.h
*** src/include/utils/builtins.h 17 Jun 2005 22:32:50 -0000 1.258
--- src/include/utils/builtins.h 3 Jul 2005 18:30:20 -0000
***************
*** 207,216 ****
--- 207,219 ----
extern int namecpy(Name n1, Name n2);
extern int namestrcpy(Name name, const char *str);
extern int namestrcmp(Name name, const char *str);
+ extern Datum current_role(PG_FUNCTION_ARGS);
extern Datum current_user(PG_FUNCTION_ARGS);
extern Datum session_user(PG_FUNCTION_ARGS);
+ extern Datum system_user(PG_FUNCTION_ARGS);
extern Datum current_schema(PG_FUNCTION_ARGS);
extern Datum current_schemas(PG_FUNCTION_ARGS);
+ extern Datum has_role(PG_FUNCTION_ARGS);
/* numutils.c */
extern int32 pg_atoi(char *s, int size, int c);
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
