Task:
Create new auditor user, add as a database user and limit access, and allow network access from source IP subnet.
Steps:
a. Login to the primary database:
$ ssh myadminid@dbserver.mindwatering.net
<enter password>
$ sudo su
<enter password>
# su postgres -
b. Create user:
$ psql widgetdb
widgetdb=# CREATE USER widgetauditor WITH PASSWORD 'AbCd1234eFGh4321etc' VALID UNTIL '2025-12-01';
<view CREATE ROLE response>
- or, without expiration, another variant is:
widgetdb=# CREATE ROLE widgetauditor WITH LOGIN ENCRYPTED PASSWORD 'AbCd1234eFGh4321etc';
widgetdb=# \du
<view users and password expiration (if given) under List of role Attributes column>
c. Add user to database:
Options:
- In PostgreSQL 9.x, CONNECT, USAGE, and SELECT are no longer required to be individually run.
- - Run the first command to give the user access to the schema, the second to give access to the tables, and the third command to keep the public roles users from creating new tables with data:
widgetdb=# GRANT USAGE ON SCHEMA public TO widgetauditor
widgetdb=# GRANT SELECT ON ALL TABLES in SCHEMA public to widgetauditor;
widgetdb=# REVOKE CREATE ON SCHEMA public FROM public;
- In PostgreSQL 14.x, If you wish ALL databases on this instance be available for audit, grant the pg_read_all_data predifined role built into PostgreSQL 14 and higher:
widgetdb=# GRANT pg_read_all_data to widgetauditor;
d. Logout psql and logout postgres user:
widgetdb=# \q
$ exit
d. Update conf with widthauditor's department IP subnet, adding the db and new user line:
Note: if you want all internal networks use: 0.0.0.0/0.
# vi /etc/postgresql/<versionnumber>/main/pg_hba.conf
...
hostssl widgetdb widgetauditor 10.0.53.0/24 md5
<esc>:wq (to save)
# systemctl reload postgresql
<wait for a second>
# systemctl status postgresql
<confirm started/active, and check for any errors>
previous page
|