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

Commit05f245a

Browse files
author
Vladimir Ershov
committed
fix roles access && SPI_connect problem
1 parent4aa57e8 commit05f245a

File tree

7 files changed

+124
-99
lines changed

7 files changed

+124
-99
lines changed

‎pgpro_scheduler--1.0.sql‎

Lines changed: 23 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,19 @@ CREATE TYPE schedule.cron_job AS(
111111
-- FUNCTIONS --
112112
---------------
113113

114+
CREATEFUNCTIONschedule.onlySuperUser() RETURNSbooleanAS
115+
$BODY$
116+
DECLARE
117+
is_superuserboolean;
118+
BEGIN
119+
EXECUTE'SELECT rolsuper FROM pg_roles WHERE rolname = session_user'
120+
INTO is_superuser;
121+
IF NOT is_superuser THEN
122+
RAISE EXCEPTION'access denied';
123+
END IF;
124+
END
125+
$BODY$ LANGUAGE plpgsql;
126+
114127
CREATEFUNCTIONschedule.on_cron_update() RETURNS TRIGGER
115128
AS $BODY$
116129
DECLARE
@@ -148,8 +161,8 @@ BEGIN
148161
RAISE EXCEPTION'there is no such job with id %', jobId;
149162
WHEN TOO_MANY_ROWS THEN
150163
RAISE EXCEPTION'there are more than one job with id %', jobId;
151-
END;
152-
EXECUTE'SELECTusesuper FROMpg_user WHEREusename = session_user'
164+
END;
165+
EXECUTE'SELECTrolsuper FROMpg_roles WHERErolname = session_user'
153166
INTO is_superuser;
154167
IF is_superuser THEN
155168
RETURN true;
@@ -306,7 +319,7 @@ BEGIN
306319
IF params?'run_as'AND params->>'run_as'<>session_user THEN
307320
executor := params->>'run_as';
308321
BEGIN
309-
SELECT* INTO STRICT recFROMpg_userWHEREusename= executor;
322+
SELECT* INTO STRICT recFROMpg_rolesWHERErolname= executor;
310323
EXCEPTION
311324
WHEN NO_DATA_FOUND THEN
312325
RAISE EXCEPTION'there is no such user %', executor;
@@ -703,14 +716,9 @@ LANGUAGE plpgsql;
703716
CREATEFUNCTIONschedule.clean_log() RETURNSINTAS
704717
$BODY$
705718
DECLARE
706-
is_superuserboolean;
707719
cntinteger;
708720
BEGIN
709-
EXECUTE'SELECT usesuper FROM pg_user WHERE usename = session_user'
710-
INTO is_superuser;
711-
IF NOT is_superuser THEN
712-
RAISE EXCEPTION'access denied';
713-
END IF;
721+
SELECT onlySuperUser();
714722

715723
WITH aAS (DELETEFROMschedule.log RETURNING1)
716724
SELECTcount(*) INTO cntFROM a;
@@ -742,13 +750,8 @@ $BODY$
742750
DECLARE
743751
iischedule.cron;
744752
ooschedule.cron_rec;
745-
is_superuserboolean;
746753
BEGIN
747-
EXECUTE'SELECT usesuper FROM pg_user WHERE usename = session_user'
748-
INTO is_superuser;
749-
IF NOT is_superuser THEN
750-
RAISE EXCEPTION'access denied: only superuser allowed';
751-
END IF;
754+
SELECT onlySuperUser();
752755

753756
FOR iiINSELECT*FROMschedule.cron LOOP
754757
oo :=schedule._make_cron_rec(ii);
@@ -781,14 +784,9 @@ $BODY$
781784
DECLARE
782785
iischedule.cron;
783786
ooschedule.cron_rec;
784-
is_superuserboolean;
785787
BEGIN
786788
IF usename<>session_user THEN
787-
EXECUTE'SELECT usesuper FROM pg_user WHERE usename = session_user'
788-
INTO is_superuser;
789-
IF NOT is_superuser THEN
790-
RAISE EXCEPTION'access denied';
791-
END IF;
789+
SELECT onlySuperUser();
792790
END IF;
793791

794792
FOR iiINSELECT*FROMschedule.cronWHERE owner= usename LOOP
@@ -822,14 +820,9 @@ $BODY$
822820
DECLARE
823821
iischedule.cron;
824822
ooschedule.cron_rec;
825-
is_superuserboolean;
826823
BEGIN
827824
IF usename<>session_user THEN
828-
EXECUTE'SELECT usesuper FROM pg_user WHERE usename = session_user'
829-
INTO is_superuser;
830-
IF NOT is_superuser THEN
831-
RAISE EXCEPTION'access denied';
832-
END IF;
825+
SELECT onlySuperUser();
833826
END IF;
834827

835828
FOR iiINSELECT*FROMschedule.cronWHERE executor= usename LOOP
@@ -847,7 +840,6 @@ $BODY$
847840
DECLARE
848841
ii record;
849842
ooschedule.cron_job;
850-
is_superuserboolean;
851843
BEGIN
852844
FOR iiINSELECT*FROMschedule.atas at,schedule.cronas cronWHEREcron.executor=session_userANDcron.id=at.cronANDat.active LOOP
853845
oo.cron=ii.id;
@@ -882,13 +874,8 @@ $BODY$
882874
DECLARE
883875
ii record;
884876
ooschedule.cron_job;
885-
is_superuserboolean;
886877
BEGIN
887-
EXECUTE'SELECT usesuper FROM pg_user WHERE usename = session_user'
888-
INTO is_superuser;
889-
IF NOT is_superuser THEN
890-
RAISE EXCEPTION'access denied';
891-
END IF;
878+
SELECT onlySuperUser();
892879
FOR iiINSELECT*FROMschedule.atas at,schedule.cronas cronWHEREcron.id=at.cronANDat.active LOOP
893880
oo.cron=ii.id;
894881
oo.node=ii.node;
@@ -922,14 +909,9 @@ $BODY$
922909
DECLARE
923910
ii record;
924911
ooschedule.cron_job;
925-
is_superuserboolean;
926912
BEGIN
927913
IF usename<>session_user THEN
928-
EXECUTE'SELECT usesuper FROM pg_user WHERE usename = session_user'
929-
INTO is_superuser;
930-
IF NOT is_superuser THEN
931-
RAISE EXCEPTION'access denied';
932-
END IF;
914+
SELECT onlySuperUser();
933915
END IF;
934916

935917
FOR iiINSELECT*FROMschedule.atas at,schedule.cronas cronWHEREcron.executor= usenameANDcron.id=at.cronANDat.active LOOP
@@ -983,15 +965,10 @@ $BODY$
983965
DECLARE
984966
ii record;
985967
ooschedule.cron_job;
986-
is_superuserboolean;
987968
sql_cmdtext;
988969
BEGIN
989970
IF usename<>session_user THEN
990-
EXECUTE'SELECT usesuper FROM pg_user WHERE usename = session_user'
991-
INTO is_superuser;
992-
IF NOT is_superuser THEN
993-
RAISE EXCEPTION'access denied';
994-
END IF;
971+
SELECT onlySuperUser();
995972
END IF;
996973

997974
IF usename='___all___' THEN

‎src/sched_manager_poll.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -210,7 +210,7 @@ pid_t registerManagerWorker(schd_manager_t *man)
210210
worker.bgw_flags=BGWORKER_SHMEM_ACCESS |
211211
BGWORKER_BACKEND_DATABASE_CONNECTION;
212212
worker.bgw_start_time=BgWorkerStart_ConsistentState;
213-
worker.bgw_restart_time=1;/*BGW_NEVER_RESTART; */
213+
worker.bgw_restart_time=BGW_NEVER_RESTART;
214214
worker.bgw_main=NULL;
215215
worker.bgw_main_arg=UInt32GetDatum(dsm_segment_handle(man->shared));
216216
sprintf(worker.bgw_library_name,"pgpro_scheduler");

‎src/scheduler_executor.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -213,7 +213,7 @@ int set_session_authorization(char *username, char **error)
213213
Datumvalues[1];
214214
boolis_superuser;
215215
intret;
216-
char*sql="selectusesysid, usesuper from pg_catalog.pg_user whereusename = $1";
216+
char*sql="selectoid, rolsuper from pg_catalog.pg_roles whererolname = $1";
217217
charbuff[1024];
218218

219219
values[0]=CStringGetTextDatum(username);

‎src/scheduler_manager.c‎

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -69,24 +69,26 @@ int checkSchedulerNamespace(void)
6969
}
7070
elseif(count>1||count==0 )
7171
{
72-
elog(LOG,"Scheduler manager: %s: cannot check namespace:found %d namespaces",
73-
MyBgworkerEntry->bgw_name,count);
72+
elog(LOG,"Scheduler manager: %s: cannot check namespace:"
73+
"found %d namespaces",MyBgworkerEntry->bgw_name,count);
7474
}
7575
elseif(count==-2)
7676
{
77-
elog(LOG,"Scheduler manager: %s: cannot check namespace:count return null",
78-
MyBgworkerEntry->bgw_name);
77+
elog(LOG,"Scheduler manager: %s: cannot check namespace:"
78+
"count return null",MyBgworkerEntry->bgw_name);
7979
}
8080
elseif(count!=1)
8181
{
82-
elog(ERROR,"Scheduler manager: %s: cannot check namespace:unknown error %d",
83-
MyBgworkerEntry->bgw_name,count);
82+
elog(ERROR,"Scheduler manager: %s: cannot check namespace:"
83+
"unknown error %d",MyBgworkerEntry->bgw_name,count);
8484
}
8585

8686
SPI_finish();
8787
PopActiveSnapshot();
8888
CommitTransactionCommand();
89-
if(count)SetConfigOption("search_path","schedule",PGC_USERSET,PGC_S_SESSION);
89+
if(count) {
90+
SetConfigOption("search_path",schema,PGC_USERSET,PGC_S_SESSION);
91+
}
9092

9193
returncount;
9294
}

‎src/scheduler_spi_utils.c‎

Lines changed: 65 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,41 @@
1010
#include"catalog/pg_type.h"
1111
#include"memutils.h"
1212

13+
voidSTART_SNAP(void)
14+
{
15+
SetCurrentStatementStartTimestamp();
16+
StartTransactionCommand();
17+
PushActiveSnapshot(GetTransactionSnapshot());
18+
}
19+
20+
voidSTOP_SNAP(void)
21+
{
22+
PopActiveSnapshot();
23+
CommitTransactionCommand();
24+
}
25+
26+
voidSTART_SPI_SNAP(void)
27+
{
28+
SetCurrentStatementStartTimestamp();
29+
StartTransactionCommand();
30+
PushActiveSnapshot(GetTransactionSnapshot());
31+
SPI_connect();
32+
}
33+
34+
voidSTOP_SPI_SNAP(void)
35+
{
36+
SPI_finish();
37+
PopActiveSnapshot();
38+
CommitTransactionCommand();
39+
}
40+
41+
voidABORT_SPI_SNAP(void)
42+
{
43+
PopActiveSnapshot();
44+
AbortCurrentTransaction();
45+
SPI_finish();
46+
}
47+
1348
char*_copy_string(char*str)
1449
{
1550
intlen=strlen(str);
@@ -202,9 +237,12 @@ int execute_spi_sql_with_args(const char *sql, int n, Oid *argtypes, Datum *valu
202237
intret=-100;
203238
ErrorData*edata;
204239
MemoryContextold;
240+
interrorSet=0;
241+
charother[100];
205242

206243
*error=NULL;
207244

245+
208246
PG_TRY();
209247
{
210248
ret=SPI_execute_with_args(sql,n,argtypes,values,nulls, false,0);
@@ -226,17 +264,43 @@ int execute_spi_sql_with_args(const char *sql, int n, Oid *argtypes, Datum *valu
226264
{
227265
*error=_copy_string("unknown error");
228266
}
267+
errorSet=1;
229268
FreeErrorData(edata);
230269
MemoryContextSwitchTo(old);
231270
FlushErrorState();
232271
}
233272
PG_END_TRY();
234273

274+
if(!errorSet&&ret<0)
275+
{
276+
if(ret==SPI_ERROR_CONNECT)
277+
{
278+
*error=_copy_string("Connection error");
279+
}
280+
elseif(ret==SPI_ERROR_COPY)
281+
{
282+
*error=_copy_string("COPY error");
283+
}
284+
elseif(ret==SPI_ERROR_OPUNKNOWN)
285+
{
286+
*error=_copy_string("SPI_ERROR_OPUNKNOWN");
287+
}
288+
elseif(ret==SPI_ERROR_UNCONNECTED)
289+
{
290+
*error=_copy_string("Unconnected call");
291+
}
292+
else
293+
{
294+
sprintf(other,"error number: %d",ret);
295+
*error=_copy_string(other);
296+
}
297+
}
298+
235299
returnret;
236300
}
237301

238302
intexecute_spi(constchar*sql,char**error)
239-
{
303+
{
240304
returnexecute_spi_sql_with_args(sql,0,NULL,NULL,NULL,error);
241305
}
242306

‎src/scheduler_spi_utils.h‎

Lines changed: 5 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -9,30 +9,11 @@
99

1010
#defineselect_count_sql(SQL) select_oneintvalue_sql(SQL, 0);
1111

12-
#defineSTART_SNAP() \
13-
SetCurrentStatementStartTimestamp(); \
14-
StartTransactionCommand(); \
15-
PushActiveSnapshot(GetTransactionSnapshot());
16-
17-
#defineSTOP_SNAP() \
18-
PopActiveSnapshot(); \
19-
CommitTransactionCommand();
20-
21-
#defineSTART_SPI_SNAP() \
22-
SetCurrentStatementStartTimestamp(); \
23-
StartTransactionCommand(); \
24-
AssertState(SPI_connect() == SPI_OK_CONNECT); \
25-
PushActiveSnapshot(GetTransactionSnapshot());
26-
27-
#defineSTOP_SPI_SNAP() \
28-
SPI_finish(); \
29-
PopActiveSnapshot(); \
30-
CommitTransactionCommand();
31-
32-
#defineABORT_SPI_SNAP() \
33-
PopActiveSnapshot(); \
34-
AbortCurrentTransaction(); \
35-
SPI_finish();
12+
voidSTART_SNAP(void);
13+
voidSTOP_SNAP(void);
14+
voidSTART_SPI_SNAP(void);
15+
voidSTOP_SPI_SNAP(void);
16+
voidABORT_SPI_SNAP(void);
3617

3718
char*_copy_string(char*str);
3819
TimestampTzget_timestamp_from_spi(introw_n,intpos,TimestampTzdef);

0 commit comments

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