Solving "Permission Denied" Problem When Accessing PostgreSQL Database

I am getting the "permission denied" error when accessing a PostgreSQL database. The PostgreSQL server is version 11.8.

mydb=> select version();
                                                  version

------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.8 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 9.3.1 20200408 (Red Hat 9.3.1-2), 64-bit
(1 row)

mydb=>

I follow the suggestions given in several online discussions to grant necessary privileges to the tables, views, and sequences in the database. These online discussiones include,

• Permission denied in Postgres
• How to fix permission denied for relation some_table_name in PostgreSQL?
• Granted all privileges on my PostGres table, but still am getting a “Permission denied” error

However, the problem remains. The solution that actually works for me is in the discussion below,

• PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL

The solution is to alter all tables, views, and sequences' ownership to the database role or user we wish to grant the privileges to and requires a user to enter the password to the database user or role multiple times. To simply this, I rewrite the solution as follows in a shell script.

#!/bin/bash

DB_NAME="mydb"          # replace mydb by the database name
DB_USER="mydbuser"      # repalce mydbuser by the database username
SQL_FILE="fixpermission.sql"

[ -f ${SQL_FILE} ] && rm -f ${SQL_FILE}

for tbl in \
  `psql -qAt -c \
  "select tablename from pg_tables where schemaname = 'public';" $DB_NAME`;
do
  echo "alter table \"$tbl\" owner to $DB_USER;" >> $SQL_FILE
done

for tbl in \
  `psql -qAt -c \
  "select sequence_name from information_schema.sequences where sequence_schema = 'public';" $DB_NAME`;
do
   echo "alter sequence \"$tbl\" owner to $DB_USER;" >> $SQL_FILE
done


for tbl in \
  `psql -qAt -c \
  "select table_name from information_schema.views where table_schema = 'public';" $DB_NAME`;
do
  echo "alter view \"$tbl\" owner to $DB_USER;" >> $SQL_FILE
done

psql -U postgres -h localhost $DB_NAME < $SQL_FILE

rm -f $SQL_FILE

This script only requires a user to enter the data role or user's password for 4 times.