Postgres: Inserting rows into table with foreign key

Question

I have a table with a foreign key type which links to table wp_types. Whenever I try and insert a row into that table I get the error:

ERROR:  permission denied for schema base
LINE 1: SELECT 1 FROM ONLY "base"."wp_types" x WHERE "id" OPERATOR(p...
                       ^
QUERY:  SELECT 1 FROM ONLY "base"."wp_types" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x

The query is being executed as postgres. Also I do not understand what this query is trying to do, it is clearly linked with checking the foreign key constraints but I don't understand how.

What is the reason for using ONLY in your query?

Jerzy Pawlikowski
– Jerzy Pawlikowski

2013-12-08 04:19:27 +00:00
Commented Dec 8, 2013 at 4:19 — Jerzy Pawlikowski
– Jerzy Pawlikowski, Commented Dec 8, 2013 at 4:19

Jerzy Pawlikowski · Accepted Answer · 2013-12-08 04:24:44Z

2

From postgresql docs:

If ONLY is specified, only that table is scanned. If ONLY is not specified, the table and all its descendant tables (if any) are scanned.

As you mentioned error is connected with checking foreign keys. When ONLY is specified postgres can not check them and it may cause an error.

answered Dec 8, 2013 at 4:24

Jerzy Pawlikowski

1,9991 gold badge21 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Russell Fulton Over a year ago

I get that. What I don't understand is the permissions issue or how to work out what is wrong. The both tables are owned by the user that is trying to do the insert and that user has ALL on all tables and sequences in the schema.

Felix ZY · Accepted Answer · 2024-10-26 21:18:00Z

I ran into this very issue tonight and found the answer to the question of why in this answer by @jnas. Paraphrasing here as I found this question first:

The SELECT statement mentioned in the original question (I had to enable extra logging to even see this) is generated by Postgres to validate your foreign key constraint. It's this check that causes the permission denied for schema error, not your INSERT query.

Essentially, Postgres executes the FK checks as the database owner, not as the current user. If the database owner does not have permission to access your schema (for example, if you - like me - created it with the postgres user), the check will fail due to insufficient permissions.

In my case,

ALTER SCHEMA :SCHEMA_NAME OWNER TO :DB_OWNER;

was enough to fix this issue.

Chris Travers · Accepted Answer · 2013-12-23 02:53:21Z

0

Two points:

Is it possible you dropped the superuser permissions from the postgres user?
What are the permissions for the base schema?

Now one thing to consider is that you aren't sure what the query is doing. A simple EXPLAIN can show you what it is doing, but the error suggests the problem is with schema permissions, not with what the query is doing.

Try:

 GRANT USAGE ON SCHEMA base TO postgres;

answered Dec 23, 2013 at 2:53

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Collectives™ on Stack Overflow

Postgres: Inserting rows into table with foreign key

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related