aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/catalog/aclchk.c127
-rw-r--r--src/backend/nodes/copyfuncs.c3
-rw-r--r--src/backend/nodes/equalfuncs.c3
-rw-r--r--src/backend/parser/gram.y52
-rw-r--r--src/include/nodes/parsenodes.h10
-rw-r--r--src/include/parser/kwlist.h5
-rw-r--r--src/test/regress/expected/privileges.out56
-rw-r--r--src/test/regress/sql/privileges.sql35
8 files changed, 279 insertions, 12 deletions
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index b06e587a1b1..8b2599a99f5 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.155 2009/10/05 19:24:35 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.156 2009/10/12 20:39:39 tgl Exp $
*
* NOTES
* See acl.h.
@@ -110,6 +110,8 @@ static void SetDefaultACLsInSchemas(InternalDefaultACL *iacls, List *nspnames);
static void SetDefaultACL(InternalDefaultACL *iacls);
static List *objectNamesToOids(GrantObjectType objtype, List *objnames);
+static List *objectsInSchemaToOids(GrantObjectType objtype, List *nspnames);
+static List *getRelationsInNamespace(Oid namespaceId, char relkind);
static void expand_col_privileges(List *colnames, Oid table_oid,
AclMode this_privileges,
AclMode *col_privileges,
@@ -335,7 +337,22 @@ ExecuteGrantStmt(GrantStmt *stmt)
*/
istmt.is_grant = stmt->is_grant;
istmt.objtype = stmt->objtype;
- istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
+
+ /* Collect the OIDs of the target objects */
+ switch (stmt->targtype)
+ {
+ case ACL_TARGET_OBJECT:
+ istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
+ break;
+ case ACL_TARGET_ALL_IN_SCHEMA:
+ istmt.objects = objectsInSchemaToOids(stmt->objtype, stmt->objects);
+ break;
+ /* ACL_TARGET_DEFAULTS should not be seen here */
+ default:
+ elog(ERROR, "unrecognized GrantStmt.targtype: %d",
+ (int) stmt->targtype);
+ }
+
/* all_privs to be filled below */
/* privileges to be filled below */
istmt.col_privs = NIL; /* may get filled below */
@@ -657,6 +674,112 @@ objectNamesToOids(GrantObjectType objtype, List *objnames)
}
/*
+ * objectsInSchemaToOids
+ *
+ * Find all objects of a given type in specified schemas, and make a list
+ * of their Oids. We check USAGE privilege on the schemas, but there is
+ * no privilege checking on the individual objects here.
+ */
+static List *
+objectsInSchemaToOids(GrantObjectType objtype, List *nspnames)
+{
+ List *objects = NIL;
+ ListCell *cell;
+
+ foreach(cell, nspnames)
+ {
+ char *nspname = strVal(lfirst(cell));
+ Oid namespaceId;
+ List *objs;
+
+ namespaceId = LookupExplicitNamespace(nspname);
+
+ switch (objtype)
+ {
+ case ACL_OBJECT_RELATION:
+ /* Process both regular tables and views */
+ objs = getRelationsInNamespace(namespaceId, RELKIND_RELATION);
+ objects = list_concat(objects, objs);
+ objs = getRelationsInNamespace(namespaceId, RELKIND_VIEW);
+ objects = list_concat(objects, objs);
+ break;
+ case ACL_OBJECT_SEQUENCE:
+ objs = getRelationsInNamespace(namespaceId, RELKIND_SEQUENCE);
+ objects = list_concat(objects, objs);
+ break;
+ case ACL_OBJECT_FUNCTION:
+ {
+ ScanKeyData key[1];
+ Relation rel;
+ HeapScanDesc scan;
+ HeapTuple tuple;
+
+ ScanKeyInit(&key[0],
+ Anum_pg_proc_pronamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ rel = heap_open(ProcedureRelationId, AccessShareLock);
+ scan = heap_beginscan(rel, SnapshotNow, 1, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ objects = lappend_oid(objects, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+ heap_close(rel, AccessShareLock);
+ }
+ break;
+ default:
+ /* should not happen */
+ elog(ERROR, "unrecognized GrantStmt.objtype: %d",
+ (int) objtype);
+ }
+ }
+
+ return objects;
+}
+
+/*
+ * getRelationsInNamespace
+ *
+ * Return Oid list of relations in given namespace filtered by relation kind
+ */
+static List *
+getRelationsInNamespace(Oid namespaceId, char relkind)
+{
+ List *relations = NIL;
+ ScanKeyData key[2];
+ Relation rel;
+ HeapScanDesc scan;
+ HeapTuple tuple;
+
+ ScanKeyInit(&key[0],
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+ ScanKeyInit(&key[1],
+ Anum_pg_class_relkind,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(relkind));
+
+ rel = heap_open(RelationRelationId, AccessShareLock);
+ scan = heap_beginscan(rel, SnapshotNow, 2, key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ relations = lappend_oid(relations, HeapTupleGetOid(tuple));
+ }
+
+ heap_endscan(scan);
+ heap_close(rel, AccessShareLock);
+
+ return relations;
+}
+
+
+/*
* ALTER DEFAULT PRIVILEGES statement
*/
void
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 8365586a00b..efbb0f57be6 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -15,7 +15,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.445 2009/10/12 19:49:24 adunstan Exp $
+ * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.446 2009/10/12 20:39:39 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -2350,6 +2350,7 @@ _copyGrantStmt(GrantStmt *from)
GrantStmt *newnode = makeNode(GrantStmt);
COPY_SCALAR_FIELD(is_grant);
+ COPY_SCALAR_FIELD(targtype);
COPY_SCALAR_FIELD(objtype);
COPY_NODE_FIELD(objects);
COPY_NODE_FIELD(privileges);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 2c916af1b78..de5497c4920 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -22,7 +22,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.367 2009/10/12 19:49:24 adunstan Exp $
+ * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.368 2009/10/12 20:39:40 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -990,6 +990,7 @@ static bool
_equalGrantStmt(GrantStmt *a, GrantStmt *b)
{
COMPARE_SCALAR_FIELD(is_grant);
+ COMPARE_SCALAR_FIELD(targtype);
COMPARE_SCALAR_FIELD(objtype);
COMPARE_NODE_FIELD(objects);
COMPARE_NODE_FIELD(privileges);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 50ce33070fd..4f502876438 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.683 2009/10/12 19:49:24 adunstan Exp $
+ * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.684 2009/10/12 20:39:41 tgl Exp $
*
* HISTORY
* AUTHOR DATE MAJOR EVENT
@@ -95,6 +95,7 @@
/* Private struct for the result of privilege_target production */
typedef struct PrivTarget
{
+ GrantTargetType targtype;
GrantObjectType objtype;
List *objs;
} PrivTarget;
@@ -480,7 +481,7 @@ static TypeName *TableFuncTypeName(List *columns);
EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
- FREEZE FROM FULL FUNCTION
+ FREEZE FROM FULL FUNCTION FUNCTIONS
GLOBAL GRANT GRANTED GREATEST GROUP_P
@@ -518,13 +519,13 @@ static TypeName *TableFuncTypeName(List *columns);
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
- SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE
+ SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
SYMMETRIC SYSID SYSTEM_P
- TABLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
+ TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
@@ -4321,6 +4322,7 @@ GrantStmt: GRANT privileges ON privilege_target TO grantee_list
GrantStmt *n = makeNode(GrantStmt);
n->is_grant = true;
n->privileges = $2;
+ n->targtype = ($4)->targtype;
n->objtype = ($4)->objtype;
n->objects = ($4)->objs;
n->grantees = $6;
@@ -4337,6 +4339,7 @@ RevokeStmt:
n->is_grant = false;
n->grant_option = false;
n->privileges = $2;
+ n->targtype = ($4)->targtype;
n->objtype = ($4)->objtype;
n->objects = ($4)->objs;
n->grantees = $6;
@@ -4350,6 +4353,7 @@ RevokeStmt:
n->is_grant = false;
n->grant_option = true;
n->privileges = $5;
+ n->targtype = ($7)->targtype;
n->objtype = ($7)->objtype;
n->objects = ($7)->objs;
n->grantees = $9;
@@ -4432,6 +4436,7 @@ privilege_target:
qualified_name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_RELATION;
n->objs = $1;
$$ = n;
@@ -4439,6 +4444,7 @@ privilege_target:
| TABLE qualified_name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_RELATION;
n->objs = $2;
$$ = n;
@@ -4446,6 +4452,7 @@ privilege_target:
| SEQUENCE qualified_name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_SEQUENCE;
n->objs = $2;
$$ = n;
@@ -4453,6 +4460,7 @@ privilege_target:
| FOREIGN DATA_P WRAPPER name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_FDW;
n->objs = $4;
$$ = n;
@@ -4460,6 +4468,7 @@ privilege_target:
| FOREIGN SERVER name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_FOREIGN_SERVER;
n->objs = $3;
$$ = n;
@@ -4467,6 +4476,7 @@ privilege_target:
| FUNCTION function_with_argtypes_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_FUNCTION;
n->objs = $2;
$$ = n;
@@ -4474,6 +4484,7 @@ privilege_target:
| DATABASE name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_DATABASE;
n->objs = $2;
$$ = n;
@@ -4481,6 +4492,7 @@ privilege_target:
| LANGUAGE name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_LANGUAGE;
n->objs = $2;
$$ = n;
@@ -4488,6 +4500,7 @@ privilege_target:
| SCHEMA name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_NAMESPACE;
n->objs = $2;
$$ = n;
@@ -4495,10 +4508,35 @@ privilege_target:
| TABLESPACE name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
n->objtype = ACL_OBJECT_TABLESPACE;
n->objs = $2;
$$ = n;
}
+ | ALL TABLES IN_P SCHEMA name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_ALL_IN_SCHEMA;
+ n->objtype = ACL_OBJECT_RELATION;
+ n->objs = $5;
+ $$ = n;
+ }
+ | ALL SEQUENCES IN_P SCHEMA name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_ALL_IN_SCHEMA;
+ n->objtype = ACL_OBJECT_SEQUENCE;
+ n->objs = $5;
+ $$ = n;
+ }
+ | ALL FUNCTIONS IN_P SCHEMA name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_ALL_IN_SCHEMA;
+ n->objtype = ACL_OBJECT_FUNCTION;
+ n->objs = $5;
+ $$ = n;
+ }
;
@@ -4648,6 +4686,7 @@ DefACLAction:
GrantStmt *n = makeNode(GrantStmt);
n->is_grant = true;
n->privileges = $2;
+ n->targtype = ACL_TARGET_DEFAULTS;
n->objtype = $4;
n->objects = NIL;
n->grantees = $6;
@@ -4661,6 +4700,7 @@ DefACLAction:
n->is_grant = false;
n->grant_option = false;
n->privileges = $2;
+ n->targtype = ACL_TARGET_DEFAULTS;
n->objtype = $4;
n->objects = NIL;
n->grantees = $6;
@@ -4674,6 +4714,7 @@ DefACLAction:
n->is_grant = false;
n->grant_option = true;
n->privileges = $5;
+ n->targtype = ACL_TARGET_DEFAULTS;
n->objtype = $7;
n->objects = NIL;
n->grantees = $9;
@@ -10535,6 +10576,7 @@ unreserved_keyword:
| FORCE
| FORWARD
| FUNCTION
+ | FUNCTIONS
| GLOBAL
| GRANTED
| HANDLER
@@ -10644,6 +10686,7 @@ unreserved_keyword:
| SECOND_P
| SECURITY
| SEQUENCE
+ | SEQUENCES
| SERIALIZABLE
| SERVER
| SESSION
@@ -10664,6 +10707,7 @@ unreserved_keyword:
| SUPERUSER_P
| SYSID
| SYSTEM_P
+ | TABLES
| TABLESPACE
| TEMP
| TEMPLATE
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index cf755724ccc..7d7f6da1598 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -13,7 +13,7 @@
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
- * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.407 2009/10/12 19:49:24 adunstan Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.408 2009/10/12 20:39:42 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -1182,6 +1182,13 @@ typedef struct AlterDomainStmt
* Grant|Revoke Statement
* ----------------------
*/
+typedef enum GrantTargetType
+{
+ ACL_TARGET_OBJECT, /* grant on specific named object(s) */
+ ACL_TARGET_ALL_IN_SCHEMA, /* grant on all objects in given schema(s) */
+ ACL_TARGET_DEFAULTS /* ALTER DEFAULT PRIVILEGES */
+} GrantTargetType;
+
typedef enum GrantObjectType
{
ACL_OBJECT_COLUMN, /* column */
@@ -1200,6 +1207,7 @@ typedef struct GrantStmt
{
NodeTag type;
bool is_grant; /* true = GRANT, false = REVOKE */
+ GrantTargetType targtype; /* type of the grant target */
GrantObjectType objtype; /* kind of object being operated on */
List *objects; /* list of RangeVar nodes, FuncWithArgs nodes,
* or plain names (as Value strings) */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 4b1284f3382..97b3c16ed00 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -11,7 +11,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/include/parser/kwlist.h,v 1.4 2009/10/12 19:49:24 adunstan Exp $
+ * $PostgreSQL: pgsql/src/include/parser/kwlist.h,v 1.5 2009/10/12 20:39:42 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -164,6 +164,7 @@ PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
+PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
@@ -329,6 +330,7 @@ PG_KEYWORD("second", SECOND_P, UNRESERVED_KEYWORD)
PG_KEYWORD("security", SECURITY, UNRESERVED_KEYWORD)
PG_KEYWORD("select", SELECT, RESERVED_KEYWORD)
PG_KEYWORD("sequence", SEQUENCE, UNRESERVED_KEYWORD)
+PG_KEYWORD("sequences", SEQUENCES, UNRESERVED_KEYWORD)
PG_KEYWORD("serializable", SERIALIZABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("server", SERVER, UNRESERVED_KEYWORD)
PG_KEYWORD("session", SESSION, UNRESERVED_KEYWORD)
@@ -357,6 +359,7 @@ PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD)
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD)
+PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD)
PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD)
PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 24239276dbf..8214b77901b 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -3,7 +3,7 @@
--
-- Clean up in case a prior regression run failed
-- Suppress NOTICE messages when users/groups don't exist
-SET client_min_messages TO 'error';
+SET client_min_messages TO 'warning';
DROP ROLE IF EXISTS regressgroup1;
DROP ROLE IF EXISTS regressgroup2;
DROP ROLE IF EXISTS regressuser1;
@@ -947,6 +947,60 @@ SELECT d.* -- check that entries went away
------------+-----------------+---------------+-----------
(0 rows)
+-- Grant on all objects of given type in a schema
+\c -
+CREATE SCHEMA testns;
+CREATE TABLE testns.t1 (f1 int);
+CREATE TABLE testns.t2 (f1 int);
+SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+GRANT ALL ON ALL TABLES IN SCHEMA testns TO regressuser1;
+SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regressuser1', 'testns.t2', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regressuser1;
+SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regressuser1', 'testns.t2', 'SELECT'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
+SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default
+ has_function_privilege
+------------------------
+ t
+(1 row)
+
+REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
+SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE'); -- false
+ has_function_privilege
+------------------------
+ f
+(1 row)
+
+SET client_min_messages TO 'warning';
+DROP SCHEMA testns CASCADE;
+RESET client_min_messages;
-- clean up
\c
drop sequence x_seq;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index eaa879efa22..c5aaffc8d57 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -5,7 +5,7 @@
-- Clean up in case a prior regression run failed
-- Suppress NOTICE messages when users/groups don't exist
-SET client_min_messages TO 'error';
+SET client_min_messages TO 'warning';
DROP ROLE IF EXISTS regressgroup1;
DROP ROLE IF EXISTS regressgroup2;
@@ -551,6 +551,39 @@ SELECT d.* -- check that entries went away
FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
WHERE nspname IS NULL AND defaclnamespace != 0;
+
+-- Grant on all objects of given type in a schema
+\c -
+
+CREATE SCHEMA testns;
+CREATE TABLE testns.t1 (f1 int);
+CREATE TABLE testns.t2 (f1 int);
+
+SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- false
+
+GRANT ALL ON ALL TABLES IN SCHEMA testns TO regressuser1;
+
+SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- true
+SELECT has_table_privilege('regressuser1', 'testns.t2', 'SELECT'); -- true
+
+REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regressuser1;
+
+SELECT has_table_privilege('regressuser1', 'testns.t1', 'SELECT'); -- false
+SELECT has_table_privilege('regressuser1', 'testns.t2', 'SELECT'); -- false
+
+CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
+
+SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default
+
+REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
+
+SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE'); -- false
+
+SET client_min_messages TO 'warning';
+DROP SCHEMA testns CASCADE;
+RESET client_min_messages;
+
+
-- clean up
\c
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-17 23:25:35 +0000