Postgresql USER GRANT not Applied version 17 Fix - Solved

 I came across a weird issue that none of the below changes getting applied to the rashiduser

1. Grant All Privileges on the Public Schema:

   sql

   GRANT ALL ON SCHEMA public TO rashiduser;
   

2. Grant All Privileges on All Tables in the Public Schema:

   sql

   GRANT ALL ON ALL TABLES IN SCHEMA public TO rashiduser;
   

3. Grant All Privileges on All Sequences in the Public Schema:

   sql

   GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO rashiduser;
   

4. Grant All Privileges on All Functions in the Public Schema:

   sql

   GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO rashiduser;
   

5. Set Default Privileges for Future Objects:

   sql

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO rashiduser;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO rashiduser;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO rashiduser;
   

After running these commands, you can verify the privileges again using:

sql

\dp public.*

Still not applied

The Solution

First change to the database you want.

\c databasename

qanaardb=# ALTER SCHEMA public OWNER TO rashiduser;
ALTER SCHEMA
qanaardb=# GRANT ALL PRIVILEGES ON SCHEMA public TO rashiduser;
GRANT
qanaardb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rashiduser;
GRANT
qanaardb=# \dn
   List of schemas
  Name  |   Owner    
--------+------------
 public | rashiduser
(1 row)

GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO rashiduser;
GRANT

GRANT postgres TO rashiduser;

Tadaa. now that works

Now I want to perform database import but it got interrupted

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;