When my postgres function is tries to create a new sequence, it returns a permission denied error. It looks like the only way to make it work is to give Create permission on schema using the below statement.
GRANT CREATE ON SCHEMA public to "myuser"
But, this will allow the user to create any other type of object as well, including tables. How can we control this? I want my user to be able to create a sequence, but not tables.
Note: Create is not a valid grant on Sequences.
From this answer, an example of how to do this with an event trigger is
CREATE OR REPLACE FUNCTION check_ddl_event_user()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF (SELECT current_user) = 'USER_TO_BLOCK' AND tg_tag NOT = 'CREATE SEQUENCE' THEN
RAISE EXCEPTION 'USER_TO_BLOCK tried to access a forbidden resource, the action was %', tg_tag;
END IF;
END;
$$;
CREATE EVENT TRIGGER track_ddl_event
ON ddl_command_start
EXECUTE FUNCTION check_ddl_event_user();
Note that the trigger used is ddl_command_start which fires on a lot of events. The full list can be found here
There is no way to do this using permissions. The only option would be to create an event trigger that throws an exception if an undesirable object is created.
Make the fuction have security definer then the actions taken by the fuction will be applied with the permissions of the creator of the function.
https://www.postgresql.org/docs/current/sql-createfunction.html
