5

In my postgreSQL DB applications, I sometimes need to retrieve the next value of a sequence BEFORE running an insert. I used to make this by giving a “usage” privilege on such sequences to my users and using the “nextval” function. I recently begun to use “GENERATED BY DEFAULT AS IDENTITY” columns as primary keys, I am still able to retrieve nextval as superuser, but I cannot grant such privilege to other users. Where’s my mistake?

Here's an example:

-- <sequence>
CREATE SEQUENCE public.apps_apps_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE public.apps_apps_id_seq
    OWNER TO postgres;

GRANT USAGE ON SEQUENCE public.apps_apps_id_seq TO udocma;

GRANT ALL ON SEQUENCE public.apps_apps_id_seq TO postgres;
-- </sequence>

-- <table>
CREATE TABLE public.apps
(
    apps_id integer NOT NULL DEFAULT nextval('apps_apps_id_seq'::regclass),
    apps_born timestamp without time zone NOT NULL DEFAULT now(),
    apps_vrsn character varying(50) COLLATE pg_catalog."default",
    apps_ipad character varying(200) COLLATE pg_catalog."default",
    apps_dscr character varying(500) COLLATE pg_catalog."default",
    apps_date timestamp without time zone DEFAULT now(),
    CONSTRAINT apps_id_pkey PRIMARY KEY (apps_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.apps
    OWNER to postgres;

GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE public.apps TO udocma;
-- </table>

The client application is connected as ‘udocma’ and can use the “nextval” function to retrieve the next key of the sequence. If I use the identity column instead, I still can do this if I log as postgres, but if I log as udocma I don’t have a privilege to execute nextval on the “hidden” sequence that generates values for the identity column.

Thanyou. I realized that the statements

GRANT USAGE ON SEQUENCE public.apps_apps_id_seq TO udocma;

and

select nextval('apps_apps_id_seq'::regclass);

are still working if I define apps.apps_id as identity column instead of serial. So I guess that a field named 'somefield' defined as identity column in a table named 'sometable' should have some 'hidden' underlying sequence named 'sometable_somefiled_seq'. Is it right?

Jacopo Russo
  • 103
  • 1
  • 4
  • What grant exactly did you use? `nextval()` should be usable for the owner of the table. For others, you need to grant `usage` privilege on the underlying sequence –  Jul 24 '19 at 18:17
  • I don't see any grants you did for the table when using an `identity` column? –  Jul 25 '19 at 06:28
  • 7
    Yes, `identity` columns also use sequences. You can use `select pg_get_serial_sequence('app', 'apps_id')` to get the name of that sequence –  Jul 25 '19 at 12:55

0 Answers0