Postgres Security 101: User Access and Authorization (4/8)

Managing user access and authorization is one of the foundational elements of database security in PostgreSQL. A well-defined user management strategy helps prevent unauthorized access, data breaches, and ensures that only the right users have the necessary permissions. In this article, we’ll explore PostgreSQL’s user roles, how to create users with different privilege levels, and the importance of adhering to the principle of least privilege. We’ll also cover schema and object-level permissions, which allow for fine-grained access control in your PostgreSQL environment.

4.1 Ensure Sudo Is Configured Correctly

When created, the PostgreSQL user may have interactive access to the operating system, which means that the PostgreSQL user could login to the host as any other user would.

sudo grep postgres /etc/shadow | cut -d: -f1-2
# If this output is not postgres:!<something> then this is a failure.

# Execute the following command:
sudo passwd -l postgres

4.2 Ensure Sudo Is Configured Correctly

• Review and configure sudo permissions to limit administrative access. This grants any Operating System user that is a member of the dba group the ability to use sudo -iu postgres to become the postgres user

sudo groupadd dba

useradd -m -G dba kemal

echo '%dba ALL=(postgres) PASSWD: ALL' > /etc/sudoers.d/postgres

chmod 600 /etc/sudoers.d/postgres

su - kemal

sudo -iu postgres

4.3 Ensure Excessive Administrative Privileges Are Revoked

• With respect to PostgreSQL administrative SQL commands, only superusers should have elevated privileges. PostgreSQL regular, or application, users should not possess the ability to create roles, create new databases, manage replication, or perform any other action deemed privileged. Typically, regular users should only be granted the minimal set of privileges commensurate with managing the application

psql -c "\du+ kemal*"

List of roles
 Role name | Attributes |Member of | Description
-----------+------------+-------------------------+-------------
 kemal.oz  | Superuser  | {"DBA"}  |
 
psql -c "ALTER ROLE kemal.oz…