Postgres INSERT ERROR: permission denied for schema public

Question

In Postgres I created the following table inside a db called testing:

CREATE TABLE category_google_taxonomy (
    category_id integer references category ON UPDATE CASCADE ON DELETE CASCADE,
    google_taxonomy_id integer references google_taxonomy ON UPDATE CASCADE ON DELETE     CASCADE
);

When I try to populate the table:

INSERT INTO category_google_taxonomy (category_id, google_taxonomy_id) VALUES
(1,7),
(2,12);

I get the following error:

ERROR:  permission denied for schema public
LINE 1: SELECT 1 FROM ONLY "public"."category" x WHERE "category_id"...
                       ^
QUERY:  SELECT 1 FROM ONLY "public"."category" x WHERE "category_id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x

I read up a bit and eventually granted ALL PRIVILEGES out of exasperation, but it still doesn't work:

testing=# GRANT ALL PRIVILEGES ON public.category TO testing;
GRANT

testing=# \dp category_google_taxonomy
                                   Access privileges
 Schema |           Name           | Type  |    Access privileges    | Column access privileges 
--------+--------------------------+-------+-------------------------+--------------------------
 public | category_google_taxonomy | table | testing=arwdDxt/testing | 
                                           : super=arwdDxt/testing 


testing=# \dp category
                           Access privileges
 Schema |   Name   | Type  |   Access privileges    | Column access privileges 
--------+----------+-------+------------------------+--------------------------
 public | category | table | testing=arwdDxt/super | category_id:
                                                :   testing=arwx/super
(1 row)

On @Daniel's suggestion I tried GRANT USAGE ON schema public TO super;, now when I run the INSERT command I get:

ERROR:  permission denied for relation category
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."category" x WHERE "category_id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

Here is the relevant part of \d:

public | category                               | table    | super
public | category_google_taxonomy               | table    | testing

Daniel Vérité · Answer 1 · 2013-03-18T01:10:33.347

23

Assuming the username is testing, you probably want to do:

GRANT ALL ON schema public TO testing;

Note about granting ALL PRIVILEGES: you don't say on what this GRANT command was applied. Assuming it was ON DATABASE..., it just means CONNECT, CREATE and TEMP privileges, nothing about the public schema or any other contained object, which is why it "doesn't work".

EDIT: when that's not sufficient

If the tables referenced by the foreign keys are not owned by testing, their owner needs also to have the USAGE privilege on the schema in order to look up the referenced tables.

It's not obvious from the result of \dp (the result of \d would tell for sure) but if category is owned by super and that user also has no privilege on the schema, you'd need to assign it with:

GRANT USAGE ON schema public TO super;

edited Mar 18 '13 at 01:10

answered Mar 17 '13 at 21:00

Daniel Vérité

3,045
16
19

2

I ran the `GRANT ALL ON schema` but I am still getting the same error as before. `testing` is the correct user. I have added my original GRANT command, it was on the table, not the database as a whole. – Rudolf Vavruch Mar 17 '13 at 21:09
@Rudolf: ok, I think it's because `testing` does not own the referenced tables, see my edit – Daniel Vérité Mar 18 '13 at 01:11
thanks. I tried that and now I have a new error, which is progress. See above. – Rudolf Vavruch Mar 18 '13 at 18:36
Thankyou, finally solved my problem, stuck for a day with a table that has a foreign key that is not owned by the current user – Muklas Jan 17 '23 at 11:03

score 2 · Answer 2 · answered Mar 18 '13 at 19:07

2

I managed to solve this by doing:

ALTER TABLE category OWNER TO testing;

After which the INSERT went smoothly. I am concerned that I may have broken other things, by changing the owner from super, but that remains to be seen.

answered Mar 18 '13 at 19:07

Rudolf Vavruch

1,295
2
11
16

Did anything go bad from this? – VarunAgrawal Feb 08 '20 at 21:59
This was a few years ago, but as far as I recall nothing went wrong. – Rudolf Vavruch Feb 09 '20 at 10:28
Awesome. Thank you! – VarunAgrawal Feb 09 '20 at 11:50
Worked for me, even in pg15 – KhoPhi Dec 07 '22 at 01:57

score 0 · Answer 3 · answered Feb 14 '19 at 06:27

0

Theoretically, a user has select privilege on the parent table is good enough to insert into a child table. Do not need to be the owner of the parent table. I believe it is a bug from postgresql.

answered Feb 14 '19 at 06:27

Jinglun Zhang

1

Postgres INSERT ERROR: permission denied for schema public

3 Answers3