SELECT procpid, current_query, waiting
FROM   pg_stat_activity
WHERE  datname = 'your_database_name'
AND    current_query <> '<IDLE>';

sql

1

Monitor active PostgreSQL queries against a database

SELECT procpid, current_query, waiting
FROM   pg_stat_activity
WHERE  datname = 'your_database_name'
AND    current_query <> '<IDLE>';

copy | embed

• db, 
• psql, 
• query

psql -F\; -A --pset footer -f input_file.sql -o output_file.csv database_name

bash

1

Output PostgreSQL command as CSV

psql -F\; -A --pset footer -f input_file.sql -o output_file.csv database_name

copy | embed

• csv, 
• postgresql, 
• psql

ER="$2"
PSQL="psql -q -n -A -t"
SCHEMES="'public'"

if [ -z "$1" ]; then
        echo "Something like: ./grant mydatabase myuser | psql mydatabase"
        exit
fi

if [ -z "$2" ]; then
        USER="$1"
fi
echo "-- Granting rights on $DB to $USER ($SCHEMES)"
# tables
Q="select 'grant all on '||schemaname||'.'||tablename||' to \\\"$USER\\\";' from pg_tables where schemaname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# views
Q="select 'grant all on '||schemaname||'.'||viewname||' to \\\"$USER\\\";' from pg_views where schemaname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# sequences
Q="select 'grant all on function '||n.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') to \\\"$USER\\\";' from pg_proc p, pg_namespace n where n.oid = p.pronamespace and n.nspname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# functions
Q="select 'grant all on '||n.nspname||'.'||c.relname||' to \\\"$USER\\\";' from pg_class c, pg_namespace n where n.oid = c.relnamespace and c.relkind IN ('S') and n.nspname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

bash

1

Grant all permissions on PostgreSQL DB

ER="$2"
PSQL="psql -q -n -A -t"
SCHEMES="'public'"

if [ -z "$1" ]; then
        echo "Something like: ./grant mydatabase myuser | psql mydatabase"
        exit
fi

if [ -z "$2" ]; then
        USER="$1"
fi
echo "-- Granting rights on $DB to $USER ($SCHEMES)"
# tables
Q="select 'grant all on '||schemaname||'.'||tablename||' to \\\"$USER\\\";' from pg_tables where schemaname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# views
Q="select 'grant all on '||schemaname||'.'||viewname||' to \\\"$USER\\\";' from pg_views where schemaname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# sequences
Q="select 'grant all on function '||n.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') to \\\"$USER\\\";' from pg_proc p, pg_namespace n where n.oid = p.pronamespace and n.nspname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# functions
Q="select 'grant all on '||n.nspname||'.'||c.relname||' to \\\"$USER\\\";' from pg_class c, pg_namespace n where n.oid = c.relnamespace and c.relkind IN ('S') and n.nspname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

copy | embed

• db, 
• grant, 
• permissions, 
• postgresql, 
• psql