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

Commita5f7d58

Browse files
committed
Add tests for sequence privileges
1 parent69fed5b commita5f7d58

File tree

3 files changed

+321
-0
lines changed

3 files changed

+321
-0
lines changed

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

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -367,6 +367,41 @@ DROP SEQUENCE seq2;
367367
SELECT lastval();
368368
ERROR: lastval is not yet defined in this session
369369
CREATE USER seq_user;
370+
-- privileges tests
371+
-- nextval
372+
BEGIN;
373+
SET LOCAL SESSION AUTHORIZATION seq_user;
374+
CREATE SEQUENCE seq3;
375+
REVOKE ALL ON seq3 FROM seq_user;
376+
GRANT SELECT ON seq3 TO seq_user;
377+
SELECT nextval('seq3');
378+
ERROR: permission denied for sequence seq3
379+
ROLLBACK;
380+
BEGIN;
381+
SET LOCAL SESSION AUTHORIZATION seq_user;
382+
CREATE SEQUENCE seq3;
383+
REVOKE ALL ON seq3 FROM seq_user;
384+
GRANT UPDATE ON seq3 TO seq_user;
385+
SELECT nextval('seq3');
386+
nextval
387+
---------
388+
1
389+
(1 row)
390+
391+
ROLLBACK;
392+
BEGIN;
393+
SET LOCAL SESSION AUTHORIZATION seq_user;
394+
CREATE SEQUENCE seq3;
395+
REVOKE ALL ON seq3 FROM seq_user;
396+
GRANT USAGE ON seq3 TO seq_user;
397+
SELECT nextval('seq3');
398+
nextval
399+
---------
400+
1
401+
(1 row)
402+
403+
ROLLBACK;
404+
-- currval
370405
BEGIN;
371406
SET LOCAL SESSION AUTHORIZATION seq_user;
372407
CREATE SEQUENCE seq3;
@@ -377,8 +412,96 @@ SELECT nextval('seq3');
377412
(1 row)
378413

379414
REVOKE ALL ON seq3 FROM seq_user;
415+
GRANT SELECT ON seq3 TO seq_user;
416+
SELECT currval('seq3');
417+
currval
418+
---------
419+
1
420+
(1 row)
421+
422+
ROLLBACK;
423+
BEGIN;
424+
SET LOCAL SESSION AUTHORIZATION seq_user;
425+
CREATE SEQUENCE seq3;
426+
SELECT nextval('seq3');
427+
nextval
428+
---------
429+
1
430+
(1 row)
431+
432+
REVOKE ALL ON seq3 FROM seq_user;
433+
GRANT UPDATE ON seq3 TO seq_user;
434+
SELECT currval('seq3');
435+
ERROR: permission denied for sequence seq3
436+
ROLLBACK;
437+
BEGIN;
438+
SET LOCAL SESSION AUTHORIZATION seq_user;
439+
CREATE SEQUENCE seq3;
440+
SELECT nextval('seq3');
441+
nextval
442+
---------
443+
1
444+
(1 row)
445+
446+
REVOKE ALL ON seq3 FROM seq_user;
447+
GRANT USAGE ON seq3 TO seq_user;
448+
SELECT currval('seq3');
449+
currval
450+
---------
451+
1
452+
(1 row)
453+
454+
ROLLBACK;
455+
-- lastval
456+
BEGIN;
457+
SET LOCAL SESSION AUTHORIZATION seq_user;
458+
CREATE SEQUENCE seq3;
459+
SELECT nextval('seq3');
460+
nextval
461+
---------
462+
1
463+
(1 row)
464+
465+
REVOKE ALL ON seq3 FROM seq_user;
466+
GRANT SELECT ON seq3 TO seq_user;
467+
SELECT lastval();
468+
lastval
469+
---------
470+
1
471+
(1 row)
472+
473+
ROLLBACK;
474+
BEGIN;
475+
SET LOCAL SESSION AUTHORIZATION seq_user;
476+
CREATE SEQUENCE seq3;
477+
SELECT nextval('seq3');
478+
nextval
479+
---------
480+
1
481+
(1 row)
482+
483+
REVOKE ALL ON seq3 FROM seq_user;
484+
GRANT UPDATE ON seq3 TO seq_user;
380485
SELECT lastval();
381486
ERROR: permission denied for sequence seq3
487+
ROLLBACK;
488+
BEGIN;
489+
SET LOCAL SESSION AUTHORIZATION seq_user;
490+
CREATE SEQUENCE seq3;
491+
SELECT nextval('seq3');
492+
nextval
493+
---------
494+
1
495+
(1 row)
496+
497+
REVOKE ALL ON seq3 FROM seq_user;
498+
GRANT USAGE ON seq3 TO seq_user;
499+
SELECT lastval();
500+
lastval
501+
---------
502+
1
503+
(1 row)
504+
382505
ROLLBACK;
383506
-- Sequences should get wiped out as well:
384507
DROP TABLE serialTest, serialTest2;

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

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -367,6 +367,41 @@ DROP SEQUENCE seq2;
367367
SELECT lastval();
368368
ERROR: lastval is not yet defined in this session
369369
CREATE USER seq_user;
370+
-- privileges tests
371+
-- nextval
372+
BEGIN;
373+
SET LOCAL SESSION AUTHORIZATION seq_user;
374+
CREATE SEQUENCE seq3;
375+
REVOKE ALL ON seq3 FROM seq_user;
376+
GRANT SELECT ON seq3 TO seq_user;
377+
SELECT nextval('seq3');
378+
ERROR: permission denied for sequence seq3
379+
ROLLBACK;
380+
BEGIN;
381+
SET LOCAL SESSION AUTHORIZATION seq_user;
382+
CREATE SEQUENCE seq3;
383+
REVOKE ALL ON seq3 FROM seq_user;
384+
GRANT UPDATE ON seq3 TO seq_user;
385+
SELECT nextval('seq3');
386+
nextval
387+
---------
388+
1
389+
(1 row)
390+
391+
ROLLBACK;
392+
BEGIN;
393+
SET LOCAL SESSION AUTHORIZATION seq_user;
394+
CREATE SEQUENCE seq3;
395+
REVOKE ALL ON seq3 FROM seq_user;
396+
GRANT USAGE ON seq3 TO seq_user;
397+
SELECT nextval('seq3');
398+
nextval
399+
---------
400+
1
401+
(1 row)
402+
403+
ROLLBACK;
404+
-- currval
370405
BEGIN;
371406
SET LOCAL SESSION AUTHORIZATION seq_user;
372407
CREATE SEQUENCE seq3;
@@ -377,8 +412,96 @@ SELECT nextval('seq3');
377412
(1 row)
378413

379414
REVOKE ALL ON seq3 FROM seq_user;
415+
GRANT SELECT ON seq3 TO seq_user;
416+
SELECT currval('seq3');
417+
currval
418+
---------
419+
1
420+
(1 row)
421+
422+
ROLLBACK;
423+
BEGIN;
424+
SET LOCAL SESSION AUTHORIZATION seq_user;
425+
CREATE SEQUENCE seq3;
426+
SELECT nextval('seq3');
427+
nextval
428+
---------
429+
1
430+
(1 row)
431+
432+
REVOKE ALL ON seq3 FROM seq_user;
433+
GRANT UPDATE ON seq3 TO seq_user;
434+
SELECT currval('seq3');
435+
ERROR: permission denied for sequence seq3
436+
ROLLBACK;
437+
BEGIN;
438+
SET LOCAL SESSION AUTHORIZATION seq_user;
439+
CREATE SEQUENCE seq3;
440+
SELECT nextval('seq3');
441+
nextval
442+
---------
443+
1
444+
(1 row)
445+
446+
REVOKE ALL ON seq3 FROM seq_user;
447+
GRANT USAGE ON seq3 TO seq_user;
448+
SELECT currval('seq3');
449+
currval
450+
---------
451+
1
452+
(1 row)
453+
454+
ROLLBACK;
455+
-- lastval
456+
BEGIN;
457+
SET LOCAL SESSION AUTHORIZATION seq_user;
458+
CREATE SEQUENCE seq3;
459+
SELECT nextval('seq3');
460+
nextval
461+
---------
462+
1
463+
(1 row)
464+
465+
REVOKE ALL ON seq3 FROM seq_user;
466+
GRANT SELECT ON seq3 TO seq_user;
467+
SELECT lastval();
468+
lastval
469+
---------
470+
1
471+
(1 row)
472+
473+
ROLLBACK;
474+
BEGIN;
475+
SET LOCAL SESSION AUTHORIZATION seq_user;
476+
CREATE SEQUENCE seq3;
477+
SELECT nextval('seq3');
478+
nextval
479+
---------
480+
1
481+
(1 row)
482+
483+
REVOKE ALL ON seq3 FROM seq_user;
484+
GRANT UPDATE ON seq3 TO seq_user;
380485
SELECT lastval();
381486
ERROR: permission denied for sequence seq3
487+
ROLLBACK;
488+
BEGIN;
489+
SET LOCAL SESSION AUTHORIZATION seq_user;
490+
CREATE SEQUENCE seq3;
491+
SELECT nextval('seq3');
492+
nextval
493+
---------
494+
1
495+
(1 row)
496+
497+
REVOKE ALL ON seq3 FROM seq_user;
498+
GRANT USAGE ON seq3 TO seq_user;
499+
SELECT lastval();
500+
lastval
501+
---------
502+
1
503+
(1 row)
504+
382505
ROLLBACK;
383506
-- Sequences should get wiped out as well:
384507
DROP TABLE serialTest, serialTest2;

‎src/test/regress/sql/sequence.sql

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -168,11 +168,86 @@ SELECT lastval();
168168

169169
CREATEUSERseq_user;
170170

171+
-- privileges tests
172+
173+
-- nextval
174+
BEGIN;
175+
SET LOCAL SESSION AUTHORIZATION seq_user;
176+
CREATESEQUENCEseq3;
177+
REVOKE ALLON seq3FROM seq_user;
178+
GRANTSELECTON seq3 TO seq_user;
179+
SELECT nextval('seq3');
180+
ROLLBACK;
181+
182+
BEGIN;
183+
SET LOCAL SESSION AUTHORIZATION seq_user;
184+
CREATESEQUENCEseq3;
185+
REVOKE ALLON seq3FROM seq_user;
186+
GRANTUPDATEON seq3 TO seq_user;
187+
SELECT nextval('seq3');
188+
ROLLBACK;
189+
190+
BEGIN;
191+
SET LOCAL SESSION AUTHORIZATION seq_user;
192+
CREATESEQUENCEseq3;
193+
REVOKE ALLON seq3FROM seq_user;
194+
GRANT USAGEON seq3 TO seq_user;
195+
SELECT nextval('seq3');
196+
ROLLBACK;
197+
198+
-- currval
199+
BEGIN;
200+
SET LOCAL SESSION AUTHORIZATION seq_user;
201+
CREATESEQUENCEseq3;
202+
SELECT nextval('seq3');
203+
REVOKE ALLON seq3FROM seq_user;
204+
GRANTSELECTON seq3 TO seq_user;
205+
SELECT currval('seq3');
206+
ROLLBACK;
207+
208+
BEGIN;
209+
SET LOCAL SESSION AUTHORIZATION seq_user;
210+
CREATESEQUENCEseq3;
211+
SELECT nextval('seq3');
212+
REVOKE ALLON seq3FROM seq_user;
213+
GRANTUPDATEON seq3 TO seq_user;
214+
SELECT currval('seq3');
215+
ROLLBACK;
216+
217+
BEGIN;
218+
SET LOCAL SESSION AUTHORIZATION seq_user;
219+
CREATESEQUENCEseq3;
220+
SELECT nextval('seq3');
221+
REVOKE ALLON seq3FROM seq_user;
222+
GRANT USAGEON seq3 TO seq_user;
223+
SELECT currval('seq3');
224+
ROLLBACK;
225+
226+
-- lastval
227+
BEGIN;
228+
SET LOCAL SESSION AUTHORIZATION seq_user;
229+
CREATESEQUENCEseq3;
230+
SELECT nextval('seq3');
231+
REVOKE ALLON seq3FROM seq_user;
232+
GRANTSELECTON seq3 TO seq_user;
233+
SELECT lastval();
234+
ROLLBACK;
235+
236+
BEGIN;
237+
SET LOCAL SESSION AUTHORIZATION seq_user;
238+
CREATESEQUENCEseq3;
239+
SELECT nextval('seq3');
240+
REVOKE ALLON seq3FROM seq_user;
241+
GRANTUPDATEON seq3 TO seq_user;
242+
SELECT lastval();
243+
ROLLBACK;
244+
171245
BEGIN;
172246
SET LOCAL SESSION AUTHORIZATION seq_user;
173247
CREATESEQUENCEseq3;
174248
SELECT nextval('seq3');
175249
REVOKE ALLON seq3FROM seq_user;
250+
GRANT USAGEON seq3 TO seq_user;
176251
SELECT lastval();
177252
ROLLBACK;
178253

0 commit comments

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