postgresql: get all users and alter user

Get all users:
SELECT * FROM pg_user;

SELECT usename FROM pg_user;


Rename a user:
ALTER USER bsmith RENAME TO bjohns;

Make a user superuser
ALTER USER bsmith WITH SUPERUSER;

Change a user's password:
ALTER USER bjohns PASSWORD '12345bjxy';

Set the validity of a user's password:
ALTER USER bjohns VALID UNTIL 'Dec 31 2020';

ALTER USER bjohns VALID UNTIL 'infinity';

Give a user ability to do something:
ALTER USER bjohns CREATEUSER;

ALTER USER bjohns CREATEUSER NOCREATEDB;


pg_user is a view in posgresql, having the following information about all database users.

Name	Type	Description
usename	name	User name
usesysid	oid	ID of this user
usecreatedb	bool	User can create databases
usesuper	bool	User is a superuser
usecatupd	bool	User can update system catalogs. (Even a superuser cannot do this unless this column is true.)
userepl	bool	User can initiate streaming replication and put the system in and out of backup mode.
passwd	text	Not the password (always reads as ********)
valuntil	abstime	Password expiry time (only used for password authentication)
useconfig	text[]	Session defaults for run-time configuration variables

Reference:

1. PostgreSQL 9.2.25 Documentation: pg_user


                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one?Read the book free here.