Sunday, September 27, 2015

Run PLpgSQL function before login

I wrote simple extension session_exec. This extension enforce running some specified function before user is connected to PostgreSQL. This function can be used for some complex user management.

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.

Attention:

When you use login function, the connect to database will be little bit slower. Use it only when it is necessary.

2 Comments:

At September 28, 2015 at 2:40 PM , Blogger Ross Reedstrom said...

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.

 
At November 16, 2015 at 12:36 PM , Blogger Ross Reedstrom said...

I 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.

 

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home