Day: June 11, 2019

PostgreSQL: Script to Kill Sessions

This is often used script to kill PostgreSQL session(s).

pg_terminate_backend() AND pg_cancel_backend()

1) kill all connections  of  a specified database name.

-- List all available databases

postgres=>\l

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
           FROM   pg_stat_activity
           WHERE  datname = 'DATABASE NAME'
              AND pid <> pg_backend_pid();

2) kill all connections  of a specified user name.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE usename = 'testuser'
              AND pid <> pg_backend_pid();

3) kill all sessions of current database.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE datname = current_database()
              AND pid <> pg_backend_pid();

4) kill a connection  of a specified pid.

postgres=> SELECT pg_terminate_backend(pid);

5) kill a running SQL of a session, but does not kill the session.

postgres=> SELECT pg_cancel_backend( pid );

It is useful when a running SQL is taking long time than expected.

Drop a Role with Dependent Objects in PostgreSQL

Try to drop a role/user in PostgreSQL, and get below errors:

postgres=>drop role testuser_read;
ERROR: role "testuser_read" cannot be dropped because some objects depend on it
DETAIL: privileges for table user1.tab1
privileges for table table user1.tab1
privileges for sequence user1.seq1
...
..
.

SOLUTION

1)Revoke objects privileges from this role/user which is to be dropped:

postgres=> select grantee,table_schema,table_name,privilege_type
            from information_schema.role_table_grants
           where grantee='testuser_read' 
           order by table_name,privilege_type;
grantee    | table_schema | table_name | privilege_type
-----------+--------------+------------+----------------
testuser_read |    user1     | tab1       | SELECT
testuser_read |    user1     | tab2       | SELECT
...
..
.

postgres=> select 'revoke '||privilege_type ||' on '||
           table_schema||'.'||table_name||' from '||grantee||' ;'
from information_schema.role_table_grants
where grantee='testuser_read' ;

-----------------------------------------------------------------------------
 revoke SELECT on user1.tab1 from testuser_read ;
 revoke SELECT on user1.tab2 from testuser_read ;
...
..
.

OR just ...

postgres=>REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA user1 
          FROM testuser_read; 

postgres=>REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA user1
          FROM testuser_read; 

postgres=>alter default privileges in schema testuser revoke select  
          on tables from testuser_read;

postgres=>alter default privileges in schema testuser revoke select ,  
          usage on sequences from testuser_read;

2) Revoke depended roles.

\du to find dependent roles

postgres=>\du
postgres=>revoke other_role from testuser_read;

3) Drop the role.

postgres=> drop role testuser_read;
ERROR:  role "testuser_read" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new sequences belonging to role testuser in schema testuser
privileges for default privileges on new relations belonging to role tetsuser in schema testuser

postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON TABLES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON SEQUENCES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> drop role testuser_read;
DROP ROLE
postgres=>