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