postgres user has no access to public schema

Question

I am getting the following error on postgresql 12:

ERROR:  permission denied for schema public

even though I am logged in as postgres (who is superuser), and I have granted all on the schema, and on all the tables:

GRANT all ON SCHEMA public TO postgres;
grant all on all tables in schema "public" to postgres;

And postgres is superuser:

=# \duS+
                                                                            List of roles
         Role name         |                         Attributes                         |                          Member of                           | Description 
---------------------------+------------------------------------------------------------+--------------------------------------------------------------+-------------
 postgres                  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}                                                           |

When checking the grants, it looks like I should have INSERT privileges:

 SELECT *                                                                                               
  FROM information_schema.role_table_grants 
 WHERE grantee ='postgres' and table_name='wms_zone';
 grantor  | grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+------------+----------------+--------------+----------------
 sapphire | postgres | table1        | public       | wms_zone   | TRIGGER        | YES          | NO
 sapphire | postgres | table1        | public       | wms_zone   | REFERENCES     | YES          | NO
 sapphire | postgres | table1        | public       | wms_zone   | TRUNCATE       | YES          | NO
 sapphire | postgres | table1        | public       | wms_zone   | DELETE         | YES          | NO
 sapphire | postgres | table1        | public       | wms_zone   | UPDATE         | YES          | NO
 sapphire | postgres | table1        | public       | wms_zone   | SELECT         | YES          | YES
 sapphire | postgres | table1        | public       | wms_zone   | INSERT         | YES          | NO

Update INSERTing into another table in the same database and schema does work.

Are you sure postgres role still has the Superuser attribute? What's \duS+ showing in psql? — Zegarek
– Zegarek, Commented Oct 4, 2023 at 9:44
What I'm getting at is that it's possible to rename postgres role to something else and create a regular role of that name, without the superuser attribute. The bootstrap role that does have to have this attribute, doesn't necessarily have to be named postgres, leaving the name to be used freely. If your postgres was in fact a superuser, they shouldn't be checked for privileges of any kind except for login. — Zegarek
– Zegarek, Commented Oct 4, 2023 at 9:56
Another possibility is that you issued (accidentally or through client initial query config) a set role not_postgres; making it so that even though you were connected as postgres, from that point you were acting as not_postgres role. select current_user; should clarify that. — Zegarek
– Zegarek, Commented Oct 4, 2023 at 11:16
@Zegarek Yes, postgres is superuser. See updated question. — Bart Friederichs
– Bart Friederichs, Commented Oct 4, 2023 at 12:04

Bart Friederichs · Accepted Answer · 2023-10-04 08:25:01Z

0

I don't know exactly why this causes this weird behaviour, but the problem was in the ownership of the tables.

This particular table had a foreign key set on a table that was not owned by postgres (it had all permissions though).

Setting all objects to the postgres owner made it work:

 REASSIGN OWNED BY olduser TO postgres

answered Oct 4, 2023 at 8:25

Bart Friederichs

33.8k16 gold badges112 silver badges219 bronze badges

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

3 Comments

jjanes Over a year ago

When checking foreign key constraints, the real user checks them as the owner of the table, which might mean temporarily giving up its super powers. So even a superuser can get permission errors in such cases. The complete error message should have contained more info which would have made this more clear.

Bart Friederichs Over a year ago

I understand, but the user had all permissions on the foreign table as well.

jjanes Over a year ago

The error says it was lacking access on the schema, not the table itself, so that is compatible with your observation. You can get into this state if the schema access was revoked after the table was already created, or maybe if the table ownership was changed by a superuser.

Collectives™ on Stack Overflow

postgres user has no access to public schema

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related