In this section we will discuss several things you can do with postgresql in terms of management. Let's start with user management:

Every role has a unique name and also an OID value, which represents the role as a numerical value. This is similar to how users are represented in the Unix system (and many others) where the numerical value of a role is used only internally.

User management in postgresql is quite different than other databases. In PostgreSQL, you should remember one thing, EVERYTHING IS ROLE. You have roles which can connect (e.g. users) and roles which cannot connect (groups). You can nest roles one into another and thus granding privileges and groups to other users.

In order to create a user in PostgreSQL, you have to:

1. Create the role with password
2. Allow the role in the pg_hba.conf

So let's get going:

Again, user is a role which can connect and has password, that is the only difference between user and group in PostgreSQL. So to create a user we can use the following:

CREATE ROLE jcaruana  with password 'yxKEt045ZKcWiiu2YBxJ' INHERIT LOGIN;

grant dev to jcaruana;
grant select on all tables in schema public to dba;

To reset the password, we can use the following command:

ALTER USER integration_playson WITH PASSWORD 'xjaR3IvMDXF9NvPHwVSe';

forumdb=> SELECT * FROM pg_roles WHERE rolname = 'luca';
-[ RECORD 1 ]--+---------
rolname        | luca
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | 1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | f
rolconfig      |
oid            | 16390

forumdb=# \x
Expanded display is on.
forumdb=# SELECT * FROM pg_authid WHERE rolname = 'luca';
-[ RECORD 1 ]--+------------------------------------
oid            | 16390
rolname        | luca
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolbypassrls   | f
rolconnlimit   | 1
rolpassword    | md5bd18b4163ec8a3ad833d867a5933c8ec
rolvaliduntil  |

forumdb=> SELECT r.rolname, g.rolname AS group,
                 m.admin_option AS is_admin
          FROM pg_auth_members m
               JOIN pg_roles r ON r.oid = m.member
               JOIN pg_roles g ON g.oid = m.roleid
          ORDER BY r.rolname;
  rolname   |        group         | is_admin
------------+----------------------+----------
 enrico     | book_authors         | f
 enrico     | forum_admins         | f
 luca       | forum_stats          | f
 luca       | book_authors         | f
 pg_monitor | pg_read_all_settings | f
 pg_monitor | pg_read_all_stats    | f
 pg_monitor | pg_stat_scan_tables  | f
 test       | forum_stats          | f
(8 rows)

PostgreSQL stores permissions assigned to roles and objects as Access Control Lists (ACLs), and, when needed, it examines the ACLs for a specific role and a database object in order to understand whether the command or query can be performed. In this section, you will learn what ACLs are, how they are stored, and how to interpret them to understand what permissions an ACL provides.

An ACL is a representation of a group of permissions with the following structure:

luca=arw/enrico

First of all, identify the roles involed: luca and enrico. luca is the role before the equals sign, hence it is the role the ACL refers to, which means this ACL describes what permissions the luca role has. The other role, enrico, is after the slash sign and therefore is the role that granted luca permissions. Now, with respect to the flags, the ACL provides an append (a), read ®, and write (w) permissions. The above reads as “enrico granted luca to perform INSERT, UPDATE, and SELECT on the table.”

Let's now see an example of ACLs from a table in the database: you can use the special \dp psql command to get information about a table:

forumdb=> \dp categories
                                  Access privileges
 Schema |    Name    | Type  |   Access privileges   | Column privileges  | Policies
--------+------------+-------+-----------------------+--------------------+----------
 public | categories | table | enrico=arwdDxt/enrico+|                    |
        |            |       | luca=arw/enrico      +|                    |
        |            |       | =d/enrico             |                    |

forumdb=> WITH acl AS (
             SELECT relname, 
                    (aclexplode(relacl)).grantor, 
                    (aclexplode(relacl)).grantee, 
                    (aclexplode(relacl)).privilege_type 
            FROM pg_class )
         SELECT g.rolname AS grantee, 
                acl.privilege_type AS permission, 
                gg.rolname AS grantor
         FROM acl 
         JOIN pg_roles g ON g.oid = acl.grantee 
         JOIN pg_roles gg ON gg.oid = acl.grantor
         WHERE acl.relname = 'categories';
         
   grantee   | permission | grantor 
-------------+------------+---------
 luca        | INSERT     | luca
 luca        | SELECT     | luca
 luca        | UPDATE     | luca
 luca        | DELETE     | luca
 luca        | TRUNCATE   | luca
 luca        | REFERENCES | luca
 luca        | TRIGGER    | luca
 forum_stats | INSERT     | luca
 forum_stats | SELECT     | luca
 forum_stats | UPDATE     | luca

When it comes to database management, there are MANY possibilities of what you want to do. Let's see how we can dump data to CSV:

We can use the COPY tool from WITHIN the database as follows:

hunter_dev=# \copy (SQL STATEMENT) to 'out.csv' with csv header

That will execute teh SQL statement and put the data into a CSV file with the name of: “out.csv” and it will put the header on the top

Now, to clean pg_wal folder, we need to back them up. Assuming, we have done that OR that we don't care, we can use the following process:

[root@s24-se-db01 pg_wal]# /usr/pgsql-11/bin/pg_controldata -D /var/lib/pgsql/11/data/
pg_control version number:            1100
Catalog version number:               201809051
Database system identifier:           6742286774524545149
Database cluster state:               in production
pg_control last modified:             Fri 21 May 2021 21:37:51 CEST
Latest checkpoint location:           B6D/62D62990
Latest checkpoint's REDO location:    B6D/62941780
Latest checkpoint's REDO WAL file:    0000000100000B6D00000062                    <- This entry
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:774839045
Latest checkpoint's NextOID:          300868
Latest checkpoint's NextMultiXactId:  75180307
Latest checkpoint's NextMultiOffset:  30502311
Latest checkpoint's oldestXID:        600002360
Latest checkpoint's oldestXID's DB:   16406
Latest checkpoint's oldestActiveXID:  760450727
Latest checkpoint's oldestMultiXid:   62073622
Latest checkpoint's oldestMulti's DB: 16407
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Fri 21 May 2021 21:35:46 CEST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    logical
wal_log_hints setting:                off
max_connections setting:              1500
max_worker_processes setting:         28
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996

Before officially cleaning we can do a dry run:

[root@s24-se-db01 pg_wal]# /usr/pgsql-11/bin/pg_archivecleanup -n /var/lib/pgsql/11/data/pg_wal/ 0000000100000B6D00000061
[root@s24-se-db01 pg_wal]#

Then we can do the actual delete:

[root@s24-se-db01 pg_wal]# /usr/pgsql-11/bin/pg_archivecleanup -d /var/lib/pgsql/11/data/pg_wal/ 0000000100000B6D00000061
pg_archivecleanup: keeping WAL file "/var/lib/pgsql/11/data/pg_wal//0000000100000B6D00000061" and later
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B6A0000008C"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B6400000053"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B5400000008"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B550000007A"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B4C0000000E"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B65000000E1"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B480000008F"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B61000000BC"
[root@s24-se-db01 pg_wal]#