Limiting a User’s Open Session Count

[Deleted User]

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!