I want to create a "demi/sub" super user, which can perform duties as admin user, but shouldn't be able to delete or create Dbs, alter system, but must have all privileges except delete on all tables, all dbs, and all schemas. (I know with a "global" role on all dbs, tables...etc. its possible to achieve this, or assigning a session role at login -like postgres-, but I prefer to avoid this methods).
I have this query:
CREATE USER db_admin NOSUPERUSER NOREPLICATION NOBYPASSRLS NOINHERIT NOCREATEDB NOCREATEROLE LOGIN PASSWORD 'mypassword';
(optionally tried:) IN ROLE postgres; -with inherit-
GRANT USAGE ON SCHEMA public TO db_admin;
GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_admin;
(Also tried with GRANT ALL PRIVILEGES ON SCHEMA)
But it returns:
SQL Error [42501]: ERROR: permission denied for schema public
Position: 14
ERROR: permission denied for schema public
Position: 14
ERROR: permission denied for schema public
Position: 14
Any ideas on how make this work?