#] root@web:~ 21:47:16 0 [# cat /usr/sbin/apacheAccessLog.sh #!/bin/bash filename="/var/log/apache2/access.log.1" hostname="localhost" username="apache" password="******" database="apache" sqltable="accessLog" mysql -h${hostname} -u${username} -p${password} ${database} << EOQ LOAD DATA LOCAL INFILE "${filename}" INTO TABLE $sqltable FIELDS TERMINATED BY '\t' EOQ
mysql> select * from accessLog where datetime > date_sub( now( ), interval 1 day) and status = 404; mysql> select count(*), substring( request, 1, 64 ), filename from accessLog where datetime > date_sub( now( ), interval 1 day) and status = 404 group by request, filename order by count(*) desc; mysql> select * from accessLog where datetime > date_sub( now( ), interval 1 day) and status not in( 401, 404 ); mysql> select count(*), substring( request, 1, 64 ), filename from accessLog where datetime > date_sub( now( ), interval 1 day) and status not in( 401, 404 ) group by request, filename order by count(*) desc; mysql> select filename, count(*) from accessLog where datetime > date_sub( now( ), interval 1 day ) and status <> 404 group by filename order by count(*) desc; mysql> select filename, count(*) from accessLog where datetime > date_sub( now( ), interval 1 day ) and status not in( 401, 404) group by filename order by count(*) desc; mysql> select substr( filename, 1, 29 ), count(*) from accessLog where datetime > date_sub( now( ), interval 1 day ) and status not in( 401, 404 ) and filename regexp '/var/www/html/us/kohlmeyer/[cekp]' group by substr( filename, 1, 29 ) order by count(*);
#] kemiko@box:/usr/local/bin 12:56:56 0 [# cat .aliases # .aliases; 2024/06/17; Kent M Kohlmeyer (kemiko) alias cp='cp -i' alias mv='mv -i' alias rm='rm -i' alias www-data="chown -R www-data:www-data ." alias viSwap='ls -latr | grep " \..*\\.sw.$"' alias backup='/usr/local/bin/backup.sh' alias du='du -h --max-depth=1 . | sort -h' alias job='fnJob' alias tsls='ls -l --time-style=+%Y-%m-%d@%H:%M' alias tssort='sort -k6,6' # tsls | tssort alias free='/usr/local/bin/free.sh' alias topc="top -o COMMAND" # sort by field "%COMMAND"...if already in top press 'C' alias topm="top -o %MEM" # sort by field "%MEM"...if already in top press 'M' alias topp="top -o %CPU" # sort by field "%CPU"...if already in top press 'P' alias topu="top -o USER" # sort by field "%USER"...if already in top press 'U' alias less='/usr/bin/less -SX' # --chop-long-lines, --no-init alias gdb='gdb -q' # -quiet #] kemiko@box:/usr/local/bin 12:56:57 0 [#
#] kemiko@box:/usr/local/bin 22:39:27 0 [# cat .defines # .defines; 2024/06/17; Kent M Kohlmeyer (kemiko) BOLD="\e[1m" BASE="\e[0m" BLK="\e[30;1m" RED="\e[31;1m" GRN="\e[32;1m" YLW="\e[33;1m" BLU="\e[34;1m" WHT="\e[37;1m" GRY="\e[90;1m" #] kemiko@box:/usr/local/bin 22:39:28 0 [#
#] kemiko@box:/usr/local/bin 11:22:03 0 [# cat .functions # .functions; 2024/06/17; Kent M Kohlmeyer (kemiko) fnFileExtension( ) { BASENAME=$(basename ${1}) echo ${BASENAME##*.} } fnFileBasename( ) { basename=$(basename ${1}) extension=$(fnFileExtension $basename) echo $basename | awk -F".$(echo $extension)" '{print $1}' } fnDate( ) { date +%Y%m%d } fnDateLog( ) { date +%Y-%m-%d } fnTime( ) { date +%H%M%S } fnTimeLog( ) { date +%H:%M:%S } fnAbsoluteValue( ) { if [ "${1}" -eq "${1}" ] 2>/dev/null then [ ${1} -lt 0 ] && echo $((-${1})) || echo ${1} fi } function fnPrompt { EXITSTATUS="$?" BOLD="\[\e[1m\]" RED="\[\e[1;31m\]" BLACK="\[\e[30;1m\]" GREEN="\[\e[32;1m\]" BLUE="\[\e[34;1m\]" YELLOW="\[\e[33;1m\]" WHITE="\[\e[37;1m\]" GRAY="\[\e[90;1m\]" OFF="\[\e[0m\]" PROMPT="${GRAY}#] ${RED}\u${GRAY}@${YELLOW}\h${GRAY}:${BLUE}\w ${WHITE}\t" if [ "${EXITSTATUS}" -eq 0 ] then PS1="${PROMPT} ${GREEN}0 ${GRAY}[#${OFF} " else PS1="${PROMPT} ${RED}1 ${GRAY}[#${OFF} " fi PS2="${BOLD}>${OFF} " export PS1 PS2 } # Awesome function for bringing a background job (by name NOT number) to the foreground... # I couldn't use an alias (issues with pipes, etc) or shell script ("jobs" command doesn't work...probably because it starts a subprocess) # An argument, job string, must be entered # If there isn't a matching job in the background the first match will be opened with "vi" function fnJob( ) { if [ ${#} -eq 0 ] then # Can't use the standard shell script "${0}" to get the function name printf '\e[1mUsage: %s <filename>\n' "${FUNCNAME[0]}\e[0m" return 1 fi jobNumber=$(jobs | grep "${1}" | cut -d] -f1 | cut -c2- | head -n 1) fileName=$(ls | grep "${1}" | head -n 1) if [ "${jobNumber}" != "" ] then fg "${jobNumber}" elif [ "${fileName}" != "" ] then eval vi "${fileName}" else eval vi "${1}" fi } #] kemiko@box:/usr/local/bin 11:22:04 0 [#
#] kemiko@box:/usr/local/bin 08:51:35 0 [# cat backup.sh #!/bin/bash #set -x # backup.sh; 2017/07/18; Kent M Kohlmeyer (kemiko) # source environment file(s) . /usr/local/bin/.defines > /dev/null 2>&1 . /usr/local/bin/.functions > /dev/null 2>&1 if [ ${#} -eq 0 ] then printf "\e[1;29mUsage: %s <file>\e[0m\n" "${0}" exit 1 fi if [ ! -s ${1} ] then printf "\e[1;29mFile \"${1}\" does not exist with size!\e[0m\n" exit 1 fi dir=".backup" date=$(fnDate) file=$(basename ${1}) if [ -s ${file} ] then if [ ! -d ${dir} ] then mkdir ${dir} chmod 750 ${dir} fi cp -ip ${file} ${dir}/${file}.${date} chmod 440 ${dir}/${file}.${date} fi #] kemiko@box:/usr/local/bin 08:51:36 0 [#
#] kemiko@box:/usr/local/bin 20:30:25 0 [# cat lines.sh #!/bin/bash #set -x # lines.sh; 2024/06/15; Kent M Kohlmeyer (kemiko) # display range of lines from a file # source defines . /usr/local/bin/.defines > /dev/null 2>&1 if [ ${#} -lt 3 ] then printf "${BOLD}Usage: $(basename ${0})${BASE}\n" exit 1 fi if [ ! -s ${1} ] then printf "${BOLD}\"${1}\" is not a regular file with size!${BASE}\n" exit 1 fi if [ "${2}" -eq "${2}" ] 2>/dev/null && [ "${3}" -eq "${3}" ] 2>/dev/null then : else printf "${BOLD}\"${2}\" (start) and/or \"${3}\" (stop) is not an integer${BASE}\n" exit 1 fi if [ ${2} -le 0 ] || [ ${3} -le 0 ] then printf "${BOLD}\"${2}\" (start) and/or \"${3}\" (stop) can't be zero/negative${BASE}\n" exit 1 fi if [ ${3} -gt $(wc -l ${1} | cut -d' ' -f1) ] then printf "${BOLD}\"${3}\" (stop) shouldn't be greater than the file line count of \"$(wc -l ${1} | cut -d' ' -f1)\"${BASE}\n" exit 1 fi if [ "${2}" -gt "${3}" ] 2>/dev/null then printf "${BOLD}\"${2}\" (start) can't be greater than \"${3}\" (stop)${BASE}\n" exit 1 fi head -n ${3} ${1} | tail -n $(expr ${3} - ${2} + 1) #] kemiko@box:/usr/local/bin 20:30:26 0 [#
#] kemiko@box:/usr/local/bin 08:57:18 0 [# cat free.sh #!/bin/bash #set -x # 2024-07-26; free.sh; Kent M Kohlmeyer (kemiko) # This script shows memory usage in... # bytes, kibibytes, mebibytes, or gibibytes # Add seconds argument to pause until... # <enter> is pressed # don't echo key presses to screen stty -echo # don't show cursor tput civis trap 'tput cnorm; stty echo; exit 1' 0 1 2 15 # set defaults size=m seconds=86400 if [ ${#} -eq 0 ] then printf "\e[1mUsage: %s <b|k|m|g> [seconds]\e[0m\n" "${0}" exit 1 fi if [ ${1} != b ] && [ ${1} != k ] && [ ${1} != m ] && [ ${1} != g ] then printf "\e[1mArgument one, \"${1}\", is not \"b|k|m|g\"\e[0m\n" exit 1 else size=${1} fi # test if argument two is set if [ -z ${2+x} ] then : else # test if argument two is an integer if [ "${2}" -eq "${2}" ] 2>/dev/null then seconds=${2} else printf "\e[1mArgument two, \"${2}\", is not an integer\e[0m\n" exit 1 fi fi clear while [ 1 ] do printf "\e[1m%s\e[0m\n" "$(date '+%Y-%m-%d @ %H:%M:%S (%A)')" free -w${size} read -t ${seconds} done #] kemiko@box:/usr/local/bin 08:57:19 0 [#
#] kemiko@box:/tmp 23:47:18 0 [# cat glibc.c #include <stdio.h> #include <gnu/libc-version.h> int main( void ) { puts( gnu_get_libc_version ( ) ); return 0; } #] kemiko@box:/tmp 23:47:19 0 [#
#] kemiko@box:/usr/local/bin 09:13:01 0 [# #!/bin/bash #set -x # 2025-01-06; highlight.sh; Kent M Kohlmeyer (kemiko) # This script highlights pattern(s) in file(s) if [ ${#} -eq 0 ] then printf "\e[1mUsage: %s <regex> <file(s)>\e[0m\n" "${0}" exit 1 fi init=0 filesValid="" filesError="" errors=0 for file in "${@}" do echo file: ${file} if [ ${init} -eq 0 ] then init=1 else if [ -f ${file} ] then filesValid=$(printf "%s %s" "${filesValid}" "${file}") else errors=$(expr ${errors} + 1) filesError=$(printf "%s \"%s\"" "${filesError}" "${file}") fi fi done if [ ${errors} -gt 0 ] then printf "\e[1mError: %s file(s) are not valid...%s\e[0m\n" "${errors}" "${filesError}" exit ${errors} fi egrep -iIn --color=auto "${1}|$" ${filesValid} /dev/null exit 0 #] kemiko@box:/usr/local/bin 09:13:02 0 [#
#] kemiko@box:~/src 19:42:47 0 [# cat itop.ec #include <unistd.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <time.h> static char SourceID[] = "itop.ec; 2016/12/03; Kent M Kohlmeyer"; /* This program displays the top processes the Informix engine is currently running. To "make" the executable with debugging symbols or not esql -G itop.ec -o itop OR esql itop.ec -o itop To Do: 1) catch SIGINT ctrl-c...fclose( fp ); AND pin( cursor, 0 ); #include <signal.h> static volatile int keepRunning = 1; void intHandler(int dummy) { keepRunning = 0; } // ... int main(void) { signal(SIGINT, intHandler); while (keepRunning) { // ... 2) Take argv[0] out of the output */ #define pin( row, col ) printf( "%c[%d;%dH", 27, ( row ), ( col ) ) #define SIDRECS 30 #define PIDRECS 1500 struct recordsid { int sid; int pid; char hostname[16+1]; char username[32+1]; char connected[25+1]; int ops; }; struct recordpid { int pid; int ppid; char command[64+1]; }; int compare( const void *a, const void *b ) { struct recordsid *orderA = (struct recordsid *)a; struct recordsid *orderB = (struct recordsid *)b; if( orderA->ops < orderB->ops ) { return 1; } else if( orderA->ops > orderB->ops ) { return -1; } else { return 0; } } int clear( int line ) { printf( "%c[%d;%df", 27, line, 0 ); printf( "%c[0J", 27 ); } void rtrim( char *string ) { int i; for( i = strlen( string ) - 1; i > 0; i-- ) { if( string[i] == ' ' ) { string[i] = '\0'; } else { break; } } } void lpad( char *string, int width ) { int i; int j; char buffer[15+1] = ""; strcpy( buffer, string ); for( i = 0; i < width - strlen( buffer ); i++ ) { string[i] = ' '; } for( j = 0; j < width - i; j++ ) { string[i+j] = buffer[j]; } string[j+i] = '\0'; } int main( int argc, char **argv ) { /* struct recordpid { int pid; int ppid; char command[64+1]; }; */ struct recordsid sidold[SIDRECS]; struct recordsid sidnew[SIDRECS]; struct recordsid siddelta[SIDRECS]; struct recordpid pidrec[PIDRECS]; memset( &sidold, '\0', sizeof( sidold ) ); memset( &sidnew, '\0', sizeof( sidnew ) ); memset( &siddelta, '\0', sizeof( siddelta ) ); EXEC SQL BEGIN DECLARE SECTION; int sid; int pid; char hostname[15]; char username[15]; char connected[25]; int ops; EXEC SQL END DECLARE SECTION; int i; int j; int k; int display; char line[128+1]; int looppid = 1; int loopppid = 1; //EXEC SQL END DECLARE SECTION; int argr = 15; int args = 5; //int argt = 0; int argb = 0; char system[16+1]; FILE *fp; char batch[64+1]; int loopmain = 0; int iops = 0; time_t seconds; struct tm *datetime; char datesystem[20]; int cursor = 0; int foundpid = 0; int foundppid = 0; sprintf( system, "%s", getenv( "SYSTEM_NAME" ) ); for( i = 1; i < argc; i++ ) { if( strstr( argv[i], "-h" ) ) { printf( "%c[1m", 27 ); printf( "\nUsage: %s -[hrs]\n\n", argv[0] ); printf( "-h ...help\n" ); printf( "-rX ...records to display (default is 15)\n" ); /* printf( "-s ....display SQL\n" ); */ printf( "-sX ...seconds to refresh (default is 5)\n\n" ); printf( "-b ...batch mode\n\n" ); printf( "%c[0m", 27 ); exit( 1 ); } if( strstr( argv[i], "-r" ) ) { sscanf( argv[i] + 2, "%d", &argr ); if( argr <= 0 || argr > 30 ) { argr = 30; } } /* if( strstr( argv[i], "-t" ) ) { argt = 1; } */ if( strstr( argv[i], "-s" ) ) { sscanf( argv[i] + 2, "%d", &args ); if( args < 0 || args > 60 ) { args = 5; } } if( strstr( argv[i], "-b" ) ) { sscanf( argv[i] + 2, "%d", &argb ); if( argb < 0 || argb > 60 ) { argb = 5; } } } if( argb ) { sprintf( batch, "%s.log", argv[0] ); if( (fp = fopen( batch, "w" )) == NULL ) { printf( "Can not open file %s\n", batch ); exit( 1 ); } /* else { fprintf( stdout, "HELLO" ); fflush( stdout ); } */ } else { clear( 0 ); } for( ; ; ) { memset( &sidnew, '\0', sizeof( sidnew ) ); cursor = 2; EXEC SQL WHENEVER SQLERROR STOP; EXEC SQL CONNECT to 'sysmaster'; EXEC SQL declare sysmastercursor cursor for SELECT FIRST 30 s.sid, pid, trim(hostname), trim(username), dbinfo('utc_to_datetime',connected), isreads+bufreads+bufwrites+pagreads+pagwrites INTO :sid, :pid, :hostname, :username, :connected, :ops FROM syssessions s, syssesprof p WHERE s.sid = p.sid ORDER BY 6 DESC; EXEC SQL open sysmastercursor; memset( &pidrec, '\0', sizeof( pidrec ) ); FILE *ps = popen( "ps -ef", "r" ); for( i = 0; fgets( line, sizeof( line ), ps ) != 0; i++ ) { if( i > 0 ) { pidrec[i].pid = atoi( &line[9] ); pidrec[i].ppid = atoi( &line[15] ); strcpy( pidrec[i].command, &line[48] ); } } pclose( ps ); for( i = 0; i < SIDRECS; i++ ) { EXEC SQL fetch sysmastercursor; if( strncmp( SQLSTATE, "00", 2 ) != 0 ) { break; } sidnew[i].sid = sid; sidnew[i].pid = pid; memcpy( &sidnew[i].hostname, &hostname, strlen( hostname ) + 1 ); memcpy( &sidnew[i].username, &username, strlen( username ) + 1 ); memcpy( &sidnew[i].connected, &connected, strlen( connected ) + 1 ); sidnew[i].ops = ops; } EXEC SQL close sysmastercursor; EXEC SQL free sysmastercursor; EXEC SQL disconnect current; memcpy( &siddelta, &sidnew, sizeof( struct recordsid ) * SIDRECS ); for( i = 0; i < SIDRECS; i++ ) { for( j = 0; j < SIDRECS; j++ ) { if( siddelta[i].sid == sidold[j].sid ) { siddelta[i].ops -= sidold[j].ops; /* This causes some rows to be off between hostname and username siddelta[i].hostname[10] = '\0'; siddelta[i].username[10] = '\0'; siddelta[i].connected[19] = '\0'; changed later before output */ } } } qsort( siddelta, SIDRECS, sizeof( struct recordsid ), compare ); if( !argb ) { clear( 2 ); //printf( "Host: %-29sDate: %-41sIops: %10d\n", system, "2016-12-19 20:01:14", iops ); printf( "\n" ); cursor++; } /* Output the records to screen or file */ for( i = 0, iops = 0; i < argr; i++ ) { if( siddelta[i].ops ) { if( strstr( siddelta[i].hostname, system ) ) { rtrim( siddelta[i].username ); lpad( siddelta[i].username, 12 ); rtrim( siddelta[i].hostname ); rtrim( siddelta[i].connected ); if( !argb ) { printf( "%c[1m%8d %7d%s@%-10s %22s %35d%c[0m\n",27,siddelta[i].sid,siddelta[i].pid,siddelta[i].username,siddelta[i].hostname,siddelta[i].connected,siddelta[i].ops,27 ); cursor += 2; } else { fprintf( fp, "%8d %7d%s@%-10s %22s %35d\n",siddelta[i].sid,siddelta[i].pid,siddelta[i].username,siddelta[i].hostname,siddelta[i].connected,siddelta[i].ops ); } /* find and print first parent */ for( j = 0, foundpid = 0, foundppid = 0, looppid = 1; j < PIDRECS && looppid == 1; j++ ) { if( siddelta[i].pid == pidrec[j].pid ) { foundpid = 1; if( !argb ) { printf( " %7d %7d %s", pidrec[j].pid, pidrec[j].ppid, pidrec[j].command ); cursor++; } else { fprintf( fp, " %7d %7d %s", pidrec[j].pid, pidrec[j].ppid, pidrec[j].command ); } /* find and print second parent */ for( k = 0, loopppid = 1; k < PIDRECS && loopppid == 1; k++ ) { if( pidrec[j].ppid == pidrec[k].pid ) { foundppid = 1; if( !argb ) { printf( " %7d %7d %s\n", pidrec[k].pid, pidrec[k].ppid, pidrec[k].command ); cursor++; } else { fprintf( fp, " %7d %7d %s\n", pidrec[k].pid, pidrec[k].ppid, pidrec[k].command ); } loopppid = 0; } } looppid = 0; } } if( !foundpid ) { if( !argb ) { printf( "\n\n" ); cursor++; } else { fprintf( fp, "\n\n" ); } } if( !foundppid ) { if( !argb ) { printf( "\n" ); cursor++; } else { fprintf( fp, "\n" ); } } } else // isn't going to have local PPIDs { rtrim( siddelta[i].username ); lpad( siddelta[i].username, 12 ); rtrim( siddelta[i].hostname ); rtrim( siddelta[i].connected ); if( !argb ) { printf( "%c[1m%8d %7d%s@%-10s %22s %35d%c[0m\n\n\n\n",27,siddelta[i].sid,siddelta[i].pid,siddelta[i].username,siddelta[i].hostname,siddelta[i].connected,siddelta[i].ops,27 ); cursor += 4; } else { fprintf( fp, "%8d %7d%s@%-10s %22s %35d\n\n\n\n",siddelta[i].sid,siddelta[i].pid,siddelta[i].username,siddelta[i].hostname,siddelta[i].connected,siddelta[i].ops ); } } iops += siddelta[i].ops; } else // make the screen and file fixed length { if( !argb ) { printf( "\n\n\n\n" ); cursor += 4; } else { fprintf( fp, "\n\n\n\n" ); } } } memcpy( &sidold, &sidnew, sizeof( struct recordsid ) * SIDRECS ); /* loopmain++; if( argb == loopmain ) { fclose( fp ); return( 0 ); } */ // printf( "Host: %-29sDate: %-41sIops: %10d\n", system, "2016-12-19 20:01:14", iops ); seconds = time( 0 ); datetime = localtime( &seconds ); sprintf( datesystem, "%04d-%02d-%02d %02d:%02d:%02d", datetime->tm_year + 1900, datetime->tm_mon + 1, datetime->tm_mday, datetime->tm_hour, datetime->tm_min, datetime->tm_sec ); //clear( ); if( argb ) { //chg fprintf( fp, "Host: %-29sDate: %-41sLoad: %10d\n", system, datesystem, iops ); fprintf( fp, "Host: %-31sDate: %-39sLoad: %10d\n", system, datesystem, iops ); fprintf( fp, "--------------------------------------------------------------------------------------------------\n" ); fflush( fp ); } else { pin( 0, 0 ); //chg fprintf( stdout, "Host: %-29sDate: %-42sLoad: %9d\n", system, datesystem, iops ); fprintf( stdout, "Host: %-31sDate: %-39sLoad: %10d\n", system, datesystem, iops ); pin( cursor, 0 ); fflush( stdout ); } loopmain++; if( argb == loopmain ) { fclose( fp ); return( 0 ); } sleep( args ); } } #] kemiko@box:~/src 19:42:51 0 [#
Alpha and Omega Semiconductor Limited, or AOS, is a designer, developer and global supplier of a broad range of power semiconductors, including a wide portfolio of Power MOSFET, IGBT and Power IC products. AOS has developed extensive intellectual property and technical knowledge that encompasses the latest advancements in the power semiconductor industry, which enables it to introduce innovative products to address the increasingly complex power requirements of advanced electronics. AOS differentiates itself by integrating its Discrete and IC semiconductor process technology, product design, and advanced packaging know-how to develop high performance power management solutions. AOS's portfolio of products targets high-volume applications, including portable computers, flat panel TVs, LED lighting, smart phones, battery packs, consumer and industrial motor controls and power supplies for TVs, computers, servers and telecommunications equipment. For more information, please visit http://www.aosmd.com. For investor relations, please contact So-Yeon Jeong at investors@aosmd.com.
CONTACT: Alpha and Omega Semiconductor Limited Investor Relations So-Yeon Jeong 408-789-3172 investors@aosmd.com
1. Filter for interactive SSH logins bash> grep sshd.\*Failed /var/log/auth.log | less 2. Filter for failed connections (i.e. no login attempted, could be a port scanner, etc) bash> grep sshd.\*Did /var/log/auth.log | less
![]() |
;)u | winky tongue |
![]() |
^^)u | silly tongue |
![]() |
;3* | kissy |
![]() |
(bb) | bear (brown) ... pairs well with "huggy" |
![]() |
(bp) | bear (panda) ... pairs well with "huggy" |
![]() |
(^^8)) | huggy |
![]() |
**^o^ | heart eyes |
![]() |
:(? | thought |
![]() |
:'( | sad |
![]() |
:8^v^ | embarrassed |
![]() |
^^8) | blush |
![]() |
:8| | rolling eyes |
![]() |
'><8 | very upset |
![]() |
^^)*** | three hearts (kisses) on face |
--shows base table information SELECT * FROM pg_catalog.pg_tables --WHERE -- tableowner = current_user --WHERE -- schemaname NOT IN ('public','pg_catalog','information_schema') --WHERE -- schemaname IN ('public','pg_catalog','information_schema') --ORDER BY -- schemaname DESC, tablename ; ┌────────────────────┬─────────────────────────┬────────────┬────────────┬────────────┬──────────┬─────────────┬─────────────┐ │ schemaname │ tablename │ tableowner │ tablespace │ hasindexes │ hasrules │ hastriggers │ rowsecurity │ ├────────────────────┼─────────────────────────┼────────────┼────────────┼────────────┼──────────┼─────────────┼─────────────┤ │ ... │ ... │ ... │ (NULL) │ t │ f │ f │ f │ │ public │ exampleTable │ dba │ (NULL) │ t │ f │ f │ f │ │ pg_catalog │ pg_aggregate │ postgres │ (NULL) │ t │ f │ f │ f │ │ ... │ ... │ ... │ (NULL) │ t │ f │ f │ f │ │ information_schema │ sql_features │ postgres │ (NULL) │ f │ f │ f │ f │ │ ... │ ... │ ... │ (NULL) │ t │ f │ f │ f │ └────────────────────┴─────────────────────────┴────────────┴────────────┴────────────┴──────────┴─────────────┴─────────────┘ (999 rows)
--shows "table catalog" and "table type", but not "table owner" compared to previous query SELECT * FROM information_schema.tables --WHERE -- table_schema = 'public' --WHERE -- table_schema NOT IN ('public','pg_catalog','information_schema') --WHERE -- table_schema IN ('public','pg_catalog','information_schema') ORDER BY table_schema DESC, table_name ; ┌────────────────┬────────────────────┬───────────────────────────────────────┬────────────┬──────────────────────────────┬──────────────────────┬───────────────────────────┬──────────────────────────┬────────────────────────┬────────────────────┬──────────┬───────────────┐ │ table_catalog │ table_schema │ table_name │ table_type │ self_referencing_column_name │ reference_generation │ user_defined_type_catalog │ user_defined_type_schema │ user_defined_type_name │ is_insertable_into │ is_typed │ commit_action │ ├────────────────┼────────────────────┼───────────────────────────────────────┼────────────┼──────────────────────────────┼──────────────────────┼───────────────────────────┼──────────────────────────┼────────────────────────┼────────────────────┼──────────┼───────────────┤ │ exampleCatalog │ public │ exampleTable │ BASE TABLE │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ YES │ NO │ (NULL) │ │ ... │ ... │ ... │ BASE TABLE │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ YES │ NO │ (NULL) │ │ ... │ pg_catalog │ pg_aggregate │ BASE TABLE │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ YES │ NO │ (NULL) │ │ ... │ information_schema │ administrable_role_authorizations │ VIEW │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ NO │ NO │ (NULL) │ │ ... │ ... │ ... │ BASE TABLE │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ (NULL) │ YES │ NO │ (NULL) │ └────────────────┴────────────────────┴───────────────────────────────────────┴────────────┴──────────────────────────────┴──────────────────────┴───────────────────────────┴──────────────────────────┴────────────────────────┴────────────────────┴──────────┴───────────────┘ (999 rows)
CREATE VIEW tables AS SELECT table_catalog, table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ;
SELECT * FROM tables ; ─ ┌────────────────┬───────────────┬──────────────────┬─────────────┐ │ table_catalog │ table_schema │ table_name │ table_type │ ├────────────────┼───────────────┼──────────────────┼─────────────┤ │ exampleCatalog │ exampleSchema │ exampletable │ exampleType │ │ ... │ ... │ ... │ ... │ │ postgres │ public │ tables │ VIEW │ └────────────────┴───────────────┴──────────────────┴─────────────┘ (999 rows)
--shows non system objects owned by current user SELECT nsp.nspname AS object_schema, cls.relname AS object_name, CASE cls.relkind WHEN 'r' THEN 'table' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 'c' THEN 'type' ELSE cls.relkind::text END AS object_type, rol.rolname AS object_owner FROM pg_class cls JOIN pg_roles rol ON rol.oid = cls.relowner JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace WHERE nsp.nspname NOT IN ('information_schema', 'pg_catalog') AND nsp.nspname NOT LIKE 'pg_toast%' AND rol.rolname = current_user ORDER BY nsp.nspname, cls.relname ; ┌───────────────┬─────────────────────┬─────────────┬──────────────┐ │ object_schema │ object_name │ object_type │ object_owner │ ├───────────────┼─────────────────────┼─────────────┼──────────────┤ │ public │ tables │ view │ postgres │ │ ... │ ... │ ... │ ... │ │ exampleSchema │ example │ table │ dba │ │ exampleSchema │ example_id_seq │ sequence │ dba │ │ exampleSchema │ example_pkey │ index │ dba │ └───────────────┴─────────────────────┴─────────────┴──────────────┘ (XXX rows)
SELECT r.usename AS grantor, e.usename AS grantee, nspname, privilege_type, is_grantable FROM pg_namespace JOIN LATERAL (SELECT * FROM aclexplode(nspacl) AS x) a ON true JOIN pg_user e ON a.grantee = e.usesysid JOIN pg_user r ON a.grantor = r.usesysid WHERE e.usename = current_user ; ┌──────────┬──────────┬────────────────────┬────────────────┬──────────────┐ │ grantor │ grantee │ nspname │ privilege_type │ is_grantable │ ├──────────┼──────────┼────────────────────┼────────────────┼──────────────┤ │ postgres │ postgres │ pg_catalog │ USAGE │ f │ │ postgres │ postgres │ pg_catalog │ CREATE │ f │ │ postgres │ postgres │ public │ USAGE │ f │ │ postgres │ postgres │ public │ CREATE │ f │ │ postgres │ postgres │ information_schema │ USAGE │ f │ │ postgres │ postgres │ information_schema │ CREATE │ f │ └──────────┴──────────┴────────────────────┴────────────────┴──────────────┘ (6 rows)
SELECT * FROM pg_stat_activity ; ┌───────┬───────────┬───────┬──────────┬──────────┬──────────────────┬─────────────┬─────────────────┬─────────────┬───────────────────────────────┬───────────────────────────────┬───────────────────────────────┬───────────────────────────────┬─────────┬────────┬─────────────┬──────────────┬─────────────────────────────────────────────────────────────────────────────┐ │ datid │ datname │ pid │ usesysid │ usename │ application_name │ client_addr │ client_hostname │ client_port │ backend_start │ xact_start │ query_start │ state_change │ waiting │ state │ backend_xid │ backend_xmin │ query │ ├───────┼───────────┼───────┼──────────┼──────────┼──────────────────┼─────────────┼─────────────────┼─────────────┼───────────────────────────────┼───────────────────────────────┼───────────────────────────────┼───────────────────────────────┼─────────┼────────┼─────────────┼──────────────┼─────────────────────────────────────────────────────────────────────────────┤ │ 12411 │ postgres │ 16459 │ 24655 │ dba │ psql │ 127.0.0.1 │ (NULL) │ 49568 │ 2024-11-28 13:39:41.091595-08 │ 2024-12-01 20:09:47.198842-08 │ 2024-12-01 20:09:47.198842-08 │ 2024-12-01 20:09:47.198847-08 │ f │ active │ (NULL) │ 4838 │ SELECT * FROM pg_stat_activity; │ │ 12411 │ postgres │ 23698 │ 10 │ postgres │ psql │ (NULL) │ (NULL) │ -1 │ 2024-11-30 11:51:07.192089-08 │ (NULL) │ 2024-11-30 11:51:58.141689-08 │ 2024-11-30 11:51:58.156049-08 │ f │ idle │ (NULL) │ (NULL) │ select table_name, ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ table_schema as schema_name, ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ pg_database_size(current_database()) as total_database_size, ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ pg_total_relation_size(table_name) as total_table_size, ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ pg_relation_size(table_name) as table_size, ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ pg_indexes_size(table_name) as index_size ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ from information_schema.tables ↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ where table_schema=current_schema() and table_name like 'table_%'↵│ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ order by total_table_size; │ │ ... │ ... │ ..... │ ..... │ ........ │ .... │ ......... │ ......... │ ..... │ ............................. │ ............................. │ ............................. │ ............................. │ . │ ...... │ ...... │ ...... │ ...... │ │ 24732 │ xxxxxxx │ 1015 │ 10 │ postgres │ psql │ (NULL) │ (NULL) │ -1 │ 2024-11-27 20:21:49.750157-08 │ (NULL) │ 2024-11-27 20:21:58.891633-08 │ 2024-11-27 20:21:58.900124-08 │ f │ idle │ (NULL) │ (NULL) │ select * from xxx.ledger; │ └───────┴───────────┴───────┴──────────┴──────────┴──────────────────┴─────────────┴─────────────────┴─────────────┴───────────────────────────────┴───────────────────────────────┴───────────────────────────────┴───────────────────────────────┴─────────┴────────┴─────────────┴──────────────┴─────────────────────────────────────────────────────────────────────────────┘ (XXX rows)
SELECT pg_terminate_backend( pid );