splendiddata / login_hook Goto Github PK

Hello,

First, thanks for publishing this extension.

We are interested using your extension, however we have a few comments on the source code:

l108: get_database_name() return value may be NULL and is not tested as such.
l121: emptyArgVector is not used, so line may be deleted, including l148.
l133: If error occurs in SPI_execute(), because something is wrong in the login_hook.login SQL function, nobody can log in, including SUPERUSER. Any error should be caught here with an appropriate log message.
l134: this line may be useless.

Regards

Hello,

For some time now, two or three months, I started to notice this error, when execute "create extension login_hook":

 ERROR:  catcache reference 0x7f1d28844cf0 is not owned by resource owner TopTransaction

On 16.0 version.

What's even stranger is that, if I run it a second time, immediately afterwards, I no longer have the error and the extension is created successfully.

Could it be a bug?

Thanks

When the replication target database has got the login_hook extension installed and the postgresql.conf file of that database has session_preload_libraries = 'login_hook' set, then that database crashes with logging:
2021-06-15 10:10:59 CEST [unknown] repmgr_repl pid:113094 xid:0 ip:10.18.0.8 FATAL: cannot read pg_class without having selected a database 2021-06-15 10:11:04 CEST [unknown] repmgr_repl pid:113095 xid:0 ip:10.18.0.8 FATAL: cannot read pg_class without having selected a database 2021-06-15 10:11:04 CEST [unknown] streaming_barman pid:113096 xid:0 ip:10.18.0.3 FATAL: cannot read pg_class without having selected a database 2021-06-15 10:11:09 CEST [unknown] repmgr_repl pid:113098 xid:0 ip:10.18.0.8 FATAL: cannot read pg_class without having selected a database

Hello,

after building login_hook extension, I get

postgres=# create extension login_hook;
ERROR: could not access file "login_hook.so": No such file or directory

I had to replace 'login_hook.so' by 'login_hook'
in file login_hook--1.0.sql
for C functions definition

this should had been 'MODULE_PATHNAME' but it didn't seem to work.

Regards
PAscal

Compiled and installed login_hook extension as described
With psql:

postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+---------------------------------------------------------------
login_hook | 1.2 | login_hook | login_hook - hook to execute login_hook.login() at login time
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=#
postgres=#
postgres=# select login_hook.get_login_hook_version();
get_login_hook_version

1.3
(1 row)

There is a discrepancy between the version number of login_hok extension reported by \dx and the version reported by the get_login_hook_version() function.

Could you please tag a release version?

Hi,

I have compiled login_hooks with PG 13 and to enable I have added it to "shared_preload_libraries", but after adding my server won't start and no error messages also.

[postgres@rrr data]$ grep -i preload /var/lib/pgsql/13/data/postgresql.conf
# - Shared Library Preloading -
shared_preload_libraries = 'login_hook'	# (change requires restart)
#local_preload_libraries = ''
#session_preload_libraries = ''
[postgres@rrr data]$ /usr/pgsql-13/bin/pg_ctl -l a.log -D /var/lib/pgsql/13/data/ start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@rrr data]$ more a.log

Any suggesions?

--Raghav

Hi,

After upgrade postgresql from v12 to v16, login hook has different behavior,
for example:

v12 , on login_hook.login function :
select count(*) into vcount from pg_stat_activity where pid = pg_backend_pid(); -- return "1" , ok my session pid on pg_stat_activity

v16 , on login_hook.login function :
select count(*) into vcount from pg_stat_activity where pid = pg_backend_pid(); -- return "0" , does not exist my session pid on pg_stat_activity

when print pg_backend_pid() , value its "ok", but on 16 , not exists on pg_stat_activity

ah, login_hook 1.4 , not work on PG16, i need to use 1.5 last update.

why?????

Would it be possible to make password available to login_hook.login function? That would make it possible to enforce password complexity rules.
Regards

It seems this also fires each time I switch to a different database after logging in, so perhaps a change in the documentation would be helpful?

Seems to also work well on Postgresql 12 (as packaged in Debian Testing, and running on a RPi4). Having the C piece so I could just focus on the pl/pgsql part was exactly what I needed, thank you very much.

I used the login_hook to record the logons in the database. My problem is that not all data is available when login_hook.login() is called, for example the "application_name", when the connections are via jdbc, normally I don't have the application_name, just after the session is already effective.
After the session is already effective, the name of the application already appears normally. Either by current_setting('application_name') or in "SELECT * FROM pg_settings where name like '%application_name%';".
This happens when I establish the connection by DBeaver, or by SQuirreL. Already by pgadmin and local connections, with psql , I don't have this problem

On this code (just to exemplify):
CREATE OR REPLACE FUNCTION login_hook.login()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE

BEGIN
IF NOT login_hook.is_executing_login_hook()
THEN
RAISE EXCEPTION 'The login_hook.login() function should only be invoked by the login_hook code';
END IF;

   declare 
   
   vCurrent_user text:=current_user;
   vCurrent_DB text :=current_database();
   vapplication_name text;
   insert_statement text;
   
    BEGIN
		select application_name
			into vapplication_name
		from pg_stat_activity where pid = pg_backend_pid();
		   
		insert_statement = 'INSERT INTO myschema.audit_log
			(login, app,  db_name )
			VALUES(''' || vCurrent_user || ''', ''' 
			|| vapplication_name  || ''', ''' 
			|| vCurrent_DB || ''');';
						
		execute insert_statement;

  END ;

END
$function$
;

variable vapplication_name always comes up blank/empty when bindings are via jdbc, but then, if I see it in pg_stat_activity, the application_name is already normal

In Postgres 14beta1 everything looked fine. But with Postgres 14beta2 there appears to be a problem:

2021-06-30 17:03:37.152 CEST [116710] DEBUG: InitPostgres
2021-06-30 17:03:37.154 CEST [116710] DEBUG: find_in_dynamic_libpath: trying "/usr/local/postgresql-14beta2/lib/login_hook"
2021-06-30 17:03:37.154 CEST [116710] DEBUG: find_in_dynamic_libpath: trying "/usr/local/postgresql-14beta2/lib/login_hook.so"
2021-06-30 17:03:37.154 CEST [116710] DEBUG: _PG_init() in login_hook.so, MyProcPid=116710, MyDatabaseId=78997, IsBackgroundWorker=0, isExecutingLogin=0
2021-06-30 17:03:37.154 CEST [116710] DEBUG: login_hook will execute select login_hook.login() in database contrib_regression
2021-06-30 17:03:37.452 CEST [72052] DEBUG: server process (PID 116710) was terminated by signal 11: Segmentation fault
2021-06-30 17:03:37.452 CEST [116709] DEBUG: shmem_exit(0): 1 before_shmem_exit callbacks to make
2021-06-30 17:03:37.452 CEST [72052] LOG: server process (PID 116710) was terminated by signal 11: Segmentation fault
2021-06-30 17:03:37.452 CEST [72052] LOG: terminating any other active server processes
2021-06-30 17:03:37.452 CEST [116709] DEBUG: shmem_exit(0): 7 on_shmem_exit callbacks to make
2021-06-30 17:03:37.452 CEST [72052] DEBUG: sending SIGQUIT to process 116268
2021-06-30 17:03:37.452 CEST [116709] DEBUG: proc_exit(0): 3 callbacks to make
2021-06-30 17:03:37.452 CEST [116709] DEBUG: exit(0)
2021-06-30 17:03:37.452 CEST [116709] DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
2021-06-30 17:03:37.453 CEST [116709] DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
2021-06-30 17:03:37.453 CEST [116709] DEBUG: proc_exit(-1): 0 callbacks to make

Regarding the Database crashes issue, I've been in contact with EDB and they are willing to provide you a copy of EDB Postgres Advanced Server and even work with you to get login_hook working in their database. You interested ?

Hi,

i recently updates our postgres cluster from 13.3 to 13.5, after that he login_hook function stopped working.
Whenever i tried to login i got the following message:

gs3-pgres-prd01:/opt/postgresql/13/data3/log [pgd13]$ psql
WARNING: Function login_hook.login() returned with error in database postgres.
Please resolve the error as only superusers can login now.
HINT: original message = cannot execute SQL without an outer snapshot or portal
psql (13.5)
Type "help" for help.

i increased the login parameters and tried to see anything in the logs which were causing an issue but couldnt see any.

2021-12-10 09:27:11.498 CET [110808] LOG: connection received: host=[local]
2021-12-10 09:27:11.499 CET [110808] LOG: connection authorized: user=postgres database=postgres application_name=psql
2021-12-10 09:27:11.501 CET [110808] WARNING: Function login_hook.login() returned with error in database postgres.
Please resolve the error as only superusers can login now.
2021-12-10 09:27:11.501 CET [110808] HINT: original message = cannot execute SQL without an outer snapshot or portal
2021-12-10 09:27:11.501 CET [110808] DEBUG: loaded library "login_hook"
2021-12-10 09:27:11.527 CET [110808] LOG: statement: select substring(''||13.5, 1, position('.' in ''||13.5)-1) as pgreleasenum
2021-12-10 09:27:11.527 CET [110808] LOG: duration: 0.821 ms
2021-12-10 09:27:13.294 CET [110808] LOG: disconnection: session time: 0:00:01.797 user=postgres database=postgres host=[local]

I replaced the login_hook.login() function with the default one mentioned here on github:
CREATE OR REPLACE FUNCTION login_hook.login() RETURNS VOID LANGUAGE PLPGSQL AS $$
DECLARE
ex_state TEXT;
ex_message TEXT;
ex_detail TEXT;
ex_hint TEXT;
ex_context TEXT;
BEGIN
IF NOT login_hook.is_executing_login_hook()
THEN
RAISE EXCEPTION 'The login_hook.login() function should only be invoked by the login_hook code';
END IF;

BEGIN
   -- 
   -- Do whatever you need to do at login here.
   -- For example:
   RAISE NOTICE 'Hello %', current_user;
EXCEPTION
   WHEN OTHERS THEN
       GET STACKED DIAGNOSTICS ex_state   = RETURNED_SQLSTATE
                             , ex_message = MESSAGE_TEXT
                             , ex_detail  = PG_EXCEPTION_DETAIL
                             , ex_hint    = PG_EXCEPTION_HINT
                             , ex_context = PG_EXCEPTION_CONTEXT;
       RAISE LOG e'Error in login_hook.login()\nsqlstate: %\nmessage : %\ndetail  : %\nhint    : %\ncontext : %'
               , ex_state
               , ex_message
               , ex_detail
               , ex_hint
               , ex_context;
END	;       

END
$$;
GRANT EXECUTE ON FUNCTION login_hook.login() TO PUBLIC;

but i just got the exact same message. I cant find the problem.
The only solution to get non super users to be able to connect again is to delete the login_hook.login() function.
Hope someone can help me with this.

We've installed this into an EDB Postgres AS 12 database on a Centos7 system and the installation/make went fine.
However, upon database startup there are a series of fatal errors - I ended up just stopping the service.
I comment the session_preload_libraries = 'login_hook' line from postgresql.conf and the database starts up fine.

I've attached the log - all I can see is 'Segmentation fault' errors so there's not much to go on.
Any thoughts? Thanks.

edb-2020-11-27_100315.log