PostgreSQL

PostgreSQL SQL error

password authentication failed

psql: FATAL:  password authentication failed for user "postgres" password retrieved from file "/var/lib/pgsql/.pgpass"

Solution:
cp /var/lib/pgsql/9.6/data/pg_hba.conf /var/lib/pgsql/9.6/data/pg_hba.conf_backup
chattr -aie /var/lib/pgsql/data/pg_hba.conf
Copy the password from: cat /var/lib/pgsql/.pgpass
Login to WHM > Configure PostgreSQL and update the password. 
Then, ----ia-------e-- /var/lib/pgsql/data/pg_hba.conf

Unable to drop a user

When Unable to drop a user with error:

postgres=# drop USER australe;
ERROR:  role "australe" cannot be dropped because some objects depend on it
DETAIL:  privileges for database betterf2_futureainet

Do the following:

REASSIGN OWNED BY myuser TO postgres;
DROP OWNED BY myuser;
DROP USER [IF EXISTS] australe;

Unable to export the database

Login to server sudo su - then su - postgres psql then connect to database using

postgres=# \connect DB_NAME;

Then Run the following commands:

GRANT USAGE ON SCHEMA topology TO USER ;
GRANT SELECT ON ALL TABLES IN SCHEMA topology TO USER ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA topology TO USER ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO USER ;

GRANT USAGE ON SCHEMA public TO USER;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO USER;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO USER ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO USER ;

SCRAM authentication requires libpq version 10 or above

During remote PSQL connection, sometime customer encounters following error:

Unable to connect to PostgreSQL server: SCRAM authentication requires libpq version 10 or above in /home/punkuuuc/public_html/xxx.php on line 3

Need to perform these steps on destination server:

Step 1: Open the configuration file /var/lib/pgsql/xx/data/postgresql.conf then set password_encryption = md5 (#password_encryption = scram-sha-256 # scram-sha-256 or md5) Step 2: open /var/lib/pgsql/xx/data/pg_hba.conf then change all METHOD to md5

Restart PGSQL after this step.

Step 3: Open command line (cmd,cmder,git bash...) and run psql -U postgres then enter your password when installed postgres sql Step 4: Then change your password by run ALTER USER postgres WITH PASSWORD 'new-password' in command line