task_003_managing_users

Managing Users

• Managing Users
  • Create a User
  • Grant Priviledges to user app_user_microservice on Database student
  • Validate Priviledges for user app_user_microservice on Database
    • Database level priviledges
    • Schema level priviledges
  • Grant Priviledges to user app_user_microservice on ALL Tables in public schema for bank database
    • Validate Priviledges for user app_user_microservice on Tables
    • Table level priviledges
  • Show all Users
  • Reset Password for a User

Create a User

To create a user you can run

CREATE USER app_user_microservice WITH PASSWORD 'somepassword';

# CREATE USER <username> WITH PASSWORD '<password>'; 
postgres=> CREATE USER app_user_microservice WITH PASSWORD 'somepassword';
CREATE ROLE

Grant Priviledges to user app_user_microservice on Database student

Granting priviledge to a database for that user

GRANT ALL PRIVILEGES ON DATABASE student to app_user_microservice;

Output

postgres=> GRANT ALL PRIVILEGES ON DATABASE <dbname> to <username>;
GRANT

Validate Priviledges for user app_user_microservice on Database

Database level priviledges

SELECT datname, has_database_privilege('app_user_microservice', datname, 'CONNECT') 
FROM pg_database;

postgres=# SELECT datname, has_database_privilege('app_user_microservice', datname, 'CONNECT') 
postgres-# FROM pg_database;
   datname   | has_database_privilege 
-------------+------------------------
 postgres    | t
 root        | t
 template1   | t
 template0   | t
 bank | t
(5 rows)

Schema level priviledges

SELECT nspname, has_schema_privilege('app_user_microservice', nspname, 'USAGE') 
FROM pg_catalog.pg_namespace;

postgres=# SELECT nspname, has_schema_privilege('app_user_microservice', nspname, 'USAGE') 
postgres-# FROM pg_catalog.pg_namespace;
      nspname       | has_schema_privilege 
--------------------+----------------------
 pg_toast           | f
 pg_catalog         | t
 public             | t
 information_schema | t
(4 rows)

Grant Priviledges to user app_user_microservice on ALL Tables in public schema for bank database

Make sure you are connected to the database bank

\c bank

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user_microservice;

postgres=# \c bank
You are now connected to database "bank" as user "root".
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user_microservice;
GRANT

Validate Priviledges for user app_user_microservice on Tables

Table level priviledges

SELECT tablename, has_table_privilege('app_user_microservice', tablename, 'SELECT') 
FROM pg_tables
WHERE schemaname = 'public';

bank=# SELECT tablename, has_table_privilege('app_user_microservice', tablename, 'SELECT') 
bank-# FROM pg_tables
bank-# WHERE schemaname = 'public';
     tablename     | has_table_privilege 
-------------------+---------------------
 accounts          | t
 entries           | t
 transfers         | t
 schema_migrations | t
(4 rows)

Show all Users

Run the following

\du

Output

postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 root      | Superuser, Create role, Create DB, Replication, Bypass RLS

Reset Password for a User

postgresql.org/docs » ALTER USER

ALTER USER app_user_microservice WITH PASSWORD 'secret';

Output

## Username : app_user_microservice, new password : secret
postgres=# ALTER USER app_user_microservice WITH PASSWORD 'secret';