Hello, This is the new version. (WIP v2) The first attachment is the patch and the second is test sql script.
- Behavior changing Almost all syntax taking role accepts CURRENT_USER and SESSION_USER and they are distinguished from "current_user" and "session_user". The exceptions are follows. - CREATE USER/GROUP <roleid> - ALTER ROLE/GROUP/USER <roleid> RENAME TO <newname> These syntax still do not accept the keywords like CURRENT_USER and special names like "public" at all, but accepts "current_user". The error message is changed as follows. | postgres=# create user current_user; | ERROR: role name should not be a keyword nor reserved name. | LINE 1: create user current_user; | ^ # Some other messages may changed... USER and CURRENT_ROLE haven't been extended to other syntax. The former still can be used only in CREATE/ALTER/DROP USER MAPPING and the latter cannot be used out of function expressions. - Storage for new information The new struct NameId stores an identifier which telling what it logically is using the new enum NameIdTypes. This is still be a bit suffered by the difference between CURRENT_USER and PUBLIC but now it makes distinction between current_user and "current_user". User oid does not have the room for representing the difference among PUBLIC, NONE and 'not found' as the result of get_nameid_oid(), so members of NameId is exposed in foreigncmds.c and it gets a bit uglier. - Changes of related structs and grammar. Type of role member is changed to NameId in some of parser structs. AlterTableCmd.name has many other usage so I added new member NameId *newowner for exclusive usage. Type of OWNER clause of CREATE TABLESPACE is changed to RoleId. I suppose there's no particular reason that the non-terminal was "name". Usage of "public" and "none" had been blocked for CREATE/RENAME USER in user.c but now it is blocked in gram.y - New function to resolve NameId New function get_nameid_oid() is added. It is an alternative of get_role_oid which can handle current_user and "current_user" properly. get_role_oid() still be used in many places having no direct relation to syntax. - Others No doc provided for now. regards, > > Adam Brightwell <adam.brightw...@crunchydatasolutions.com> writes: > > > FWIW, I found the following in the archives: > > > > > http://www.postgresql.org/message-id/15516.1038718...@sss.pgh.pa.us > > > > > Now this is from 2002 and it appears it wasn't necessary to change at the > > > time, but I haven't yet found anything else related (it's a big archive). > > > Though, as I understand it, PUBLIC is now non-reserved as of SQL:2011 > > > which > > > might make a compelling argument to leave it as is? > > > > The current spec does list PUBLIC as a non-reserved keyword, but it also > > says (5.4 "Names and identifiers" syntax rules) > > > > 20) No <authorization identifier> shall specify "PUBLIC". > > > > which, oddly enough, seems to license us to handle "PUBLIC" the way > > we are doing. OTOH, it lists CURRENT_USER as a reserved word, suggesting > > that they don't think the same type of hack should be used for that. > > > > I'd be inclined to leave the grammar as such alone (ie CURRENT_USER is > > a keyword, PUBLIC isn't). Changing that has more downside than upside, > > and we do have justification in the spec for treating the two cases > > differently. However, I agree that we should fix the subsequent > > processing so that "current_user" is not confused with CURRENT_USER. > > Sure, maybe. > > - PUBLIC should be left as it is, as an supecial identifier > which cannot be used even if quoted under some syntax. > > - CURRENT_USER should be a kayword as it is, but we shouldn't > inhibit it from being used as an identifier if > quoted. SESSION_USER and USER should be treated in the same way. > > We don't want to use something other than string (prefixed by > zero-byte) as a matter of course:). And resolving the name to > roleid instantly in gram.y is not allowed for the reason shown > upthread. > > So it is necessary to add a new member for the struct, say > "int special_role;:... Let me have more sane name for it :( > > - USER and CURRENT_ROLE are not needed for the syntax other than > them which already uses them. > > I will work on this way. Let me know if something is not acceptable. -- Kyotaro Horiguchi NTT Open Source Software Center
>From f18d078d5e6e4005e803ecc954e59c899dbfd557 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi <horiguchi.kyot...@lab.ntt.co.jp> Date: Mon, 10 Nov 2014 19:08:42 +0900 Subject: [PATCH] ALTER USER CURRENT_USER v2 --- src/backend/catalog/aclchk.c | 13 ++- src/backend/commands/alter.c | 2 +- src/backend/commands/foreigncmds.c | 39 ++++----- src/backend/commands/schemacmds.c | 26 +++++- src/backend/commands/tablecmds.c | 2 +- src/backend/commands/tablespace.c | 2 +- src/backend/commands/user.c | 70 +++++++-------- src/backend/nodes/copyfuncs.c | 37 +++++--- src/backend/nodes/equalfuncs.c | 35 +++++--- src/backend/parser/gram.y | 174 ++++++++++++++++++++++++++++--------- src/backend/parser/parse_utilcmd.c | 2 +- src/backend/utils/adt/acl.c | 31 +++++++ src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 44 +++++++--- src/include/utils/acl.h | 2 +- 15 files changed, 338 insertions(+), 142 deletions(-) diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index d30612c..a2b6a70 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -434,9 +434,14 @@ ExecuteGrantStmt(GrantStmt *stmt) if (grantee->rolname == NULL) istmt.grantees = lappend_oid(istmt.grantees, ACL_ID_PUBLIC); else - istmt.grantees = - lappend_oid(istmt.grantees, - get_role_oid(grantee->rolname, false)); + { + Oid grantee_uid = get_nameid_oid(grantee->rolname, false); + + if (grantee_uid == InvalidOid) + grantee_uid = ACL_ID_PUBLIC; + + istmt.grantees = lappend_oid(istmt.grantees, grantee_uid); + } } /* @@ -919,7 +924,7 @@ ExecAlterDefaultPrivilegesStmt(AlterDefaultPrivilegesStmt *stmt) else iacls.grantees = lappend_oid(iacls.grantees, - get_role_oid(grantee->rolname, false)); + get_nameid_oid(grantee->rolname, false)); } /* diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index c9a9baf..5117a35 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -678,7 +678,7 @@ AlterObjectNamespace_internal(Relation rel, Oid objid, Oid nspOid) Oid ExecAlterOwnerStmt(AlterOwnerStmt *stmt) { - Oid newowner = get_role_oid(stmt->newowner, false); + Oid newowner = get_nameid_oid(stmt->newowner, false); switch (stmt->objectType) { diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c index ab4ed6c..d5eb4cc 100644 --- a/src/backend/commands/foreigncmds.c +++ b/src/backend/commands/foreigncmds.c @@ -198,24 +198,6 @@ transformGenericOptions(Oid catalogId, /* - * Convert the user mapping user name to OID - */ -static Oid -GetUserOidFromMapping(const char *username, bool missing_ok) -{ - if (!username) - /* PUBLIC user mapping */ - return InvalidOid; - - if (strcmp(username, "current_user") == 0) - /* map to the owner */ - return GetUserId(); - - /* map to provided user */ - return get_role_oid(username, missing_ok); -} - -/* * Internal workhorse for changing a data wrapper's owner. * * Allow this only for superusers; also the new owner must be a @@ -1099,13 +1081,17 @@ CreateUserMapping(CreateUserMappingStmt *stmt) rel = heap_open(UserMappingRelationId, RowExclusiveLock); - useId = GetUserOidFromMapping(stmt->username, false); + useId = get_nameid_oid(stmt->username, false); /* Check that the server exists. */ srv = GetForeignServerByName(stmt->servername, false); user_mapping_ddl_aclcheck(useId, srv->serverid, stmt->servername); + /* XXX: Inhibit none to be used explicitly*/ + if (!OidIsValid(useId) && stmt->username->idtype != NAMEID_PUBLIC) + elog(ERROR, "role \"%s\" does not exist", stmt->username->str); + /* * Check that the user mapping is unique within server. */ @@ -1194,9 +1180,13 @@ AlterUserMapping(AlterUserMappingStmt *stmt) rel = heap_open(UserMappingRelationId, RowExclusiveLock); - useId = GetUserOidFromMapping(stmt->username, false); + useId = get_nameid_oid(stmt->username, false); srv = GetForeignServerByName(stmt->servername, false); + /* XXX: Inhibit none to be used explicitly*/ + if (!OidIsValid(useId) && stmt->username->idtype != NAMEID_PUBLIC) + elog(ERROR, "role \"%s\" does not exist", stmt->username->str); + umId = GetSysCacheOid2(USERMAPPINGUSERSERVER, ObjectIdGetDatum(useId), ObjectIdGetDatum(srv->serverid)); @@ -1276,16 +1266,19 @@ RemoveUserMapping(DropUserMappingStmt *stmt) Oid umId; ForeignServer *srv; - useId = GetUserOidFromMapping(stmt->username, stmt->missing_ok); + useId = get_nameid_oid(stmt->username, stmt->missing_ok); srv = GetForeignServerByName(stmt->servername, true); - if (stmt->username && !OidIsValid(useId)) + /* XXX: Inhibit none to be used explicitly*/ + if (!OidIsValid(useId) && stmt->username->idtype != NAMEID_PUBLIC) { + if (!stmt->missing_ok) + elog(ERROR, "role \"%s\" does not exist", stmt->username->str); /* * IF EXISTS specified, role not found and not public. Notice this and * leave. */ - elog(NOTICE, "role \"%s\" does not exist, skipping", stmt->username); + elog(NOTICE, "role \"%s\" does not exist, skipping", stmt->username->str); return InvalidOid; } diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c index 03f5514..f97183a 100644 --- a/src/backend/commands/schemacmds.c +++ b/src/backend/commands/schemacmds.c @@ -21,6 +21,7 @@ #include "catalog/dependency.h" #include "catalog/indexing.h" #include "catalog/namespace.h" +#include "catalog/pg_authid.h" #include "catalog/objectaccess.h" #include "catalog/pg_namespace.h" #include "commands/dbcommands.h" @@ -43,7 +44,7 @@ Oid CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString) { const char *schemaName = stmt->schemaname; - const char *authId = stmt->authid; + const NameId *authId = stmt->authid; Oid namespaceId; OverrideSearchPath *overridePath; List *parsetree_list; @@ -59,10 +60,31 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString) * Who is supposed to own the new schema? */ if (authId) - owner_uid = get_role_oid(authId, false); + { + owner_uid = get_nameid_oid(authId, false); + if (!OidIsValid(owner_uid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("role \"%s\" does not exist", authId->str))); + + } else owner_uid = saved_uid; + /* fill schema name with the user name if not specified */ + if (!schemaName) + { + HeapTuple tuple; + + tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(owner_uid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("invalid role oid: %u", owner_uid))); + schemaName = + pstrdup(NameStr(((Form_pg_authid) GETSTRUCT(tuple))->rolname)); + ReleaseSysCache(tuple); + } /* * To create a schema, must have schema-create privilege on the current * database and must be able to become the target role (this does not diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 714a9f1..27bc760 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -3474,7 +3474,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, break; case AT_ChangeOwner: /* ALTER OWNER */ ATExecChangeOwner(RelationGetRelid(rel), - get_role_oid(cmd->name, false), + get_nameid_oid(cmd->newowner, false), false, lockmode); break; case AT_ClusterOn: /* CLUSTER ON */ diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c index 378e355..0e611f3 100644 --- a/src/backend/commands/tablespace.c +++ b/src/backend/commands/tablespace.c @@ -252,7 +252,7 @@ CreateTableSpace(CreateTableSpaceStmt *stmt) /* However, the eventual owner of the tablespace need not be */ if (stmt->owner) - ownerId = get_role_oid(stmt->owner, false); + ownerId = get_nameid_oid(stmt->owner, false); else ownerId = GetUserId(); diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 1a73fd8..5c32c3e 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -310,13 +310,6 @@ CreateRole(CreateRoleStmt *stmt) errmsg("permission denied to create role"))); } - if (strcmp(stmt->role, "public") == 0 || - strcmp(stmt->role, "none") == 0) - ereport(ERROR, - (errcode(ERRCODE_RESERVED_NAME), - errmsg("role name \"%s\" is reserved", - stmt->role))); - /* * Check the pg_authid relation to be certain the role doesn't already * exist. @@ -481,6 +474,7 @@ AlterRole(AlterRoleStmt *stmt) HeapTuple tuple, new_tuple; ListCell *option; + char *rolename = NULL; char *password = NULL; /* user password */ bool encrypt_password = Password_encryption; /* encrypt password? */ char encrypted_password[MD5_PASSWD_LEN + 1]; @@ -649,18 +643,25 @@ AlterRole(AlterRoleStmt *stmt) pg_authid_rel = heap_open(AuthIdRelationId, RowExclusiveLock); pg_authid_dsc = RelationGetDescr(pg_authid_rel); - tuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role)); - if (!HeapTupleIsValid(tuple)) + roleid = get_nameid_oid(stmt->role, false); + if (!OidIsValid(roleid)) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("role \"%s\" does not exist", stmt->role))); - - roleid = HeapTupleGetOid(tuple); + errmsg("role \"%s\" does not exist", stmt->role->str))); /* * To mess with a superuser you gotta be superuser; else you need * createrole, or just want to change your own password */ + tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("invalid role oid: %u", roleid))); + + rolename = + pstrdup(NameStr(((Form_pg_authid) GETSTRUCT(tuple))->rolname)); + if (((Form_pg_authid) GETSTRUCT(tuple))->rolsuper || issuper >= 0) { if (!superuser()) @@ -720,12 +721,12 @@ AlterRole(AlterRoleStmt *stmt) * Call the password checking hook if there is one defined */ if (check_password_hook && password) - (*check_password_hook) (stmt->role, - password, - isMD5(password) ? PASSWORD_TYPE_MD5 : PASSWORD_TYPE_PLAINTEXT, - validUntil_datum, - validUntil_null); - + (*check_password_hook)(rolename , + password, + isMD5(password) ? PASSWORD_TYPE_MD5 : PASSWORD_TYPE_PLAINTEXT, + validUntil_datum, + validUntil_null); + /* * Build an updated tuple, perusing the information just obtained */ @@ -794,7 +795,7 @@ AlterRole(AlterRoleStmt *stmt) CStringGetTextDatum(password); else { - if (!pg_md5_encrypt(password, stmt->role, strlen(stmt->role), + if (!pg_md5_encrypt(password, rolename, strlen(rolename), encrypted_password)) elog(ERROR, "password encryption failed"); new_record[Anum_pg_authid_rolpassword - 1] = @@ -841,11 +842,11 @@ AlterRole(AlterRoleStmt *stmt) CommandCounterIncrement(); if (stmt->action == +1) /* add members to role */ - AddRoleMems(stmt->role, roleid, + AddRoleMems(rolename, roleid, rolemembers, roleNamesToIds(rolemembers), GetUserId(), false); else if (stmt->action == -1) /* drop members from role */ - DelRoleMems(stmt->role, roleid, + DelRoleMems(rolename, roleid, rolemembers, roleNamesToIds(rolemembers), false); @@ -870,12 +871,20 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (stmt->role) { - roletuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role)); + roleid = get_nameid_oid(stmt->role, false); + + if (!OidIsValid(roleid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("role \"%s\" does not exist", stmt->role->str))); + + + roletuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); if (!HeapTupleIsValid(roletuple)) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("role \"%s\" does not exist", stmt->role))); + errmsg("invalid role oid: %u", roleid))); roleid = HeapTupleGetOid(roletuple); @@ -1163,13 +1172,6 @@ RenameRole(const char *oldname, const char *newname) (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("role \"%s\" already exists", newname))); - if (strcmp(newname, "public") == 0 || - strcmp(newname, "none") == 0) - ereport(ERROR, - (errcode(ERRCODE_RESERVED_NAME), - errmsg("role name \"%s\" is reserved", - newname))); - /* * createrole is enough privilege unless you want to mess with a superuser */ @@ -1240,7 +1242,7 @@ GrantRole(GrantRoleStmt *stmt) ListCell *item; if (stmt->grantor) - grantor = get_role_oid(stmt->grantor, false); + grantor = get_nameid_oid(stmt->grantor, false); else grantor = GetUserId(); @@ -1335,7 +1337,7 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) } /* Must have privileges on the receiving side too */ - newrole = get_role_oid(stmt->newrole, false); + newrole = get_nameid_oid(stmt->newrole, false); if (!has_privs_of_role(GetUserId(), newrole)) ereport(ERROR, @@ -1360,8 +1362,8 @@ roleNamesToIds(List *memberNames) foreach(l, memberNames) { - char *rolename = strVal(lfirst(l)); - Oid roleid = get_role_oid(rolename, false); + NameId *rolename = (NameId*) lfirst(l); + Oid roleid = get_nameid_oid(rolename, false); result = lappend_oid(result, roleid); } diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 7b51d33..6a7efec 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2470,6 +2470,17 @@ _copyXmlSerialize(const XmlSerialize *from) return newnode; } +static NameId * +_copyNameId(const NameId *from) +{ + NameId *newnode = makeNode(NameId); + + COPY_SCALAR_FIELD(idtype); + COPY_STRING_FIELD(str); + + return newnode; +} + static Query * _copyQuery(const Query *from) { @@ -2616,6 +2627,7 @@ _copyAlterTableCmd(const AlterTableCmd *from) COPY_SCALAR_FIELD(subtype); COPY_STRING_FIELD(name); + COPY_NODE_FIELD(newowner); COPY_NODE_FIELD(def); COPY_SCALAR_FIELD(behavior); COPY_SCALAR_FIELD(missing_ok); @@ -2660,7 +2672,7 @@ _copyPrivGrantee(const PrivGrantee *from) { PrivGrantee *newnode = makeNode(PrivGrantee); - COPY_STRING_FIELD(rolname); + COPY_NODE_FIELD(rolname); return newnode; } @@ -2696,7 +2708,7 @@ _copyGrantRoleStmt(const GrantRoleStmt *from) COPY_NODE_FIELD(grantee_roles); COPY_SCALAR_FIELD(is_grant); COPY_SCALAR_FIELD(admin_opt); - COPY_STRING_FIELD(grantor); + COPY_NODE_FIELD(grantor); COPY_SCALAR_FIELD(behavior); return newnode; @@ -3003,7 +3015,7 @@ _copyAlterOwnerStmt(const AlterOwnerStmt *from) COPY_NODE_FIELD(relation); COPY_NODE_FIELD(object); COPY_NODE_FIELD(objarg); - COPY_STRING_FIELD(newowner); + COPY_NODE_FIELD(newowner); return newnode; } @@ -3388,7 +3400,7 @@ _copyCreateTableSpaceStmt(const CreateTableSpaceStmt *from) CreateTableSpaceStmt *newnode = makeNode(CreateTableSpaceStmt); COPY_STRING_FIELD(tablespacename); - COPY_STRING_FIELD(owner); + COPY_NODE_FIELD(owner); COPY_STRING_FIELD(location); COPY_NODE_FIELD(options); @@ -3525,7 +3537,7 @@ _copyCreateUserMappingStmt(const CreateUserMappingStmt *from) { CreateUserMappingStmt *newnode = makeNode(CreateUserMappingStmt); - COPY_STRING_FIELD(username); + COPY_NODE_FIELD(username); COPY_STRING_FIELD(servername); COPY_NODE_FIELD(options); @@ -3537,7 +3549,7 @@ _copyAlterUserMappingStmt(const AlterUserMappingStmt *from) { AlterUserMappingStmt *newnode = makeNode(AlterUserMappingStmt); - COPY_STRING_FIELD(username); + COPY_NODE_FIELD(username); COPY_STRING_FIELD(servername); COPY_NODE_FIELD(options); @@ -3549,7 +3561,7 @@ _copyDropUserMappingStmt(const DropUserMappingStmt *from) { DropUserMappingStmt *newnode = makeNode(DropUserMappingStmt); - COPY_STRING_FIELD(username); + COPY_NODE_FIELD(username); COPY_STRING_FIELD(servername); COPY_SCALAR_FIELD(missing_ok); @@ -3662,7 +3674,7 @@ _copyAlterRoleStmt(const AlterRoleStmt *from) { AlterRoleStmt *newnode = makeNode(AlterRoleStmt); - COPY_STRING_FIELD(role); + COPY_NODE_FIELD(role); COPY_NODE_FIELD(options); COPY_SCALAR_FIELD(action); @@ -3674,7 +3686,7 @@ _copyAlterRoleSetStmt(const AlterRoleSetStmt *from) { AlterRoleSetStmt *newnode = makeNode(AlterRoleSetStmt); - COPY_STRING_FIELD(role); + COPY_NODE_FIELD(role); COPY_STRING_FIELD(database); COPY_NODE_FIELD(setstmt); @@ -3735,7 +3747,7 @@ _copyCreateSchemaStmt(const CreateSchemaStmt *from) CreateSchemaStmt *newnode = makeNode(CreateSchemaStmt); COPY_STRING_FIELD(schemaname); - COPY_STRING_FIELD(authid); + COPY_NODE_FIELD(authid); COPY_NODE_FIELD(schemaElts); COPY_SCALAR_FIELD(if_not_exists); @@ -3820,7 +3832,7 @@ _copyReassignOwnedStmt(const ReassignOwnedStmt *from) ReassignOwnedStmt *newnode = makeNode(ReassignOwnedStmt); COPY_NODE_FIELD(roles); - COPY_STRING_FIELD(newrole); + COPY_NODE_FIELD(newrole); return newnode; } @@ -4703,6 +4715,9 @@ copyObject(const void *from) case T_XmlSerialize: retval = _copyXmlSerialize(from); break; + case T_NameId: + retval = _copyNameId(from); + break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(from)); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index d5db71d..407d229 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -971,6 +971,7 @@ _equalAlterTableCmd(const AlterTableCmd *a, const AlterTableCmd *b) { COMPARE_SCALAR_FIELD(subtype); COMPARE_STRING_FIELD(name); + COMPARE_NODE_FIELD(newowner); COMPARE_NODE_FIELD(def); COMPARE_SCALAR_FIELD(behavior); COMPARE_SCALAR_FIELD(missing_ok); @@ -1009,7 +1010,7 @@ _equalGrantStmt(const GrantStmt *a, const GrantStmt *b) static bool _equalPrivGrantee(const PrivGrantee *a, const PrivGrantee *b) { - COMPARE_STRING_FIELD(rolname); + COMPARE_NODE_FIELD(rolname); return true; } @@ -1039,7 +1040,7 @@ _equalGrantRoleStmt(const GrantRoleStmt *a, const GrantRoleStmt *b) COMPARE_NODE_FIELD(grantee_roles); COMPARE_SCALAR_FIELD(is_grant); COMPARE_SCALAR_FIELD(admin_opt); - COMPARE_STRING_FIELD(grantor); + COMPARE_NODE_FIELD(grantor); COMPARE_SCALAR_FIELD(behavior); return true; @@ -1292,7 +1293,7 @@ _equalAlterOwnerStmt(const AlterOwnerStmt *a, const AlterOwnerStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(object); COMPARE_NODE_FIELD(objarg); - COMPARE_STRING_FIELD(newowner); + COMPARE_NODE_FIELD(newowner); return true; } @@ -1614,7 +1615,7 @@ static bool _equalCreateTableSpaceStmt(const CreateTableSpaceStmt *a, const CreateTableSpaceStmt *b) { COMPARE_STRING_FIELD(tablespacename); - COMPARE_STRING_FIELD(owner); + COMPARE_NODE_FIELD(owner); COMPARE_STRING_FIELD(location); COMPARE_NODE_FIELD(options); @@ -1731,7 +1732,7 @@ _equalAlterForeignServerStmt(const AlterForeignServerStmt *a, const AlterForeign static bool _equalCreateUserMappingStmt(const CreateUserMappingStmt *a, const CreateUserMappingStmt *b) { - COMPARE_STRING_FIELD(username); + COMPARE_NODE_FIELD(username); COMPARE_STRING_FIELD(servername); COMPARE_NODE_FIELD(options); @@ -1741,7 +1742,7 @@ _equalCreateUserMappingStmt(const CreateUserMappingStmt *a, const CreateUserMapp static bool _equalAlterUserMappingStmt(const AlterUserMappingStmt *a, const AlterUserMappingStmt *b) { - COMPARE_STRING_FIELD(username); + COMPARE_NODE_FIELD(username); COMPARE_STRING_FIELD(servername); COMPARE_NODE_FIELD(options); @@ -1751,7 +1752,7 @@ _equalAlterUserMappingStmt(const AlterUserMappingStmt *a, const AlterUserMapping static bool _equalDropUserMappingStmt(const DropUserMappingStmt *a, const DropUserMappingStmt *b) { - COMPARE_STRING_FIELD(username); + COMPARE_NODE_FIELD(username); COMPARE_STRING_FIELD(servername); COMPARE_SCALAR_FIELD(missing_ok); @@ -1849,7 +1850,7 @@ _equalCreateRoleStmt(const CreateRoleStmt *a, const CreateRoleStmt *b) static bool _equalAlterRoleStmt(const AlterRoleStmt *a, const AlterRoleStmt *b) { - COMPARE_STRING_FIELD(role); + COMPARE_NODE_FIELD(role); COMPARE_NODE_FIELD(options); COMPARE_SCALAR_FIELD(action); @@ -1859,7 +1860,7 @@ _equalAlterRoleStmt(const AlterRoleStmt *a, const AlterRoleStmt *b) static bool _equalAlterRoleSetStmt(const AlterRoleSetStmt *a, const AlterRoleSetStmt *b) { - COMPARE_STRING_FIELD(role); + COMPARE_NODE_FIELD(role); COMPARE_STRING_FIELD(database); COMPARE_NODE_FIELD(setstmt); @@ -1910,7 +1911,7 @@ static bool _equalCreateSchemaStmt(const CreateSchemaStmt *a, const CreateSchemaStmt *b) { COMPARE_STRING_FIELD(schemaname); - COMPARE_STRING_FIELD(authid); + COMPARE_NODE_FIELD(authid); COMPARE_NODE_FIELD(schemaElts); COMPARE_SCALAR_FIELD(if_not_exists); @@ -1981,7 +1982,7 @@ static bool _equalReassignOwnedStmt(const ReassignOwnedStmt *a, const ReassignOwnedStmt *b) { COMPARE_NODE_FIELD(roles); - COMPARE_STRING_FIELD(newrole); + COMPARE_NODE_FIELD(newrole); return true; } @@ -2453,6 +2454,15 @@ _equalXmlSerialize(const XmlSerialize *a, const XmlSerialize *b) return true; } +static bool +_equalNameId(const NameId *a, const NameId *b) +{ + COMPARE_SCALAR_FIELD(idtype); + COMPARE_STRING_FIELD(str); + + return true; +} + /* * Stuff from pg_list.h */ @@ -3163,6 +3173,9 @@ equal(const void *a, const void *b) case T_XmlSerialize: retval = _equalXmlSerialize(a, b); break; + case T_NameId: + retval = _equalNameId(a, b); + break; default: elog(ERROR, "unrecognized node type: %d", diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index bd180e7..d523776 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -192,6 +192,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); int ival; char *str; const char *keyword; + NameId *nameid; char chr; bool boolean; @@ -291,7 +292,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <str> opt_type %type <str> foreign_server_version opt_foreign_server_version -%type <str> auth_ident +%type <nameid> auth_ident %type <str> opt_in_database %type <str> OptSchemaName @@ -474,7 +475,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <ival> Iconst SignedIconst %type <str> Sconst comment_text notify_payload -%type <str> RoleId opt_granted_by opt_boolean_or_string +%type <nameid> RoleId opt_granted_by +%type <str> opt_boolean_or_string %type <list> var_list %type <str> ColId ColLabel var_name type_function_name param_name %type <str> NonReservedWord NonReservedWord_or_Sconst @@ -494,7 +496,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <list> constraints_set_list %type <boolean> constraints_set_mode -%type <str> OptTableSpace OptConsTableSpace OptTableSpaceOwner +%type <str> OptTableSpace OptConsTableSpace +%type <nameid> OptTableSpaceOwner %type <ival> opt_check_option %type <str> opt_provider security_label @@ -864,8 +867,14 @@ CreateRoleStmt: CREATE ROLE RoleId opt_with OptRoleList { CreateRoleStmt *n = makeNode(CreateRoleStmt); + NameId *nid = $3; + if (nid->idtype != NAMEID_CSTRING) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("role name should not be a keyword nor reserved name."), + parser_errposition(@3))); n->stmt_type = ROLESTMT_ROLE; - n->role = $3; + n->role = nid->str; n->options = $5; $$ = (Node *)n; } @@ -1021,8 +1030,14 @@ CreateUserStmt: CREATE USER RoleId opt_with OptRoleList { CreateRoleStmt *n = makeNode(CreateRoleStmt); + NameId *nid = $3; + if (nid->idtype != NAMEID_CSTRING) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("role name should not be a keyword nor reserved name."), + parser_errposition(@3))); n->stmt_type = ROLESTMT_USER; - n->role = $3; + n->role = nid->str; n->options = $5; $$ = (Node *)n; } @@ -1164,8 +1179,14 @@ CreateGroupStmt: CREATE GROUP_P RoleId opt_with OptRoleList { CreateRoleStmt *n = makeNode(CreateRoleStmt); + NameId *nid = $3; + if (nid->idtype != NAMEID_CSTRING) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("role name should not be a keyword nor reserved name."), + parser_errposition(@3))); n->stmt_type = ROLESTMT_GROUP; - n->role = $3; + n->role = nid->str; n->options = $5; $$ = (Node *)n; } @@ -1231,10 +1252,7 @@ CreateSchemaStmt: { CreateSchemaStmt *n = makeNode(CreateSchemaStmt); /* One can omit the schema name or the authorization id. */ - if ($3 != NULL) - n->schemaname = $3; - else - n->schemaname = $5; + n->schemaname = $3; n->authid = $5; n->schemaElts = $6; n->if_not_exists = false; @@ -1254,10 +1272,7 @@ CreateSchemaStmt: { CreateSchemaStmt *n = makeNode(CreateSchemaStmt); /* One can omit the schema name or the authorization id. */ - if ($6 != NULL) - n->schemaname = $6; - else - n->schemaname = $8; + n->schemaname = $6; n->authid = $8; if ($9 != NIL) ereport(ERROR, @@ -2263,7 +2278,7 @@ alter_table_cmd: { AlterTableCmd *n = makeNode(AlterTableCmd); n->subtype = AT_ChangeOwner; - n->name = $3; + n->newowner = $3; $$ = (Node *)n; } /* ALTER TABLE <name> SET TABLESPACE <tablespacename> */ @@ -3727,7 +3742,7 @@ CreateTableSpaceStmt: CREATE TABLESPACE name OptTableSpaceOwner LOCATION Sconst } ; -OptTableSpaceOwner: OWNER name { $$ = $2; } +OptTableSpaceOwner: OWNER RoleId { $$ = $2; } | /*EMPTY */ { $$ = NULL; } ; @@ -4458,9 +4473,19 @@ CreateUserMappingStmt: CREATE USER MAPPING FOR auth_ident SERVER name create_gen /* User mapping authorization identifier */ auth_ident: - CURRENT_USER { $$ = "current_user"; } - | USER { $$ = "current_user"; } - | RoleId { $$ = (strcmp($1, "public") == 0) ? NULL : $1; } + RoleId + { $$ = $1; } + /* + * The keyword USER won't be appear in other syntxes so it is + * processed here, not at RoleId. + */ + | USER + { + NameId *n = makeNode(NameId); + n->idtype = NAMEID_CURRENT_USER; + n->str = $1; + $$ = n; + } ; /***************************************************************************** @@ -5403,7 +5428,7 @@ DropOwnedStmt: ; ReassignOwnedStmt: - REASSIGN OWNED BY role_list TO name + REASSIGN OWNED BY role_list TO RoleId { ReassignOwnedStmt *n = makeNode(ReassignOwnedStmt); n->roles = $4; @@ -6221,21 +6246,27 @@ grantee_list: grantee: RoleId { PrivGrantee *n = makeNode(PrivGrantee); - /* This hack lets us avoid reserving PUBLIC as a keyword*/ - if (strcmp($1, "public") == 0) - n->rolname = NULL; - else - n->rolname = $1; + NameId *nid = $1; + if (nid->idtype == NAMEID_NONE) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("%s cannot be used as grantee name", + nid->str))); + n->rolname = nid; $$ = (Node *)n; } | GROUP_P RoleId + /* Treat GROUP PUBLIC as a synonym for PUBLIC */ { PrivGrantee *n = makeNode(PrivGrantee); - /* Treat GROUP PUBLIC as a synonym for PUBLIC */ - if (strcmp($2, "public") == 0) - n->rolname = NULL; - else - n->rolname = $2; + NameId *nid = $2; + + if (nid->idtype == NAMEID_NONE) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("%s cannot be used as grantee name", + nid->str))); + n->rolname = nid; $$ = (Node *)n; } ; @@ -7340,9 +7371,19 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name | ALTER GROUP_P RoleId RENAME TO RoleId { RenameStmt *n = makeNode(RenameStmt); + NameId *nold = $3; + NameId *nnew = $6; + if (nold->idtype != NAMEID_CSTRING || + nnew->idtype != NAMEID_CSTRING) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("role name should not be a keyword nor reserved name."), + parser_errposition( + nold->idtype != NAMEID_CSTRING ? + @3 : @6))); n->renameType = OBJECT_ROLE; - n->subname = $3; - n->newname = $6; + n->subname = nold->str; + n->newname = nnew->str; n->missing_ok = false; $$ = (Node *)n; } @@ -7640,18 +7681,38 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name | ALTER ROLE RoleId RENAME TO RoleId { RenameStmt *n = makeNode(RenameStmt); + NameId *nold = $3; + NameId *nnew = $6; + if (nold->idtype != NAMEID_CSTRING || + nnew->idtype != NAMEID_CSTRING) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("role name should not be a keyword nor reserved name."), + parser_errposition( + nold->idtype != NAMEID_CSTRING ? + @3 : @6))); n->renameType = OBJECT_ROLE; - n->subname = $3; - n->newname = $6; + n->subname = nold->str; + n->newname = nnew->str; n->missing_ok = false; $$ = (Node *)n; } | ALTER USER RoleId RENAME TO RoleId { RenameStmt *n = makeNode(RenameStmt); + NameId *nold = $3; + NameId *nnew = $6; + if (nold->idtype != NAMEID_CSTRING || + nnew->idtype != NAMEID_CSTRING) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("role name should not be a keyword nor reserved name."), + parser_errposition( + nold->idtype != NAMEID_CSTRING ? + @3 : @6))); n->renameType = OBJECT_ROLE; - n->subname = $3; - n->newname = $6; + n->subname = nold->str; + n->newname = nnew->str; n->missing_ok = false; $$ = (Node *)n; } @@ -10716,7 +10777,7 @@ opt_float: '(' Iconst ')' if ($2 < 1) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("precision for type float must be at least 1 bit"), + errmsg("recision for type float must be at least 1 bit"), parser_errposition(@2))); else if ($2 <= 24) $$ = SystemTypeName("float4"); @@ -12946,12 +13007,45 @@ AexprConst: Iconst Iconst: ICONST { $$ = $1; }; Sconst: SCONST { $$ = $1; }; -RoleId: NonReservedWord { $$ = $1; }; +RoleId: NonReservedWord + { + /* + * The name "public" and "none" are decided not to be + * keywords. They cannot be used as identifier by any + * means as the result. + */ + NameId *n = makeNode(NameId); + char *w = pstrdup($1); + if (strcmp(w, "public") == 0) + n->idtype = NAMEID_PUBLIC; + else if (strcmp(w, "none") == 0) + n->idtype = NAMEID_NONE; + else + n->idtype = NAMEID_CSTRING; + n->str = w; + $$ = n; + } + | CURRENT_USER + { + NameId *n = makeNode(NameId); + n->idtype = NAMEID_CURRENT_USER; + n->str = pstrdup($1); + $$ = n; + } + | SESSION_USER + { + NameId *n = makeNode(NameId); + n->idtype = NAMEID_SESSION_USER; + n->str = pstrdup($1); + $$ = n; + } + ; + role_list: RoleId - { $$ = list_make1(makeString($1)); } + { $$ = list_make1($1); } | role_list ',' RoleId - { $$ = lappend($1, makeString($3)); } + { $$ = lappend($1, $3); } ; SignedIconst: Iconst { $$ = $1; } diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 7c1939f..6753aea 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -90,7 +90,7 @@ typedef struct { const char *stmtType; /* "CREATE SCHEMA" or "ALTER SCHEMA" */ char *schemaname; /* name of schema */ - char *authid; /* owner of schema */ + NameId *authid; /* owner of schema */ List *sequences; /* CREATE SEQUENCE items */ List *tables; /* CREATE TABLE items */ List *views; /* CREATE VIEW items */ diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index dc6eb2c..f160dd1 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -5133,3 +5133,34 @@ get_role_oid_or_public(const char *rolname) return get_role_oid(rolname, false); } + +Oid +get_nameid_oid(const NameId *rolname, bool missing_ok) +{ + Oid oid; + + switch (rolname->idtype) + { + case NAMEID_CSTRING: + oid = get_role_oid(rolname->str, missing_ok); + break; + case NAMEID_CURRENT_USER: + oid = GetUserId(); + break; + case NAMEID_SESSION_USER: + oid = GetSessionUserId(); + break; + case NAMEID_PUBLIC: + case NAMEID_NONE: + /* + * XXX: this returning oid cannot be distinguised with not found in + * get_role_oid when messimg_ok. + */ + oid = InvalidOid; + break; + default: + elog(ERROR, "Unexpected nameid for role: %d", rolname->idtype); + } + + return oid; +} diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 154d943..0393ea6 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -410,6 +410,7 @@ typedef enum NodeTag T_XmlSerialize, T_WithClause, T_CommonTableExpr, + T_NameId, /* * TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 3e4f815..1a0c35d 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -278,6 +278,25 @@ typedef struct CollateClause } CollateClause; /* + * NameId - Stroes arbitrary string or predefined id number + */ +typedef enum NameIdTypes +{ + NAMEID_CSTRING, + NAMEID_CURRENT_USER, + NAMEID_SESSION_USER, + NAMEID_PUBLIC, + NAMEID_NONE +} NameIdTypes; + +typedef struct NameId +{ + NodeTag type; + NameIdTypes idtype; /* Type of this identifier */ + char *str; /* Will be filled only for NAMEID_CSTRING */ +} NameId; + +/* * FuncCall - a function or aggregate invocation * * agg_order (if not NIL) indicates we saw 'foo(... ORDER BY ...)', or if @@ -1254,7 +1273,7 @@ typedef struct CreateSchemaStmt { NodeTag type; char *schemaname; /* the name of the schema to create */ - char *authid; /* the owner of the created schema */ + NameId *authid; /* the owner of the created schema */ List *schemaElts; /* schema components (list of parsenodes) */ bool if_not_exists; /* just do nothing if schema already exists? */ } CreateSchemaStmt; @@ -1353,7 +1372,8 @@ typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ NodeTag type; AlterTableType subtype; /* Type of table alteration to apply */ char *name; /* column, constraint, or trigger to act on, - * or new owner or tablespace */ + * or tablespace */ + NameId *newowner; /* new owner could be special values */ Node *def; /* definition of new column, index, * constraint, or parent table */ DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */ @@ -1433,7 +1453,7 @@ typedef struct GrantStmt typedef struct PrivGrantee { NodeTag type; - char *rolname; /* if NULL then PUBLIC */ + NameId *rolname; /* if NULL then PUBLIC */ } PrivGrantee; /* @@ -1478,7 +1498,7 @@ typedef struct GrantRoleStmt List *grantee_roles; /* list of member roles to add/delete */ bool is_grant; /* true = GRANT, false = REVOKE */ bool admin_opt; /* with admin option */ - char *grantor; /* set grantor to other than current role */ + NameId *grantor; /* set grantor to other than current role */ DropBehavior behavior; /* drop behavior (for REVOKE) */ } GrantRoleStmt; @@ -1690,7 +1710,7 @@ typedef struct CreateTableSpaceStmt { NodeTag type; char *tablespacename; - char *owner; + NameId *owner; char *location; List *options; } CreateTableSpaceStmt; @@ -1816,7 +1836,7 @@ typedef struct CreateForeignTableStmt typedef struct CreateUserMappingStmt { NodeTag type; - char *username; /* username or PUBLIC/CURRENT_USER */ + NameId *username; /* username or PUBLIC/CURRENT_USER */ char *servername; /* server name */ List *options; /* generic options to server */ } CreateUserMappingStmt; @@ -1824,7 +1844,7 @@ typedef struct CreateUserMappingStmt typedef struct AlterUserMappingStmt { NodeTag type; - char *username; /* username or PUBLIC/CURRENT_USER */ + NameId *username; /* username or PUBLIC/CURRENT_USER */ char *servername; /* server name */ List *options; /* generic options to server */ } AlterUserMappingStmt; @@ -1832,7 +1852,7 @@ typedef struct AlterUserMappingStmt typedef struct DropUserMappingStmt { NodeTag type; - char *username; /* username or PUBLIC/CURRENT_USER */ + NameId *username; /* username or PUBLIC/CURRENT_USER */ char *servername; /* server name */ bool missing_ok; /* ignore missing mappings */ } DropUserMappingStmt; @@ -1982,7 +2002,7 @@ typedef struct CreateRoleStmt typedef struct AlterRoleStmt { NodeTag type; - char *role; /* role name */ + NameId *role; /* role name */ List *options; /* List of DefElem nodes */ int action; /* +1 = add members, -1 = drop members */ } AlterRoleStmt; @@ -1990,7 +2010,7 @@ typedef struct AlterRoleStmt typedef struct AlterRoleSetStmt { NodeTag type; - char *role; /* role name */ + NameId *role; /* role name */ char *database; /* database name, or NULL */ VariableSetStmt *setstmt; /* SET or RESET subcommand */ } AlterRoleSetStmt; @@ -2365,7 +2385,7 @@ typedef struct AlterOwnerStmt RangeVar *relation; /* in case it's a table */ List *object; /* in case it's some other object */ List *objarg; /* argument types, if applicable */ - char *newowner; /* the new owner */ + NameId *newowner; /* the new owner */ } AlterOwnerStmt; @@ -2813,7 +2833,7 @@ typedef struct ReassignOwnedStmt { NodeTag type; List *roles; - char *newrole; + NameId *newrole; } ReassignOwnedStmt; /* diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index a8e3164..0707f6c 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -229,7 +229,7 @@ extern bool is_admin_of_role(Oid member, Oid role); extern void check_is_member_of_role(Oid member, Oid role); extern Oid get_role_oid(const char *rolname, bool missing_ok); extern Oid get_role_oid_or_public(const char *rolname); - +extern Oid get_nameid_oid(const NameId *rolname, bool missing_ok); extern void select_best_grantor(Oid roleId, AclMode privileges, const Acl *acl, Oid ownerId, Oid *grantorId, AclMode *grantOptions); -- 2.1.0.GIT
/* Current database is 'testdb' */ CREATE EXTENSION postgres_fdw; -- postgres user is expected to be a superuser \c testdb postgres CREATE OR REPLACE FUNCTION chkrolattr() RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool) AS $$ SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication FROM pg_roles r JOIN (VALUES(CURRENT_USER, 'current_user'), (SESSION_USER, 'session_user'), ('current_user', '-'), ('session_user', '-'), ('Public', '-'), ('None', '-')) AS v(uname, keyword) ON (r.rolname = v.uname) ORDER BY 1; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION chksetconfig() RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[]) AS $$ SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'), COALESCE(v.keyword, '-'), s.setconfig FROM pg_db_role_setting s LEFT JOIN pg_roles r ON (r.oid = s.setrole) LEFT JOIN pg_database d ON (d.oid = s.setdatabase) LEFT JOIN (VALUES(CURRENT_USER, 'current_user'), (SESSION_USER, 'session_user')) AS v(uname, keyword) ON (r.rolname = v.uname) ORDER BY 1, 2; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION chkumapping() RETURNS TABLE (umname name, umserver name, umoptions text[]) AS $$ SELECT r.rolname, s.srvname, m.umoptions FROM pg_user_mapping m LEFT JOIN pg_roles r ON (r.oid = m.umuser) JOIN pg_foreign_server s ON (s.oid = m.umserver) ORDER BY 2; $$ LANGUAGE SQL; DROP ROLE IF EXISTS "Public"; DROP ROLE IF EXISTS "None"; DROP ROLE IF EXISTS "current_user"; DROP ROLE IF EXISTS "user"; DROP ROLE IF EXISTS "testrol0"; DROP ROLE IF EXISTS "testrol1"; DROP ROLE IF EXISTS "testrol2"; DROP ROLE IF EXISTS "testrolx"; CREATE ROLE "Public"; CREATE ROLE "None"; CREATE ROLE "current_user"; CREATE ROLE "session_user"; CREATE ROLE "user"; CREATE ROLE current_user; -- error CREATE ROLE current_role; -- error CREATE ROLE session_user; -- error CREATE ROLE user; -- error CREATE ROLE all; -- error CREATE ROLE public; -- error CREATE ROLE "public"; -- error CREATE ROLE none; -- error CREATE ROLE "none"; -- error CREATE ROLE testrol0 SUPERUSER LOGIN; CREATE ROLE testrolx SUPERUSER LOGIN; CREATE ROLE testrol2 SUPERUSER; CREATE ROLE testrol1 SUPERUSER LOGIN IN ROLE testrol2; \c testdb testrol1 SET ROLE testrol2; -- ALTER ROLE BEGIN; SELECT * FROM chkrolattr(); ALTER ROLE CURRENT_USER WITH REPLICATION; SELECT * FROM chkrolattr(); ALTER ROLE "current_user" WITH REPLICATION; SELECT * FROM chkrolattr(); ALTER ROLE SESSION_USER WITH REPLICATION; SELECT * FROM chkrolattr(); ALTER ROLE "session_user" WITH REPLICATION; SELECT * FROM chkrolattr(); ALTER USER "Public" WITH REPLICATION; ALTER USER "None" WITH REPLICATION; SELECT * FROM chkrolattr(); ALTER USER testrol1 WITH NOREPLICATION; ALTER USER testrol2 WITH NOREPLICATION; SELECT * FROM chkrolattr(); ROLLBACK; ALTER ROLE USER WITH LOGIN; -- error ALTER ROLE CURRENT_ROLE WITH LOGIN; --error ALTER ROLE ALL WITH REPLICATION; -- error ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error ALTER ROLE PUBLIC WITH NOREPLICATION; -- error ALTER ROLE "public" WITH NOREPLICATION; -- error ALTER ROLE NONE WITH NOREPLICATION; -- error ALTER ROLE "none" WITH NOREPLICATION; -- error ALTER ROLE nonexistent WITH NOREPLICATION; -- error -- ALTER USER BEGIN; SELECT * FROM chkrolattr(); ALTER USER CURRENT_USER WITH NOREPLICATION; SELECT * FROM chkrolattr(); ALTER USER 'current_user' WITH NOREPLICATION; SELECT * FROM chkrolattr(); ALTER USER SESSION_USER WITH REPLICATION; SELECT * FROM chkrolattr(); ALTER USER 'session_user' WITH REPLICATION; SELECT * FROM chkrolattr(); ALTER USER 'Public' WITH REPLICATION; ALTER USER 'None' WITH REPLICATION; SELECT * FROM chkrolattr(); ALTER USER testrol1 WITH NOREPLICATION; ALTER USER testrol2 WITH NOREPLICATION; SELECT * FROM chkrolattr(); ROLLBACK; ALTER USER USER WITH LOGIN; -- error ALTER USER CURRENT_ROLE WITH LOGIN; -- error ALTER USER ALL WITH REPLICATION; -- error ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error ALTER USER PUBLIC WITH NOREPLICATION; -- error ALTER USER "public" WITH NOREPLICATION; -- error ALTER USER NONE WITH NOREPLICATION; -- error ALTER USER "none" WITH NOREPLICATION; -- error ALTER USER nonexistent WITH NOREPLICATION; -- error -- ALTER ROLE SET/RESET SELECT * FROM chksetconfig(); ALTER ROLE CURRENT_USER SET application_name to 'FOO'; ALTER ROLE SESSION_USER SET application_name to 'BAR'; ALTER ROLE "current_user" SET application_name to 'FOOFOO'; ALTER ROLE "Public" SET application_name to 'BARBAR'; ALTER ROLE ALL SET application_name to 'SLAP'; SELECT * FROM chksetconfig(); ALTER ROLE testrol1 SET application_name to 'SLAM'; SELECT * FROM chksetconfig(); ALTER ROLE CURRENT_USER RESET application_name; ALTER ROLE SESSION_USER RESET application_name; ALTER ROLE "current_user" RESET application_name; ALTER ROLE "Public" RESET application_name; ALTER ROLE ALL RESET application_name; SELECT * FROM chksetconfig(); ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error ALTER ROLE USER SET application_name to 'BOOM'; -- error ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error -- ALTER USER SET/RESET SELECT * FROM chksetconfig(); ALTER USER CURRENT_USER SET application_name to 'FOO'; ALTER USER SESSION_USER SET application_name to 'BAR'; ALTER USER "current_user" SET application_name to 'FOOFOO'; ALTER USER "Public" SET application_name to 'BARBAR'; ALTER USER ALL SET application_name to 'SLAP'; SELECT * FROM chksetconfig(); ALTER USER testrol1 SET application_name to 'SLAM'; SELECT * FROM chksetconfig(); ALTER USER CURRENT_USER RESET application_name; ALTER USER SESSION_USER RESET application_name; ALTER USER "current_user" RESET application_name; ALTER USER "Public" RESET application_name; ALTER USER ALL RESET application_name; SELECT * FROM chksetconfig(); ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error ALTER USER USER SET application_name to 'BOOM'; -- error ALTER USER PUBLIC SET application_name to 'BOMB'; -- error ALTER USER NONE SET application_name to 'BOMB'; -- error ALTER USER nonexistent SET application_name to 'BOMB'; -- error -- CREAETE SCHEMA set client_min_messages to error; CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER; CREATE SCHEMA newschema4 AUTHORIZATION testrolx; CREATE SCHEMA newschema5 AUTHORIZATION "Public"; CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error SELECT n.nspname, r.rolname FROM pg_namespace n JOIN pg_roles r ON (r.oid = n.nspowner) WHERE n.nspname LIKE 'newschema_' ORDER BY 1; DROP SCHEMA IF EXISTS newschema1; DROP SCHEMA IF EXISTS newschema2; DROP SCHEMA IF EXISTS newschema3; DROP SCHEMA IF EXISTS newschema4; DROP SCHEMA IF EXISTS newschema5; DROP SCHEMA IF EXISTS newschema6; CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER; CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user"; CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER; CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION testrolx; CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error SELECT n.nspname, r.rolname FROM pg_namespace n JOIN pg_roles r ON (r.oid = n.nspowner) WHERE n.nspname LIKE 'newschema_' ORDER BY 1; -- ALTER TABLE OWNER TO \c testdb testrol0 set client_min_messages to error; CREATE TABLE testtab1 (a int); CREATE TABLE testtab2 (a int); CREATE TABLE testtab3 (a int); CREATE TABLE testtab4 (a int); CREATE TABLE testtab5 (a int); CREATE TABLE testtab6 (a int); \c testdb testrol1 SET ROLE testrol2; ALTER TABLE testtab1 OWNER TO CURRENT_USER; ALTER TABLE testtab2 OWNER TO "current_user"; ALTER TABLE testtab3 OWNER TO SESSION_USER; ALTER TABLE testtab4 OWNER TO testrolx; ALTER TABLE testtab5 OWNER TO "Public"; ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error ALTER TABLE testtab6 OWNER TO USER; --error ALTER TABLE testtab6 OWNER TO PUBLIC; -- error ALTER TABLE testtab6 OWNER TO "public"; -- error ALTER TABLE testtab6 OWNER TO nonexistent; -- error SELECT c.relname, r.rolname FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner) WHERE relname LIKE 'testtab_' ORDER BY 1; -- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are -- changed their owner in the same way. -- ALTER AGGREGATE \c testdb testrol0 DROP AGGREGATE IF EXISTS testagg1(int2); DROP AGGREGATE IF EXISTS testagg2(int2); DROP AGGREGATE IF EXISTS testagg3(int2); DROP AGGREGATE IF EXISTS testagg4(int2); DROP AGGREGATE IF EXISTS testagg5(int2); DROP AGGREGATE IF EXISTS testagg6(int2); DROP AGGREGATE IF EXISTS testagg7(int2); DROP AGGREGATE IF EXISTS testagg8(int2); DROP AGGREGATE IF EXISTS testagg9(int2); CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8); CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8); CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8); CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8); CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8); CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8); CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8); CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8); \c testdb testrol1 SET ROLE testrol2; ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER; ALTER AGGREGATE testagg2(int2) OWNER TO "current_user"; ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER; ALTER AGGREGATE testagg4(int2) OWNER TO testrolx; ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; ALTER AGGREGATE testagg6(int2) OWNER TO CURRENT_ROLE; -- error ALTER AGGREGATE testagg6(int2) OWNER TO USER; -- error ALTER AGGREGATE testagg6(int2) OWNER TO PUBLIC; -- error ALTER AGGREGATE testagg6(int2) OWNER TO "public"; -- error ALTER AGGREGATE testagg6(int2) OWNER TO nonexistent; -- error SELECT p.proname, r.rolname FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner) WHERE proname LIKE 'testagg_' ORDER BY 1; ALTER AGGREGATE testagg1(int2) OWNER TO postgres; ALTER AGGREGATE testagg2(int2) OWNER TO postgres; ALTER AGGREGATE testagg3(int2) OWNER TO postgres; ALTER AGGREGATE testagg4(int2) OWNER TO postgres; ALTER AGGREGATE testagg5(int2) OWNER TO postgres; ALTER AGGREGATE testagg6(int2) OWNER TO postgres; ALTER AGGREGATE testagg7(int2) OWNER TO postgres; ALTER AGGREGATE testagg8(int2) OWNER TO postgres; ALTER AGGREGATE testagg9(int2) OWNER TO postgres; -- ALTER COLLATION, CONVERSION, DATABASE, DOMAIN, FUNCTION, LANGUAGE, -- LARGEOBJECT, OPERATOR, OPCLASS, OPFAMILY, SCHEMA, TYPE, -- TABLESPACE, TSDICTIONARY, TSCONFIGURATION, FDW, FOREIGN_SERVER, -- EVENT_TRIGGER are processed in the same way. -- See ExecAlterOwner Stmt for details. -- CREATE USER MAPPING -- needs postgres_fdw CREATE SERVER sv1 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER sv2 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER sv3 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER sv4 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER sv5 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER sv6 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER sv7 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER sv8 FOREIGN DATA WRAPPER postgres_fdw; CREATE SERVER sv9 FOREIGN DATA WRAPPER postgres_fdw; CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); CREATE USER MAPPING FOR testrolx SERVER sv8 OPTIONS (user 'testrolx'); CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 OPTIONS (user 'CURRENT_ROLE'); -- error CREATE USER MAPPING FOR nonexistent SERVER sv9 OPTIONS (user 'nonexistent'); -- error; SELECT * FROM chkumapping(); -- ALTER USER MAPPING ALTER USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (SET user 'CURRENT_USER_alt'); ALTER USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (SET user '"current_user"_alt'); ALTER USER MAPPING FOR USER SERVER sv3 OPTIONS (SET user 'USER_alt'); ALTER USER MAPPING FOR "user" SERVER sv4 OPTIONS (SET user '"user"_alt'); ALTER USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (SET user 'SESSION_USER_alt'); ALTER USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (SET user 'public_alt'); ALTER USER MAPPING FOR "Public" SERVER sv7 OPTIONS (SET user '"Public"_alt'); ALTER USER MAPPING FOR testrolx SERVER sv8 OPTIONS (SET user 'testrolx_alt'); ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 OPTIONS (SET user 'CURRENT_ROLE_alt'); ALTER USER MAPPING FOR nonexistent SERVER sv9 OPTIONS (SET user 'nonexistent_alt'); -- error SELECT * FROM chkumapping(); -- DROP USER MAPPING DROP USER MAPPING FOR CURRENT_USER SERVER sv1; DROP USER MAPPING FOR "current_user" SERVER sv2; DROP USER MAPPING FOR USER SERVER sv3; DROP USER MAPPING FOR "user" SERVER sv4; DROP USER MAPPING FOR SESSION_USER SERVER sv5; DROP USER MAPPING FOR PUBLIC SERVER sv6; DROP USER MAPPING FOR "Public" SERVER sv7; DROP USER MAPPING FOR testrolx SERVER sv8; DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error DROP USER MAPPING FOR nonexistent SERVER sv; -- error SELECT * FROM chkumapping(); CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); CREATE USER MAPPING FOR testrolx SERVER sv8 OPTIONS (user 'testrolx'); SELECT * FROM chkumapping(); -- DROP USER MAPPING IF EXISTS DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1; SELECT * FROM chkumapping(); DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; SELECT * FROM chkumapping(); DROP USER MAPPING IF EXISTS FOR USER SERVER sv3; SELECT * FROM chkumapping(); DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; SELECT * FROM chkumapping(); DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; SELECT * FROM chkumapping(); DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; SELECT * FROM chkumapping(); DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; SELECT * FROM chkumapping(); DROP USER MAPPING IF EXISTS FOR testrolx SERVER sv8; SELECT * FROM chkumapping(); DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error -- GRANT/REVOKE UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_'; SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC; REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC; REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC; REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC; REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC; REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC; REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC; GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC; GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER; GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user"; GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER; GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO testrolx; GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) TO current_user, public, testrolx; SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER; REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user"; REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER; REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM testrolx; REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM current_user, public, testrolx; SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers