How to call DROP USER from pl/pgsql?

user_1980863 0
Viewed 96

How to call DROP USER from Pl/PgSql (see example) ?

CREATE PROCEDURE myfunc()
LANGUAGE PLPGSQL AS
$$
DECLARE
  super_users TEXT[];
  ldap_users TEXT[];
  u TEXT;
BEGIN
  super_users := ARRAY(SELECT usename::TEXT FROM pg_catalog.pg_user WHERE usesuper);
  ldap_users := ARRAY(SELECT uid::TEXT FROM ldap_users);

  FOREACH u IN ARRAY ldap_users LOOP
    IF (u <> 'postgres' AND u <> ALL(super_users)) THEN
      DROP USER IF EXISTS u;
    END IF;
  END LOOP;
END;
$$;

It leads to error that "role u does not exist"...

IMHO PL/PGSQL does not treat u as a variable, but as a name. And DROP USER... is not SQL but some extension. How to do it? Maybe some system function? Or special syntax to substitute u?

EDIT: My solution (just found):

DECLARE
  stm TEXT;
  ...
BEGIN
  ...
  stm := 'DROP USER IF EXISTS "' || u '"';
  EXECUTE stm;
...

It seems to work. Maybe there is other solutions? More canonical?

dynamic-sql plpgsql postgresql
2 Answers

You need dynamic SQL for this:

execute format('DROP USER IF EXISTS %I', u);
user_3303150

Try this:

DROP OWNED BY user;
DROP ROLE user;
DROP USER user;

It works for me.

P.d: After checking SOF, you can try:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM user;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM user;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM user;
DROP USER user;

Cheers mate.

user_57702160
Related