Limiting a User’s Open Session Count: Quick Tip

Jim Knicely authored this tip. By default, a user can have an unlimited number of connections across the database cluster. Example: [dbadmin@s18384357 ~]$ vsql -U jim -w 'pw' Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit jim=> \! vsql -U jim -w ‘pw’ Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit jim=> \! vsql -U jim -w ‘pw’ Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit jim=> SELECT COUNT(*) FROM v_monitor.sessions WHERE user_name = ‘jim’; COUNT ——- 3 (1 row) Letting a user connect to a database an unlimited number of times could potentially lock out all other users if a malicious user or rogue process opens a number of sessions equal to the database’s max client sessions setting (100 by default). To avoid that situation, limit the number of open database connections a user can have by setting the user’s MAXCONNECTIONS parameter. Example: dbadmin=> ALTER USER jim MAXCONNECTIONS 1; ALTER USER dbadmin=> \! vsql -U jim -w pw Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit jim=> \! vsql -U jim -w pw vsql: FATAL 7470: New session rejected because connection limit of 1 on database already met for jim Have fun!