.::: Create user, change password, change role & drop user , create user with specification limited access PostgreSQL EDB :::.

1. Create user

su - postgres

create user admin;
ALTER ROLE admin SUPERUSER CREATEDB CREATEROLE REPLICATION;
ALTER ROLE admin PASSWORD 'admin';
ALTER USER admin WITH PASSWORD 'admin';

DROP user admin;

[root@teguhth ~]# su - postgres
Last login: Thu Oct 13 15:13:33 +07 2022 on pts/0
-bash-4.2$ psql
psql (9.2.24, server 14.5)
WARNING: psql version 9.2, server version 14.0.
         Some psql features might not work.
Type "help" for help.

postgres=#

postgres=# create user testuser;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication             | {}
 myuser    |                                                            | {}
 pgb       | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 teguh     | Superuser, Create role, Create DB, Replication             | {}
 testuser  |                                                            | {}

2. change or add role

postgres=# ALTER ROLE testuser SUPERUSER CREATEDB CREATEROLE REPLICATION;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication             | {}
 myuser    |                                                            | {}
 pgb       | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 teguh     | Superuser, Create role, Create DB, Replication             | {}
 testuser  | Superuser, Create role, Create DB, Replication             | {}

postgres=#
 
3. add or change password

postgres=# ALTER ROLE testuser PASSWORD 'admin';
ALTER ROLE
postgres=# ALTER USER testuser WITH PASSWORD 'admin'
ALTER ROLE
postgres=#


4. Drop user
postgres=# DROP user testuser;
DROP ROLE
postgres=#


postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication             | {}
 myuser    |                                                            | {}
 pgb       | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 teguh     | Superuser, Create role, Create DB, Replication             | {}

postgres=#

5. create user with specify access or limited access

CREATE USER readonly WITH PASSWORD 'readonly';

GRANT CONNECT ON DATABASE teguhth TO readonly;
GRANT CONNECT ON DATABASE edbmaria TO readonly;

GRANT insert,update,delete,select ON pembelian TO readonly;
GRANT insert,update,delete,select ON barang TO readonly;

GRANT SELECT, INSERT, UPDATE, DELETE ON Sp_SimpleCalculator TO readonly;
GRANT EXECUTE ON PROCEDURE teguhth.SimpleCalculator TO readonly;
GRANT EXECUTE ON FUNCTION teguhth.calculate TO readonly;

6. test access user with specify access or limited access

psql -h 10.10.10.8 -U readonly -d teguhth

psql -h 10.10.10.8 -U admin -d teguhth

select Sp_SimpleCalculator(100,400,'*');