Configuration:
session_preload_libraries to session_exec session_exec.login_name to login
Usage:
# first login [pavel@dhcppc4 plpgsql_check]$ psql WARNING: function "login()" does not exist postgres=# CREATE OR REPLACE FUNCTION login() postgres-# RETURNS void AS $$ postgres$# BEGIN postgres$# IF current_database() <> 'admindb' AND session_user = 'postgres' THEN postgres$# RAISE EXCEPTION 'user postgres is blocked'; postgres$# ELSE postgres$# RAISE NOTICE 'Hello, %', session_user; postgres$# END IF; postgres$# RETURN; postgres$# END; postgres$# $$ LANGUAGE plpgsql; # new login [pavel@dhcppc4 plpgsql_check]$ psql NOTICE: Hello, pavel -- try to login as Postgres postgres=# \c postgres postgres FATAL: unhandled exception in login function "login" DETAIL: user postgres is blocked CONTEXT: PL/pgSQL function login() line 4 at RAISE session_exec: perform login function "login" Previous connection kept
This is example only. For this specific use case the modification of
pg_hba.conf
is better. But you can implement more complex logic in plpgsql. The login function can be specific for database or for user via ALTER DATABASE xx SET
or ALTER ROLE xx SET
.
Looks useful - I've been looking for a way to run a specific function to configure the plpython interpreter on a per-database basis (enabling using diff. virtualenv setups) this looks like it will do the trick.
ReplyDeleteI have in fact gotten this to work for initializing the plpythonu interpreter per session, including activating a virtualenv. I'll fire a PR at you for two things: back ported to build on > 9.0, since it can use the local_preload_libraries setting (not quite as convenient as setting session_preload_libraries on a per database basis, but still works) and I'm going to suggest renaming the variable from login_name to function_name, since it's really a per session exec of an arbitrary function. This leads to the rather nice IMHO setting name 'session_exec.function_name', which I think fairly clearly specifies 'name of function to execute at session start' login_name seems to me to be something about the username used to login, by contrast.
ReplyDelete