CODEWITHSUNDEEP
sqlpostgresql
Russell Fulton
Russell Fulton

Reputation: 590

Postgres: Inserting rows into table with foreign key

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.

Upvotes: 4

Views: 1661

Answers (3)

Felix ZY
Felix ZY

Reputation: 976

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.

Upvotes: 1

Chris Travers
Chris Travers

Reputation: 26454

Two points:

  1. Is it possible you dropped the superuser permissions from the postgres user?

  2. 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;

Upvotes: 0

Jerzy Pawlikowski
Jerzy Pawlikowski

Reputation: 1969

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.

Upvotes: 2

Related Questions