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.
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
postgresowner made it work: