Horton

session_variable_priv.diff

application/x-patch

Download
Filename: session_variable_priv.diff
Type: application/x-patch
Part: 0
Message: Re: Re: proposal: schema variables

Patch

Same data as JSON: GET /api/v1/attachments/:id/patch the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes. API reference →
Format: unified
File+−
src/test/regress/expected/session_variables.out 82 40
src/test/regress/sql/session_variables.sql 27 21 
diff --git a/src/test/regress/expected/session_variables.out b/src/test/regress/expected/session_variables.out
index 9f75128c42..4873ac5438 100644
--- a/src/test/regress/expected/session_variables.out
+++ b/src/test/regress/expected/session_variables.out
@@ -115,85 +115,126 @@ ALTER DEFAULT PRIVILEGES
 SET ROLE TO regress_variable_owner;
 CREATE VARIABLE svartest.var1 AS int;
 SET ROLE TO DEFAULT;
-\dV+ svartest.var1
-                                                        List of variables
-  Schema  | Name |  Type   | Collation |         Owner          |                Access privileges                 | Description 
-----------+------+---------+-----------+------------------------+--------------------------------------------------+-------------
- svartest | var1 | integer |           | regress_variable_owner | regress_variable_owner=rw/regress_variable_owner+| 
-          |      |         |           |                        | regress_variable_reader=r/regress_variable_owner | 
+--should be ok. since ALTER DEFAULT PRIVILEGES
+--allow regress_variable_reader to have SELECT priviledge
+SELECT has_session_variable_privilege('regress_variable_reader', 'svartest.var1', 'SELECT'); -- t
+ has_session_variable_privilege 
+--------------------------------
+ t
 (1 row)
 
 DROP VARIABLE svartest.var1;
 DROP SCHEMA svartest;
 DROP ROLE regress_variable_reader;
--- check WITH GRANT OPTION
+-----begin of check GRANT WITH GRANT OPTION and REVOKE GRANTED BY
 CREATE ROLE regress_variable_r1;
 CREATE ROLE regress_variable_r2;
 SET ROLE TO regress_variable_owner;
-CREATE VARIABLE var1 AS int;
+CREATE VARIABLE var1 AS int;      --var1 will owned by regress_variable_owner
 GRANT SELECT ON VARIABLE var1 TO regress_variable_r1 WITH GRANT OPTION;
 SET ROLE TO regress_variable_r1;
 GRANT SELECT ON VARIABLE var1 TO regress_variable_r2 WITH GRANT OPTION;
 SET ROLE TO DEFAULT;
-SELECT varacl FROM pg_variable WHERE varname = 'var1';
-                                                                   varacl                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------
- {regress_variable_owner=rw/regress_variable_owner,regress_variable_r1=r*/regress_variable_owner,regress_variable_r2=r*/regress_variable_r1}
+SELECT has_session_variable_privilege('regress_variable_r1', 'public.var1', 'SELECT'); -- t
+ has_session_variable_privilege 
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2', 'public.var1', 'SELECT'); -- t
+ has_session_variable_privilege 
+--------------------------------
+ t
 (1 row)
 
 REVOKE ALL PRIVILEGES ON VARIABLE var1 FROM regress_variable_r1 CASCADE;
-SELECT varacl FROM pg_variable WHERE varname = 'var1';
-                       varacl                       
-----------------------------------------------------
- {regress_variable_owner=rw/regress_variable_owner}
+SELECT has_session_variable_privilege('regress_variable_r1', 'public.var1', 'SELECT'); -- f
+ has_session_variable_privilege 
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2', 'public.var1', 'SELECT'); -- f
+ has_session_variable_privilege 
+--------------------------------
+ f
 (1 row)
 
-SET ROLE TO regress_variable_owner;
-GRANT SELECT ON VARIABLE var1 TO regress_variable_r1 WITH GRANT OPTION;
-SET ROLE TO regress_variable_r1;
-GRANT SELECT ON VARIABLE var1 TO regress_variable_r2 WITH GRANT OPTION;
 SET ROLE TO regress_variable_owner;
 GRANT SELECT ON VARIABLE var1 TO regress_variable_r2;
-SELECT varacl FROM pg_variable WHERE varname = 'var1';
-                                                                                          varacl                                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- {regress_variable_owner=rw/regress_variable_owner,regress_variable_r1=r*/regress_variable_owner,regress_variable_r2=r*/regress_variable_r1,regress_variable_r2=r/regress_variable_owner}
+SELECT has_session_variable_privilege('regress_variable_r1', 'public.var1', 'SELECT'); -- f
+ has_session_variable_privilege 
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2', 'public.var1', 'SELECT'); -- t
+ has_session_variable_privilege 
+--------------------------------
+ t
 (1 row)
 
 REVOKE ALL ON VARIABLE var1 FROM regress_variable_r2 GRANTED BY regress_variable_owner;
-SELECT varacl FROM pg_variable WHERE varname = 'var1';
-                                                                   varacl                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------
- {regress_variable_owner=rw/regress_variable_owner,regress_variable_r1=r*/regress_variable_owner,regress_variable_r2=r*/regress_variable_r1}
+SELECT has_session_variable_privilege('regress_variable_r1', 'public.var1', 'SELECT'); -- f
+ has_session_variable_privilege 
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r2', 'public.var1', 'SELECT'); -- f
+ has_session_variable_privilege 
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_owner', 'public.var1', 'SELECT'); -- t
+ has_session_variable_privilege 
+--------------------------------
+ t
 (1 row)
 
 SET ROLE TO DEFAULT;
 DROP VARIABLE var1;
+-----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;
 GRANT ALL ON SCHEMA svartest TO regress_variable_owner;
 SET ROLE TO regress_variable_owner;
 CREATE VARIABLE svartest.var1 AS int;
 CREATE VARIABLE svartest.var2 AS int;
 GRANT SELECT ON ALL VARIABLES IN SCHEMA svartest TO regress_variable_r1;
-SELECT varacl FROM pg_variable WHERE varname IN ('var1', 'var2');
-                                             varacl                                              
--------------------------------------------------------------------------------------------------
- {regress_variable_owner=rw/regress_variable_owner,regress_variable_r1=r/regress_variable_owner}
- {regress_variable_owner=rw/regress_variable_owner,regress_variable_r1=r/regress_variable_owner}
-(2 rows)
+SELECT has_session_variable_privilege('regress_variable_r1', 'svartest.var1', 'SELECT'); -- t
+ has_session_variable_privilege 
+--------------------------------
+ t
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1', 'svartest.var2', 'SELECT'); -- t
+ has_session_variable_privilege 
+--------------------------------
+ t
+(1 row)
 
 REVOKE SELECT ON ALL VARIABLES IN SCHEMA svartest FROM regress_variable_r1;
-SELECT varacl FROM pg_variable WHERE varname IN ('var1', 'var2');
-                       varacl                       
-----------------------------------------------------
- {regress_variable_owner=rw/regress_variable_owner}
- {regress_variable_owner=rw/regress_variable_owner}
-(2 rows)
+SELECT has_session_variable_privilege('regress_variable_r1', 'svartest.var1', 'SELECT'); -- f
+ has_session_variable_privilege 
+--------------------------------
+ f
+(1 row)
+
+SELECT has_session_variable_privilege('regress_variable_r1', 'svartest.var2', 'SELECT'); -- f
+ has_session_variable_privilege 
+--------------------------------
+ f
+(1 row)
 
 SET ROLE TO DEFAULT;
 DROP VARIABLE svartest.var1;
 DROP VARIABLE svartest.var2;
 DROP SCHEMA svartest;
+-------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;
 CREATE VARIABLE public.var1 AS int;
 SET search_path TO public;
@@ -353,6 +394,7 @@ DROP VARIABLE public.var1;
 DROP ROLE regress_variable_r1;
 DROP ROLE regress_variable_r2;
 DROP ROLE regress_variable_owner;
+---end of function has_session_variable_privilege tests.
 -- check access rights
 CREATE ROLE regress_noowner;
 CREATE VARIABLE var1 AS int;
diff --git a/src/test/regress/sql/session_variables.sql b/src/test/regress/sql/session_variables.sql
index 24e897a8c4..3680ec29dd 100644
--- a/src/test/regress/sql/session_variables.sql
+++ b/src/test/regress/sql/session_variables.sql
@@ -137,54 +137,55 @@ SET ROLE TO regress_variable_owner;
 CREATE VARIABLE svartest.var1 AS int;
 SET ROLE TO DEFAULT;
 
-\dV+ svartest.var1
+--should be ok. since ALTER DEFAULT PRIVILEGES
+--allow regress_variable_reader to have SELECT priviledge
+SELECT has_session_variable_privilege('regress_variable_reader', 'svartest.var1', 'SELECT'); -- t
 
 DROP VARIABLE svartest.var1;
-
 DROP SCHEMA svartest;
 DROP ROLE regress_variable_reader;
 
--- check WITH GRANT OPTION
+
+-----begin of check GRANT WITH GRANT OPTION and REVOKE GRANTED BY
 CREATE ROLE regress_variable_r1;
 CREATE ROLE regress_variable_r2;
 
 SET ROLE TO regress_variable_owner;
-CREATE VARIABLE var1 AS int;
+CREATE VARIABLE var1 AS int;      --var1 will owned by regress_variable_owner
 
 GRANT SELECT ON VARIABLE var1 TO regress_variable_r1 WITH GRANT OPTION;
 SET ROLE TO regress_variable_r1;
 GRANT SELECT ON VARIABLE var1 TO regress_variable_r2 WITH GRANT OPTION;
 SET ROLE TO DEFAULT;
 
-SELECT varacl FROM pg_variable WHERE varname = 'var1';
+SELECT has_session_variable_privilege('regress_variable_r1', 'public.var1', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r2', 'public.var1', 'SELECT'); -- t
 
 REVOKE ALL PRIVILEGES ON VARIABLE var1 FROM regress_variable_r1 CASCADE;
 
-SELECT varacl FROM pg_variable WHERE varname = 'var1';
-
-SET ROLE TO regress_variable_owner;
-
-GRANT SELECT ON VARIABLE var1 TO regress_variable_r1 WITH GRANT OPTION;
-SET ROLE TO regress_variable_r1;
-GRANT SELECT ON VARIABLE var1 TO regress_variable_r2 WITH GRANT OPTION;
+SELECT has_session_variable_privilege('regress_variable_r1', 'public.var1', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2', 'public.var1', 'SELECT'); -- f
 
 SET ROLE TO regress_variable_owner;
 GRANT SELECT ON VARIABLE var1 TO regress_variable_r2;
 
-SELECT varacl FROM pg_variable WHERE varname = 'var1';
+SELECT has_session_variable_privilege('regress_variable_r1', 'public.var1', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2', 'public.var1', 'SELECT'); -- t
 
 REVOKE ALL ON VARIABLE var1 FROM regress_variable_r2 GRANTED BY regress_variable_owner;
 
-SELECT varacl FROM pg_variable WHERE varname = 'var1';
-
+SELECT has_session_variable_privilege('regress_variable_r1', 'public.var1', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_r2', 'public.var1', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_owner', 'public.var1', 'SELECT'); -- t
 SET ROLE TO DEFAULT;
 
 DROP VARIABLE var1;
+-----end of check GRANT WITH GRANT OPTION and REVOKE GRANTED BY
 
-CREATE SCHEMA svartest;
 
+-------begin of test: GRANT|REVOKE SELECT|UPDATE ON ALL VARIABLES IN SCHEMA
+CREATE SCHEMA svartest;
 GRANT ALL ON SCHEMA svartest TO regress_variable_owner;
-
 SET ROLE TO regress_variable_owner;
 
 CREATE VARIABLE svartest.var1 AS int;
@@ -192,19 +193,23 @@ CREATE VARIABLE svartest.var2 AS int;
 
 GRANT SELECT ON ALL VARIABLES IN SCHEMA svartest TO regress_variable_r1;
 
-SELECT varacl FROM pg_variable WHERE varname IN ('var1', 'var2');
+SELECT has_session_variable_privilege('regress_variable_r1', 'svartest.var1', 'SELECT'); -- t
+SELECT has_session_variable_privilege('regress_variable_r1', 'svartest.var2', 'SELECT'); -- t
 
 REVOKE SELECT ON ALL VARIABLES IN SCHEMA svartest FROM regress_variable_r1;
 
-SELECT varacl FROM pg_variable WHERE varname IN ('var1', 'var2');
+SELECT has_session_variable_privilege('regress_variable_r1', 'svartest.var1', 'SELECT'); -- f
+SELECT has_session_variable_privilege('regress_variable_r1', 'svartest.var2', 'SELECT'); -- f
 
 SET ROLE TO DEFAULT;
-
 DROP VARIABLE svartest.var1;
 DROP VARIABLE svartest.var2;
-
 DROP SCHEMA svartest;
+-------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;
 
 CREATE VARIABLE public.var1 AS int;
@@ -261,6 +266,7 @@ DROP ROLE regress_variable_r1;
 DROP ROLE regress_variable_r2;
 
 DROP ROLE regress_variable_owner;
+---end of function has_session_variable_privilege tests.
 
 -- check access rights
 CREATE ROLE regress_noowner;