Re: Converting to identity columns with domains on PK columns

On 7/5/19 1:55 PM, PegoraroF10 wrote:
> - Because we don´t need to give rigths to user on sequences;
> - Nobody will change values of pk fields, because we would like to have
> GENERATE ALWAYS on those PK Fields.

An IDENTITY column is still backed by a sequence:

create table identity_test(id integer PRIMARY KEY GENERATED BY DEFAULT
AS IDENTITY);

\ds identity_test_id_seq
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+---------
public | identity_test_id_seq | sequence | aklaver

You end up with same thing as using a sequence(with some additional
syntax over its behavior):

create table seq_id_test(id integer PRIMARY KEY);

create sequence seq_id_test_seq AS integer OWNED BY seq_id_test.id;

\ds seq_id_test_seq
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+---------
public | seq_id_test_seq | sequence | aklaver

Rights are the same:

\c - production
You are now connected to database "test" as user "production".

test_(production)> insert into identity_test (id) values(default);
ERROR: permission denied for table identity_test
test_(production)> insert into seq_id_test (id) values(default);
ERROR: permission denied for table seq_id_test

A user can change the PK by using OVERRIDING SYSTEM VALUE in an INSERT.

>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com