PostgreSQL：在循环中授予权限的触发器函数

我有一个表用户：

CREATE TABLE users (
    id serial primary key,
    name text,
    status text,
    tier text,
    project text[]);

列项目是一个模式名称数组。我想创建一个触发器函数，当用户添加到表中时，该函数将赠款用户一组权限。一个用户可以访问多个模式，因此我的想法是循环遍历一个模式名称数组。

CREATE OR REPLACE FUNCTION common.add_user()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
DECLARE
    u_name text = NEW.name;
    m text;
    project text[] = NEW.project;
begin 
    FOREACH m IN ARRAY project LOOP
        EXECUTE 'GRANT USAGE ON SCHEMA ' || m || ' TO ' || u_name;
    END LOOP;
RETURN NEW;
end;
$BODY$;

触发器：

CREATE TRIGGER add_user
    BEFORE INSERT
    ON common.users
    FOR EACH ROW
    EXECUTE PROCEDURE common.add_user();

当我向common.users表添加条目时：

INSERT INTO common.users (name, project) VALUES ('user01', '{"schema01", "schema02"}');

它执行时没有错误，一个条目被添加到表中，但没有授予任何特权。当然，我确保插入查询是由实际上可以授予那些特权的用户执行的。

实际上，我已经很接近了，也许是循环内部执行语法的问题？无论如何，这正是我想要的：

CREATE OR REPLACE FUNCTION common.add_user()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
DECLARE
    u_name text = NEW.name;
    m text;
    project text[] = NEW.project;
begin 
    EXECUTE 'GRANT CONNECT ON DATABASE x TO ' ||u_name;
    EXECUTE 'GRANT USAGE ON SCHEMA public TO ' ||u_name;
    EXECUTE 'GRANT USAGE ON SCHEMA common TO ' ||u_name;
    EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA common TO ' ||u_name;
    FOREACH m IN ARRAY project LOOP
        EXECUTE format('GRANT USAGE ON SCHEMA %1$s to %2$s', m, u_name);
        EXECUTE format('GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA %1$s to %2$s', m, u_name);
        EXECUTE format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA %1$s to %2$s', m, u_name);
        EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %1$s to %2$s', m, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES to %2$s', m, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s GRANT EXECUTE ON FUNCTIONS to %2$s', m, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s GRANT USAGE, SELECT ON SEQUENCES to %2$s', m, u_name);
    END LOOP;
RETURN NEW;
end;
$BODY$;

我还希望能够通过修改列项目中的值来轻松地管理对项目（模式）的访问。与表上的RLS一起管理权限现在非常简单。

CREATE OR REPLACE FUNCTION common.update_user()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
DECLARE
    u_name text = OLD.name;
    m text;
    n text;
    deleted text[];
    added text[];
begin 
    deleted := ARRAY(select * from unnest(OLD.project) except select * from unnest(NEW.project));
    added := ARRAY(select * from unnest(NEW.project) except select * from unnest(OLD.project));
    FOREACH m IN ARRAY deleted LOOP
        Raise warning 'REVOKED ACCESS ON PROJECT % FROM %', m, u_name;
        EXECUTE format('REVOKE USAGE ON SCHEMA %1$s FROM %2$s', m, u_name);
        EXECUTE format('REVOKE ALL ON ALL TABLES IN SCHEMA %1$s FROM %2$s', m, u_name);
        EXECUTE format('REVOKE USAGE, SELECT ON ALL SEQUENCES IN SCHEMA %1$s FROM %2$s', m, u_name);
        EXECUTE format('REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA %1$s FROM %2$s', m, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s REVOKE ALL ON TABLES FROM %2$s', m, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s REVOKE EXECUTE ON FUNCTIONS FROM %2$s', m, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s REVOKE USAGE, SELECT ON SEQUENCES FROM %2$s', m, u_name);
    END LOOP;
    FOREACH n IN ARRAY added LOOP
        Raise warning 'GRANTED ACCESS ON PROJECT % TO %', n, u_name;
        EXECUTE format('GRANT USAGE ON SCHEMA %1$s to %2$s', n, u_name);
        EXECUTE format('GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA %1$s to %2$s', n, u_name);
        EXECUTE format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA %1$s to %2$s', n, u_name);
        EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %1$s to %2$s', n, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES to %2$s', n, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s GRANT EXECUTE ON FUNCTIONS to %2$s', n, u_name);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %1$s GRANT USAGE, SELECT ON SEQUENCES to %2$s', n, u_name);
    END LOOP;
RETURN NEW;
end;
$BODY$;

PostgreSQL：在循环中授予权限的触发器函数

1条答案

相关问题

热门标签

最新问答