Thread: BUG #17490: unconsistency between TRUNCATE RESTART IDENTITY and setval()
BUG #17490: unconsistency between TRUNCATE RESTART IDENTITY and setval()
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 17490 Logged by: Sébastien Lardière Email address: sebastien@lardiere.net PostgreSQL version: 14.3 Operating system: Debian Description: I've found an unconsistency between TRUNCATE RESTART IDENTITY and setval() function. First, let's quote the documentation, about TRUNCATE: > When RESTART IDENTITY is specified, the implied ALTER SEQUENCE RESTART operations are also done transactionally; and about ALTER SEQUENCE RESTART: > This is similar to calling the setval function with is_called = false Actually, both are not equivalent in term of grants: role has to be owner of the sequence to run ALTER SEQUENCE RESTART, but only needs the grant UPDATE to run setval() on the sequence. So, when role is not the owner, he can TRUNCATE and setval() with the grants, but can't TRUNCATE RESTART IDENTITY, because of ALTER SEQUENCE RESTART. It's probably not a bug, but maybe ALTER SEQUENCE RESTART should be run with the same grant than setval(). Here's a simple usecase : \c postgres postgres DROP DATABASE IF EXISTS restart_seq_failed; DROP ROLE IF EXISTS dumbowner; DROP ROLE IF EXISTS dumbuser; CREATE ROLE dumbowner LOGIN; CREATE ROLE dumbuser LOGIN; CREATE DATABASE restart_seq_failed OWNER dumbowner ; \c restart_seq_failed dumbowner CREATE TABLE test_table ( id bigint GENERATED ALWAYS AS IDENTITY , val integer ); GRANT UPDATE , SELECT , USAGE ON SEQUENCE test_table_id_seq TO dumbuser; GRANT TRUNCATE ON TABLE test_table TO dumbuser; INSERT INTO test_table (val) SELECT g.v FROM generate_series(0 , 100) AS g(v); \c restart_seq_failed dumbuser TRUNCATE test_table RESTART IDENTITY; -- ERROR: must be owner of sequence test_table_id_seq ALTER SEQUENCE test_table_id_seq RESTART WITH 1; -- ERROR: must be owner of sequence test_table_id_seq TRUNCATE test_table; -- works SELECT setval('test_table_id_seq'::regclass,1,false); -- works \q