CODEWITHSUNDEEP
sqlpostgresqlplpgsqlsql-grantpostgresql-9.6
fadedbee
fadedbee

Reputation: 44807

How to use data (rather than an identifier) to grant postgres permissions

I want to use the following function (as a trigger on a table's inserts):

CREATE OR REPLACE FUNCTION insert_authid_fn() RETURNS trigger AS $$
BEGIN
  GRANT ALL ON DATABASE testdb TO NEW.username;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

I get the error:

ERROR:  syntax error at or near "."
LINE 3:   GRANT ALL ON DATABASE testdb TO NEW.username;

because New.username is value, rather than an identifier.

How can I achieve this GRANT?

Upvotes: 0

Views: 16

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51609

syntactically:

CREATE OR REPLACE FUNCTION insert_authid_fn() RETURNS trigger AS $$
BEGIN
  execute format ('GRANT ALL ON DATABASE testdb TO %I',NEW.username);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

But I doubt it is a good idea in general

Upvotes: 1

Related Questions