Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb376ec6

Browse files
committed
Show default privileges in information schema
Hitherto, the information schema only showed explicitly grantedprivileges that were visible in the *acl catalog columns. If noprivileges had been granted, the implicit privileges were not shown.To fix that, add an SQL-accessible version of the acldefault()function, and use that inside the aclexplode() calls to substitute thecatalog-specific default privilege set for null values.reviewed by Abhijit Menon-Sen
1 parentbf90562 commitb376ec6

File tree

7 files changed

+79
-18
lines changed

7 files changed

+79
-18
lines changed

‎src/backend/catalog/information_schema.sql‎

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -554,7 +554,7 @@ CREATE VIEW column_privileges AS
554554
pr_c.prtype,
555555
pr_c.grantable,
556556
pr_c.relowner
557-
FROM (SELECToid, relname, relnamespace, relowner, (aclexplode(relacl)).*
557+
FROM (SELECToid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*
558558
FROM pg_class
559559
WHERE relkindIN ('r','v','f')
560560
) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
@@ -571,8 +571,8 @@ CREATE VIEW column_privileges AS
571571
pr_a.prtype,
572572
pr_a.grantable,
573573
c.relowner
574-
FROM (SELECT attrelid, attname, (aclexplode(attacl)).*
575-
FROM pg_attribute
574+
FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).*
575+
FROM pg_attribute aJOIN pg_class ccON (a.attrelid=cc.oid)
576576
WHERE attnum>0
577577
AND NOT attisdropped
578578
) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
@@ -1276,7 +1276,7 @@ CREATE VIEW routine_privileges AS
12761276
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_grantable
12771277

12781278
FROM (
1279-
SELECToid, proname, proowner, pronamespace, (aclexplode(proacl)).*FROM pg_proc
1279+
SELECToid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).*FROM pg_proc
12801280
) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
12811281
pg_namespace n,
12821282
pg_authid u_grantor,
@@ -1797,7 +1797,7 @@ CREATE VIEW table_privileges AS
17971797
CAST(CASE WHENc.prtype='SELECT' THEN'YES' ELSE'NO' ENDAS yes_or_no)AS with_hierarchy
17981798

17991799
FROM (
1800-
SELECToid, relname, relnamespace, relkind, relowner, (aclexplode(relacl)).*FROM pg_class
1800+
SELECToid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*FROM pg_class
18011801
)AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
18021802
pg_namespace nc,
18031803
pg_authid u_grantor,
@@ -2043,7 +2043,7 @@ CREATE VIEW udt_privileges AS
20432043
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_grantable
20442044

20452045
FROM (
2046-
SELECToid, typname, typnamespace, typtype, typowner, (aclexplode(typacl)).*FROM pg_type
2046+
SELECToid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).*FROM pg_type
20472047
)AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
20482048
pg_namespace n,
20492049
pg_authid u_grantor,
@@ -2129,7 +2129,7 @@ CREATE VIEW usage_privileges AS
21292129
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_grantable
21302130

21312131
FROM (
2132-
SELECToid, typname, typnamespace, typtype, typowner, (aclexplode(typacl)).*FROM pg_type
2132+
SELECToid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).*FROM pg_type
21332133
)AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
21342134
pg_namespace n,
21352135
pg_authid u_grantor,
@@ -2166,7 +2166,7 @@ CREATE VIEW usage_privileges AS
21662166
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_grantable
21672167

21682168
FROM (
2169-
SELECT fdwname, fdwowner, (aclexplode(fdwacl)).*FROM pg_foreign_data_wrapper
2169+
SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).*FROM pg_foreign_data_wrapper
21702170
)AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
21712171
pg_authid u_grantor,
21722172
(
@@ -2200,7 +2200,7 @@ CREATE VIEW usage_privileges AS
22002200
THEN'YES' ELSE'NO' ENDAS yes_or_no)AS is_grantable
22012201

22022202
FROM (
2203-
SELECT srvname, srvowner, (aclexplode(srvacl)).*FROM pg_foreign_server
2203+
SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).*FROM pg_foreign_server
22042204
)AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
22052205
pg_authid u_grantor,
22062206
(

‎src/backend/utils/adt/acl.c‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -834,6 +834,64 @@ acldefault(GrantObjectType objtype, Oid ownerId)
834834
}
835835

836836

837+
/*
838+
* SQL-accessible version of acldefault(). Hackish mapping from "char" type to
839+
* ACL_OBJECT_* values, but it's only used in the information schema, not
840+
* documented for general use.
841+
*/
842+
Datum
843+
acldefault_sql(PG_FUNCTION_ARGS)
844+
{
845+
charobjtypec=PG_GETARG_CHAR(0);
846+
Oidowner=PG_GETARG_OID(1);
847+
GrantObjectTypeobjtype=0;
848+
849+
switch (objtypec)
850+
{
851+
case'c':
852+
objtype=ACL_OBJECT_COLUMN;
853+
break;
854+
case'r':
855+
objtype=ACL_OBJECT_RELATION;
856+
break;
857+
case's':
858+
objtype=ACL_OBJECT_SEQUENCE;
859+
break;
860+
case'd':
861+
objtype=ACL_OBJECT_DATABASE;
862+
break;
863+
case'f':
864+
objtype=ACL_OBJECT_FUNCTION;
865+
break;
866+
case'l':
867+
objtype=ACL_OBJECT_LANGUAGE;
868+
break;
869+
case'L':
870+
objtype=ACL_OBJECT_LARGEOBJECT;
871+
break;
872+
case'n':
873+
objtype=ACL_OBJECT_NAMESPACE;
874+
break;
875+
case't':
876+
objtype=ACL_OBJECT_TABLESPACE;
877+
break;
878+
case'F':
879+
objtype=ACL_OBJECT_FDW;
880+
break;
881+
case'S':
882+
objtype=ACL_OBJECT_FOREIGN_SERVER;
883+
break;
884+
case'T':
885+
objtype=ACL_OBJECT_TYPE;
886+
break;
887+
default:
888+
elog(ERROR,"unrecognized objtype abbreviation: %c",objtypec);
889+
}
890+
891+
PG_RETURN_ACL_P(acldefault(objtype,owner));
892+
}
893+
894+
837895
/*
838896
* Update an ACL array to add or remove specified privileges.
839897
*

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201201262
56+
#defineCATALOG_VERSION_NO201201271
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1094,6 +1094,8 @@ DESCR("contains");
10941094
DATA(insertOID=1062 (aclitemeqPGNSPPGUID121000ffftfi2016"1033 1033"_null__null__null__null_aclitem_eq_null__null__null_ ));
10951095
DATA(insertOID=1365 (makeaclitemPGNSPPGUID121000ffftfi401033"26 26 25 16"_null__null__null__null_makeaclitem_null__null__null_ ));
10961096
DESCR("make ACL item");
1097+
DATA(insertOID=3943 (acldefaultPGNSPPGUID121000ffftfi201034"18 26"_null__null__null__null_acldefault_sql_null__null__null_ ));
1098+
DESCR("TODO");
10971099
DATA(insertOID=1689 (aclexplodePGNSPPGUID1211000ffftts102249"1034""{1034,26,26,25,16}""{i,o,o,o,o}""{acl,grantor,grantee,privilege_type,is_grantable}"_null_aclexplode_null__null__null_ ));
10981100
DESCR("convert ACL item array to table, for use by information schema");
10991101
DATA(insertOID=1044 (bpcharinPGNSPPGUID121000ffftfi301042"2275 26 23"_null__null__null__null_bpcharin_null__null__null_ ));

‎src/include/utils/acl.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -245,6 +245,7 @@ extern Datum aclcontains(PG_FUNCTION_ARGS);
245245
externDatummakeaclitem(PG_FUNCTION_ARGS);
246246
externDatumaclitem_eq(PG_FUNCTION_ARGS);
247247
externDatumhash_aclitem(PG_FUNCTION_ARGS);
248+
externDatumacldefault_sql(PG_FUNCTION_ARGS);
248249
externDatumaclexplode(PG_FUNCTION_ARGS);
249250

250251
/*

‎src/test/regress/expected/foreign_data.out‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -896,7 +896,7 @@ SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorizati
896896
regress_test_role | regression | t1 | username | bob
897897
(7 rows)
898898

899-
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
899+
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%'AND object_name IN ('s6', 'foo')ORDER BY 1, 2, 3, 4, 5;
900900
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
901901
-------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
902902
foreign_data_user | foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES
@@ -905,7 +905,7 @@ SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIG
905905
foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
906906
(4 rows)
907907

908-
SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
908+
SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%'AND object_name IN ('s6', 'foo')ORDER BY 1, 2, 3, 4, 5;
909909
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
910910
-------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
911911
foreign_data_user | foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES
@@ -939,14 +939,14 @@ SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
939939
regress_test_role | regression | t1 | username | bob
940940
(5 rows)
941941

942-
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
942+
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%'AND object_name IN ('s6', 'foo')ORDER BY 1, 2, 3, 4, 5;
943943
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
944944
-------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
945945
foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
946946
foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
947947
(2 rows)
948948

949-
SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
949+
SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%'AND object_name IN ('s6', 'foo')ORDER BY 1, 2, 3, 4, 5;
950950
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
951951
-------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
952952
foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO

‎src/test/regress/sql/foreign_data.sql‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -362,14 +362,14 @@ SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
362362
SELECT*FROMinformation_schema.foreign_server_optionsORDER BY1,2,3;
363363
SELECT*FROMinformation_schema.user_mappingsORDER BYlower(authorization_identifier),2,3;
364364
SELECT*FROMinformation_schema.user_mapping_optionsORDER BYlower(authorization_identifier),2,3,4;
365-
SELECT*FROMinformation_schema.usage_privilegesWHERE object_typeLIKE'FOREIGN%'ORDER BY1,2,3,4,5;
366-
SELECT*FROMinformation_schema.role_usage_grantsWHERE object_typeLIKE'FOREIGN%'ORDER BY1,2,3,4,5;
365+
SELECT*FROMinformation_schema.usage_privilegesWHERE object_typeLIKE'FOREIGN%'AND object_nameIN ('s6','foo')ORDER BY1,2,3,4,5;
366+
SELECT*FROMinformation_schema.role_usage_grantsWHERE object_typeLIKE'FOREIGN%'AND object_nameIN ('s6','foo')ORDER BY1,2,3,4,5;
367367
SELECT*FROMinformation_schema.foreign_tablesORDER BY1,2,3;
368368
SELECT*FROMinformation_schema.foreign_table_optionsORDER BY1,2,3,4;
369369
SET ROLE regress_test_role;
370370
SELECT*FROMinformation_schema.user_mapping_optionsORDER BY1,2,3,4;
371-
SELECT*FROMinformation_schema.usage_privilegesWHERE object_typeLIKE'FOREIGN%'ORDER BY1,2,3,4,5;
372-
SELECT*FROMinformation_schema.role_usage_grantsWHERE object_typeLIKE'FOREIGN%'ORDER BY1,2,3,4,5;
371+
SELECT*FROMinformation_schema.usage_privilegesWHERE object_typeLIKE'FOREIGN%'AND object_nameIN ('s6','foo')ORDER BY1,2,3,4,5;
372+
SELECT*FROMinformation_schema.role_usage_grantsWHERE object_typeLIKE'FOREIGN%'AND object_nameIN ('s6','foo')ORDER BY1,2,3,4,5;
373373
DROPUSER MAPPING FORcurrent_user SERVER t1;
374374
SET ROLE regress_test_role2;
375375
SELECT*FROMinformation_schema.user_mapping_optionsORDER BY1,2,3,4;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp