A Cheat Sheet to Database Access Control: PostgreSQL

PostgreSQL, often called Postgres, is a powerful open source relational database management system (RDBMS). Postgres is known for its advanced features, its extensibility and its adherence to SQL standards. It is widely used in various industries for applications ranging from small-scale projects to large, mission-critical systems.

For part three of our how-to series on access management for the most commonly used databases in today’s cloud environments, we’ll do a deep dive into PostgreSQL, including a cheat sheet to all PostgreSQL access commands. Don’t miss the first two articles in this series, which look at access control in MySQL and MongoDB.

Why You Need to Manage Access to PostgreSQL

As with any other database, PostgreSQL requires dedicated access management to maintain security, data integrity and compliance.

• Enforce data integrity: Ensuring that only authorized users can modify or delete data helps prevent accidental or intentional corruption of the database.
• Prevent unauthorized access: Failing to ensure that only authorized individuals or applications have the appropriate permissions to interact with your PostgreSQL database can lead to data breaches, unauthorized data modifications or data theft.
• Safeguard sensitive information: Protecting sensitive data helps prevent unauthorized users from accessing or manipulating it.
• Meet compliance requirements: It is crucial to meet industry and region-specific compliance standards to avoid legal consequences and regulatory penalties.
• Establish visibility and auditability: Access management allows you to create detailed audit logs that provide a record of who accessed the database and what actions they performed. Regulatory frameworks often require organizations to maintain audit trails of database activities.
• Mitigate security risks: Inadequate access controls can expose a PostgreSQL database to security risks, making it a target for malicious activities such as SQL injection or unauthorized access attempts. Effective access management helps mitigate these risks and prevents data breaches.
• Empower developers: Ensuring that individuals have the appropriate level of access — when they need it — enables them to perform their duties.
• Facilitate incident response: In the event of a security incident, access controls play a crucial role in containing the impact and facilitating incident response. Restrict access to prevent further unauthorized actions and limit the incident’s scope.

Considerations for Implementing PostgreSQL Access Control

PostgreSQL access control and management should include the following capabilities:

• Control when and how long: Access should be delivered “just in time” and “just enough” to prevent overprivileged users and roles. Some access control solutions like Apono specialize in just-in-time access control for PostgreSQL.
• Get granular: Define roles and assign specific permissions to users and roles based on their responsibilities. Set up access controls for resources as granular as database tables, rows and columns.
• Customize: Create custom roles, groups and workflows, and define specific permissions tailored to your application’s requirements.
• Automate: Automation is beneficial for managing large-scale deployments, creating consistency and saving valuable time.
• Generate a log: Logging is crucial for monitoring user activities, detecting suspicious behavior and maintaining compliance.
• Scale with your business: To scale efficiently with the size and complexity of your PostgreSQL deployment, access control should be able to handle a growing number of users and permissions.

A PostgreSQL Cheat Sheet for Commands to Control Access

Here’s a quick reference cheat sheet for PostgreSQL access control commands:

• Create a new user:
  CREATE USER username WITH PASSWORD 'password';
• Create a role:
  CREATE ROLE rolename;
• Grant privileges:
  GRANT SELECT ON TABLE tablename TO username;
• Revoke privileges:
  REVOKE SELECT ON TABLE tablename FROM username;
• View user roles and privileges:
  SELECT rolname, rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = 'username';
• Create policies at the row level:
  CREATE POLICY policyname
ON tablename
USING (condition)
FOR ALL
TO rolename;
• Remove user:
  DROP USER username;
• Remove role:
  DROP ROLE rolename;

Conclusion

PostgreSQL is a powerful database management system with built-in access control capabilities. However, the highly manual nature of setting up Postgres security configurations increases the potential for overprivileged and unauthorized access. The landscape is shifting towards dedicated solutions that help organizations do this at scale, thereby helping make data more secure and better managed.