PostgreSQL users
Introduction
Users
Get the list of users
postgres=# \du
List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication | {}
Create a user
To create a user run
postgres=# CREATE USER user01 WITH PASSWORD 'pwd01';
CREATE ROLE
Check
postgres=# \du
List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} user01 | | {}
Grant rights to a user
It is more correct to say add attributes to a role, because in the users table they look like this
\du
List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- andrei | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
As you can see, the user (role) andrei has no attributes at all. Let us add them with the command
postgres=# ALTER ROLE andrei WITH SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS;
ALTER ROLE
Check
postgres=# \du
List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- andrei | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Change the password
To change a user password run
sudo -u postgres psql -c "alter user USER_NAME with encrypted password NEW_PASSWORD"
If you call the command from Python
f"sudo -u postgres psql -c \"alter user {dbuser} with encrypted password '{dbpass}'\"
Article author: Andrei Olegovich