postgres user has no access to public schema

721 views Asked by At

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.

1

There are 1 answers

3
Bart Friederichs On

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