动态检测Postgresql中的角色 [英] Detect role in Postgresql dynamically
问题描述
我一直在尝试创建一个脚本,该脚本检测某个角色已经存在,并且如果这样做,则应该撤消所有特权。这样可以很好地完成工作:
I have been trying to create a script that detects that a role already excists and if it does it should revoke all privileges. This works fine doing it like this:
DO $$DECLARE count int;
BEGIN
SELECT count(*) INTO count FROM pg_roles WHERE rolname = 'superman';
IF count > 0 THEN
REVOKE ALL PRIVILEGES ON TABLE FROM superman;
END IF;
END$$;
但是现在我希望每个环境都是动态的,因为每个环境将使用不同的角色名称。因此,我尝试使用\set机制,但在使用pl / sql时似乎不起作用,因此,如果我做类似以下Postgresql的操作时会抱怨语法错误:
But now I want this to be dynamic per environment since I will be using different role names per environment. So I tried to use the \set mechanism but that doesn't seem to work when using pl/sql so if I would do something like the following Postgresql is complaining with syntax errors:
/set environment _int
DO $$DECLARE count int;
BEGIN
SELECT count(*) INTO count FROM pg_roles WHERE rolname = 'superman';
IF count > 0 THEN
REVOKE ALL PRIVILEGES ON TABLE FROM superman:environment;
END IF;
END$$;
尽管如果我不愿意在pl / sql中执行,则撤销声明也可以正常工作。因此,我的问题是如何通过向其传递参数来使脚本动态化,以便将其替换?
Although if I would not do it in pl/sql the revoke statment works just fine. So my question is how can I make my script dynamic by passing parameters to it so they will be replaced?
推荐答案
使用 EXECUTE
用于动态SQL。另外, DO
语句不能带有参数。创建一个plpgsql函数:
You have to use EXECUTE
for dynamic SQL. Also, a DO
statement cannot take parameters. Create a plpgsql function:
CREATE OR REPLACE FUNCTION f_revoke_all_from_role(_role text)
RETURNS void AS
$BODY$
BEGIN
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = _role) THEN
EXECUTE 'REVOKE ALL PRIVILEGES ON TABLE x FROM ' || quote_ident(_role);
END IF;
END;
$BODY$ LANGUAGE plpgsql;
致电:
SELECT f_revoke_all_from_role('superman');
-
IF
块使用EXISTS
更简单。我使用
quote_ident()
避免使用SQLi。I use
quote_ident()
to avoid SQLi.表名可以是函数的第二个参数...
The table name could be the second parameter of the function ...
这篇关于动态检测Postgresql中的角色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!