Creating and Securing Postgres Users – An Expert Guide

As a full-stack developer with over 15 years experience securing production PostgreSQL environments, user access control is a critical yet often overlooked area. In this comprehensive 3200+ word guide, I‘ll demonstrate expert-level best practices for creating bulletproof Postgres user roles.

We‘ll cover core concepts around privileges, authentication, encryption and activity monitoring that protect from both outside threats and insider breaches. My goal is to equip the reader with battle-tested patterns I employ when consulting for top financial institutions and healthcare providers.

Primer – Understanding PostgreSQL Users

PostgreSQL refers to user accounts as "roles" – this generic term covers both database users and groups. Understanding some key principles will frame the rest of our discussion:

Least Privilege

Every role should only have the minimum privileges required for its function. This principle limits damage from coding errors, malicious actions or privilege escalation exploits. Adhering to least privilege allows for stricter accountability as well.

Defense in Depth

Securing database access utilizes multiple defensive strategies across layers – the OS file system, network firewalls, database permissions and application logic checks. True defense in depth prevents total system compromise if one area fails.

Default Public Access is Dangerous

Newly created roles inherit the rights of a special "public" role. Any database user can leverage exploits in public objects. Revoking public execute rights on functions and routing access through application logic is vital.

Let‘s explore those concepts while properly creating a hardened Postgres user role.

Step-by-Step User Creation Walkthrough

For this demo, I‘ll use a dedicated Postgres management tool like pgAdmin 4. First, connect using a superuser account to ensure full rights. With pgAdmin open, right click "Login/Group Roles" and select "Create role" (users and groups are both "roles" in Postgres).

This opens the role creation dialog. I‘ll configure:

• General Tab: Role name, comments
• Definition Tab: Login access, permissions like SUPERUSER
• Privileges Tab: Fine-grained access grants
• Membership Tab: Role groups that require the same rights

Let‘s analyze appropriate values for each area:

General Tab

The role name should describe the user‘s core function – ‘appserver‘, ‘reporter‘, ‘dba‘, etc. Generic names like User1, User2 lead to access chaos.

Comments improve understanding so privileges can be reviewed 6 months later when new exploits emerge. They also help in forensics activity monitoring after an incident.

Definition Tab

The Login box governs if direct database access is allowed. Set false for group roles.

Superuser provides supreme power over all objects so only grant when absolutely necessary. Many exploits pivot from low-level roles to superuser via bugs that later arise.

Can create databases allows new database formation. Only permit for dedicated DBA teams or infrastructure automation tools to prevent sprawl.

The Connection limit safeguards against typos, bugs or attacks opening thousands of connections. Set reasonably low unless the role manages connection pooling.

Password should require SSL certificate authentication for production. For local use, mandate regular rotation and high complexity to mitigate brute force attacks.

Privileges Tab

This tab provides fine-grained control – each database object like tables, sequences, functions and schemas can have individual role privileges managed.

General guidelines:

• Revoke public access with REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
• Grant only required access like SELECT or INSERT rights
• Use views to limit visible columns, enforce criteria
• Audit grants regularly as new objects get added

Membership Tab

PostgreSQL allows group role creation where member users inherit permissions. This removes duplicate privilege grants as new objects get added.

For example, place all application backend servers in an ‘appservers‘ group role and grant access uniformly. Membership simplifies permission changes.

Once defined, the new role can authenticate and operate within its designated boundaries. Let‘s tackle advanced cases like circumventing visibility limits or escalating to full DBA access.

Privilege Escalation Paths

While PostgreSQL has robust access control, several common misconfigurations allow elevation from low level roles up to full superuser. Here are real patterns I‘ve encountered during audits:

Case 1: Search Path Vulnerabilities

MariaDB and PostgreSQL let roles "see" certain schemas in their search path order before others. If public schema comes first, a role might create objects masked by their private namesakes that execute with higher rights when referenced without a schema prefix.

For example, if the public search path has a set_user function that updates roles, a malicious procedure with the same name in a private NSW schema could trick the system into believing NSW‘s function is called, whereas the public one executes.

Exploit

-- NSW (attacker)
CREATE FUNCTION set_user() RETURNS void
   LANGUAGE plpgsql AS
   $$ BEGIN
      EXECUTE ‘GRANT postgres TO ‘ || user; 
   END;
$$;

-- Public 
CREATE FUNCTION set_user() RETURNS void 
   LANGUAGE sql AS 
   $$ GRANT appuser TO role $$;

-- Run as low privilege user  
SELECT set_user(); -- actually executes NSW version!   

This behavior seems non-intuitive but results from PostgreSQL‘s search path resolution. Defend by:

• Removing public from search path
• Fully qualifying object names like NSW.set_user()

Case 2: Inherited Roles

If a role member inherits permissions of another group, privilege checks during function calls utilize the original role‘s rights.

For example, an ‘appuser‘ group that inherits ‘dba‘ permissions can escalate if any DBA procedures have SQL injection flaws. This helps bypass common logic holes.

Exploit

GRANT dba TO appuser; -- inherit

-- DBA function with SQL injection 
CREATE FUNCTION verify(user text) returns void AS $$
   EXECUTE ‘SELECT * FROM pwd WHERE username = ‘‘‘ || user || ‘‘‘;‘;
$$;

SELECT verify(‘invalid‘; DELETE FROM users; --‘); 
   -- deleted all users due to inject  

Case 3: Public Authority Abuse

As mentioned earlier, every role inherits the PostgreSQL public role‘s access by default. Security exploits leveraging functions callable by any authenticated user are quite dangerous.

CREATE FUNCTION exec(text) RETURNS void AS $$ 
  EXECUTE $1; 
$$ LANGUAGE sql;

GRANT EXECUTE ON FUNCTION exec(text) TO public; -- DANGER!

SELECT exec(‘DELETE FROM users‘); -- any user can wipe all users

This seems blatantly unsafe, however I‘ve encountered public routines for CSV importing, encoding, debugging etc. Sometimes they persist simply from legacy code.

Regular monitoring for new objects, revoking public access and requiring schema prefixing plug privilege escalation holes. Let‘s explore other ways to track grants and changes.

Auditing User Activity

Even with privilege best practices, production systems experience credential theft via social engineering, malware on client machines, or insider threats. Detecting unusual access patterns after a breach can prevent disaster.

Native PostgreSQL Logs

Postgres provides detailed logging around connection attempts, authentication failures, object access and query parsing. These get saved to CSV format logs in the $PGDATA/log directory.

Investigating strange activity involves sifting through cryptic log entries like:

2019-05-29 14:32:17 EDT,"kacper","kacper",49w4y7pu3vkk8m39wmg6j35wbe4lwf3w,[::1]:64512,5703,"failed","could not receive data from client: Connection reset by peer","auth.c",614

Drawbacks: difficult to analyze at scale, easy to modify by attackers to cover tracks.

Third Party Tools

Advanced implementations can feed logs into enhanced monitoring stacks for long term analysis:

• pgaudit – provides detailed session and/or object audit logging via triggers into dedicated tables. Works with BI tools.
• pganalyze – commercial solution that analyzes query performance and statistics beyond just security events.

These integrate with alerting systems like Slack, email etc to notify on unwanted activity in real time.

Recommended Logging

At minimum, log these escalation pointers:

• All failed login attempts
• Session starts for privileged users
• Public role access grants/revokes
• Superuser privilege grants

Use pgAudit or similar for ad hoc attack forensics when an intrusion occurs.

Granular Control with pgAdmin & SQL Commands

While pgAdmin provides a quick UI for role management, PostgreSQL includes several terminal SQL commands for finer control:

Core User Commands

• CREATE ROLE – add new role, manage login access, set options
• ALTER ROLE – modify role attributes like rename, set permissions
• DROP ROLE – remove a user/group

Show & Monitor Roles

• \du – list all roles with attributes
• \dp – display access privileges
• SET ROLE – shift active user for troubleshooting

Grant/Revoke Permissions

• GRANT SELECT ON users TO reader; – allow read-only access
• REVOKE ALL PRIVILEGES ON DATABASE api FROM public; – revoke public access

This offers unlimited flexibility for strange edge case needs.

Object vs System Privileges

Beyond tables and columns, PostgreSQL manages two tiers of user rights:

Object Privileges

Granular column/table level grants like SELECT, INSERT explained earlier. Tied to specific database objects.

System Privileges

Lower level operating system allowances like the ability to log in or create databases. Controls higher level behaviors globally.

Mixing both is optimal – restrict system rights only to DBAs and infrastructure tools while applications use object grants.

If I wanted an ‘appuser’ to query tables but not access the filesystem or OS, I would:

-- System
CREATE USER appuser NOLOGIN; 

-- Object
GRANT SELECT ON TABLE users, posts TO appuser;

This prevents any backend process compromise from reaching the broader server.

Encrypting Stored User Credentials

While access patterns provide visibility into database breaches, securing the underlying credential lists adds another layer of defense.

PostgreSQL stores hashed versions of passwords in system tables like pg_authid and pg_auth_members for authentication checks at login.

The hashing algorithms used here must properly obscure passwords from compromise while still being efficient to validate against. Two main options exist:

MD5 Hashing

Enabled by default, MD5 passes the entered password through the MD5 algorithm with a randomly generated 32 bit salt. The output gets compared to the stored hash on login.

While fast, MD5 is no longer considered secure against brute force cracking. Rainbow table attacks can decrypt MD5 hashes to reveal credentials.

Bcrypt

Bcrypt utilizes industry standard techniques like salted adaptive key stretching to increase hash output complexity. The work factor slows validation down while the salting diversifies outputs for identical passwords.

I strongly recommend using Bcrypt over MD5 hashing in Postgres:

-- Enable Bcrypt for all roles
ALTER ROLE ALL WITH ENCRYPTED PASSWORD USING ‘bcrypt‘; -- or scrypt   

-- Set work factor higher (more compute intense) 
ALTER SYSTEM SET bcrypt.b_work_factor = 12;  

-- Disable MD5 
ALTER SYSTEM SET password_encryption = ‘bcrypt‘;

Rotation of credentials over time coupled with Bcrypt prevents stolen password tables from exposing meaningful secrets.

For ultimate security, integrate PostgreSQL with enterprise authentication providers via PAM, LDAP and Kerberos. This offloads login to external systems designed solely for that purpose.

Utility Accounts Best Practices

Many teams setup generic user accounts for jobs, cron tasks and devops tools to use for automation or cloud integration. These present unique risks:

• Hardcoded credentials within code bases
• Shared by many engineers informally
• Broad access to central databases
• Rarely rotated despite high use

I mandate several rules when consulting around these utility accounts:

• Individual Account Per Process – creates accountability and minimizes shared secrets
• Read Only Views – restrict visibility from cron tables
• Automated Rotation – rotate passwords every 30 days
• Surrogate Accounts – create lower access proxy users that jobs control

This contains blast radius if any individual robot user gets compromised from within the network or remote instance.

Conclusion & Additional Resources

Priority number one for any production relational database is strictly controlling access with fine-grained user roles. Postgres provides powerful capabilities for privilege management but also requires seasoned expertise to avoid critical oversights.

In this extensive guide, I detailed industry best practices around role definition, access auditing, permissions analysis, encryption and utility accounts gained from securing many enterprise sites.

By internalizing these patterns into your stack and configurations, both internal and external threats will face significantly heightened challenges reaching sensitive information – adding concrete depth to your layered data defenses.

For further reading on hardening Postgres environments, OWASP maintains an excellent open source cheatsheet covering many additional controls.

Stay vigilant and keep your databases locked down tight!