apply_priv

Part of my go/vue/postgres project template, this function applies a standard set of privileges for a user. In my project template this is applied to the app_user whcih the go backend uses to conenct with.

CREATE OR REPLACE FUNCTION apply_priv(schema_name TEXT, user_name TEXT) RETURNS void AS $$
DECLARE
    func RECORD;
    proc RECORD;
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = user_name) THEN
        -- ✅ Grant database connection
        EXECUTE format('GRANT CONNECT ON DATABASE %I TO %I;', current_database(), user_name);

        -- ✅ Grant CRUD (DML) permissions
        EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA %I TO %I;', schema_name, user_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO %I;', schema_name, user_name);

        -- ❌ Prevent Schema modification
        EXECUTE format('REVOKE CREATE ON SCHEMA %I FROM %I;', schema_name, user_name);

        -- ✅ Grant EXECUTE on all FUNCTIONS
        FOR func IN 
            SELECT n.nspname AS schema, p.proname AS function
            FROM pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
            WHERE n.nspname = schema_name AND p.prokind = 'f'  -- 'f' = function
        LOOP
            EXECUTE format('GRANT EXECUTE ON FUNCTION %I.%I TO %I;', func.schema, func.function, user_name);
        END LOOP;

        -- ✅ Grant EXECUTE on all PROCEDURES
        FOR proc IN 
            SELECT n.nspname AS schema, p.proname AS procedure
            FROM pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
            WHERE n.nspname = schema_name AND p.prokind = 'p'  -- 'p' = procedure
        LOOP
            EXECUTE format('GRANT EXECUTE ON PROCEDURE %I.%I TO %I;', proc.schema, proc.procedure, user_name);
        END LOOP;

        -- ❌ Prevent creating/dropping triggers, but allow execution
        EXECUTE format('REVOKE TRIGGER ON ALL TABLES IN SCHEMA %I FROM %I;', schema_name, user_name); --Why no loop? See section Why No GRANT EXECUTE ON TRIGGER
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I REVOKE TRIGGER ON TABLES FROM %I;', schema_name, user_name);

        -- ✅ Allow sequence usage for auto-increment fields
        EXECUTE format('GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA %I TO %I;', schema_name, user_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO %I;', schema_name, user_name);

        RAISE NOTICE 'Privileges granted to %', user_name;
    ELSE
        RAISE NOTICE 'Role % does not exist. Skipping grants.', user_name;
    END IF;
END $$ LANGUAGE plpgsql;

-- Usage
SELECT apply_priv('public', 'app_user');

🚀 Why No GRANT EXECUTE ON TRIGGER?

🚨 In PostgreSQL, there is no EXECUTE ON TRIGGER privilege because:

• Triggers are part of tables.
• Any user with INSERT, UPDATE, or DELETE on the table will fire the trigger automatically.

✅ You only need to revoke TRIGGER if you want to prevent app_user from creating new ones:

REVOKE TRIGGER ON ALL TABLES IN SCHEMA public FROM app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE TRIGGER ON TABLES FROM app_user;

❌ This does NOT prevent the user from firing existing triggers—just from creating or deleting them.

---

🔥 Summary

Type	Manually Executed?	How to Grant Execution?
Functions (SELECT my_func())	✅ Yes	GRANT EXECUTE ON FUNCTION
Procedures (CALL my_proc())	✅ Yes	GRANT EXECUTE ON PROCEDURE
Triggers (AFTER INSERT ON my_table)	❌ No (Runs Automatically)	No EXECUTE needed, just TRIGGER privileges

---

🚀 Takeaways

✔ Procedures need explicit GRANT EXECUTE because they are manually called.
✔ Triggers don’t need EXECUTE because they are fired automatically when conditions are met.
✔ If you don’t want the user to modify triggers, revoke TRIGGER (but of course they will still execute/fire).