Ошибка нет доступа к схеме public

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with:

revoke usage on schema public from public;

This is required when it’s not desirable that people peek into other people schemas, even without selecting data from tables (which is granted through different privileges).

If this REVOKE hasn’t been done in that database, it may have happened in the template database by which new databases are modelled (see CREATE DATABASE).

When a user has USAGE privilege, but lacks CREATE privilege on the schema, it’s a different error when trying to create an object: permission denied for schema public.

To check the privileges inside psql, use dn+ public.

By default (shown with extended display x for readability):

# dn+ public
List of schemas
-[ RECORD 1 ]-----+-----------------------
Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =UC/postgres
Description       | standard public schema

lack of a rolename before = means it’s for all roles (=public)

Without public USAGE privilege

Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =C/postgres
Description       | standard public schema

Without public USAGE or CREATE privileges

Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
Description       | standard public schema

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with:

revoke usage on schema public from public;

This is required when it’s not desirable that people peek into other people schemas, even without selecting data from tables (which is granted through different privileges).

If this REVOKE hasn’t been done in that database, it may have happened in the template database by which new databases are modelled (see CREATE DATABASE).

When a user has USAGE privilege, but lacks CREATE privilege on the schema, it’s a different error when trying to create an object: permission denied for schema public.

To check the privileges inside psql, use dn+ public.

By default (shown with extended display x for readability):

# dn+ public
List of schemas
-[ RECORD 1 ]-----+-----------------------
Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =UC/postgres
Description       | standard public schema

lack of a rolename before = means it’s for all roles (=public)

Without public USAGE privilege

Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
                  | =C/postgres
Description       | standard public schema

Without public USAGE or CREATE privileges

Name              | public
Owner             | postgres
Access privileges | postgres=UC/postgres
Description       | standard public schema

grant usage on schema myschema to newuser;

grant select on all tables in schema myschema to newuser;

select * from myschema.table;

SQL Error [42501]: ERROR: permission denied for schema myschema

Ответы
2

Другие вопросы по теме

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