PostgreSQL Database Change User Password

Photo by olieman.eth on Unsplash

For changing the user password in a PostgreSQL database you need to execute the following command:

ALTER USER user_view_0003 WITH PASSWORD 'testExam&pelPw1';

This SQL statement will set the password ‘testExam&pelPw1’ to the user ‘user_view_0003’.

If you need to create a new user follow those steps:

CREATE USER user_view_0001 WITH PASSWORD 'plA8sefsuiefUHBDI&aNDo';
GRANT CONNECT ON DATABASE postgres TO user_view_0001;

The command creates the user and lets the user connect to the database “postgres”.

GRANT USAGE ON SCHEMA public TO user_view_0001;

This command grants usage permissions to the schema “public”.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user_view_0001;

This command allows the select SQL statement for all tables inside the public schema. This means the user is allowed to see all the tables and their content.

Conclusion

Let's put everything together:

CREATE USER user_view_0001 WITH PASSWORD 'pLseFoi3houib&I&aNDo';
GRANT CONNECT ON DATABASE postgres TO user_view_0001;
GRANT USAGE ON SCHEMA public TO user_view_0001;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user_view_0001;

I hope it helped. Happy coding.