pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: sebastien@lardiere.net
Subject: BUG #17490: unconsistency between TRUNCATE RESTART IDENTITY and setval()
Date: Fri, 20 May 2022 13:13:36 +0000
Message-ID: <17490-fd63590229a42d2e@postgresql.org> (raw)

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

Message-ID: <17490-fd63590229a42d2e@postgresql.org>
Permalink:  ../17490-fd63590229a42d2e@postgresql.org/
Also on:    postgresql.org/message-id/17490-fd63590229a42d2e@postgresql.org

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, sebastien@lardiere.net
  Subject: Re: BUG #17490: unconsistency between TRUNCATE RESTART IDENTITY and setval()
  In-Reply-To: <17490-fd63590229a42d2e@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox