diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index cc40c14..7ad9543 100644 *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *************** *** 3171,3176 **** --- 3171,3247 ---- + + <structname>pg_default_acl</structname> + + + pg_default_acl + + + + The catalog pg_default_acl stores default + privileges for newly created objects. + + + + <structname>pg_default_acl</> Columns + + + + + Name + Type + References + Description + + + + + + defaclrole + oid + pg_authid.oid + The OID of the role associated with this entry + + + + defaclnamespace + oid + pg_namespace.oid + The OID of the namespace associated with this entry + + + + defaclgrantobjtype + char + + + r = relation (table, view) + f = function, S = sequence + + + + + defacllist + aclitem[] + + + Access privileges that the object should have on creation. + This is NOT a mask, it's exactly what the object will get. + See + , + and + + for details. + + + + +
+ + + + <structname>pg_opclass</structname> diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index dbf3a3a..87f33c8 100644 *** a/doc/src/sgml/ref/allfiles.sgml --- b/doc/src/sgml/ref/allfiles.sgml *************** Complete list of usable sgml source file *** 9,14 **** --- 9,15 ---- + diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index ...72bde1b . *** a/doc/src/sgml/ref/alter_default_privileges.sgml --- b/doc/src/sgml/ref/alter_default_privileges.sgml *************** *** 0 **** --- 1,197 ---- + + + + + ALTER DEFAULT PRIVILEGES + 7 + SQL - Language Statements + + + + ALTER DEFAULT PRIVILEGES + define default access privileges + + + + ALTER DEFAULT PRIVILEGES + + + + + ALTER DEFAULT PRIVILEGES + [ FOR { ROLE | USER } role_name [, ...] ] + [ IN SCHEMA schema_name [, ...] ] + grant_statement + + where grant_statement is: + GRANT { { { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + [,...] | ALL [ PRIVILEGES ] } + ON TABLE + TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] } | + { { { USAGE | SELECT | UPDATE } + [,...] | ALL [ PRIVILEGES ] } + ON SEQUENCE + TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] } | + { { EXECUTE | ALL [ PRIVILEGES ] } + ON FUNCTION + TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] } } + + ALTER DEFAULT PRIVILEGES + [ FOR { ROLE | USER } role_name [, ...] ] + [ IN SCHEMA schema_name [, ...] ] + revoke_statement + + where revoke_statement is: + REVOKE [ GRANT OPTION FOR ] + { { { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + [,...] | ALL [ PRIVILEGES ] } + ON TABLE + FROM { [ GROUP ] rolename | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] } | + { { { USAGE | SELECT | UPDATE } + [,...] | ALL [ PRIVILEGES ] } + ON SEQUENCE + FROM { [ GROUP ] rolename | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] } | + { { EXECUTE | ALL [ PRIVILEGES ] } + ON FUNCTION + FROM { [ GROUP ] rolename | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] } } + + + + + Description + + + ALTER DEFAULT PRIVILEGES command allows you to define privileges for newly + created objects. + + + + You can change default privileges only for yourself or roles for which + you have ADMIN privilege. + + + + If you ommit the schema_name parameter, + the specified default privileges will be used globaly for whole database. + + + + Default privileges are not inherited in any way. So if you for example + define database wide default privileges and also the default privileges + for new objects in schema public, then upon creating + new objects in schema public only those default + privileges defined for the schema will apply. + + + + See the description of the and + commands for more detailed + explanation of privilege system and the meaning of the privilege types. + + + + Parameters + + + + role_name + + + The name of an existing role for which you have ADMIN privilege. + + + + + + schema_name + + + The name of an existing schema. + + + + + + + + + Examples + + + Grant select privilege for every tables and views you'll create in schema + public to everybody: + + + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLE TO public; + + + + + Grant admin SELECT, UPDATE, INSERT and DELETE + privileges on all tables and views you'll create in the users schema. + + + ALTER DEFAULT PRIVILEGES IN SCHEMA users GRANT SELECT, UPDATE, INSERT, DELETE TO admin; + + + + + Give user webuser SELECT privilege on all new + tables, views, and sequences, and EXECUTE on all new functions in + the public schema. + Allow the admin to not only select but also + change the contents of new tables and views in the public schema, and grant those permissions + to other users. The admin user will also be allowed to execute new functions, + and read new sequences like webuser. + Finally, allow admin to update new sequences. + All above only for objects your role will create. + + + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLE TO webuser, admin; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT UPDATE, INSERT, DELETE ON TABLE TO admin WITH GRANT OPTION; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTION TO webuser, admin; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCE TO webuser; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCE TO admin; + + + + + For new tables and views created by role admin in any + schema in current database give role webuser + SELECT privilege (per schema default privileges can override it). + And for new functions created in schema public by role + admin give role webuser + EXECUTE privilege. + + + ALTER DEFAULT PRIVILEGES FOR ROLE admin GRANT SELECT ON TABLE TO webuser; + ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA public GRANT EXECUTE ON FUNCTION TO webuser; + + + + + + Compatibility + + + There is no ALTER DEFAULT PRIVILEGES statement in the SQL + standard. + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 8aaedb3..fa123bb 100644 *** a/doc/src/sgml/ref/grant.sgml --- b/doc/src/sgml/ref/grant.sgml *************** GRANT rol *** 137,142 **** --- 137,144 ---- include granting some privileges to PUBLIC. The default is no public access for tables, columns, schemas, and tablespaces; + For tables, views, functions and sequences the initial default privileges + can also be changed using ALTER DEFAULT PRIVILEGES command; CONNECT privilege and TEMP table creation privilege for databases; EXECUTE privilege for functions; and diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 723bb9c..ac95ec8 100644 *** a/doc/src/sgml/reference.sgml --- b/doc/src/sgml/reference.sgml *************** *** 37,42 **** --- 37,43 ---- &alterAggregate; &alterConversion; &alterDatabase; + &alterDefaultPrivileges; &alterDomain; &alterForeignDataWrapper; &alterFunction; diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile index 861cb1d..cb77a76 100644 *** a/src/backend/catalog/Makefile --- b/src/backend/catalog/Makefile *************** POSTGRES_BKI_SRCS = $(addprefix $(top_sr *** 37,42 **** --- 37,43 ---- pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \ pg_ts_parser.h pg_ts_template.h \ pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \ + pg_default_acl.h \ toasting.h indexing.h \ ) diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index ec4aaf0..2ac2bb9 100644 *** a/src/backend/catalog/aclchk.c --- b/src/backend/catalog/aclchk.c *************** *** 27,32 **** --- 27,33 ---- #include "catalog/pg_authid.h" #include "catalog/pg_conversion.h" #include "catalog/pg_database.h" + #include "catalog/pg_default_acl.h" #include "catalog/pg_foreign_data_wrapper.h" #include "catalog/pg_foreign_server.h" #include "catalog/pg_language.h" *************** static AclMode restrict_and_check_grant( *** 79,84 **** --- 80,87 ---- static AclMode pg_aclmask(AclObjectKind objkind, Oid table_oid, AttrNumber attnum, Oid roleid, AclMode mask, AclMaskHow how); + static Acl *get_default_acl(Oid roleId, Oid nsp_oid, char objtype); + #ifdef ACLDEBUG static void *************** objectNamesToOids(GrantObjectType objtyp *** 610,615 **** --- 613,1060 ---- } /* + * Default ACLs + * Implements ALTER DEFAULT PRIVILEGES + */ + void + ExecAlterDefaultPrivilegesStmt(AlterDefaultPrivilegesStmt *stmt) + { + InternalDefaultACL iacls; + GrantStmt *action; + ListCell *cell; + ListCell *rolecell; + ListCell *nspcell; + List *rolenames = NIL; + List *roleids = NIL; + List *nspnames = NIL; + List *nspids = NIL; + DefElem *drolenames = NULL; + DefElem *dnspnames = NULL; + Relation rolerel; + Relation nsprel; + const char *errormsg; + AclMode all_privileges; + + Assert(IsA(stmt->action, GrantStmt)); + + foreach(cell, stmt->options) + { + DefElem *defel = (DefElem *) lfirst(cell); + + if (strcmp(defel->defname, "schemas") == 0) + { + if (dnspnames) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dnspnames = defel; + } + else if (strcmp(defel->defname, "roles") == 0) + { + if (drolenames) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + drolenames = defel; + } + } + + if (dnspnames) + nspnames = (List *) dnspnames->arg; + if (drolenames) + rolenames = (List *) drolenames->arg; + + action = (GrantStmt *) stmt->action; + iacls.is_grant = action->is_grant; + iacls.objtype = action->objtype; + /* privileges to be filled below */ + iacls.grantees = NIL; /* filled below */ + iacls.grant_option = action->grant_option; + iacls.behavior = action->behavior; + + /* + * Convert the PrivGrantee list into an Oid list. Note that at this point + * we insert an ACL_ID_PUBLIC into the list if an empty role name is + * detected (which is what the grammar uses if PUBLIC is found), so + * downstream there shouldn't be any additional work needed to support + * this case. + */ + foreach(cell, action->grantees) + { + PrivGrantee *grantee = (PrivGrantee *) lfirst(cell); + + if (grantee->rolname == NULL) + iacls.grantees = lappend_oid(iacls.grantees, ACL_ID_PUBLIC); + else + iacls.grantees = + lappend_oid(iacls.grantees, + get_roleid_checked(grantee->rolname)); + } + + /* + * Convert stmt->privileges, a list of AccessPriv nodes, into an AclMode + * bitmask. Note: objtype can't be ACL_OBJECT_COLUMN. + */ + switch (action->objtype) + { + case ACL_OBJECT_RELATION: + all_privileges = ACL_ALL_RIGHTS_RELATION; + errormsg = gettext_noop("invalid privilege type %s for relation"); + break; + case ACL_OBJECT_SEQUENCE: + all_privileges = ACL_ALL_RIGHTS_SEQUENCE; + errormsg = gettext_noop("invalid privilege type %s for sequence"); + break; + case ACL_OBJECT_FUNCTION: + all_privileges = ACL_ALL_RIGHTS_FUNCTION; + errormsg = gettext_noop("invalid privilege type %s for function"); + break; + default: + /* keep compiler quiet */ + all_privileges = ACL_NO_RIGHTS; + errormsg = NULL; + elog(ERROR, "unrecognized GrantStmt.objtype: %d", + (int) action->objtype); + } + + /* + * Convert stmt->action->privileges, a list of privilege strings, + * into an AclMode bitmask. + */ + if (action->privileges == NIL) + { + iacls.all_privs = true; + iacls.privileges = ACL_NO_RIGHTS; + } + else + { + iacls.all_privs = false; + iacls.privileges = ACL_NO_RIGHTS; + + foreach(cell, action->privileges) + { + AccessPriv *privnode = (AccessPriv *) lfirst(cell); + AclMode priv; + + /* + * If it's a column-level specification, we just set it aside in + * col_privs for the moment; but insist it's for a relation. + */ + if (privnode->cols) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_GRANT_OPERATION), + errmsg("column privileges are not valid for default privileges"))); + } + + if (privnode->priv_name == NULL) /* parser mistake? */ + elog(ERROR, "AccessPriv node must specify privilege"); + priv = string_to_privilege(privnode->priv_name); + + if (priv & ~((AclMode) all_privileges)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_GRANT_OPERATION), + errmsg(errormsg, privilege_to_string(priv)))); + + iacls.privileges |= priv; + } + } + + /* + * Ensure roles and namespaces don't get deleted while + * we are adding default privileges for them. + */ + rolerel = heap_open(AuthIdRelationId, ShareLock); + nsprel = heap_open(NamespaceRelationId, ShareLock); + + /* + * Convert role and namespace names to oids + * and check permissions while we are at it. + */ + foreach(rolecell, rolenames) + { + char *rolename = strVal(lfirst(rolecell)); + Oid roleid = get_roleid_checked(rolename); + + + if (!have_createrole_privilege() && + !is_admin_of_role(GetUserId(), roleid)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin option on role \"%s\"", + rolename))); + + roleids = lappend_oid(roleids, roleid); + } + + foreach(nspcell, nspnames) + { + char *nspname = strVal(lfirst(nspcell)); + Oid nspid = LookupExplicitNamespace(nspname); + + if (nspid == PG_CATALOG_NAMESPACE || nspid == PG_TOAST_NAMESPACE) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("can't set default permissions for system catalog"))); + + nspids = lappend_oid(nspids, nspid); + } + + /* + * We need to set default acls for current role if no roles were specified + * and database wide if no schema was specified. + */ + if (roleids == NIL) + roleids = lappend_oid(roleids, GetUserId()); + if (nspids == NIL) + nspids = lappend_oid(nspids, InvalidOid); + + /* + * Go through roles and namespaces and update the catalog for them. + */ + foreach(rolecell, roleids) + { + iacls.roleid = lfirst_oid(rolecell); + foreach(nspcell, nspids) + { + iacls.nspid = lfirst_oid(nspcell); + SetDefaultACL(&iacls); + } + } + + heap_close(nsprel, ShareLock); + heap_close(rolerel, ShareLock); + } + + + /* + * Create or update the DefaultACL entry + * + * Caller is responsible for checking privileges + */ + void + SetDefaultACL(InternalDefaultACL *iacls) + { + Form_pg_default_acl pg_default_acl_tuple; + char objtype; + Datum aclDatum; + bool isNew; + bool isNull; + AclMode this_privileges = iacls->privileges; + ScanKeyData key[3]; + HeapScanDesc scan; + HeapTuple tuple; + Relation rel; + Acl *old_acl; + Acl *new_acl; + Oid grantorId; + AclMode avail_goptions; + HeapTuple newtuple; + Datum values[Natts_pg_default_acl]; + bool nulls[Natts_pg_default_acl]; + bool replaces[Natts_pg_default_acl]; + int noldmembers; + int nnewmembers; + Oid *oldmembers; + Oid *newmembers; + + /* + * Convert ACL object type to DefaultACL object type + * and handle all_privs option + */ + switch (iacls->objtype) + { + case ACL_OBJECT_RELATION: + objtype = DEFACLOBJ_RELATION; + if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS) + this_privileges = ACL_ALL_RIGHTS_RELATION; + break; + + case ACL_OBJECT_FUNCTION: + objtype = DEFACLOBJ_FUNCTION; + if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS) + this_privileges = ACL_ALL_RIGHTS_FUNCTION; + break; + + case ACL_OBJECT_SEQUENCE: + objtype = DEFACLOBJ_SEQUENCE; + if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS) + this_privileges = ACL_ALL_RIGHTS_SEQUENCE; + break; + + default: + elog(ERROR, "unrecognized objtype: %d", + (int) iacls->objtype); + objtype = 0; /* keep compiler quiet */ + break; + } + + /* Search for existing row for our object type in catalog */ + ScanKeyInit(&key[0], + Anum_pg_default_acl_defaclrole, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(iacls->roleid)); + + ScanKeyInit(&key[1], + Anum_pg_default_acl_defaclnamespace, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(iacls->nspid)); + + ScanKeyInit(&key[2], + Anum_pg_default_acl_defaclgrantobjtype, + BTEqualStrategyNumber, F_CHAREQ, + CharGetDatum(objtype)); + + rel = heap_open(DefaultAclRelationId, RowExclusiveLock); + + scan = heap_beginscan(rel, SnapshotNow, 3, key); + + tuple = heap_getnext(scan, ForwardScanDirection); + if (HeapTupleIsValid(tuple)) + { + pg_default_acl_tuple = (Form_pg_default_acl) GETSTRUCT(tuple); + aclDatum = heap_getattr(tuple, Anum_pg_default_acl_defacldefacllist, + RelationGetDescr(rel), &isNull); + isNew = false; + } + else + { + isNull = true; + isNew = true; + } + + if (isNull) + old_acl = acldefault(iacls->objtype, iacls->roleid); + else + old_acl = DatumGetAclPCopy(aclDatum); + + noldmembers = aclmembers(old_acl, &oldmembers); + + /* + * Determine ID to do the grant as, we don't use avail_goptions since + * user has to have all available options to be able to set + * default privileges + */ + select_best_grantor(GetUserId(), this_privileges, + old_acl, iacls->roleid, + &grantorId, &avail_goptions); + + /* + * Generate new ACL. + * + * We need the members of both old and new ACLs so we can correct + * the shared dependency information. + */ + new_acl = merge_acl_with_grant(old_acl, + iacls->is_grant, + iacls->grant_option, + iacls->behavior, + iacls->grantees, + this_privileges, + grantorId, + iacls->roleid); + + /* finished building new ACL value, now insert it */ + MemSet(values, 0, sizeof(values)); + MemSet(nulls, false, sizeof(nulls)); + MemSet(replaces, false, sizeof(replaces)); + + if (isNew) + { + values[Anum_pg_default_acl_defaclrole - 1] = ObjectIdGetDatum(iacls->roleid); + values[Anum_pg_default_acl_defaclnamespace - 1] = ObjectIdGetDatum(iacls->nspid); + values[Anum_pg_default_acl_defaclgrantobjtype - 1] = CharGetDatum(objtype); + values[Anum_pg_default_acl_defacldefacllist - 1] = PointerGetDatum(new_acl); + + newtuple = heap_form_tuple(rel->rd_att, values, nulls); + simple_heap_insert(rel, newtuple); + + /* dependency on role */ + recordDependencyOnOwner(DefaultAclRelationId, HeapTupleGetOid(newtuple), iacls->roleid); + + /* dependency on namespace */ + if (OidIsValid(iacls->nspid)) + { + ObjectAddress myself, + referenced; + + myself.classId = DefaultAclRelationId; + myself.objectId = HeapTupleGetOid(newtuple); + myself.objectSubId = 0; + + referenced.classId = NamespaceRelationId; + referenced.objectId = iacls->nspid; + referenced.objectSubId = 0; + + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + } + } + else + { + values[Anum_pg_default_acl_defacldefacllist - 1] = PointerGetDatum(new_acl); + replaces[Anum_pg_default_acl_defacldefacllist - 1] = true; + + newtuple = heap_modify_tuple(tuple, RelationGetDescr(rel), + values, nulls, replaces); + simple_heap_update(rel, &newtuple->t_self, newtuple); + } + + /* keep the catalog indexes up to date */ + CatalogUpdateIndexes(rel, newtuple); + + /* + * Update the shared dependency ACL info + */ + nnewmembers = aclmembers(new_acl, &newmembers); + + updateAclDependencies(DefaultAclRelationId, HeapTupleGetOid(newtuple), 0, + iacls->roleid, iacls->is_grant, + noldmembers, oldmembers, + nnewmembers, newmembers); + + pfree(new_acl); + + heap_endscan(scan); + heap_close(rel, RowExclusiveLock); + } + + + /* + * Remove DefaultACL tuple. + * + * Used for dependency removal. + */ + void + RemoveDefaultACLById(Oid nsdaOid) + { + Relation rel; + ScanKeyData key[1]; + HeapScanDesc scan; + HeapTuple tuple; + + rel = heap_open(DefaultAclRelationId, RowExclusiveLock); + + ScanKeyInit(&key[0], + ObjectIdAttributeNumber, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(nsdaOid)); + + scan = heap_beginscan(rel, SnapshotNow, 1, key); + + tuple = heap_getnext(scan, ForwardScanDirection); + + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "could not find tuple for default acls %u", + nsdaOid); + + simple_heap_delete(rel, &tuple->t_self); + + heap_endscan(scan); + heap_close(rel, RowExclusiveLock); + } + + + /* * expand_col_privileges * * OR the specified privilege(s) into per-column array entries for each *************** pg_conversion_ownercheck(Oid conv_oid, O *** 3532,3534 **** --- 3977,4078 ---- return has_privs_of_role(roleid, ownerId); } + + /* + * Search for default permissions for given role, namespace and objecttype + */ + Acl * + get_default_acl(Oid roleId, Oid nsp_oid, char objtype) + { + ScanKeyData key[3]; + HeapScanDesc scan; + HeapTuple tuple; + Relation rel; + Datum aclDatum; + bool isNull; + Acl *result = NULL; + + ScanKeyInit(&key[0], + Anum_pg_default_acl_defaclrole, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(roleId)); + + ScanKeyInit(&key[1], + Anum_pg_default_acl_defaclnamespace, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(nsp_oid)); + + ScanKeyInit(&key[2], + Anum_pg_default_acl_defaclgrantobjtype, + BTEqualStrategyNumber, F_CHAREQ, + CharGetDatum(objtype)); + + rel = heap_open(DefaultAclRelationId, AccessShareLock); + + scan = heap_beginscan(rel, SnapshotNow, 3, key); + + tuple = heap_getnext(scan, ForwardScanDirection); + + if (HeapTupleIsValid(tuple)) + { + aclDatum = heap_getattr(tuple, Anum_pg_default_acl_defacldefacllist, + RelationGetDescr(rel), &isNull); + + if (!isNull) + result = DatumGetAclPCopy(aclDatum); + } + + heap_endscan(scan); + heap_close(rel, AccessShareLock); + + return result; + } + + /* + * Get default permissions for newly created object inside given schema + */ + Acl * + pg_namespace_object_default_acl(Oid nsp_oid, char objtype, Oid ownerId) + { + Acl *result; + Acl *tmpres1; + Acl *tmpres2; + GrantObjectType aclobjtype; + + /* + * Use NULL value for object acls if it is created in system schema + * or if it does not have default permissions support. + */ + if (nsp_oid == PG_CATALOG_NAMESPACE || nsp_oid == PG_TOAST_NAMESPACE) + return NULL; + + switch (objtype) + { + case DEFACLOBJ_RELATION: + aclobjtype = ACL_OBJECT_RELATION; + break; + + case DEFACLOBJ_FUNCTION: + aclobjtype = ACL_OBJECT_FUNCTION; + break; + + case DEFACLOBJ_SEQUENCE: + aclobjtype = ACL_OBJECT_SEQUENCE; + break; + + default: + return NULL; + } + + tmpres1 = get_default_acl(ownerId, nsp_oid, objtype); + tmpres2 = get_default_acl(ownerId, InvalidOid, objtype); + + result = aclmerge(tmpres1, tmpres2, ownerId); + + if (tmpres1 != NULL) + pfree(tmpres1); + if (tmpres2 != NULL) + pfree(tmpres2); + + return result; + } diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index cb9a9c2..e6486ab 100644 *** a/src/backend/catalog/dependency.c --- b/src/backend/catalog/dependency.c *************** *** 32,37 **** --- 32,38 ---- #include "catalog/pg_conversion.h" #include "catalog/pg_conversion_fn.h" #include "catalog/pg_database.h" + #include "catalog/pg_default_acl.h" #include "catalog/pg_depend.h" #include "catalog/pg_foreign_data_wrapper.h" #include "catalog/pg_foreign_server.h" *************** *** 70,75 **** --- 71,77 ---- #include "utils/lsyscache.h" #include "utils/syscache.h" #include "utils/tqual.h" + #include "utils/acl.h" /* *************** static const Oid object_classes[MAX_OCLA *** 146,152 **** TableSpaceRelationId, /* OCLASS_TBLSPACE */ ForeignDataWrapperRelationId, /* OCLASS_FDW */ ForeignServerRelationId, /* OCLASS_FOREIGN_SERVER */ ! UserMappingRelationId /* OCLASS_USER_MAPPING */ }; --- 148,155 ---- TableSpaceRelationId, /* OCLASS_TBLSPACE */ ForeignDataWrapperRelationId, /* OCLASS_FDW */ ForeignServerRelationId, /* OCLASS_FOREIGN_SERVER */ ! UserMappingRelationId, /* OCLASS_USER_MAPPING */ ! DefaultAclRelationId /* OCLASS_DEFACL */ }; *************** static bool object_address_present_add_f *** 178,183 **** --- 181,187 ---- ObjectAddresses *addrs); static void getRelationDescription(StringInfo buffer, Oid relid); static void getOpFamilyDescription(StringInfo buffer, Oid opfid); + static void getDefaultACLDescription(StringInfo buffer, char objtype); /* *************** doDeletion(const ObjectAddress *object) *** 1136,1141 **** --- 1140,1149 ---- RemoveUserMappingById(object->objectId); break; + case OCLASS_DEFACL: + RemoveDefaultACLById(object->objectId); + break; + default: elog(ERROR, "unrecognized object class: %u", object->classId); *************** getObjectClass(const ObjectAddress *obje *** 2055,2060 **** --- 2063,2072 ---- case UserMappingRelationId: Assert(object->objectSubId == 0); return OCLASS_USER_MAPPING; + + case DefaultAclRelationId: + Assert(object->objectSubId == 0); + return OCLASS_DEFACL; } /* shouldn't get here */ *************** getObjectDescription(const ObjectAddress *** 2597,2602 **** --- 2609,2657 ---- break; } + case OCLASS_DEFACL: + { + Relation rel; + ScanKeyData key[1]; + HeapScanDesc scan; + HeapTuple tup; + Form_pg_default_acl defacl; + + rel = heap_open(DefaultAclRelationId, AccessShareLock); + + ScanKeyInit(&key[0], + ObjectIdAttributeNumber, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(object->objectId)); + + scan = heap_beginscan(rel, SnapshotNow, 1, key); + + tup = heap_getnext(scan, ForwardScanDirection); + + if (!HeapTupleIsValid(tup)) + elog(ERROR, "could not find tuple for default acls %u", + object->objectId); + + defacl = (Form_pg_default_acl) GETSTRUCT(tup); + + appendStringInfo(&buffer, + _("default acls for role %s on new "), + GetUserNameFromId(defacl->defaclrole)); + + getDefaultACLDescription(&buffer, defacl->defaclobjtype); + + if (OidIsValid(defacl->defaclnamespace)) + { + appendStringInfo(&buffer, + _(" in namespace %s"), + get_namespace_name(defacl->defaclnamespace)); + } + + heap_endscan(scan); + heap_close(rel, AccessShareLock); + break; + } + default: appendStringInfo(&buffer, "unrecognized object %u %u %d", object->classId, *************** getOpFamilyDescription(StringInfo buffer *** 2715,2717 **** --- 2770,2797 ---- ReleaseSysCache(amTup); ReleaseSysCache(opfTup); } + + + /* + * subroutine for getObjectDescription: describe DefaultACL object type + */ + static void + getDefaultACLDescription(StringInfo buffer, char objtype) + { + switch (objtype) + { + case DEFACLOBJ_RELATION: + appendStringInfo(buffer, _("relation")); + break; + case DEFACLOBJ_FUNCTION: + appendStringInfo(buffer, _("function")); + break; + case DEFACLOBJ_SEQUENCE: + appendStringInfo(buffer, _("sequence")); + break; + default: + /* shouldn't get here */ + appendStringInfo(buffer, _("unknown")); + break; + } + } diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index e404dbe..0f85b83 100644 *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** *** 41,46 **** --- 41,47 ---- #include "catalog/indexing.h" #include "catalog/pg_attrdef.h" #include "catalog/pg_constraint.h" + #include "catalog/pg_default_acl.h" #include "catalog/pg_inherits.h" #include "catalog/pg_namespace.h" #include "catalog/pg_statistic.h" *************** *** 67,72 **** --- 68,74 ---- #include "utils/snapmgr.h" #include "utils/syscache.h" #include "utils/tqual.h" + #include "utils/acl.h" static void AddNewRelationTuple(Relation pg_class_desc, *************** AddNewAttributeTuples(Oid new_rel_oid, *** 636,643 **** * Caller has already opened and locked pg_class. * Tuple data is taken from new_rel_desc->rd_rel, except for the * variable-width fields which are not present in a cached reldesc. ! * We always initialize relacl to NULL (i.e., default permissions), ! * and reloptions is set to the passed-in text array (if any). * -------------------------------- */ void --- 638,645 ---- * Caller has already opened and locked pg_class. * Tuple data is taken from new_rel_desc->rd_rel, except for the * variable-width fields which are not present in a cached reldesc. ! * Initial permissions are obtained from pg_default_acl ! * (if applicable) and reloptions is set to the passed-in text array (if any). * -------------------------------- */ void *************** InsertPgClassTuple(Relation pg_class_des *** 649,654 **** --- 651,659 ---- Form_pg_class rd_rel = new_rel_desc->rd_rel; Datum values[Natts_pg_class]; bool nulls[Natts_pg_class]; + bool isNull; + Acl *relacl; + char aclobjtype; HeapTuple tup; /* This is a tad tedious, but way cleaner than what we used to do... */ *************** InsertPgClassTuple(Relation pg_class_des *** 678,685 **** values[Anum_pg_class_relhastriggers - 1] = BoolGetDatum(rd_rel->relhastriggers); values[Anum_pg_class_relhassubclass - 1] = BoolGetDatum(rd_rel->relhassubclass); values[Anum_pg_class_relfrozenxid - 1] = TransactionIdGetDatum(rd_rel->relfrozenxid); ! /* start out with empty permissions */ ! nulls[Anum_pg_class_relacl - 1] = true; if (reloptions != (Datum) 0) values[Anum_pg_class_reloptions - 1] = reloptions; else --- 683,716 ---- values[Anum_pg_class_relhastriggers - 1] = BoolGetDatum(rd_rel->relhastriggers); values[Anum_pg_class_relhassubclass - 1] = BoolGetDatum(rd_rel->relhassubclass); values[Anum_pg_class_relfrozenxid - 1] = TransactionIdGetDatum(rd_rel->relfrozenxid); ! ! /* get default permissions */ ! switch (rd_rel->relkind) ! { ! case RELKIND_RELATION: ! case RELKIND_VIEW: ! isNull = false; ! aclobjtype = DEFACLOBJ_RELATION; ! break; ! case RELKIND_SEQUENCE: ! isNull = false; ! aclobjtype = DEFACLOBJ_SEQUENCE; ! break; ! default: ! isNull = true; ! break; ! } ! ! if (!isNull) ! relacl = pg_namespace_object_default_acl(rd_rel->relnamespace, aclobjtype, rd_rel->relowner); ! else ! relacl = NULL; ! ! if (relacl != NULL) ! values[Anum_pg_class_relacl - 1] = PointerGetDatum(relacl); ! else ! nulls[Anum_pg_class_relacl - 1] = true; ! if (reloptions != (Datum) 0) values[Anum_pg_class_reloptions - 1] = reloptions; else *************** InsertPgClassTuple(Relation pg_class_des *** 698,703 **** --- 729,749 ---- CatalogUpdateIndexes(pg_class_desc, tup); + if (relacl != NULL) + { + int nnewmembers; + Oid *newmembers; + + /* Update the shared dependency ACL info */ + nnewmembers = aclmembers(relacl, &newmembers); + updateAclDependencies(RelationRelationId, new_rel_oid, 0, + rd_rel->relowner, true, + 0, NULL, + nnewmembers, newmembers); + + pfree(relacl); + } + heap_freetuple(tup); } diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 342381e..4f3dc53 100644 *** a/src/backend/catalog/pg_proc.c --- b/src/backend/catalog/pg_proc.c *************** *** 18,23 **** --- 18,24 ---- #include "access/xact.h" #include "catalog/dependency.h" #include "catalog/indexing.h" + #include "catalog/pg_default_acl.h" #include "catalog/pg_language.h" #include "catalog/pg_namespace.h" #include "catalog/pg_proc.h" *************** ProcedureCreate(const char *procedureNam *** 95,100 **** --- 96,102 ---- bool nulls[Natts_pg_proc]; Datum values[Natts_pg_proc]; bool replaces[Natts_pg_proc]; + Acl *proacl; Oid relid; NameData procname; TupleDesc tupDesc; *************** ProcedureCreate(const char *procedureNam *** 330,337 **** values[Anum_pg_proc_proconfig - 1] = proconfig; else nulls[Anum_pg_proc_proconfig - 1] = true; ! /* start out with empty permissions */ ! nulls[Anum_pg_proc_proacl - 1] = true; rel = heap_open(ProcedureRelationId, RowExclusiveLock); tupDesc = RelationGetDescr(rel); --- 332,344 ---- values[Anum_pg_proc_proconfig - 1] = proconfig; else nulls[Anum_pg_proc_proconfig - 1] = true; ! ! /* get default permissions */ ! proacl = pg_namespace_object_default_acl(procNamespace, DEFACLOBJ_FUNCTION, GetUserId()); ! if (proacl != NULL) ! values[Anum_pg_proc_proacl - 1] = PointerGetDatum(proacl); ! else ! nulls[Anum_pg_proc_proacl - 1] = true; rel = heap_open(ProcedureRelationId, RowExclusiveLock); tupDesc = RelationGetDescr(rel); *************** ProcedureCreate(const char *procedureNam *** 539,544 **** --- 546,566 ---- /* dependency on owner */ recordDependencyOnOwner(ProcedureRelationId, retval, GetUserId()); + if (proacl != NULL) + { + int nnewmembers; + Oid *newmembers; + + /* Update the shared dependency ACL info */ + nnewmembers = aclmembers(proacl, &newmembers); + updateAclDependencies(ProcedureRelationId, retval, 0, + GetUserId(), true, + 0, NULL, + nnewmembers, newmembers); + + pfree(proacl); + } + heap_freetuple(tup); heap_close(rel, RowExclusiveLock); diff --git a/src/backend/catalog/pg_shdepend.c b/src/backend/catalog/pg_shdepend.c index cd04053..62684a5 100644 *** a/src/backend/catalog/pg_shdepend.c --- b/src/backend/catalog/pg_shdepend.c *************** *** 17,28 **** --- 17,30 ---- #include "access/genam.h" #include "access/heapam.h" #include "access/xact.h" + #include "access/sysattr.h" #include "catalog/catalog.h" #include "catalog/dependency.h" #include "catalog/indexing.h" #include "catalog/pg_authid.h" #include "catalog/pg_conversion.h" #include "catalog/pg_database.h" + #include "catalog/pg_default_acl.h" #include "catalog/pg_language.h" #include "catalog/pg_namespace.h" #include "catalog/pg_operator.h" *************** *** 40,45 **** --- 42,48 ---- #include "miscadmin.h" #include "utils/acl.h" #include "utils/fmgroids.h" + #include "utils/lsyscache.h" #include "utils/syscache.h" #include "utils/tqual.h" *************** static void storeObjectDescription(Strin *** 74,79 **** --- 77,84 ---- int count); static bool isSharedObjectPinned(Oid classId, Oid objectId, Relation sdepRel); + static void shdepDropObjACLs(Oid classid, Oid objid, Oid roleid); + static void shdepDropDefaultACL(Oid objid, Oid roleid); /* * recordSharedDependencyOn *************** shdepDropOwned(List *roleids, DropBehavi *** 1180,1186 **** while ((tuple = systable_getnext(scan)) != NULL) { Form_pg_shdepend sdepForm = (Form_pg_shdepend) GETSTRUCT(tuple); - InternalGrant istmt; ObjectAddress obj; /* We only operate on objects in the current database */ --- 1185,1190 ---- *************** shdepDropOwned(List *roleids, DropBehavi *** 1195,1236 **** elog(ERROR, "unexpected dependency type"); break; case SHARED_DEPENDENCY_ACL: ! switch (sdepForm->classid) ! { ! case RelationRelationId: ! /* it's OK to use RELATION for a sequence */ ! istmt.objtype = ACL_OBJECT_RELATION; ! break; ! case DatabaseRelationId: ! istmt.objtype = ACL_OBJECT_DATABASE; ! break; ! case ProcedureRelationId: ! istmt.objtype = ACL_OBJECT_FUNCTION; ! break; ! case LanguageRelationId: ! istmt.objtype = ACL_OBJECT_LANGUAGE; ! break; ! case NamespaceRelationId: ! istmt.objtype = ACL_OBJECT_NAMESPACE; ! break; ! case TableSpaceRelationId: ! istmt.objtype = ACL_OBJECT_TABLESPACE; ! break; ! default: ! elog(ERROR, "unexpected object type %d", ! sdepForm->classid); ! break; ! } ! istmt.is_grant = false; ! istmt.objects = list_make1_oid(sdepForm->objid); ! istmt.all_privs = true; ! istmt.privileges = ACL_NO_RIGHTS; ! istmt.col_privs = NIL; ! istmt.grantees = list_make1_oid(roleid); ! istmt.grant_option = false; ! istmt.behavior = DROP_CASCADE; ! ! ExecGrantStmt_oids(&istmt); break; case SHARED_DEPENDENCY_OWNER: /* Save it for deletion below */ --- 1199,1208 ---- elog(ERROR, "unexpected dependency type"); break; case SHARED_DEPENDENCY_ACL: ! if (sdepForm->classid == DefaultAclRelationId) ! shdepDropDefaultACL(sdepForm->objid, roleid); ! else ! shdepDropObjACLs(sdepForm->classid, sdepForm->objid, roleid); break; case SHARED_DEPENDENCY_OWNER: /* Save it for deletion below */ *************** shdepDropOwned(List *roleids, DropBehavi *** 1253,1258 **** --- 1225,1353 ---- free_object_addresses(deleteobjs); } + /* + * shdepDropObjACLs + * + * Does the actual ACLs (Grant) removal for shdepDropOwned + */ + void + shdepDropObjACLs(Oid classid, Oid objid, Oid roleid) + { + InternalGrant istmt; + + switch (classid) + { + case RelationRelationId: + /* it's OK to use RELATION for a sequence */ + istmt.objtype = ACL_OBJECT_RELATION; + break; + case DatabaseRelationId: + istmt.objtype = ACL_OBJECT_DATABASE; + break; + case ProcedureRelationId: + istmt.objtype = ACL_OBJECT_FUNCTION; + break; + case LanguageRelationId: + istmt.objtype = ACL_OBJECT_LANGUAGE; + break; + case NamespaceRelationId: + istmt.objtype = ACL_OBJECT_NAMESPACE; + break; + case TableSpaceRelationId: + istmt.objtype = ACL_OBJECT_TABLESPACE; + break; + default: + elog(ERROR, "unexpected object type %d", classid); + break; + } + istmt.is_grant = false; + istmt.objects = list_make1_oid(objid); + istmt.all_privs = true; + istmt.privileges = ACL_NO_RIGHTS; + istmt.col_privs = NIL; + istmt.grantees = list_make1_oid(roleid); + istmt.grant_option = false; + istmt.behavior = DROP_CASCADE; + + ExecGrantStmt_oids(&istmt); + } + + + /* + * shdepDropDefaultACL + * + * Drops roles ACLs from DefaultACL tuple, used by shdepDropOwned + */ + void + shdepDropDefaultACL(Oid objid, Oid roleid) + { + InternalDefaultACL iacls; + Form_pg_default_acl pg_default_acl_tuple; + Relation rel; + ScanKeyData key[1]; + HeapScanDesc scan; + HeapTuple tuple; + AclResult aclresult; + + /* first fetch info needed by SetDefaultACL */ + rel = heap_open(DefaultAclRelationId, RowExclusiveLock); + + ScanKeyInit(&key[0], + ObjectIdAttributeNumber, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(objid)); + + scan = heap_beginscan(rel, SnapshotNow, 1, key); + + tuple = heap_getnext(scan, ForwardScanDirection); + + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "could not find tuple for default acls %u", objid); + + pg_default_acl_tuple = (Form_pg_default_acl) GETSTRUCT(tuple); + + iacls.roleid = pg_default_acl_tuple->defaclrole; + iacls.nspid = pg_default_acl_tuple->defaclnamespace; + + switch (pg_default_acl_tuple->defaclobjtype) + { + case DEFACLOBJ_RELATION: + iacls.objtype = ACL_OBJECT_RELATION; + break; + case DEFACLOBJ_FUNCTION: + iacls.objtype = ACL_OBJECT_FUNCTION; + break; + case ACL_OBJECT_SEQUENCE: + iacls.objtype = ACL_OBJECT_SEQUENCE; + break; + default: + /* Shouldn't get here */ + elog(ERROR, "unexpected object type %d", + pg_default_acl_tuple->defaclobjtype); + break; + } + + heap_endscan(scan); + heap_close(rel, RowExclusiveLock); + + /* Check privileges */ + aclresult = pg_namespace_aclcheck(iacls.nspid, GetUserId(), ACL_USAGE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_NAMESPACE, + get_namespace_name(iacls.nspid)); + + iacls.is_grant = false; + iacls.all_privs = true; + iacls.privileges = ACL_NO_RIGHTS; + iacls.grantees = list_make1_oid(roleid); + iacls.grant_option = false; + iacls.behavior = DROP_CASCADE; + + /* Do it */ + SetDefaultACL(&iacls); + } + + /* * shdepReassignOwned * *************** shdepReassignOwned(List *roleids, Oid ne *** 1365,1370 **** --- 1460,1486 ---- AlterLanguageOwner_oid(sdepForm->objid, newrole); break; + case DefaultAclRelationId: + { + ObjectAddress obj; + + obj.classId = AuthIdRelationId; + obj.objectId = roleid; + obj.objectSubId = 0; + + ereport(WARNING, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot reassign " + "default privileges for %s", + getObjectDescription(&obj)), + errhint("If you plan to DROP ROLE %s, " + "use DROP OWNED BY to remove " + "default privileges after running " + "REASSIGN OWNED.", + getObjectDescription(&obj)))); + break; + } + default: elog(ERROR, "unexpected classid %d", sdepForm->classid); break; diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index fa260c4..658a729 100644 *** a/src/backend/commands/user.c --- b/src/backend/commands/user.c *************** static void DelRoleMems(const char *role *** 44,72 **** bool admin_opt); - /* Check if current user has createrole privileges */ - static bool - have_createrole_privilege(void) - { - bool result = false; - HeapTuple utup; - - /* Superusers can always do everything */ - if (superuser()) - return true; - - utup = SearchSysCache(AUTHOID, - ObjectIdGetDatum(GetUserId()), - 0, 0, 0); - if (HeapTupleIsValid(utup)) - { - result = ((Form_pg_authid) GETSTRUCT(utup))->rolcreaterole; - ReleaseSysCache(utup); - } - return result; - } - - /* * CREATE ROLE */ --- 44,49 ---- diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 6420e4a..f8f2aa7 100644 *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** _copyCreateSchemaStmt(CreateSchemaStmt * *** 3228,3233 **** --- 3228,3244 ---- return newnode; } + static AlterDefaultPrivilegesStmt * + _copyAlterDefaultPrivilegesStmt(AlterDefaultPrivilegesStmt *from) + { + AlterDefaultPrivilegesStmt *newnode = makeNode(AlterDefaultPrivilegesStmt); + + COPY_NODE_FIELD(options); + COPY_NODE_FIELD(action); + + return newnode; + } + static CreateConversionStmt * _copyCreateConversionStmt(CreateConversionStmt *from) { *************** copyObject(void *from) *** 3973,3978 **** --- 3984,3992 ---- case T_CreateSchemaStmt: retval = _copyCreateSchemaStmt(from); break; + case T_AlterDefaultPrivilegesStmt: + retval = _copyAlterDefaultPrivilegesStmt(from); + break; case T_CreateConversionStmt: retval = _copyCreateConversionStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index f125719..2b5c7eb 100644 *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** _equalCreateSchemaStmt(CreateSchemaStmt *** 1772,1777 **** --- 1772,1786 ---- } static bool + _equalAlterDefaultPrivilegesStmt(AlterDefaultPrivilegesStmt *a, AlterDefaultPrivilegesStmt *b) + { + COMPARE_NODE_FIELD(options); + COMPARE_NODE_FIELD(action); + + return true; + } + + static bool _equalCreateConversionStmt(CreateConversionStmt *a, CreateConversionStmt *b) { COMPARE_NODE_FIELD(conversion_name); *************** equal(void *a, void *b) *** 2750,2755 **** --- 2759,2767 ---- case T_CreateSchemaStmt: retval = _equalCreateSchemaStmt(a, b); break; + case T_AlterDefaultPrivilegesStmt: + retval = _equalAlterDefaultPrivilegesStmt(a, b); + break; case T_CreateConversionStmt: retval = _equalCreateConversionStmt(a, b); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 732b1d7..6534eb3 100644 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** static TypeName *TableFuncTypeName(List *** 179,184 **** --- 179,185 ---- ResTarget *target; struct PrivTarget *privtarget; AccessPriv *accesspriv; + GrantObjectType grantobjecttype; InsertStmt *istmt; VariableSetStmt *vsetstmt; *************** static TypeName *TableFuncTypeName(List *** 196,202 **** CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateAssertStmt CreateTrigStmt CreateUserStmt CreateUserMappingStmt CreateRoleStmt ! CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt DropGroupStmt DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt --- 197,203 ---- CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateAssertStmt CreateTrigStmt CreateUserStmt CreateUserMappingStmt CreateRoleStmt ! CreatedbStmt DeclareCursorStmt AlterDefaultPrivilegesStmt AlterDefaultPrivilegesAction DefineStmt DeleteStmt DiscardStmt DoStmt DropGroupStmt DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt *************** static TypeName *TableFuncTypeName(List *** 267,272 **** --- 268,274 ---- %type privilege %type privileges privilege_list %type privilege_target + %type defacl_privilege_target %type function_with_argtypes %type function_with_argtypes_list *************** static TypeName *TableFuncTypeName(List *** 427,432 **** --- 429,436 ---- %type opt_existing_window_name %type opt_frame_clause frame_extent frame_bound + %type OptDefACLOption + %type OptDefACLOptionList /* * Non-keyword token types. These are hard-wired into the "flex" lexer. *************** stmt : *** 672,677 **** --- 676,682 ---- | CreatedbStmt | DeallocateStmt | DeclareCursorStmt + | AlterDefaultPrivilegesStmt | DefineStmt | DeleteStmt | DiscardStmt *************** schema_stmt: *** 1114,1119 **** --- 1119,1208 ---- ; + + /***************************************************************************** + * + * DEFAULT PRIVILEGES for newly created objects in schema + * + *****************************************************************************/ + + AlterDefaultPrivilegesStmt: + ALTER DEFAULT PRIVILEGES OptDefACLOptionList AlterDefaultPrivilegesAction + { + AlterDefaultPrivilegesStmt *n = makeNode(AlterDefaultPrivilegesStmt); + n->options = $4; + n->action = $5; + $$ = (Node*)n; + } + ; + + OptDefACLOptionList: + OptDefACLOptionList OptDefACLOption { $$ = lappend($1, $2); } + | /* EMPTY */ { $$ = NIL; } + ; + + OptDefACLOption: + IN_P SCHEMA name_list + { + $$ = makeDefElem("schemas", (Node *)$3); + } + | FOR ROLE name_list + { + $$ = makeDefElem("roles", (Node *)$3); + } + | FOR USER name_list + { + $$ = makeDefElem("roles", (Node *)$3); + } + ; + + AlterDefaultPrivilegesAction: + GRANT privileges ON defacl_privilege_target TO grantee_list + opt_grant_grant_option + { + GrantStmt *n = makeNode(GrantStmt); + n->is_grant = true; + n->privileges = $2; + n->objtype = $4; + n->objects = NIL; + n->grantees = $6; + n->grant_option = $7; + $$ = (Node*)n; + } + | REVOKE privileges ON defacl_privilege_target + FROM grantee_list opt_drop_behavior + { + GrantStmt *n = makeNode(GrantStmt); + n->is_grant = false; + n->grant_option = false; + n->privileges = $2; + n->objtype = $4; + n->objects = NIL; + n->grantees = $6; + n->behavior = $7; + $$ = (Node *)n; + } + | REVOKE GRANT OPTION FOR privileges ON defacl_privilege_target + FROM grantee_list opt_drop_behavior + { + GrantStmt *n = makeNode(GrantStmt); + n->is_grant = false; + n->grant_option = true; + n->privileges = $5; + n->objtype = $7; + n->objects = NIL; + n->grantees = $9; + n->behavior = $10; + $$ = (Node *)n; + } + ; + + defacl_privilege_target: + TABLE { $$ = ACL_OBJECT_RELATION; } + | FUNCTION { $$ = ACL_OBJECT_FUNCTION; } + | SEQUENCE { $$ = ACL_OBJECT_SEQUENCE; } + ; + /***************************************************************************** * * Set PG internal variable diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index ddf87b4..e4ab20a 100644 *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** check_xact_readonly(Node *parsetree) *** 180,185 **** --- 180,186 ---- case T_AlterOpFamilyStmt: case T_RuleStmt: case T_CreateSchemaStmt: + case T_AlterDefaultPrivilegesStmt: case T_CreateSeqStmt: case T_CreateStmt: case T_CreateTableSpaceStmt: *************** ProcessUtility(Node *parsetree, *** 406,411 **** --- 407,416 ---- queryString); break; + case T_AlterDefaultPrivilegesStmt: + ExecAlterDefaultPrivilegesStmt((AlterDefaultPrivilegesStmt *) parsetree); + break; + case T_CreateStmt: { List *stmts; *************** CreateCommandTag(Node *parsetree) *** 1369,1374 **** --- 1374,1383 ---- tag = "CREATE SCHEMA"; break; + case T_AlterDefaultPrivilegesStmt: + tag = "ALTER DEFAULT PRIVILEGES"; + break; + case T_CreateStmt: tag = "CREATE TABLE"; break; *************** GetCommandLogLevel(Node *parsetree) *** 2142,2147 **** --- 2151,2160 ---- lev = LOGSTMT_DDL; break; + case T_AlterDefaultPrivilegesStmt: + lev = LOGSTMT_DDL; + break; + case T_CreateStmt: lev = LOGSTMT_DDL; break; diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index e19e96d..4ede76d 100644 *** a/src/backend/utils/adt/acl.c --- b/src/backend/utils/adt/acl.c *************** aclconcat(const Acl *left_acl, const Acl *** 424,429 **** --- 424,469 ---- } /* + * Merge two ACLs + * + * This produces properly merged ACL with no redundant entries. + * Returns NULL on NULL input. + */ + Acl * + aclmerge(const Acl *left_acl, const Acl *right_acl, Oid ownerId) + { + Acl *result_acl; + AclItem *aip; + int i, + num; + + if ((left_acl == NULL || ACL_NUM(left_acl) == 0) && + (right_acl == NULL || ACL_NUM(right_acl) == 0)) + return NULL; + if (left_acl == NULL || ACL_NUM(left_acl) == 0) + return aclcopy(right_acl); + if (right_acl == NULL || ACL_NUM(right_acl) == 0) + return aclcopy(left_acl); + + result_acl = aclcopy(left_acl); + + num = ACL_NUM(right_acl); + aip = ACL_DAT(right_acl); + + for (i = 0; i < num; i++, aip++) + { + Acl *tmp_acl; + tmp_acl = aclupdate(result_acl, aip, ACL_MODECHG_ADD, + ownerId, DROP_RESTRICT); + + pfree(result_acl); + result_acl = tmp_acl; + } + + return result_acl; + } + + /* * Verify that an ACL array is acceptable (one-dimensional and has no nulls) */ static void *************** is_admin_of_role(Oid member, Oid role) *** 4499,4504 **** --- 4539,4567 ---- } + /* Check if user has createrole privileges */ + bool + have_createrole_privilege(void) + { + bool result = false; + HeapTuple utup; + + /* Superusers can always do everything */ + if (superuser()) + return true; + + utup = SearchSysCache(AUTHOID, + ObjectIdGetDatum(GetUserId()), + 0, 0, 0); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolcreaterole; + ReleaseSysCache(utup); + } + return result; + } + + /* does what it says ... */ static int count_one_bits(AclMode mask) diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index dbd3619..cc76fe9 100644 *** a/src/backend/utils/cache/syscache.c --- b/src/backend/utils/cache/syscache.c *************** *** 31,36 **** --- 31,37 ---- #include "catalog/pg_constraint.h" #include "catalog/pg_conversion.h" #include "catalog/pg_database.h" + #include "catalog/pg_default_acl.h" #include "catalog/pg_enum.h" #include "catalog/pg_foreign_data_wrapper.h" #include "catalog/pg_foreign_server.h" diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 2d43e80..99150d7 100644 *** a/src/bin/pg_dump/common.c --- b/src/bin/pg_dump/common.c *************** getSchemaData(int *numTablesPtr) *** 93,98 **** --- 93,99 ---- TSConfigInfo *cfginfo; FdwInfo *fdwinfo; ForeignServerInfo *srvinfo; + DefaultACLInfo *daclinfo; int numNamespaces; int numAggregates; int numInherits; *************** getSchemaData(int *numTablesPtr) *** 108,113 **** --- 109,115 ---- int numTSConfigs; int numForeignDataWrappers; int numForeignServers; + int numDefaultACLs; if (g_verbose) write_msg(NULL, "reading schemas\n"); *************** getSchemaData(int *numTablesPtr) *** 216,221 **** --- 218,227 ---- write_msg(NULL, "reading triggers\n"); getTriggers(tblinfo, numTables); + if (g_verbose) + write_msg(NULL, "reading default privileges\n"); + daclinfo = getDefaultACLs(&numDefaultACLs); + *numTablesPtr = numTables; return tblinfo; } diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c index 5a3f9c3..062c318 100644 *** a/src/bin/pg_dump/dumputils.c --- b/src/bin/pg_dump/dumputils.c *************** buildACLCommands(const char *name, const *** 683,688 **** --- 683,849 ---- } /* + * Build ALTER DEFAULT PRIVILEGES command(s) for single pg_default_acl tuple. + * + * rolename: username of default privileges "owner" (will be passed through fmtId) + * nspname: name of the namespace in which these default privileges take effect; + * can be NULL indicate global default privileges + * type: the object type (as seen in GRANT command: must be one of + * TABLE, SEQUENCE, or FUNCTION) + * acls: the ACL string + * remoteVersion: version of database + * + * Returns TRUE if okay, FALSE if could not parse the acl string. + * The resulting commands (if any) are appended to the contents of 'sql'. + * + * Note: beware of passing a fmtId() result directly as 'rolename' or 'nspname', + * since this routine uses fmtId() internally. + */ + bool + buildDefaultACLCommands(char *rolename, char* nspname, char *type, char *acls, + int remoteVersion, PQExpBuffer sql) + { + char **aclitems; + int naclitems; + int i; + PQExpBuffer grantee, + grantor, + privs, + privswgo; + bool found_owner_privs = false; + + if (!parsePGArray(acls, &aclitems, &naclitems)) + { + if (aclitems) + free(aclitems); + return false; + } + + grantee = createPQExpBuffer(); + grantor = createPQExpBuffer(); + privs = createPQExpBuffer(); + privswgo = createPQExpBuffer(); + + /* Scan individual ACL items */ + for (i = 0; i < naclitems; i++) + { + if (!parseAclItem(aclitems[i], type, NULL, NULL, remoteVersion, + grantee, grantor, privs, privswgo)) + return false; + + if (privs->len > 0 || privswgo->len > 0) + { + /* there is always grantor for default acls */ + appendPQExpBuffer(sql, "SET SESSION AUTHORIZATION %s;\n", + fmtId(grantor->data)); + + if (strcmp(grantee->data, rolename) == 0 + && strcmp(grantor->data, rolename) == 0) + { + found_owner_privs = true; + + /* + * For the owner, the default privilege level is ALL WITH + * GRANT OPTION. + */ + if (strcmp(privswgo->data, "ALL") != 0) + { + appendPQExpBuffer(sql, "ALTER DEFAULT PRIVILEGES"); + if (nspname) + appendPQExpBuffer(sql, " IN SCHEMA %s", fmtId(nspname)); + + appendPQExpBuffer(sql, " REVOKE ALL"); + appendPQExpBuffer(sql, " ON %s FROM %s;\n", + type, fmtId(grantee->data)); + if (privs->len > 0) + { + appendPQExpBuffer(sql, "ALTER DEFAULT PRIVILEGES"); + if (nspname) + appendPQExpBuffer(sql, " IN SCHEMA %s", fmtId(nspname)); + + appendPQExpBuffer(sql, + " GRANT %s ON %s TO %s;\n", + privs->data, type, + fmtId(grantee->data)); + } + if (privswgo->len > 0) + { + appendPQExpBuffer(sql, "ALTER DEFAULT PRIVILEGES"); + if (nspname) + appendPQExpBuffer(sql, " IN SCHEMA %s", fmtId(nspname)); + + appendPQExpBuffer(sql, + " GRANT %s ON %s TO %s WITH GRANT OPTION;\n", + privswgo->data, type, + fmtId(grantee->data)); + } + } + } + else + { + appendPQExpBuffer(sql, "ALTER DEFAULT PRIVILEGES"); + if (nspname) + appendPQExpBuffer(sql, " IN SCHEMA %s", fmtId(nspname)); + + if (strcmp(grantor->data, rolename) != 0) + appendPQExpBuffer(sql, " FOR ROLE %s", rolename); + + /* + * Otherwise can assume we are starting from no privs. + */ + if (privs->len > 0) + { + appendPQExpBuffer(sql, " GRANT %s ON %s TO ", + privs->data, type); + if (grantee->len == 0) + appendPQExpBuffer(sql, "PUBLIC;\n"); + else + appendPQExpBuffer(sql, "%s;\n", fmtId(grantee->data)); + } + if (privswgo->len > 0) + { + appendPQExpBuffer(sql, " GRANT %s ON %s TO ", + privswgo->data, type); + if (grantee->len == 0) + appendPQExpBuffer(sql, "PUBLIC"); + else + appendPQExpBuffer(sql, "%s", fmtId(grantee->data)); + appendPQExpBuffer(sql, " WITH GRANT OPTION;\n"); + } + } + + appendPQExpBuffer(sql, "RESET SESSION AUTHORIZATION;\n"); + } + } + + /* + * If we didn't find any owner privs, the owner must have revoked 'em all + */ + if (!found_owner_privs && rolename) + { + appendPQExpBuffer(sql, "ALTER DEFAULT PRIVILEGES"); + + appendPQExpBuffer(sql, " FOR ROLE %s", rolename); + + if (nspname) + appendPQExpBuffer(sql, " IN SCHEMA %s", fmtId(nspname)); + + appendPQExpBuffer(sql, " REVOKE ALL"); + appendPQExpBuffer(sql, " ON %s FROM %s;\n", + type, rolename); + } + + destroyPQExpBuffer(grantee); + destroyPQExpBuffer(grantor); + destroyPQExpBuffer(privs); + destroyPQExpBuffer(privswgo); + + free(aclitems); + + return true; + } + + /* * This will parse an aclitem string, having the general form * username=privilegecodes/grantor * or diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h index f693922..d2820c6 100644 *** a/src/bin/pg_dump/dumputils.h --- b/src/bin/pg_dump/dumputils.h *************** extern bool buildACLCommands(const char *** 36,41 **** --- 36,43 ---- const char *type, const char *acls, const char *owner, int remoteVersion, PQExpBuffer sql); + extern bool buildDefaultACLCommands(char *rolename, char* nspname, char *type, + char *acls, int remoteVersion, PQExpBuffer sql); extern void processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern, bool have_where, bool force_escape, diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index b5c2f93..65f120e 100644 *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *************** *** 34,39 **** --- 34,40 ---- #include "access/sysattr.h" #include "catalog/pg_cast.h" #include "catalog/pg_class.h" + #include "catalog/pg_default_acl.h" #include "catalog/pg_largeobject.h" #include "catalog/pg_proc.h" #include "catalog/pg_trigger.h" *************** static void dumpACL(Archive *fout, Catal *** 168,173 **** --- 169,176 ---- const char *tag, const char *nspname, const char *owner, const char *acls); + static void dumpDefaultACL(Archive *fout, DefaultACLInfo *daclinfo); + static void getDependencies(void); static void getDomainConstraints(TypeInfo *tinfo); static void getTableData(TableInfo *tblinfo, int numTables, bool oids); *************** getForeignServers(int *numForeignServers *** 5646,5651 **** --- 5649,5730 ---- } /* + * getDefaultACLs: + * read all default acls servers in the system catalogs and return + * them in the ForeignServerInfo * structure + * + * numForeignServers is set to the number of servers read in + */ + DefaultACLInfo * + getDefaultACLs(int *numDefaultACLs) + { + DefaultACLInfo *daclinfo; + PQExpBuffer buf; + PQExpBuffer query; + PQExpBuffer tag; + PGresult *res; + int i, + ntups; + + if (g_fout->remoteVersion < 80500) + { + *numDefaultACLs = 0; + return NULL; + } + + buf = createPQExpBuffer(); + query = createPQExpBuffer(); + tag = createPQExpBuffer(); + + /* Make sure we are in proper schema */ + selectSourceSchema("pg_catalog"); + + appendPQExpBuffer(query, "SELECT oid, " + "(%s defaclrole), " + "defaclnamespace, " + "defaclobjtype, " + "defacllist " + "FROM pg_default_acl", + username_subquery); + + res = PQexec(g_conn, query->data); + check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + *numDefaultACLs = ntups; + + daclinfo = (DefaultACLInfo *) malloc(ntups * sizeof(DefaultACLInfo)); + + for (i = 0; i < ntups; i++) + { + Oid nspid = atooid(PQgetvalue(res, i, 2)); + + daclinfo[i].dobj.objType = DO_DEFAULT_ACL; + daclinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, 0)); + AssignDumpId(&daclinfo[i].dobj); + daclinfo[i].dobj.name = strdup(PQgetvalue(res, i, 1)); + + if (nspid != InvalidOid) + daclinfo[i].dobj.namespace = findNamespace(nspid, + daclinfo[i].dobj.catId.oid); + else + daclinfo[i].dobj.namespace = NULL; + + daclinfo[i].defaclobjtype = *(PQgetvalue(res, i, 3)); + daclinfo[i].defacllist = strdup(PQgetvalue(res, i, 4)); + + /* Decide whether we want to dump it */ + selectDumpableObject(&(daclinfo[i].dobj)); + } + + PQclear(res); + + destroyPQExpBuffer(query); + + return daclinfo; + } + + /* * dumpComment -- * * This routine is used to dump any comments associated with the *************** dumpDumpableObject(Archive *fout, Dumpab *** 6058,6063 **** --- 6137,6145 ---- case DO_FOREIGN_SERVER: dumpForeignServer(fout, (ForeignServerInfo *) dobj); break; + case DO_DEFAULT_ACL: + dumpDefaultACL(fout, (DefaultACLInfo *) dobj); + break; case DO_BLOBS: ArchiveEntry(fout, dobj->catId, dobj->dumpId, dobj->name, NULL, NULL, "", *************** dumpACL(Archive *fout, CatalogId objCatI *** 9840,9845 **** --- 9922,10005 ---- destroyPQExpBuffer(sql); } + + /* + * Write out default privileges information + */ + static void + dumpDefaultACL(Archive *fout, DefaultACLInfo *daclinfo) + { + PQExpBuffer q; + PQExpBuffer tag; + char *type; + + /* Skip if not to be dumped */ + if (!daclinfo->dobj.dump || dataOnly || aclsSkip) + return; + + q = createPQExpBuffer(); + tag = createPQExpBuffer(); + + switch (daclinfo->defaclobjtype) + { + case DEFACLOBJ_RELATION: + type = "TABLE"; + break; + case DEFACLOBJ_FUNCTION: + type = "FUNCTION"; + break; + case DEFACLOBJ_SEQUENCE: + type = "SEQUENCE"; + break; + default: + /* shouldn't get here */ + type = ""; /* silecne compiler */ + write_msg(NULL, "unknown object type (%c) in default privileges for role \"%s\"", + daclinfo->defaclobjtype, daclinfo->dobj.name); + if (daclinfo->dobj.namespace != NULL) + write_msg(NULL, " in schema \"%s\"", daclinfo->dobj.namespace->dobj.name); + write_msg(NULL, "\n"); + exit_nicely(); + } + + /* build the actual command(s) for this tuple */ + if (!buildDefaultACLCommands(daclinfo->dobj.name, + daclinfo->dobj.namespace != NULL ? + daclinfo->dobj.namespace->dobj.name : NULL, + type, daclinfo->defacllist, + fout->remoteVersion, q)) + { + write_msg(NULL, "could not parse default privileges acls list (%s) for role \"%s\"", + daclinfo->defacllist, daclinfo->dobj.name); + if (daclinfo->dobj.namespace != NULL) + write_msg(NULL, " in schema \"%s\"", daclinfo->dobj.namespace->dobj.name); + write_msg(NULL, "\n"); + exit_nicely(); + } + + if (daclinfo->dobj.namespace != NULL) + appendPQExpBuffer(tag, "DEFAULT PRIVILEGES for %s in %s on %s", + daclinfo->dobj.name, + daclinfo->dobj.namespace->dobj.name, type); + else + appendPQExpBuffer(tag, "DEFAULT PRIVILEGES for %s on %s", + daclinfo->dobj.name, type); + + ArchiveEntry(fout, daclinfo->dobj.catId, daclinfo->dobj.dumpId, + tag->data, + NULL, + NULL, + daclinfo->dobj.name, + false, "SERVER", SECTION_PRE_DATA, + q->data, "", NULL, + daclinfo->dobj.dependencies, daclinfo->dobj.nDeps, + NULL, NULL); + + destroyPQExpBuffer(tag); + destroyPQExpBuffer(q); + } + + /* * dumpTable * write out to fout the declarations (not data) of a user-defined table diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index ea489e8..d9dcd87 100644 *** a/src/bin/pg_dump/pg_dump.h --- b/src/bin/pg_dump/pg_dump.h *************** typedef enum *** 114,119 **** --- 114,120 ---- DO_TSCONFIG, DO_FDW, DO_FOREIGN_SERVER, + DO_DEFAULT_ACL, DO_BLOBS, DO_BLOB_COMMENTS } DumpableObjectType; *************** typedef struct _foreignServerInfo *** 432,437 **** --- 433,445 ---- char *srvoptions; } ForeignServerInfo; + typedef struct _defaultACLInfo + { + DumpableObject dobj; + char defaclobjtype; + char *defacllist; + } DefaultACLInfo; + /* global decls */ extern bool force_quotes; /* double-quotes for identifiers flag */ extern bool g_verbose; /* verbose flag */ *************** extern TSTemplateInfo *getTSTemplates(in *** 516,520 **** --- 524,529 ---- extern TSConfigInfo *getTSConfigurations(int *numTSConfigs); extern FdwInfo *getForeignDataWrappers(int *numForeignDataWrappers); extern ForeignServerInfo *getForeignServers(int *numForeignServers); + extern DefaultACLInfo *getDefaultACLs(int *numDefaultACLs); #endif /* PG_DUMP_H */ diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index bafabe9..15c7d95 100644 *** a/src/bin/pg_dump/pg_dump_sort.c --- b/src/bin/pg_dump/pg_dump_sort.c *************** static const int oldObjectTypePriority[] *** 54,59 **** --- 54,60 ---- 5, /* DO_TSCONFIG */ 3, /* DO_FDW */ 4, /* DO_FOREIGN_SERVER */ + 17, /* DO_DEFAULT_ACL */ 10, /* DO_BLOBS */ 11 /* DO_BLOB_COMMENTS */ }; *************** static const int newObjectTypePriority[] *** 90,95 **** --- 91,97 ---- 13, /* DO_TSCONFIG */ 14, /* DO_FDW */ 15, /* DO_FOREIGN_SERVER */ + 27, /* DO_DEFAULT_ACL */ 20, /* DO_BLOBS */ 21 /* DO_BLOB_COMMENTS */ }; diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index da57eb4..00120cd 100644 *** a/src/bin/psql/command.c --- b/src/bin/psql/command.c *************** exec_command(const char *cmd, *** 361,367 **** success = listCasts(pattern); break; case 'd': ! success = objectDescription(pattern, show_system); break; case 'D': success = listDomains(pattern, show_system); --- 361,375 ---- success = listCasts(pattern); break; case 'd': ! switch (cmd[2]) ! { ! case 'p': ! success = listDefaultACLs(pattern); ! break; ! default: ! success = objectDescription(pattern, show_system); ! break; ! } break; case 'D': success = listDomains(pattern, show_system); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index a193ea0..a42343e 100644 *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *************** permissionsList(const char *pattern) *** 732,737 **** --- 732,797 ---- } + /* + * \ddp + * + * List DefaultACLs. + */ + bool + listDefaultACLs(const char *pattern) + { + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + static const bool translate_columns[] = {false, false, true, false}; + + initPQExpBuffer(&buf); + + /* + * we ignore indexes and toast tables since they have no meaningful rights + */ + printfPQExpBuffer(&buf, + "SELECT r.rolname as \"%s\",\n" + " n.nspname as \"%s\",\n" + " CASE d.defaclobjtype WHEN 'r' THEN '%s' WHEN 'f' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n" + " ", + gettext_noop("Role"), + gettext_noop("Schema"), + gettext_noop("table"), gettext_noop("function"), gettext_noop("sequence"), + gettext_noop("Type")); + + printACLColumn(&buf, "d.defacllist"); + + appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_default_acl d\n" + " JOIN pg_catalog.pg_authid r ON r.oid = d.defaclrole\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n"); + + processSQLNamePattern(pset.db, &buf, pattern, false, false, + "n.nspname", "r.rolname", NULL, NULL); + + appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;"); + + res = PSQLexec(buf.data, false); + if (!res) + { + termPQExpBuffer(&buf); + return false; + } + + myopt.nullPrint = NULL; + printfPQExpBuffer(&buf, _("Default access privileges")); + myopt.title = buf.data; + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + + printQuery(res, &myopt, pset.queryFout, pset.logfile); + + termPQExpBuffer(&buf); + PQclear(res); + return true; + } + + /* * Get object comments diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 0b32ddf..1dafb47 100644 *** a/src/bin/psql/describe.h --- b/src/bin/psql/describe.h *************** extern bool describeRoles(const char *pa *** 30,35 **** --- 30,38 ---- /* \z (or \dp) */ extern bool permissionsList(const char *pattern); + /* \ddp */ + extern bool listDefaultACLs(const char *pattern); + /* \dd */ extern bool objectDescription(const char *pattern, bool showSystem); diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index d498d58..c2c3b4a 100644 *** a/src/bin/psql/help.c --- b/src/bin/psql/help.c *************** slashUsage(unsigned short int pager) *** 201,206 **** --- 201,207 ---- fprintf(output, _(" \\dc[S] [PATTERN] list conversions\n")); fprintf(output, _(" \\dC [PATTERN] list casts\n")); fprintf(output, _(" \\dd[S] [PATTERN] show comments on objects\n")); + fprintf(output, _(" \\ddp [PATTERN] list default privileges\n")); fprintf(output, _(" \\dD[S] [PATTERN] list domains\n")); fprintf(output, _(" \\des[+] [PATTERN] list foreign servers\n")); fprintf(output, _(" \\deu[+] [PATTERN] list user mappings\n")); diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h index a2f6761..3d3e1fb 100644 *** a/src/include/catalog/dependency.h --- b/src/include/catalog/dependency.h *************** typedef enum ObjectClass *** 146,151 **** --- 146,152 ---- OCLASS_FDW, /* pg_foreign_data_wrapper */ OCLASS_FOREIGN_SERVER, /* pg_foreign_server */ OCLASS_USER_MAPPING, /* pg_user_mapping */ + OCLASS_DEFACL, /* pg_default_acl */ MAX_OCLASS /* MUST BE LAST */ } ObjectClass; diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h index 81e18a1..0bbef3c 100644 *** a/src/include/catalog/indexing.h --- b/src/include/catalog/indexing.h *************** DECLARE_UNIQUE_INDEX(pg_user_mapping_oid *** 267,272 **** --- 267,277 ---- DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops)); #define UserMappingUserServerIndexId 175 + DECLARE_UNIQUE_INDEX(pg_default_acl_role_nsp_obj_index, 626, on pg_default_acl using btree(defaclrole oid_ops, defaclnamespace oid_ops, defaclobjtype char_ops)); + #define DefaultAclNspObjIndexId 626 + DECLARE_UNIQUE_INDEX(pg_default_acl_oid_index, 627, on pg_default_acl using btree(oid oid_ops)); + #define DefaultAclOidIndexId 627 + /* last step of initialization script: build the indexes declared above */ BUILD_INDICES diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h index ...4c10837 . *** a/src/include/catalog/pg_default_acl.h --- b/src/include/catalog/pg_default_acl.h *************** *** 0 **** --- 1,79 ---- + /*------------------------------------------------------------------------- + * + * pg_default_acl.h + * definition of default ACLs for new objects + * Specified by schema and grantable object type + * + * + * Copyright (c) 2009, PostgreSQL Global Development Group + * + * $Id$ + * + * NOTES + * the genbki.sh script reads this file and generates .bki + * information from the DATA() statements. + * + *------------------------------------------------------------------------- + */ + #ifndef PG_DEFAULT_ACL_H + #define PG_DEFAULT_ACL_H + + #include "catalog/genbki.h" + + /* ---------------- + * pg_default_acl definition. cpp turns this into + * typedef struct FormData_pg_default_acl + * ---------------- + */ + #define DefaultAclRelationId 1248 + + CATALOG(pg_default_acl,1248) /*BKI_BOOTSTRAP*/ + { + Oid defaclrole; /* OID of the role these ACLs apply to */ + Oid defaclnamespace; /* OID of namespace these ACLs apply to */ + char defaclobjtype; /* see DEFACL_OBJECT_xxx constants below */ + + /* + * VARIABLE LENGTH FIELDS start here. These fields may be NULL, too. + */ + + aclitem defacllist[1]; /* Permissions to be applied at CREATE time */ + } FormData_pg_default_acl; + + /* Size of fixed part of pg_default_acl tuples, not counting var-length fields */ + #define DEFAULT_ACL_TUPLE_SIZE \ + (offsetof(FormData_pg_default_acl,relfrozenxid) + sizeof(TransactionId)) + + /* ---------------- + * Form_pg_default_acl corresponds to a pointer to a tuple with + * the format of pg_default_acl relation. + * ---------------- + */ + typedef FormData_pg_default_acl *Form_pg_default_acl; + + /* ---------------- + * compiler constants for pg_default_acl + * ---------------- + */ + + #define Natts_pg_default_acl 4 + #define Anum_pg_default_acl_defaclrole 1 + #define Anum_pg_default_acl_defaclnamespace 2 + #define Anum_pg_default_acl_defaclgrantobjtype 3 + #define Anum_pg_default_acl_defacldefacllist 4 + + /* ---------------- + * pg_default_acl has no initial contents + * ---------------- + */ + + /* + * Types of objects which the user is allowed to specify default + * permissions on through pg_default_acl. + */ + + #define DEFACLOBJ_RELATION 'r' /* table, view */ + #define DEFACLOBJ_FUNCTION 'f' /* function */ + #define DEFACLOBJ_SEQUENCE 'S' /* sequence */ + + #endif /* PG_DEFAULT_ACL_H */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 3977081..9d8729f 100644 *** a/src/include/nodes/nodes.h --- b/src/include/nodes/nodes.h *************** typedef enum NodeTag *** 339,344 **** --- 339,345 ---- T_CreateUserMappingStmt, T_AlterUserMappingStmt, T_DropUserMappingStmt, + T_AlterDefaultPrivilegesStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 076ed8b..27f9de3 100644 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** typedef struct GrantRoleStmt *** 1260,1265 **** --- 1260,1277 ---- } GrantRoleStmt; /* ---------------------- + * Alter DEFAULT PRIVILEGES + * ---------------------- + */ + + typedef struct AlterDefaultPrivilegesStmt + { + NodeTag type; + List *options; + Node *action; + } AlterDefaultPrivilegesStmt; + + /* ---------------------- * Copy Statement * * We support "COPY relation FROM file", "COPY relation TO file", and diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index bde8727..29401e7 100644 *** a/src/include/utils/acl.h --- b/src/include/utils/acl.h *************** typedef struct *** 219,224 **** --- 219,243 ---- DropBehavior behavior; } InternalGrant; + + /* + * Internal format used by DefaultACL. + * Needs to be exported because it's needed for dropping owned objects. + */ + typedef struct + { + Oid roleid; /* Role for which we are changing DefaultACL */ + Oid nspid; /* Namespace this DefaultACL aplies to - can be invalidOid for database wide */ + bool is_grant; /* True means we are granting, false means we are revoking */ + GrantObjectType objtype; /* Object type affected by these DefaultACL */ + bool all_privs; /* See comment for InternalGrant */ + AclMode privileges; /* See comment for InternalGrant */ + List *grantees; /* Users to/from who we are Granting/Revoking privileges in these DefaultACL */ + bool grant_option; /* Indicates that WITH GRAN OPTION was specified */ + DropBehavior behavior; /* CASCADE or RESTRICT */ + } InternalDefaultACL; + + /* * routines used internally */ *************** extern Acl *aclupdate(const Acl *old_acl *** 228,233 **** --- 247,253 ---- extern Acl *aclnewowner(const Acl *old_acl, Oid oldOwnerId, Oid newOwnerId); extern Acl *aclcopy(const Acl *orig_acl); extern Acl *aclconcat(const Acl *left_acl, const Acl *right_acl); + extern Acl *aclmerge(const Acl *left_acl, const Acl *right_acl, Oid ownerId); extern AclMode aclmask(const Acl *acl, Oid roleid, Oid ownerId, AclMode mask, AclMaskHow how); *************** extern bool is_member_of_role(Oid member *** 238,243 **** --- 258,264 ---- extern bool is_member_of_role_nosuper(Oid member, Oid role); extern bool is_admin_of_role(Oid member, Oid role); extern void check_is_member_of_role(Oid member, Oid role); + extern bool have_createrole_privilege(void); extern void select_best_grantor(Oid roleId, AclMode privileges, const Acl *acl, Oid ownerId, *************** extern Datum hash_aclitem(PG_FUNCTION_AR *** 263,268 **** --- 284,293 ---- extern void ExecuteGrantStmt(GrantStmt *stmt); extern void ExecGrantStmt_oids(InternalGrant *istmt); + extern void ExecAlterDefaultPrivilegesStmt(AlterDefaultPrivilegesStmt *stmt); + extern void SetDefaultACL(InternalDefaultACL *iacls); + extern void RemoveDefaultACLById(Oid nsdaOid); + extern AclMode pg_attribute_aclmask(Oid table_oid, AttrNumber attnum, Oid roleid, AclMode mask, AclMaskHow how); extern AclMode pg_class_aclmask(Oid table_oid, Oid roleid, *************** extern bool pg_ts_dict_ownercheck(Oid di *** 317,320 **** --- 342,347 ---- extern bool pg_ts_config_ownercheck(Oid cfg_oid, Oid roleid); extern bool pg_foreign_server_ownercheck(Oid srv_oid, Oid roleid); + extern Acl *pg_namespace_object_default_acl(Oid nsp_oid, char objtype, Oid ownerId); + #endif /* ACL_H */ diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 809b656..91c7c9e 100644 *** a/src/test/regress/expected/privileges.out --- b/src/test/regress/expected/privileges.out *************** SELECT has_sequence_privilege('x_seq', ' *** 836,843 **** --- 836,958 ---- t (1 row) + -- Default ACLs + \c - + CREATE SCHEMA regressns; + GRANT USAGE ON SCHEMA regressns TO regressgroup1, regressuser2; + GRANT ALL ON SCHEMA regressns TO regressuser1; + -- bad input + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns GRANT USAGE ON FUNCTION TO regressgroup1; + ERROR: invalid privilege type USAGE for function + -- try different possible syntaxes from simple ones to more complex ones + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns GRANT SELECT ON TABLE TO public; + CREATE TABLE regressns.acltest1 (one int, two int, three int); + SELECT has_table_privilege('regressuser1', 'regressns.acltest1', 'INSERT'); -- false + has_table_privilege + --------------------- + f + (1 row) + + SELECT has_table_privilege('regressuser1', 'regressns.acltest1', 'SELECT'); -- true + has_table_privilege + --------------------- + t + (1 row) + + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE TO regressuser2; + CREATE TABLE regressns.acltest2 (one int, two int, three int); + SELECT has_table_privilege('regressuser2', 'regressns.acltest2', 'DELETE'); -- true + has_table_privilege + --------------------- + t + (1 row) + + SELECT has_table_privilege('regressuser4', 'regressns.acltest2', 'INSERT'); -- false + has_table_privilege + --------------------- + f + (1 row) + + SELECT has_table_privilege('regressuser4', 'regressns.acltest2', 'SELECT'); -- true (as member of regressgroup1) + has_table_privilege + --------------------- + t + (1 row) + + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns GRANT INSERT ON TABLE TO regressgroup1; + CREATE TABLE regressns.acltest3 (foo text); + SELECT has_table_privilege('regressuser4', 'regressns.acltest3', 'INSERT'); -- true + has_table_privilege + --------------------- + t + (1 row) + + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns REVOKE DELETE ON TABLE FROM regressuser2; + -- supress implicit sequence creation NOTICE + SET client_min_messages TO 'error'; + CREATE TABLE regressns.acltest4 (bar serial); + RESET client_min_messages; + SELECT has_table_privilege('regressuser2', 'regressns.acltest4', 'DELETE'); -- false + has_table_privilege + --------------------- + f + (1 row) + + SET SESSION AUTHORIZATION regressuser2; + SELECT nextval(pg_get_serial_sequence('regressns.acltest4', 'bar')::regclass); -- error + ERROR: permission denied for sequence acltest4_bar_seq + RESET SESSION AUTHORIZATION; + -- set default privileges for other user(s) + ALTER DEFAULT PRIVILEGES FOR USER regressuser1 IN SCHEMA regressns GRANT ALL ON SEQUENCE TO regressgroup1, regressuser2; + ALTER DEFAULT PRIVILEGES FOR ROLE regressuser1, regressuser5 IN SCHEMA regressns GRANT ALL PRIVILEGES ON TABLE TO regressuser2 WITH GRANT OPTION; + ALTER DEFAULT PRIVILEGES FOR ROLE regressuser1 IN SCHEMA regressns GRANT EXECUTE ON FUNCTION TO regressgroup1; + SET SESSION AUTHORIZATION regressuser1; + ALTER DEFAULT PRIVILEGES FOR USER regressuser2 IN SCHEMA regressns GRANT SELECT ON TABLE TO regressgroup1; -- error + ERROR: must have admin option on role "regressuser2" + -- supress implicit sequence creation NOTICE + SET client_min_messages TO 'error'; + CREATE TABLE regressns.acltest5 (a serial, b text); + RESET client_min_messages; + SELECT has_table_privilege('regressgroup1', 'regressns.acltest5', 'INSERT'); -- false + has_table_privilege + --------------------- + f + (1 row) + + SELECT has_table_privilege('regressuser2', 'regressns.acltest5', 'INSERT'); -- true + has_table_privilege + --------------------- + t + (1 row) + + CREATE FUNCTION regressns.testfunc1() RETURNS int AS 'select 1;' LANGUAGE sql; + SELECT has_function_privilege('regressgroup1', 'regressns.testfunc1()', 'EXECUTE'); -- true + has_function_privilege + ------------------------ + t + (1 row) + + CREATE VIEW regressns.acltest6 AS SELECT * FROM regressns.acltest4; + SET SESSION AUTHORIZATION regressuser2; + SELECT nextval(pg_get_serial_sequence('regressns.acltest5', 'a')::regclass); -- 1 + nextval + --------- + 1 + (1 row) + + GRANT SELECT ON regressns.acltest6 TO regressuser3; + SELECT has_table_privilege('regressuser3', 'regressns.acltest6', 'SELECT'); -- true + has_table_privilege + --------------------- + t + (1 row) + -- clean up \c + -- supress username dependend notices + SET client_min_messages TO 'error'; + DROP SCHEMA regressns CASCADE; + RESET client_min_messages; drop sequence x_seq; DROP FUNCTION testfunc2(int); DROP FUNCTION testfunc4(boolean); diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 7213192..1994edc 100644 *** a/src/test/regress/expected/sanity_check.out --- b/src/test/regress/expected/sanity_check.out *************** SELECT relname, relhasindex *** 95,100 **** --- 95,101 ---- pg_constraint | t pg_conversion | t pg_database | t + pg_default_acl | t pg_depend | t pg_description | t pg_enum | t *************** SELECT relname, relhasindex *** 151,157 **** timetz_tbl | f tinterval_tbl | f varchar_tbl | f ! (140 rows) -- -- another sanity check: every system catalog that has OIDs should have --- 152,158 ---- timetz_tbl | f tinterval_tbl | f varchar_tbl | f ! (141 rows) -- -- another sanity check: every system catalog that has OIDs should have diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 917e8e5..593f905 100644 *** a/src/test/regress/sql/privileges.sql --- b/src/test/regress/sql/privileges.sql *************** SET SESSION AUTHORIZATION regressuser2; *** 484,493 **** --- 484,563 ---- SELECT has_sequence_privilege('x_seq', 'USAGE'); + + -- Default ACLs + + \c - + + CREATE SCHEMA regressns; + GRANT USAGE ON SCHEMA regressns TO regressgroup1, regressuser2; + GRANT ALL ON SCHEMA regressns TO regressuser1; + + -- bad input + + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns GRANT USAGE ON FUNCTION TO regressgroup1; + + -- try different possible syntaxes from simple ones to more complex ones + + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns GRANT SELECT ON TABLE TO public; + CREATE TABLE regressns.acltest1 (one int, two int, three int); + SELECT has_table_privilege('regressuser1', 'regressns.acltest1', 'INSERT'); -- false + SELECT has_table_privilege('regressuser1', 'regressns.acltest1', 'SELECT'); -- true + + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE TO regressuser2; + CREATE TABLE regressns.acltest2 (one int, two int, three int); + SELECT has_table_privilege('regressuser2', 'regressns.acltest2', 'DELETE'); -- true + SELECT has_table_privilege('regressuser4', 'regressns.acltest2', 'INSERT'); -- false + SELECT has_table_privilege('regressuser4', 'regressns.acltest2', 'SELECT'); -- true (as member of regressgroup1) + + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns GRANT INSERT ON TABLE TO regressgroup1; + CREATE TABLE regressns.acltest3 (foo text); + SELECT has_table_privilege('regressuser4', 'regressns.acltest3', 'INSERT'); -- true + + ALTER DEFAULT PRIVILEGES IN SCHEMA regressns REVOKE DELETE ON TABLE FROM regressuser2; + -- supress implicit sequence creation NOTICE + SET client_min_messages TO 'error'; + CREATE TABLE regressns.acltest4 (bar serial); + RESET client_min_messages; + SELECT has_table_privilege('regressuser2', 'regressns.acltest4', 'DELETE'); -- false + SET SESSION AUTHORIZATION regressuser2; + SELECT nextval(pg_get_serial_sequence('regressns.acltest4', 'bar')::regclass); -- error + RESET SESSION AUTHORIZATION; + + -- set default privileges for other user(s) + ALTER DEFAULT PRIVILEGES FOR USER regressuser1 IN SCHEMA regressns GRANT ALL ON SEQUENCE TO regressgroup1, regressuser2; + ALTER DEFAULT PRIVILEGES FOR ROLE regressuser1, regressuser5 IN SCHEMA regressns GRANT ALL PRIVILEGES ON TABLE TO regressuser2 WITH GRANT OPTION; + ALTER DEFAULT PRIVILEGES FOR ROLE regressuser1 IN SCHEMA regressns GRANT EXECUTE ON FUNCTION TO regressgroup1; + + SET SESSION AUTHORIZATION regressuser1; + ALTER DEFAULT PRIVILEGES FOR USER regressuser2 IN SCHEMA regressns GRANT SELECT ON TABLE TO regressgroup1; -- error + + -- supress implicit sequence creation NOTICE + SET client_min_messages TO 'error'; + CREATE TABLE regressns.acltest5 (a serial, b text); + RESET client_min_messages; + SELECT has_table_privilege('regressgroup1', 'regressns.acltest5', 'INSERT'); -- false + SELECT has_table_privilege('regressuser2', 'regressns.acltest5', 'INSERT'); -- true + + CREATE FUNCTION regressns.testfunc1() RETURNS int AS 'select 1;' LANGUAGE sql; + SELECT has_function_privilege('regressgroup1', 'regressns.testfunc1()', 'EXECUTE'); -- true + + CREATE VIEW regressns.acltest6 AS SELECT * FROM regressns.acltest4; + SET SESSION AUTHORIZATION regressuser2; + SELECT nextval(pg_get_serial_sequence('regressns.acltest5', 'a')::regclass); -- 1 + GRANT SELECT ON regressns.acltest6 TO regressuser3; + SELECT has_table_privilege('regressuser3', 'regressns.acltest6', 'SELECT'); -- true + + -- clean up \c + -- supress username dependend notices + SET client_min_messages TO 'error'; + DROP SCHEMA regressns CASCADE; + RESET client_min_messages; + drop sequence x_seq; DROP FUNCTION testfunc2(int);