Postgresql Quick Refer Collection. [Generate SQL's For Altering Ownership for all table] [psql: FATAL: role "user" is not permitted to log in]

postgres_sql_collection.sql

# Generate SQLs For Altering Ownership for all table
SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO ipc_user;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;



# TO PERMIT LOGIN
# issue : 'psql: FATAL:  role "user" is not permitted to log in'

CREATE ROLE "user" WITH PASSWORD 'password';
DROP DATABASE IF EXISTS "testdb"; CREATE DATABASE "testdb" OWNER "user"; 
# GRANT ALL ON DATABASE "testdb" TO "user";       ## !OWNERSHIP DO THE SAME
ALTER ROLE "user" WITH LOGIN;