Can not insert a value having all permisions also owner of the table on postgresql

Question

I have this table:

sapiens=# \d financiero.almac_solicitud_paz_salvo
                                                                  Table "financiero.almac_solicitud_paz_salvo"
         Column         |            Type             |                                                        Modifiers
------------------------+-----------------------------+-------------------------------------------------------------------------------------------------------------------------
 id_solicitud_paz_salvo | bigint                      | not null default nextval(('financiero.sq_financiero_almac_solicitud_paz_salvo_id_solicitud_paz_salvo'::text)::regclass)
 fec_solicitud          | timestamp(0) with time zone | not null
 id_persona             | integer                     | not null
 observacion            | text                        |
 id_estado              | smallint                    | not null
 id_tipo_paz_salvo      | smallint                    | not null
 id_usuario             | bigint                      |
Indexes:
    "pk_financiero_almac_solicitud_paz_salvo_id_solicitud_paz_salvo" PRIMARY KEY, btree (id_solicitud_paz_salvo)
Foreign-key constraints:
    "fk_financiero_almac_solicitud_paz_salvo_id_estado" FOREIGN KEY (id_estado) REFERENCES general.gener_estado(id_estado)
    "fk_financiero_almac_solicitud_paz_salvo_id_persona" FOREIGN KEY (id_persona) REFERENCES general.gener_persona(id_persona)
    "fk_financiero_almac_solicitud_paz_salvo_id_tipo_paz_salvo" FOREIGN KEY (id_tipo_paz_salvo) REFERENCES financiero.almac_tipo_paz_salvo(id_tipo_paz_salvo)

ureport is the user that have all permissions, also I set owner of the table

sapiens=# \dp financiero.almac_solicitud_paz_salvo
                                               Access privileges
   Schema   |           Name            | Type  |       Access privileges       | Column privileges | Policies
------------+---------------------------+-------+-------------------------------+-------------------+----------
 financiero | almac_solicitud_paz_salvo | table | ureport=arwdDxt/ureport      +|                   |

but when I want to insert a value, like this:

INSERT INTO financiero.almac_solicitud_paz_salvo
(fec_solicitud, id_persona, observacion, id_estado, id_tipo_paz_salvo, id_usuario)
VALUES('2022-09-19'::timestamp, 17962, '', 2, 3, 17);

I get:

ERROR:  permission denied for relation almac_tipo_paz_salvo
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "financiero"."almac_tipo_paz_salvo" x WHERE "id_tipo_paz_salvo" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

********** Error **********

ERROR: permission denied for relation almac_tipo_paz_salvo
SQL state: 42501
Context: SQL statement "SELECT 1 FROM ONLY "financiero"."almac_tipo_paz_salvo" x WHERE "id_tipo_paz_salvo" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

why is happening that?, thanks for your kindly support

This answer will help you: https://stackoverflow.com/a/15522548/10230741 — ardritkrasniqi, Sep 20 '22 at 14:12
The error message mentions a different table, not the one used in the insert. Do you have any triggers or so? — Frank Heikens, Sep 20 '22 at 14:27
It’s the foreign key. you need select permissions on the table otherwise the foreign key can’t be validated — Frank Heikens, Sep 20 '22 at 16:19
No it doesn't, you're looking at the wrong table: You DO have permissions for almac_solicitud_paz_salvo, but NOT for almac_tipo_paz_salvo. It's that select statement that failed and because of that, the insert failed. "tips" vs "solicitud" — Frank Heikens, Sep 21 '22 at 07:30

Pauel · Answer 1 · 2022-09-20T17:47:18.963

0

You are using nexval sequence, so it should have USAGE privilege too: GRANT USAGE, SELECT ON SEQUENCE financiero.sq_financiero_almac_solicitud_paz_salvo_id_solicitud_paz_salvo TO sapiens; where sapiens is your role. Don't forget to choose the database before executing the privilege grant commands

edited Sep 20 '22 at 17:47

answered Sep 20 '22 at 17:43

Pauel

11
2

sapiens is the database, ureport is the role , and like I said it has all privileges – Manuel Córdoba Sep 20 '22 at 22:02
can you show>: \dp financiero.almac_tipo_paz_salvo ? – Pauel Sep 21 '22 at 03:37

Can not insert a value having all permisions also owner of the table on postgresql

1 Answers1