Thursday, June 24, 2021

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,

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

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.

Tuesday, June 8, 2021

Solving problem updating Windows 10 to version 20H2 or newer

I have been trying to update Windows 10 to Windows 10 version 20H2. The problems that I  encountered are,

  1. When using Windows Update, I saw the update process stuck at 61%, eventually failed. I am not the only one, clearly. For instance, there are numerous online threads. Here are several examples, example 1, example 2, and example 3. Want to see more, just Google it. 
  2. When using Windows 10 Update Assistant, one of the recommended method, I saw it stuck at 99%, for eons. 

 Finally, I found the right answer, i.e., the method documented in this post,

This  method worked for me. The caveat is that 1) contrast of using Windows update, I have to wait for two to three hours before it finishes; 2) it updates my Windows to 21H1 directly. Regardless, updating to a new version is what I wanted since Microsoft is going to cease supporting older versions of Windows 10.

The steps as outlined in the article linked in the above are,

  1. Open File Explorer, type C:\$GetCurrent, and then press Enter.
  2. Copy and paste the Media folder to the desktop. If you don't see the folder, select View and make sure the check box next to Hidden items is selected.
    Restart your PC, open File Explorer, type C:\$GetCurrent in the address bar, and then press Enter.
  3. Copy and paste the Media folder from the desktop to C:\$GetCurrent.
  4. Open the Media folder and double-click Setup.
  5. Follow the instructions to start the upgrade. On the Get important updates screen, select Not right now, and then select Next.
  6. Follow the instructions to finish upgrading to Windows 10. After you're done, make sure to install available updates. Select the Start  button, and then select Settings  > Update & Security  > Windows Update > Check for updates.