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

Commitfd321a1

Browse files
committed
Remove superuser checks in pgstattuple
Now that we track initial privileges on extension objects and changes tothose permissions, we can drop the superuser() checks from the variousfunctions which are part of the pgstattuple extension and rely on theGRANT system to control access to those functions.Since a pg_upgrade will preserve the version of the extension whichexisted prior to the upgrade, we can't simply modify the existingfunctions but instead need to create new functions which remove thechecks and update the SQL-level functions to use the new functions(and to REVOKE EXECUTE rights on those functions from PUBLIC).Thanks to Tom and Andres for adding support for extensions to followupdate paths (see:40b449a), allowing this patch to be much smallersince no new base version script needed to be included.Approach suggested by Noah.Reviewed by Michael Paquier.
1 parentf2af8dc commitfd321a1

File tree

7 files changed

+315
-15
lines changed

7 files changed

+315
-15
lines changed

‎contrib/pgstattuple/Makefile

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4,9 +4,10 @@ MODULE_big= pgstattuple
44
OBJS= pgstattuple.o pgstatindex.o pgstatapprox.o$(WIN32RES)
55

66
EXTENSION = pgstattuple
7-
DATA = pgstattuple--1.4.sql pgstattuple--1.3--1.4.sql\
8-
pgstattuple--1.2--1.3.sql pgstattuple--1.1--1.2.sql\
9-
pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
7+
DATA = pgstattuple--1.4.sql pgstattuple--1.4--1.5.sql\
8+
pgstattuple--1.3--1.4.sql pgstattuple--1.2--1.3.sql\
9+
pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql\
10+
pgstattuple--unpackaged--1.0.sql
1011
PGFILEDESC = "pgstattuple - tuple-level statistics"
1112

1213
REGRESS = pgstattuple

‎contrib/pgstattuple/pgstatapprox.c

Lines changed: 34 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,9 @@
2929
#include"commands/vacuum.h"
3030

3131
PG_FUNCTION_INFO_V1(pgstattuple_approx);
32+
PG_FUNCTION_INFO_V1(pgstattuple_approx_v1_5);
33+
34+
Datumpgstattuple_approx_internal(Oidrelid,FunctionCallInfofcinfo);
3235

3336
typedefstructoutput_type
3437
{
@@ -204,11 +207,42 @@ statapprox_heap(Relation rel, output_type *stat)
204207

205208
/*
206209
* Returns estimated live/dead tuple statistics for the given relid.
210+
*
211+
* The superuser() check here must be kept as the library might be upgraded
212+
* without the extension being upgraded, meaning that in pre-1.5 installations
213+
* these functions could be called by any user.
207214
*/
208215
Datum
209216
pgstattuple_approx(PG_FUNCTION_ARGS)
210217
{
211218
Oidrelid=PG_GETARG_OID(0);
219+
220+
if (!superuser())
221+
ereport(ERROR,
222+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
223+
(errmsg("must be superuser to use pgstattuple functions"))));
224+
225+
PG_RETURN_DATUM(pgstattuple_approx_internal(relid,fcinfo));
226+
}
227+
228+
/*
229+
* As of pgstattuple version 1.5, we no longer need to check if the user
230+
* is a superuser because we REVOKE EXECUTE on the SQL function from PUBLIC.
231+
* Users can then grant access to it based on their policies.
232+
*
233+
* Otherwise identical to pgstattuple_approx (above).
234+
*/
235+
Datum
236+
pgstattuple_approx_v1_5(PG_FUNCTION_ARGS)
237+
{
238+
Oidrelid=PG_GETARG_OID(0);
239+
240+
PG_RETURN_DATUM(pgstattuple_approx_internal(relid,fcinfo));
241+
}
242+
243+
Datum
244+
pgstattuple_approx_internal(Oidrelid,FunctionCallInfofcinfo)
245+
{
212246
Relationrel;
213247
output_typestat= {0};
214248
TupleDesctupdesc;
@@ -217,11 +251,6 @@ pgstattuple_approx(PG_FUNCTION_ARGS)
217251
HeapTupleret;
218252
inti=0;
219253

220-
if (!superuser())
221-
ereport(ERROR,
222-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
223-
(errmsg("must be superuser to use pgstattuple functions"))));
224-
225254
if (get_call_result_type(fcinfo,NULL,&tupdesc)!=TYPEFUNC_COMPOSITE)
226255
elog(ERROR,"return type must be a row type");
227256

‎contrib/pgstattuple/pgstatindex.c

Lines changed: 116 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,14 @@ PG_FUNCTION_INFO_V1(pg_relpages);
5454
PG_FUNCTION_INFO_V1(pg_relpagesbyid);
5555
PG_FUNCTION_INFO_V1(pgstatginindex);
5656

57+
PG_FUNCTION_INFO_V1(pgstatindex_v1_5);
58+
PG_FUNCTION_INFO_V1(pgstatindexbyid_v1_5);
59+
PG_FUNCTION_INFO_V1(pg_relpages_v1_5);
60+
PG_FUNCTION_INFO_V1(pg_relpagesbyid_v1_5);
61+
PG_FUNCTION_INFO_V1(pgstatginindex_v1_5);
62+
63+
Datumpgstatginindex_internal(Oidrelid,FunctionCallInfofcinfo);
64+
5765
#defineIS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
5866
#defineIS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
5967
#defineIS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
@@ -99,6 +107,10 @@ static Datum pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo);
99107
* pgstatindex()
100108
*
101109
* Usage: SELECT * FROM pgstatindex('t1_pkey');
110+
*
111+
* The superuser() check here must be kept as the library might be upgraded
112+
* without the extension being upgraded, meaning that in pre-1.5 installations
113+
* these functions could be called by any user.
102114
* ------------------------------------------------------
103115
*/
104116
Datum
@@ -119,6 +131,31 @@ pgstatindex(PG_FUNCTION_ARGS)
119131
PG_RETURN_DATUM(pgstatindex_impl(rel,fcinfo));
120132
}
121133

134+
/*
135+
* As of pgstattuple version 1.5, we no longer need to check if the user
136+
* is a superuser because we REVOKE EXECUTE on the function from PUBLIC.
137+
* Users can then grant access to it based on their policies.
138+
*
139+
* Otherwise identical to pgstatindex (above).
140+
*/
141+
Datum
142+
pgstatindex_v1_5(PG_FUNCTION_ARGS)
143+
{
144+
text*relname=PG_GETARG_TEXT_P(0);
145+
Relationrel;
146+
RangeVar*relrv;
147+
148+
relrv=makeRangeVarFromNameList(textToQualifiedNameList(relname));
149+
rel=relation_openrv(relrv,AccessShareLock);
150+
151+
PG_RETURN_DATUM(pgstatindex_impl(rel,fcinfo));
152+
}
153+
154+
/*
155+
* The superuser() check here must be kept as the library might be upgraded
156+
* without the extension being upgraded, meaning that in pre-1.5 installations
157+
* these functions could be called by any user.
158+
*/
122159
Datum
123160
pgstatindexbyid(PG_FUNCTION_ARGS)
124161
{
@@ -135,6 +172,18 @@ pgstatindexbyid(PG_FUNCTION_ARGS)
135172
PG_RETURN_DATUM(pgstatindex_impl(rel,fcinfo));
136173
}
137174

175+
/* No need for superuser checks in v1.5, see above */
176+
Datum
177+
pgstatindexbyid_v1_5(PG_FUNCTION_ARGS)
178+
{
179+
Oidrelid=PG_GETARG_OID(0);
180+
Relationrel;
181+
182+
rel=relation_open(relid,AccessShareLock);
183+
184+
PG_RETURN_DATUM(pgstatindex_impl(rel,fcinfo));
185+
}
186+
138187
staticDatum
139188
pgstatindex_impl(Relationrel,FunctionCallInfofcinfo)
140189
{
@@ -292,6 +341,8 @@ pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
292341
*
293342
* Usage: SELECT pg_relpages('t1');
294343
* SELECT pg_relpages('t1_pkey');
344+
*
345+
* Must keep superuser() check, see above.
295346
* --------------------------------------------------------
296347
*/
297348
Datum
@@ -319,6 +370,28 @@ pg_relpages(PG_FUNCTION_ARGS)
319370
PG_RETURN_INT64(relpages);
320371
}
321372

373+
/* No need for superuser checks in v1.5, see above */
374+
Datum
375+
pg_relpages_v1_5(PG_FUNCTION_ARGS)
376+
{
377+
text*relname=PG_GETARG_TEXT_P(0);
378+
int64relpages;
379+
Relationrel;
380+
RangeVar*relrv;
381+
382+
relrv=makeRangeVarFromNameList(textToQualifiedNameList(relname));
383+
rel=relation_openrv(relrv,AccessShareLock);
384+
385+
/* note: this will work OK on non-local temp tables */
386+
387+
relpages=RelationGetNumberOfBlocks(rel);
388+
389+
relation_close(rel,AccessShareLock);
390+
391+
PG_RETURN_INT64(relpages);
392+
}
393+
394+
/* Must keep superuser() check, see above. */
322395
Datum
323396
pg_relpagesbyid(PG_FUNCTION_ARGS)
324397
{
@@ -342,16 +415,58 @@ pg_relpagesbyid(PG_FUNCTION_ARGS)
342415
PG_RETURN_INT64(relpages);
343416
}
344417

418+
/* No need for superuser checks in v1.5, see above */
419+
Datum
420+
pg_relpagesbyid_v1_5(PG_FUNCTION_ARGS)
421+
{
422+
Oidrelid=PG_GETARG_OID(0);
423+
int64relpages;
424+
Relationrel;
425+
426+
rel=relation_open(relid,AccessShareLock);
427+
428+
/* note: this will work OK on non-local temp tables */
429+
430+
relpages=RelationGetNumberOfBlocks(rel);
431+
432+
relation_close(rel,AccessShareLock);
433+
434+
PG_RETURN_INT64(relpages);
435+
}
436+
345437
/* ------------------------------------------------------
346438
* pgstatginindex()
347439
*
348440
* Usage: SELECT * FROM pgstatginindex('ginindex');
441+
*
442+
* Must keep superuser() check, see above.
349443
* ------------------------------------------------------
350444
*/
351445
Datum
352446
pgstatginindex(PG_FUNCTION_ARGS)
353447
{
354448
Oidrelid=PG_GETARG_OID(0);
449+
450+
if (!superuser())
451+
ereport(ERROR,
452+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
453+
(errmsg("must be superuser to use pgstattuple functions"))));
454+
455+
PG_RETURN_DATUM(pgstatginindex_internal(relid,fcinfo));
456+
}
457+
458+
/* No need for superuser checks in v1.5, see above */
459+
Datum
460+
pgstatginindex_v1_5(PG_FUNCTION_ARGS)
461+
{
462+
Oidrelid=PG_GETARG_OID(0);
463+
464+
PG_RETURN_DATUM(pgstatginindex_internal(relid,fcinfo));
465+
}
466+
467+
Datum
468+
pgstatginindex_internal(Oidrelid,FunctionCallInfofcinfo)
469+
{
355470
Relationrel;
356471
Bufferbuffer;
357472
Pagepage;
@@ -363,11 +478,6 @@ pgstatginindex(PG_FUNCTION_ARGS)
363478
boolnulls[3]= {false, false, false};
364479
Datumresult;
365480

366-
if (!superuser())
367-
ereport(ERROR,
368-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
369-
(errmsg("must be superuser to use pgstattuple functions"))));
370-
371481
rel=relation_open(relid,AccessShareLock);
372482

373483
if (!IS_INDEX(rel)|| !IS_GIN(rel))
@@ -415,5 +525,5 @@ pgstatginindex(PG_FUNCTION_ARGS)
415525
tuple=heap_form_tuple(tupleDesc,values,nulls);
416526
result=HeapTupleGetDatum(tuple);
417527

418-
PG_RETURN_DATUM(result);
528+
return(result);
419529
}
Lines changed: 111 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,111 @@
1+
/* contrib/pgstattuple/pgstattuple--1.4--1.5.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pgstattuple UPDATE TO '1.5'" to load this file. \quit
5+
6+
CREATE OR REPLACEFUNCTIONpgstattuple(IN relnametext,
7+
OUT table_lenBIGINT,-- physical table length in bytes
8+
OUT tuple_countBIGINT,-- number of live tuples
9+
OUT tuple_lenBIGINT,-- total tuples length in bytes
10+
OUT tuple_percent FLOAT8,-- live tuples in %
11+
OUT dead_tuple_countBIGINT,-- number of dead tuples
12+
OUT dead_tuple_lenBIGINT,-- total dead tuples length in bytes
13+
OUT dead_tuple_percent FLOAT8,-- dead tuples in %
14+
OUT free_spaceBIGINT,-- free space in bytes
15+
OUT free_percent FLOAT8)-- free space in %
16+
AS'MODULE_PATHNAME','pgstattuple_v1_5'
17+
LANGUAGE C STRICT PARALLEL SAFE;
18+
19+
REVOKE EXECUTEON FUNCTION pgstattuple(text)FROM PUBLIC;
20+
21+
CREATE OR REPLACEFUNCTIONpgstatindex(IN relnametext,
22+
OUT versionINT,
23+
OUT tree_levelINT,
24+
OUT index_sizeBIGINT,
25+
OUT root_block_noBIGINT,
26+
OUT internal_pagesBIGINT,
27+
OUT leaf_pagesBIGINT,
28+
OUT empty_pagesBIGINT,
29+
OUT deleted_pagesBIGINT,
30+
OUT avg_leaf_density FLOAT8,
31+
OUT leaf_fragmentation FLOAT8)
32+
AS'MODULE_PATHNAME','pgstatindex_v1_5'
33+
LANGUAGE C STRICT PARALLEL SAFE;
34+
35+
REVOKE EXECUTEON FUNCTION pgstatindex(text)FROM PUBLIC;
36+
37+
CREATE OR REPLACEFUNCTIONpg_relpages(IN relnametext)
38+
RETURNSBIGINT
39+
AS'MODULE_PATHNAME','pg_relpages_v1_5'
40+
LANGUAGE C STRICT PARALLEL SAFE;
41+
42+
REVOKE EXECUTEON FUNCTION pg_relpages(text)FROM PUBLIC;
43+
44+
/* New stuff in 1.1 begins here*/
45+
46+
CREATE OR REPLACEFUNCTIONpgstatginindex(IN relname regclass,
47+
OUT version INT4,
48+
OUT pending_pages INT4,
49+
OUT pending_tuplesBIGINT)
50+
AS'MODULE_PATHNAME','pgstatginindex_v1_5'
51+
LANGUAGE C STRICT PARALLEL SAFE;
52+
53+
REVOKE EXECUTEON FUNCTION pgstatginindex(regclass)FROM PUBLIC;
54+
55+
/* New stuff in 1.2 begins here*/
56+
57+
CREATE OR REPLACEFUNCTIONpgstattuple(IN reloid regclass,
58+
OUT table_lenBIGINT,-- physical table length in bytes
59+
OUT tuple_countBIGINT,-- number of live tuples
60+
OUT tuple_lenBIGINT,-- total tuples length in bytes
61+
OUT tuple_percent FLOAT8,-- live tuples in %
62+
OUT dead_tuple_countBIGINT,-- number of dead tuples
63+
OUT dead_tuple_lenBIGINT,-- total dead tuples length in bytes
64+
OUT dead_tuple_percent FLOAT8,-- dead tuples in %
65+
OUT free_spaceBIGINT,-- free space in bytes
66+
OUT free_percent FLOAT8)-- free space in %
67+
AS'MODULE_PATHNAME','pgstattuplebyid_v1_5'
68+
LANGUAGE C STRICT PARALLEL SAFE;
69+
70+
REVOKE EXECUTEON FUNCTION pgstattuple(regclass)FROM PUBLIC;
71+
72+
CREATE OR REPLACEFUNCTIONpgstatindex(IN relname regclass,
73+
OUT versionINT,
74+
OUT tree_levelINT,
75+
OUT index_sizeBIGINT,
76+
OUT root_block_noBIGINT,
77+
OUT internal_pagesBIGINT,
78+
OUT leaf_pagesBIGINT,
79+
OUT empty_pagesBIGINT,
80+
OUT deleted_pagesBIGINT,
81+
OUT avg_leaf_density FLOAT8,
82+
OUT leaf_fragmentation FLOAT8)
83+
AS'MODULE_PATHNAME','pgstatindexbyid_v1_5'
84+
LANGUAGE C STRICT PARALLEL SAFE;
85+
86+
REVOKE EXECUTEON FUNCTION pgstatindex(regclass)FROM PUBLIC;
87+
88+
CREATE OR REPLACEFUNCTIONpg_relpages(IN relname regclass)
89+
RETURNSBIGINT
90+
AS'MODULE_PATHNAME','pg_relpagesbyid_v1_5'
91+
LANGUAGE C STRICT PARALLEL SAFE;
92+
93+
REVOKE EXECUTEON FUNCTION pg_relpages(regclass)FROM PUBLIC;
94+
95+
/* New stuff in 1.3 begins here*/
96+
97+
CREATE OR REPLACEFUNCTIONpgstattuple_approx(IN reloid regclass,
98+
OUT table_lenBIGINT,-- physical table length in bytes
99+
OUT scanned_percent FLOAT8,-- what percentage of the table's pages was scanned
100+
OUT approx_tuple_countBIGINT,-- estimated number of live tuples
101+
OUT approx_tuple_lenBIGINT,-- estimated total length in bytes of live tuples
102+
OUT approx_tuple_percent FLOAT8,-- live tuples in % (based on estimate)
103+
OUT dead_tuple_countBIGINT,-- exact number of dead tuples
104+
OUT dead_tuple_lenBIGINT,-- exact total length in bytes of dead tuples
105+
OUT dead_tuple_percent FLOAT8,-- dead tuples in % (based on estimate)
106+
OUT approx_free_spaceBIGINT,-- estimated free space in bytes
107+
OUT approx_free_percent FLOAT8)-- free space in % (based on estimate)
108+
AS'MODULE_PATHNAME','pgstattuple_approx_v1_5'
109+
LANGUAGE C STRICT PARALLEL SAFE;
110+
111+
REVOKE EXECUTEON FUNCTION pgstattuple_approx(regclass)FROM PUBLIC;

0 commit comments

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