PostgreSQL – USERS
Database Users are accounts that can be used to log into the database system and perform various actions, such as querying the database, creating tables, and executing administrative tasks. PostgreSQL supports both regular users and superusers (also known as administrators).
Creating a User:
To create a user, you can use the CREATE USER command:
CREATE USER username WITH PASSWORD 'password'; Replace username with the desired username and password with the password you want to set.
Assigning Privileges:
You can assign various privileges to users. For example, to grant a user the ability to create databases, you can use the CREATEDB privilege:
ALTER USER username CREATEDB; You can also grant other privileges like SUPERUSER, LOGIN, REPLICATION, etc.
Changing User Password:
To change a user’s password, you can use the ALTER USER command:
ALTER USER username WITH PASSWORD 'new_password';Deleting a User:
To delete a user, you can use the DROP USER command:
DROP USER username;Viewing User Information:
You can view information about users by querying the pg_roles system catalog table:
SELECT * FROM pg_roles; This will display information about all users and roles in the database cluster.
Logging in as a User:
You can log into the PostgreSQL database system using the psql command-line tool or another PostgreSQL client by specifying the username and database:
psql -U username -d dbname Replace username with the desired username and dbname with the name of the database you want to connect to.
Superusers:
Superusers have elevated privileges and can perform administrative tasks. Be cautious when assigning superuser privileges, as they have the potential to modify the entire database system.
Best security practices should be followed when managing users and passwords in your PostgreSQL database to ensure the safety of your data and system.