v20251102-0003-GRANT-REVOKE-variable.patch
text/x-patch
Filename: v20251102-0003-GRANT-REVOKE-variable.patch
Type: text/x-patch
Part: 12
Message:
Re: proposal: schema variables
From e7cdf812fa068352fe04d29c9b0f4f47cd57ce52 Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Tue, 5 Aug 2025 06:37:28 +0200
Subject: [PATCH 03/15] GRANT, REVOKE variable
Access to session variables can be controlled by SELECT or UPDATE rights. Both rights are
introduced by this patch too. Default ACL are supported.
---
doc/src/sgml/catalogs.sgml | 3 +-
doc/src/sgml/ddl.sgml | 19 +-
doc/src/sgml/func/func-info.sgml | 23 +-
.../sgml/ref/alter_default_privileges.sgml | 29 +-
doc/src/sgml/ref/grant.sgml | 20 +-
doc/src/sgml/ref/revoke.sgml | 8 +
src/backend/catalog/aclchk.c | 74 +++-
src/backend/catalog/objectaddress.c | 22 +-
src/backend/catalog/pg_variable.c | 11 +-
src/backend/parser/gram.y | 21 +-
src/backend/utils/adt/acl.c | 221 ++++++++++++
src/include/catalog/pg_default_acl.h | 1 +
src/include/catalog/pg_proc.dat | 20 ++
src/include/parser/kwlist.h | 1 +
src/include/utils/acl.h | 1 +
.../expected/session_variables_acl.out | 335 ++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
.../regress/sql/session_variables_acl.sql | 182 ++++++++++
18 files changed, 967 insertions(+), 26 deletions(-)
create mode 100644 src/test/regress/expected/session_variables_acl.out
create mode 100644 src/test/regress/sql/session_variables_acl.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 7c79ddc9c09..7a6d25e2920 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3363,7 +3363,8 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<literal>f</literal> = function,
<literal>T</literal> = type,
<literal>n</literal> = schema,
- <literal>L</literal> = large object
+ <literal>L</literal> = large object,
+ <literal>V</literal> = session variable
</para></entry>
</row>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fa711a09bc4..420a4d9ff11 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2025,6 +2025,7 @@ REVOKE ALL ON accounts FROM PUBLIC;
For sequences, this privilege also allows use of the
<function>currval</function> function.
For large objects, this privilege allows the object to be read.
+ For session variables, this privilege allows the object to be read.
</para>
</listitem>
</varlistentry>
@@ -2060,6 +2061,8 @@ REVOKE ALL ON accounts FROM PUBLIC;
<function>setval</function> functions.
For large objects, this privilege allows writing or truncating the
object.
+ For session variables, this privilege allows to set a value to the
+ object.
</para>
</listitem>
</varlistentry>
@@ -2304,7 +2307,8 @@ REVOKE ALL ON accounts FROM PUBLIC;
<literal>LARGE OBJECT</literal>,
<literal>SEQUENCE</literal>,
<literal>TABLE</literal> (and table-like objects),
- table column
+ table column,
+ <literal>SESSION VARIABLE</literal>
</entry>
</row>
<row>
@@ -2319,7 +2323,8 @@ REVOKE ALL ON accounts FROM PUBLIC;
<literal>LARGE OBJECT</literal>,
<literal>SEQUENCE</literal>,
<literal>TABLE</literal>,
- table column
+ table column,
+ <literal>SESSION VARIABLE</literal>
</entry>
</row>
<row>
@@ -2506,6 +2511,12 @@ REVOKE ALL ON accounts FROM PUBLIC;
<entry><literal>U</literal></entry>
<entry><literal>\dT+</literal></entry>
</row>
+ <row>
+ <entry><literal>SESSION VARIABLE</literal></entry>
+ <entry><literal>rw</literal></entry>
+ <entry><literal>none</literal></entry>
+ <entry><literal>\dV+</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -5375,6 +5386,10 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
<para>
Session variables are database objects that can hold a value.
+ Session variables, like relations, exist within a schema and their access
+ is controlled via <command>GRANT</command> and <command>REVOKE</command>
+ commands. A session variable can be created by the <command>CREATE
+ VARIABLE</command> command.
</para>
<para>
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..a57f7665054 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -740,6 +740,25 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>has_session_variable_privilege</primary>
+ </indexterm>
+ <function>has_session_variable_privilege</function> (
+ <optional> <parameter>user</parameter> <type>name</type> or <type>oid</type>, </optional>
+ <parameter>session_variable</parameter> <type>text</type> or <type>oid</type>,
+ <parameter>privilege</parameter> <type>text</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does user have privilege for session variable?
+ Allowable privilege types are
+ <literal>SELECT</literal>, and
+ <literal>UPDATE</literal>.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -1089,8 +1108,8 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
't' for <literal>TABLESPACE</literal>,
'F' for <literal>FOREIGN DATA WRAPPER</literal>,
'S' for <literal>FOREIGN SERVER</literal>,
- or
- 'T' for <literal>TYPE</literal> or <literal>DOMAIN</literal>.
+ 'T' for <literal>TYPE</literal> or <literal>DOMAIN</literal> or
+ 'V' for <literal>SESSION VARIABLE</literal>.
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index 6acd0f1df91..bc73817061f 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -56,6 +56,11 @@ GRANT { { SELECT | UPDATE }
ON LARGE OBJECTS
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+GRANT { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON VARIABLES
+ TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
@@ -95,6 +100,14 @@ REVOKE [ GRANT OPTION FOR ]
ON LARGE OBJECTS
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON VARIABLES
+ FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
@@ -129,14 +142,14 @@ REVOKE [ GRANT OPTION FOR ]
<para>
Currently,
only the privileges for schemas, tables (including views and foreign
- tables), sequences, functions, types (including domains), and large objects
- can be altered. For this command, functions include aggregates and procedures.
- The words <literal>FUNCTIONS</literal> and <literal>ROUTINES</literal> are
- equivalent in this command. (<literal>ROUTINES</literal> is preferred
- going forward as the standard term for functions and procedures taken
- together. In earlier PostgreSQL releases, only the
- word <literal>FUNCTIONS</literal> was allowed. It is not possible to set
- default privileges for functions and procedures separately.)
+ tables), sequences, functions, types (including domains), large objects
+ and session variables can be altered. For this command, functions include
+ aggregates and procedures. The words <literal>FUNCTIONS</literal> and
+ <literal>ROUTINES</literal> are equivalent in this command.
+ (<literal>ROUTINES</literal> is preferred going forward as the standard term
+ for functions and procedures taken together. In earlier PostgreSQL releases,
+ only the word <literal>FUNCTIONS</literal> was allowed. It is not possible
+ to set default privileges for functions and procedures separately.)
</para>
<para>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 999f657d5c0..c11860fa200 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -101,6 +101,12 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
[ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
+GRANT { SELECT | UPDATE | ALL [ PRIVILEGES ] }
+ ON { VARIABLE <replaceable>variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
+
<phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
[ GROUP ] <replaceable class="parameter">role_name</replaceable>
@@ -119,8 +125,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
that grants privileges on a database object (table, column, view,
foreign table, sequence, database, foreign-data wrapper, foreign server,
function, procedure, procedural language, large object, configuration
- parameter, schema, tablespace, or type), and one that grants
- membership in a role. These variants are similar in many ways, but
+ parameter, schema, session variable, tablespace, or type), and one that
+ grants membership in a role. These variants are similar in many ways, but
they are different enough to be described separately.
</para>
@@ -236,9 +242,9 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
<para>
There is also an option to grant privileges on all objects of the same
type within one or more schemas. This functionality is currently supported
- only for tables, sequences, functions, and procedures. <literal>ALL
- TABLES</literal> also affects views and foreign tables, just like the
- specific-object <command>GRANT</command> command. <literal>ALL
+ only for tables, sequences, functions, procedures and variables.
+ <literal>ALL TABLES</literal> also affects views and foreign tables, just
+ like the specific-object <command>GRANT</command> command. <literal>ALL
FUNCTIONS</literal> also affects aggregate and window functions, but not
procedures, again just like the specific-object <command>GRANT</command>
command. Use <literal>ALL ROUTINES</literal> to include procedures.
@@ -518,8 +524,8 @@ GRANT admins TO joe;
</para>
<para>
- Privileges on databases, tablespaces, schemas, languages, and
- configuration parameters are
+ Privileges on databases, tablespaces, schemas, languages, session variables
+ and configuration parameters are
<productname>PostgreSQL</productname> extensions.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8df492281a1..760fddb7c20 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -130,6 +130,14 @@ REVOKE [ { ADMIN | INHERIT | SET } OPTION FOR ]
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON { VARIABLE <replaceable>variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ FROM { <replaceable class="parameter">role_specification</replaceable> | PUBLIC } [, ...]
+ [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
+ [ CASCADE | RESTRICT ]
+
<phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
[ GROUP ] <replaceable class="parameter">role_name</replaceable>
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 00e3630e0ec..93c10beebe9 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -64,6 +64,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_variable.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
#include "commands/extension.h"
@@ -290,6 +291,9 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs,
case OBJECT_PARAMETER_ACL:
whole_mask = ACL_ALL_RIGHTS_PARAMETER_ACL;
break;
+ case OBJECT_VARIABLE:
+ whole_mask = ACL_ALL_RIGHTS_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized object type: %d", objtype);
/* not reached, but keep compiler quiet */
@@ -534,6 +538,10 @@ ExecuteGrantStmt(GrantStmt *stmt)
all_privileges = ACL_ALL_RIGHTS_PARAMETER_ACL;
errormsg = gettext_noop("invalid privilege type %s for parameter");
break;
+ case OBJECT_VARIABLE:
+ all_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ errormsg = gettext_noop("invalid privilege type %s for session variable");
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) stmt->objtype);
@@ -639,6 +647,9 @@ ExecGrantStmt_oids(InternalGrant *istmt)
case OBJECT_PARAMETER_ACL:
ExecGrant_Parameter(istmt);
break;
+ case OBJECT_VARIABLE:
+ ExecGrant_common(istmt, VariableRelationId, ACL_ALL_RIGHTS_VARIABLE, NULL);
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) istmt->objtype);
@@ -773,6 +784,18 @@ objectNamesToOids(ObjectType objtype, List *objnames, bool is_grant)
objects = lappend_oid(objects, parameterId);
}
break;
+
+ case OBJECT_VARIABLE:
+ foreach_node(RangeVar, varvar, objnames)
+ {
+ Oid relOid;
+
+ relOid = LookupVariable(varvar->schemaname,
+ varvar->relname,
+ false);
+ objects = lappend_oid(objects, relOid);
+ }
+ break;
}
return objects;
@@ -859,6 +882,32 @@ objectsInSchemaToOids(ObjectType objtype, List *nspnames)
table_close(rel, AccessShareLock);
}
break;
+ case OBJECT_VARIABLE:
+ {
+ ScanKeyData key;
+ Relation rel;
+ TableScanDesc scan;
+ HeapTuple tuple;
+
+ ScanKeyInit(&key,
+ Anum_pg_variable_varnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(namespaceId));
+
+ rel = table_open(VariableRelationId, AccessShareLock);
+ scan = table_beginscan_catalog(rel, 1, &key);
+
+ while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Oid oid = ((Form_pg_proc) GETSTRUCT(tuple))->oid;
+
+ objects = lappend_oid(objects, oid);
+ }
+
+ table_endscan(scan);
+ table_close(rel, AccessShareLock);
+ }
+ break;
default:
/* should not happen */
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
@@ -1022,6 +1071,10 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s
all_privileges = ACL_ALL_RIGHTS_LARGEOBJECT;
errormsg = gettext_noop("invalid privilege type %s for large object");
break;
+ case OBJECT_VARIABLE:
+ all_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ errormsg = gettext_noop("invalid privilege type %s for session variable");
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) action->objtype);
@@ -1222,6 +1275,11 @@ SetDefaultACL(InternalDefaultACL *iacls)
if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS)
this_privileges = ACL_ALL_RIGHTS_LARGEOBJECT;
break;
+ case OBJECT_VARIABLE:
+ objtype = DEFACLOBJ_VARIABLE;
+ if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS)
+ this_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized object type: %d",
@@ -1469,6 +1527,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid)
case DEFACLOBJ_LARGEOBJECT:
iacls.objtype = OBJECT_LARGEOBJECT;
break;
+ case DEFACLOBJ_VARIABLE:
+ iacls.objtype = OBJECT_VARIABLE;
+ break;
default:
/* Shouldn't get here */
elog(ERROR, "unexpected default ACL type: %d",
@@ -1529,6 +1590,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid)
case ParameterAclRelationId:
istmt.objtype = OBJECT_PARAMETER_ACL;
break;
+ case VariableRelationId:
+ istmt.objtype = OBJECT_VARIABLE;
+ break;
default:
elog(ERROR, "unexpected object class %u", classid);
break;
@@ -2762,6 +2826,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_TYPE:
msg = gettext_noop("permission denied for type %s");
break;
+ case OBJECT_VARIABLE:
+ msg = gettext_noop("permission denied for session variable %s");
+ break;
case OBJECT_VIEW:
msg = gettext_noop("permission denied for view %s");
break;
@@ -2784,7 +2851,6 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_TSPARSER:
case OBJECT_TSTEMPLATE:
case OBJECT_USER_MAPPING:
- case OBJECT_VARIABLE:
elog(ERROR, "unsupported object type: %d", objtype);
}
@@ -3025,6 +3091,8 @@ pg_aclmask(ObjectType objtype, Oid object_oid, AttrNumber attnum, Oid roleid,
return ACL_NO_RIGHTS;
case OBJECT_TYPE:
return object_aclmask(TypeRelationId, object_oid, roleid, mask, how);
+ case OBJECT_VARIABLE:
+ return object_aclmask(VariableRelationId, object_oid, roleid, mask, how);
default:
elog(ERROR, "unrecognized object type: %d",
(int) objtype);
@@ -4288,6 +4356,10 @@ get_user_default_acl(ObjectType objtype, Oid ownerId, Oid nsp_oid)
defaclobjtype = DEFACLOBJ_LARGEOBJECT;
break;
+ case OBJECT_VARIABLE:
+ defaclobjtype = DEFACLOBJ_VARIABLE;
+ break;
+
default:
return NULL;
}
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index ac1f8a4db3b..32cd078c162 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2029,17 +2029,21 @@ get_object_address_defacl(List *object, bool missing_ok)
case DEFACLOBJ_LARGEOBJECT:
objtype_str = "large objects";
break;
+ case DEFACLOBJ_VARIABLE:
+ objtype_str = "variables";
+ break;
default:
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized default ACL object type \"%c\"", objtype),
- errhint("Valid object types are \"%c\", \"%c\", \"%c\", \"%c\", \"%c\", \"%c\".",
+ errhint("Valid object types are \"%c\", \"%c\", \"%c\", \"%c\", \"%c\", \"%c\", \"%c\".",
DEFACLOBJ_RELATION,
DEFACLOBJ_SEQUENCE,
DEFACLOBJ_FUNCTION,
DEFACLOBJ_TYPE,
DEFACLOBJ_NAMESPACE,
- DEFACLOBJ_LARGEOBJECT)));
+ DEFACLOBJ_LARGEOBJECT,
+ DEFACLOBJ_VARIABLE)));
}
/*
@@ -3921,6 +3925,16 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok)
_("default privileges on new large objects belonging to role %s"),
rolename);
break;
+ case DEFACLOBJ_VARIABLE:
+ if (nspname)
+ appendStringInfo(&buffer,
+ _("default privileges on new session variables belonging to role %s in schema %s"),
+ rolename, nspname);
+ else
+ appendStringInfo(&buffer,
+ _("default privileges on new session variables belonging to role %s"),
+ rolename);
+ break;
default:
/* shouldn't get here */
if (nspname)
@@ -5851,6 +5865,10 @@ getObjectIdentityParts(const ObjectAddress *object,
appendStringInfoString(&buffer,
" on large objects");
break;
+ case DEFACLOBJ_VARIABLE:
+ appendStringInfoString(&buffer,
+ " on session variables");
+ break;
}
if (objname)
diff --git a/src/backend/catalog/pg_variable.c b/src/backend/catalog/pg_variable.c
index bd6a29a79e5..d8ede4fa8c8 100644
--- a/src/backend/catalog/pg_variable.c
+++ b/src/backend/catalog/pg_variable.c
@@ -38,6 +38,7 @@ create_variable(const char *varName,
Oid varCollation,
bool if_not_exists)
{
+ Acl *varacl;
NameData varname;
bool nulls[Natts_pg_variable];
Datum values[Natts_pg_variable];
@@ -97,7 +98,12 @@ create_variable(const char *varName,
values[Anum_pg_variable_varowner - 1] = ObjectIdGetDatum(varOwner);
values[Anum_pg_variable_varcollation - 1] = ObjectIdGetDatum(varCollation);
- nulls[Anum_pg_variable_varacl - 1] = true;
+ varacl = get_user_default_acl(OBJECT_VARIABLE, varOwner,
+ varNamespace);
+ if (varacl != NULL)
+ values[Anum_pg_variable_varacl - 1] = PointerGetDatum(varacl);
+ else
+ nulls[Anum_pg_variable_varacl - 1] = true;
tupdesc = RelationGetDescr(rel);
@@ -131,6 +137,9 @@ create_variable(const char *varName,
/* dependency on owner */
recordDependencyOnOwner(VariableRelationId, varid, varOwner);
+ /* dependencies on roles mentioned in default ACL */
+ recordDependencyOnNewAcl(VariableRelationId, varid, 0, varOwner, varacl);
+
/* dependency on extension */
recordDependencyOnCurrentExtension(&myself, false);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index cee1bb78f7b..a0d19e295c9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -791,7 +791,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNLISTEN UNLOGGED UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARIABLE
- VARYING VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
+ VARIABLES VARYING VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -8066,6 +8066,14 @@ privilege_target:
n->objs = $2;
$$ = n;
}
+ | VARIABLE qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
+ n->objtype = OBJECT_VARIABLE;
+ n->objs = $2;
+ $$ = n;
+ }
| ALL TABLES IN_P SCHEMA name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
@@ -8111,6 +8119,14 @@ privilege_target:
n->objs = $5;
$$ = n;
}
+ | ALL VARIABLES IN_P SCHEMA name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_ALL_IN_SCHEMA;
+ n->objtype = OBJECT_VARIABLE;
+ n->objs = $5;
+ $$ = n;
+ }
;
@@ -8309,6 +8325,7 @@ defacl_privilege_target:
| TYPES_P { $$ = OBJECT_TYPE; }
| SCHEMAS { $$ = OBJECT_SCHEMA; }
| LARGE_P OBJECTS_P { $$ = OBJECT_LARGEOBJECT; }
+ | VARIABLES { $$ = OBJECT_VARIABLE; }
;
@@ -18190,6 +18207,7 @@ unreserved_keyword:
| VALIDATOR
| VALUE_P
| VARIABLE
+ | VARIABLES
| VARYING
| VERSION_P
| VIEW
@@ -18847,6 +18865,7 @@ bare_label_keyword:
| VALUES
| VARCHAR
| VARIABLE
+ | VARIABLES
| VARIADIC
| VERBOSE
| VERSION_P
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index fbcd64a2609..9828382b900 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -31,6 +31,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_variable.h"
#include "commands/proclang.h"
#include "commands/tablespace.h"
#include "common/hashfn.h"
@@ -128,6 +129,8 @@ static AclMode convert_type_priv_string(text *priv_type_text);
static AclMode convert_parameter_priv_string(text *priv_text);
static AclMode convert_largeobject_priv_string(text *priv_type_text);
static AclMode convert_role_priv_string(text *priv_type_text);
+static Oid convert_session_variable_name(text *varname);
+static AclMode convert_session_variable_priv_string(text *priv_type_text);
static AclResult pg_role_aclcheck(Oid role_oid, Oid roleid, AclMode mode);
static void RoleMembershipCacheCallback(Datum arg, int cacheid, uint32 hashvalue);
@@ -867,6 +870,10 @@ acldefault(ObjectType objtype, Oid ownerId)
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_PARAMETER_ACL;
break;
+ case OBJECT_VARIABLE:
+ world_default = ACL_NO_RIGHTS;
+ owner_default = ACL_ALL_RIGHTS_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized object type: %d", (int) objtype);
world_default = ACL_NO_RIGHTS; /* keep compiler quiet */
@@ -964,6 +971,9 @@ acldefault_sql(PG_FUNCTION_ARGS)
case 'T':
objtype = OBJECT_TYPE;
break;
+ case 'V':
+ objtype = OBJECT_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized object type abbreviation: %c", objtypec);
}
@@ -5032,6 +5042,217 @@ pg_role_aclcheck(Oid role_oid, Oid roleid, AclMode mode)
return ACLCHECK_NO_PRIV;
}
+/*
+ * has_session_variable_privilege variants
+ * These are all named "has_session_variable_privilege" at the SQL level.
+ * They take various combinations of variable name, variable OID,
+ * user name, user OID, or implicit user = current_user.
+ *
+ * The result is a boolean value: true if user has the indicated
+ * privilege, false if not, or NULL if session variable doesn't
+ * exists.
+ */
+
+/*
+ * has_session_variable_privilege_name_name
+ * Check user privileges on a session variable given
+ * name username, text session variable name, and text priv name.
+ */
+Datum
+has_session_variable_privilege_name_name(PG_FUNCTION_ARGS)
+{
+ Name rolename = PG_GETARG_NAME(0);
+ text *varname = PG_GETARG_TEXT_PP(1);
+ text *priv_type_text = PG_GETARG_TEXT_PP(2);
+ Oid roleid;
+ Oid varid;
+ AclMode mode;
+ AclResult aclresult;
+ bool is_missing = false;
+
+ roleid = get_role_oid_or_public(NameStr(*rolename));
+ mode = convert_session_variable_priv_string(priv_type_text);
+ varid = convert_session_variable_name(varname);
+
+ aclresult = object_aclcheck_ext(VariableRelationId, varid,
+ roleid, mode,
+ &is_missing);
+
+ if (is_missing)
+ PG_RETURN_NULL();
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_session_variable_privilege_name
+ * Check user privileges on a session variable given
+ * text session variable and text priv name.
+ * current_user is assumed
+ */
+Datum
+has_session_variable_privilege_name(PG_FUNCTION_ARGS)
+{
+ text *varname = PG_GETARG_TEXT_PP(0);
+ text *priv_type_text = PG_GETARG_TEXT_PP(1);
+ Oid roleid;
+ Oid varid;
+ AclMode mode;
+ AclResult aclresult;
+ bool is_missing = false;
+
+ roleid = GetUserId();
+ mode = convert_session_variable_priv_string(priv_type_text);
+ varid = convert_session_variable_name(varname);
+
+ aclresult = object_aclcheck_ext(VariableRelationId, varid,
+ roleid, mode,
+ &is_missing);
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_session_variable_privilege_name_id
+ * Check user privileges on a session variable given
+ * name usename, session variable oid, and text priv name.
+ */
+Datum
+has_session_variable_privilege_name_id(PG_FUNCTION_ARGS)
+{
+ Name username = PG_GETARG_NAME(0);
+ Oid varid = PG_GETARG_OID(1);
+ text *priv_type_text = PG_GETARG_TEXT_PP(2);
+ Oid roleid;
+ AclMode mode;
+ AclResult aclresult;
+ bool is_missing = false;
+
+ roleid = get_role_oid_or_public(NameStr(*username));
+ mode = convert_session_variable_priv_string(priv_type_text);
+
+ aclresult = object_aclcheck_ext(VariableRelationId, varid,
+ roleid, mode,
+ &is_missing);
+
+ if (is_missing)
+ PG_RETURN_NULL();
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_session_variable_privilege_id
+ * Check user privileges on a session variable given
+ * session variable oid, and text priv name.
+ * current_user is assumed
+ */
+Datum
+has_session_variable_privilege_id(PG_FUNCTION_ARGS)
+{
+ Oid varid = PG_GETARG_OID(0);
+ text *priv_type_text = PG_GETARG_TEXT_PP(1);
+ Oid roleid;
+ AclMode mode;
+ AclResult aclresult;
+ bool is_missing = false;
+
+ roleid = GetUserId();
+ mode = convert_session_variable_priv_string(priv_type_text);
+
+ aclresult = object_aclcheck_ext(VariableRelationId, varid,
+ roleid, mode,
+ &is_missing);
+
+ if (is_missing)
+ PG_RETURN_NULL();
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_session_variable_privilege_id_name
+ * Check user privileges on a session variable given
+ * roleid, text session variable name, and text priv name.
+ */
+Datum
+has_session_variable_privilege_id_name(PG_FUNCTION_ARGS)
+{
+ Oid roleid = PG_GETARG_OID(0);
+ text *varname = PG_GETARG_TEXT_PP(1);
+ text *priv_type_text = PG_GETARG_TEXT_PP(2);
+ Oid varid;
+ AclMode mode;
+ AclResult aclresult;
+ bool is_missing = false;
+
+ mode = convert_session_variable_priv_string(priv_type_text);
+ varid = convert_session_variable_name(varname);
+
+ aclresult = object_aclcheck_ext(VariableRelationId, varid,
+ roleid, mode,
+ &is_missing);
+
+ if (is_missing)
+ PG_RETURN_NULL();
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * has_session_variable_privilege_id_id
+ * Check user privileges on a session variable given
+ * roleid, session variable oid, and text priv name.
+ */
+Datum
+has_session_variable_privilege_id_id(PG_FUNCTION_ARGS)
+{
+ Oid roleid = PG_GETARG_OID(0);
+ Oid varid = PG_GETARG_OID(1);
+ text *priv_type_text = PG_GETARG_TEXT_PP(2);
+ AclMode mode;
+ AclResult aclresult;
+ bool is_missing = false;
+
+ mode = convert_session_variable_priv_string(priv_type_text);
+
+ aclresult = object_aclcheck_ext(VariableRelationId, varid,
+ roleid, mode,
+ &is_missing);
+
+ if (is_missing)
+ PG_RETURN_NULL();
+
+ PG_RETURN_BOOL(aclresult == ACLCHECK_OK);
+}
+
+/*
+ * Given a session variable name expressed as a string, look it up and return
+ * Oid
+ */
+static Oid
+convert_session_variable_name(text *varname)
+{
+ return LookupVariableFromNameList(textToQualifiedNameList(varname), true);
+}
+
+/*
+ * convert_variable_priv_string
+ * Convert text string to AclMode value.
+ */
+static AclMode
+convert_session_variable_priv_string(text *priv_type_text)
+{
+ static const priv_map session_variable_priv_map[] = {
+ {"SELECT", ACL_SELECT},
+ {"SELECT WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_SELECT)},
+ {"UPDATE", ACL_UPDATE},
+ {"UPDATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_UPDATE)},
+ {NULL, 0}
+ };
+
+ return convert_any_priv_string(priv_type_text, session_variable_priv_map);
+}
/*
* initialization function (called by InitPostgres)
diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h
index ce6e5098eaf..087d35b943d 100644
--- a/src/include/catalog/pg_default_acl.h
+++ b/src/include/catalog/pg_default_acl.h
@@ -69,6 +69,7 @@ MAKE_SYSCACHE(DEFACLROLENSPOBJ, pg_default_acl_role_nsp_obj_index, 8);
#define DEFACLOBJ_TYPE 'T' /* type */
#define DEFACLOBJ_NAMESPACE 'n' /* namespace */
#define DEFACLOBJ_LARGEOBJECT 'L' /* large object */
+#define DEFACLOBJ_VARIABLE 'V' /* variable */
#endif /* EXPOSE_TO_CLIENT_CODE */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f76..1ea3f51841c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5479,6 +5479,26 @@
prorettype => 'bool', proargtypes => 'oid oid text',
prosrc => 'has_largeobject_privilege_id_id' },
+{ oid => '9613', descr => 'user privilege on session variable by username, seq name',
+ proname => 'has_session_variable_privilege', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'name text text',
+ prosrc => 'has_session_variable_privilege_name_name' },
+{ oid => '9614', descr => 'user privilege on session variable by username, seq oid',
+ proname => 'has_session_variable_privilege', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'name oid text', prosrc => 'has_session_variable_privilege_name_id' },
+{ oid => '9615', descr => 'user privilege on session variable by user oid, seq name',
+ proname => 'has_session_variable_privilege', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'oid text text', prosrc => 'has_session_variable_privilege_id_name' },
+{ oid => '9616', descr => 'user privilege on session variable by user oid, seq oid',
+ proname => 'has_session_variable_privilege', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'oid oid text', prosrc => 'has_session_variable_privilege_id_id' },
+{ oid => '9617', descr => 'current user privilege on session variable by seq name',
+ proname => 'has_session_variable_privilege', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'text text', prosrc => 'has_session_variable_privilege_name' },
+{ oid => '9618', descr => 'current user privilege on session variable by seq oid',
+ proname => 'has_session_variable_privilege', provolatile => 's', prorettype => 'bool',
+ proargtypes => 'oid text', prosrc => 'has_session_variable_privilege_id' },
+
{ oid => '3355', descr => 'I/O',
proname => 'pg_ndistinct_in', prorettype => 'pg_ndistinct',
proargtypes => 'cstring', prosrc => 'pg_ndistinct_in' },
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 5e6d3aff8af..7e7d262b1bb 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -489,6 +489,7 @@ PG_KEYWORD("value", VALUE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("values", VALUES, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("varchar", VARCHAR, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("variable", VARIABLE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("variables", VARIABLES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 01ae5b719fd..5e1a8a82e90 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -169,6 +169,7 @@ typedef struct ArrayType Acl;
#define ACL_ALL_RIGHTS_SCHEMA (ACL_USAGE|ACL_CREATE)
#define ACL_ALL_RIGHTS_TABLESPACE (ACL_CREATE)
#define ACL_ALL_RIGHTS_TYPE (ACL_USAGE)
+#define ACL_ALL_RIGHTS_VARIABLE (ACL_SELECT|ACL_UPDATE)
/* operation codes for pg_*_aclmask */
typedef enum
diff --git a/src/test/regress/expected/session_variables_acl.out b/src/test/regress/expected/session_variables_acl.out
new file mode 100644
index 00000000000..f2219529916
--- /dev/null
+++ b/src/test/regress/expected/session_variables_acl.out
@@ -0,0 +1,335 @@
+-- check access rights and supported ALTER
+CREATE SCHEMA svartest_acl;
+CREATE ROLE regress_variable_owner_acl;
+CREATE ROLE regress_variable_reader_acl;
+GRANT ALL ON SCHEMA svartest_acl TO regress_variable_owner_acl;
+GRANT ALL ON SCHEMA public TO regress_variable_owner_acl;
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner_acl
+ IN SCHEMA svartest_acl
+ GRANT SELECT ON VARIABLES TO regress_variable_reader_acl;
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner_acl
+ IN SCHEMA svartest_acl
+ GRANT UPDATE ON VARIABLES TO regress_variable_reader_acl;
+-- should to fail
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner_acl
+ IN SCHEMA svartest_acl
+ GRANT INSERT ON VARIABLES TO regress_variable_reader_acl;
+ERROR: invalid privilege type INSERT for session variable
+-- should to fail
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner_acl
+ IN SCHEMA svartest_acl
+ GRANT DELETE ON VARIABLES TO regress_variable_reader_acl;
+ERROR: invalid privilege type DELETE for session variable
+-- creating variable with default privileges
+SET ROLE TO regress_variable_owner_acl;
+CREATE VARIABLE svartest_acl.sesvar20 AS int;
+SET ROLE TO DEFAULT;
+-- should be ok. since ALTER DEFAULT PRIVILEGES
+-- allow regress_variable_reader_acl to have SELECT priviledge
+SELECT has_session_variable_privilege('regress_variable_reader_acl', 'svartest_acl.sesvar20', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_reader_acl', 'svartest_acl.sesvar20', 'UPDATE'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+DROP VARIABLE svartest_acl.sesvar20;
+DROP SCHEMA svartest_acl;
+DROP ROLE regress_variable_reader_acl;
+--
+-- begin of check GRANT WITH GRANT OPTION and REVOKE GRANTED BY
+--
+CREATE ROLE regress_variable_r1_acl;
+CREATE ROLE regress_variable_r2_acl;
+SET ROLE TO regress_variable_owner_acl;
+CREATE VARIABLE sesvar22_acl AS int; --sesvar22_acl will owned by regress_variable_owner_acl
+GRANT SELECT ON VARIABLE sesvar22_acl TO regress_variable_r1_acl WITH GRANT OPTION;
+SET ROLE TO regress_variable_r1_acl;
+GRANT SELECT ON VARIABLE sesvar22_acl TO regress_variable_r2_acl WITH GRANT OPTION;
+SET ROLE TO DEFAULT;
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'public.sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+REVOKE ALL PRIVILEGES ON VARIABLE sesvar22_acl FROM regress_variable_r1_acl CASCADE;
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SET ROLE TO regress_variable_owner_acl;
+-- should to fail
+GRANT INSERT ON VARIABLE sesvar22_acl TO regress_variable_r2_acl;
+ERROR: invalid privilege type INSERT for session variable
+GRANT DELETE ON VARIABLE sesvar22_acl TO regress_variable_r2_acl;
+ERROR: invalid privilege type DELETE for session variable
+-- should be ok
+GRANT SELECT ON VARIABLE sesvar22_acl TO regress_variable_r2_acl;
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'public.sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+REVOKE ALL ON VARIABLE sesvar22_acl FROM regress_variable_r2_acl GRANTED BY regress_variable_owner_acl;
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_owner_acl', 'public.sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SET ROLE TO DEFAULT;
+DROP VARIABLE sesvar22_acl;
+--
+-- end of check GRANT WITH GRANT OPTION and REVOKE GRANTED BY
+--
+--
+-- begin of test: GRANT|REVOKE SELECT|UPDATE ON ALL VARIABLES IN SCHEMA
+--
+CREATE SCHEMA svartest_acl;
+GRANT ALL ON SCHEMA svartest_acl TO regress_variable_owner_acl;
+SET ROLE TO regress_variable_owner_acl;
+CREATE VARIABLE svartest_acl.sesvar20 AS int;
+CREATE VARIABLE svartest_acl.sesvar21 AS int;
+GRANT SELECT ON ALL VARIABLES IN SCHEMA svartest_acl TO regress_variable_r1_acl;
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'svartest_acl.sesvar20', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'svartest_acl.sesvar21', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+REVOKE SELECT ON ALL VARIABLES IN SCHEMA svartest_acl FROM regress_variable_r1_acl;
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'svartest_acl.sesvar20', 'SELECT'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'svartest_acl.sesvar21', 'SELECT'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SET ROLE TO DEFAULT;
+DROP VARIABLE svartest_acl.sesvar20;
+DROP VARIABLE svartest_acl.sesvar21;
+DROP SCHEMA svartest_acl;
+--
+-- end of test: GRANT|REVOKE SELECT|UPDATE ON ALL VARIABLES IN SCHEMA
+--
+--
+-- function has_session_variable_privilege have various kind of signature.
+-- the following are extensive test for it.
+--
+SET ROLE TO regress_variable_owner_acl;
+CREATE VARIABLE public.sesvar22_acl AS int;
+SET search_path TO public;
+GRANT SELECT ON VARIABLE public.sesvar22_acl TO regress_variable_r1_acl;
+GRANT SELECT, UPDATE ON VARIABLE public.sesvar22_acl TO regress_variable_r2_acl;
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT');
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.notexists', 'SELECT') IS NULL;
+ ?column?
+----------
+ t
+(1 row)
+
+SET ROLE TO regress_variable_r1_acl;
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'sesvar22_acl', 'UPDATE'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'sesvar22_acl', 'UPDATE'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('sesvar22_acl', 'UPDATE'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT oid AS varid
+ FROM pg_variable
+ WHERE varname = 'sesvar22_acl' AND varnamespace = 'public'::regnamespace \gset
+SELECT has_session_variable_privilege('sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('sesvar22_acl', 'UPDATE'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', :varid, 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', :varid, 'UPDATE'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl', :varid, 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl', :varid, 'UPDATE'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege(:varid, 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege(:varid, 'UPDATE'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl'::regrole, 'sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl'::regrole, 'sesvar22_acl', 'UPDATE'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl'::regrole, 'sesvar22_acl', 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl'::regrole, 'sesvar22_acl', 'UPDATE'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl'::regrole, :varid, 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl'::regrole, :varid, 'UPDATE'); -- f
+ has_session_variable_privilege
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl'::regrole, :varid, 'SELECT'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2_acl'::regrole, :varid, 'UPDATE'); -- t
+ has_session_variable_privilege
+--------------------------------
+ t
+(1 row)
+
+--
+-- end of function has_session_variable_privilege tests.
+--
+SET ROLE TO DEFAULT;
+SET search_path TO DEFAULT;
+DROP VARIABLE public.sesvar22_acl;
+DROP ROLE regress_variable_r1_acl;
+DROP ROLE regress_variable_r2_acl;
+REVOKE ALL ON SCHEMA public FROM regress_variable_owner_acl;
+DROP ROLE regress_variable_owner_acl;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 1bcf69031da..d8000da3f86 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -115,7 +115,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson
# NB: temp.sql does reconnects which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml session_variables_ddl
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml session_variables_ddl session_variables_acl
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/session_variables_acl.sql b/src/test/regress/sql/session_variables_acl.sql
new file mode 100644
index 00000000000..4b3653cd4ed
--- /dev/null
+++ b/src/test/regress/sql/session_variables_acl.sql
@@ -0,0 +1,182 @@
+-- check access rights and supported ALTER
+CREATE SCHEMA svartest_acl;
+CREATE ROLE regress_variable_owner_acl;
+CREATE ROLE regress_variable_reader_acl;
+
+GRANT ALL ON SCHEMA svartest_acl TO regress_variable_owner_acl;
+GRANT ALL ON SCHEMA public TO regress_variable_owner_acl;
+
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner_acl
+ IN SCHEMA svartest_acl
+ GRANT SELECT ON VARIABLES TO regress_variable_reader_acl;
+
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner_acl
+ IN SCHEMA svartest_acl
+ GRANT UPDATE ON VARIABLES TO regress_variable_reader_acl;
+
+-- should to fail
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner_acl
+ IN SCHEMA svartest_acl
+ GRANT INSERT ON VARIABLES TO regress_variable_reader_acl;
+
+-- should to fail
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner_acl
+ IN SCHEMA svartest_acl
+ GRANT DELETE ON VARIABLES TO regress_variable_reader_acl;
+
+-- creating variable with default privileges
+SET ROLE TO regress_variable_owner_acl;
+CREATE VARIABLE svartest_acl.sesvar20 AS int;
+SET ROLE TO DEFAULT;
+
+-- should be ok. since ALTER DEFAULT PRIVILEGES
+-- allow regress_variable_reader_acl to have SELECT priviledge
+SELECT has_session_variable_privilege('regress_variable_reader_acl', 'svartest_acl.sesvar20', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_reader_acl', 'svartest_acl.sesvar20', 'UPDATE'); -- t
+
+DROP VARIABLE svartest_acl.sesvar20;
+DROP SCHEMA svartest_acl;
+DROP ROLE regress_variable_reader_acl;
+
+--
+-- begin of check GRANT WITH GRANT OPTION and REVOKE GRANTED BY
+--
+CREATE ROLE regress_variable_r1_acl;
+CREATE ROLE regress_variable_r2_acl;
+
+SET ROLE TO regress_variable_owner_acl;
+CREATE VARIABLE sesvar22_acl AS int; --sesvar22_acl will owned by regress_variable_owner_acl
+
+GRANT SELECT ON VARIABLE sesvar22_acl TO regress_variable_r1_acl WITH GRANT OPTION;
+SET ROLE TO regress_variable_r1_acl;
+GRANT SELECT ON VARIABLE sesvar22_acl TO regress_variable_r2_acl WITH GRANT OPTION;
+SET ROLE TO DEFAULT;
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'public.sesvar22_acl', 'SELECT'); -- t
+
+REVOKE ALL PRIVILEGES ON VARIABLE sesvar22_acl FROM regress_variable_r1_acl CASCADE;
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+
+SET ROLE TO regress_variable_owner_acl;
+
+-- should to fail
+GRANT INSERT ON VARIABLE sesvar22_acl TO regress_variable_r2_acl;
+GRANT DELETE ON VARIABLE sesvar22_acl TO regress_variable_r2_acl;
+
+-- should be ok
+GRANT SELECT ON VARIABLE sesvar22_acl TO regress_variable_r2_acl;
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'public.sesvar22_acl', 'SELECT'); -- t
+
+REVOKE ALL ON VARIABLE sesvar22_acl FROM regress_variable_r2_acl GRANTED BY regress_variable_owner_acl;
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'public.sesvar22_acl', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_owner_acl', 'public.sesvar22_acl', 'SELECT'); -- t
+SET ROLE TO DEFAULT;
+
+DROP VARIABLE sesvar22_acl;
+--
+-- end of check GRANT WITH GRANT OPTION and REVOKE GRANTED BY
+--
+
+--
+-- begin of test: GRANT|REVOKE SELECT|UPDATE ON ALL VARIABLES IN SCHEMA
+--
+CREATE SCHEMA svartest_acl;
+GRANT ALL ON SCHEMA svartest_acl TO regress_variable_owner_acl;
+SET ROLE TO regress_variable_owner_acl;
+
+CREATE VARIABLE svartest_acl.sesvar20 AS int;
+CREATE VARIABLE svartest_acl.sesvar21 AS int;
+
+GRANT SELECT ON ALL VARIABLES IN SCHEMA svartest_acl TO regress_variable_r1_acl;
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'svartest_acl.sesvar20', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'svartest_acl.sesvar21', 'SELECT'); -- t
+
+REVOKE SELECT ON ALL VARIABLES IN SCHEMA svartest_acl FROM regress_variable_r1_acl;
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'svartest_acl.sesvar20', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'svartest_acl.sesvar21', 'SELECT'); -- f
+
+SET ROLE TO DEFAULT;
+DROP VARIABLE svartest_acl.sesvar20;
+DROP VARIABLE svartest_acl.sesvar21;
+DROP SCHEMA svartest_acl;
+--
+-- end of test: GRANT|REVOKE SELECT|UPDATE ON ALL VARIABLES IN SCHEMA
+--
+
+--
+-- function has_session_variable_privilege have various kind of signature.
+-- the following are extensive test for it.
+--
+SET ROLE TO regress_variable_owner_acl;
+
+CREATE VARIABLE public.sesvar22_acl AS int;
+
+SET search_path TO public;
+
+GRANT SELECT ON VARIABLE public.sesvar22_acl TO regress_variable_r1_acl;
+GRANT SELECT, UPDATE ON VARIABLE public.sesvar22_acl TO regress_variable_r2_acl;
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.sesvar22_acl', 'SELECT');
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'public.notexists', 'SELECT') IS NULL;
+
+SET ROLE TO regress_variable_r1_acl;
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'sesvar22_acl', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r1_acl', 'sesvar22_acl', 'UPDATE'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'sesvar22_acl', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r2_acl', 'sesvar22_acl', 'UPDATE'); -- t
+
+SELECT has_session_variable_privilege('sesvar22_acl', 'SELECT'); -- t
+SELECT has_session_variable_privilege('sesvar22_acl', 'UPDATE'); -- f
+
+SELECT oid AS varid
+ FROM pg_variable
+ WHERE varname = 'sesvar22_acl' AND varnamespace = 'public'::regnamespace \gset
+
+SELECT has_session_variable_privilege('sesvar22_acl', 'SELECT'); -- t
+SELECT has_session_variable_privilege('sesvar22_acl', 'UPDATE'); -- f
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl', :varid, 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r1_acl', :varid, 'UPDATE'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2_acl', :varid, 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r2_acl', :varid, 'UPDATE'); -- t
+
+SELECT has_session_variable_privilege(:varid, 'SELECT'); -- t
+SELECT has_session_variable_privilege(:varid, 'UPDATE'); -- f
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl'::regrole, 'sesvar22_acl', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r1_acl'::regrole, 'sesvar22_acl', 'UPDATE'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2_acl'::regrole, 'sesvar22_acl', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r2_acl'::regrole, 'sesvar22_acl', 'UPDATE'); -- t
+
+SELECT has_session_variable_privilege('regress_variable_r1_acl'::regrole, :varid, 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r1_acl'::regrole, :varid, 'UPDATE'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2_acl'::regrole, :varid, 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r2_acl'::regrole, :varid, 'UPDATE'); -- t
+--
+-- end of function has_session_variable_privilege tests.
+--
+
+SET ROLE TO DEFAULT;
+SET search_path TO DEFAULT;
+
+DROP VARIABLE public.sesvar22_acl;
+
+DROP ROLE regress_variable_r1_acl;
+DROP ROLE regress_variable_r2_acl;
+
+REVOKE ALL ON SCHEMA public FROM regress_variable_owner_acl;
+DROP ROLE regress_variable_owner_acl;
--
2.51.1