Hello, database warriors! 
The importance of authentication in authorization
Before we jump into authorization, it’s crucial to understand its dependency on effective authentication. Authentication acts as the first checkpoint in securing your database, setting the stage for a robust authorization process.
Sequence matters: Think of authentication as your database's front door. Once it's securely locked, you can confidently decide who gets access to what within your database (authorization).
Both Fujitsu Enterprise Postgres and PostgreSQL, being highly versatile, provide several ways to manage authentication, from simple password checks to more complex integrations with external authentication systems. Here are some of the more popular methods of authentication used:
- Password authentication
The most common method, requiring users to provide a password. Used because it’s straightforward and familiar to users, making it a good choice for many general applications.
- Kerberos authentication
A network authentication protocol that uses tickets provided by a central Key Distribution Centre. An ideal solution in larger organizations or environments where centralized, high-security authentication is required.
- Ident and peer authentication
Both methods authenticate based on the operating system user. Ident is typically used over network connections, while Peer is for local connections. It’s use case is typically for secure, internal environments where database users closely map to OS users.
- LDAP authentication
Delegates authentication to an external LDAP server. This is great for organizations already using LDAP, as it centralizes user management and streamlines authentication across systems.
- Certificate authentication
Utilizes SSL client certificates for authentication. A high level of security is offered, and it is suitable for environments where strong authentication is paramount.
- GSSAPI authentication
Provides a way for applications to authenticate users, commonly used with Kerberos. It’s ideal for environments requiring integrated, strong security without transmitting passwords.
- SSPI authentication
Specifically for Windows environments and integrating with native Windows security. Its seamless integration with Windows security mechanisms simplifies management in Windows-based setups.
Now that we have familiarized ourselves with the various commonly used methods, it is crucial to select the most suitable one for your specific circumstances.
Choosing the right authentication method
Selecting the appropriate authentication method depends on several factors:
- Security needs: How critical is the security of your data?
- Operational environment: Are you in a Windows-based, Linux, or mixed environment?
- User management: Do you need centralized user management, like LDAP or Kerberos?
- Ease of use: How important is ease of use and familiarity for your users?
The vital role of database authorization
Authorization in databases, crucial yet often understated, involves defining who can access what and the extent of their actions. It's akin to distributing keys to various rooms in a building, with each key tailored to the holder's needs and responsibilities.
Why it matters: Proper authorization is the cornerstone of data integrity and security. It prevents unauthorized access, accidental data leaks, or misuse, all of which are potential sources of operational and legal issues.
Real-world impact of authorization failures
Case studies:
- Excessive permissions: A financial institution faced operational disruptions due to an error in permission settings.
- Inadequate access reviews: A healthcare provider suffered a data breach because of failure to revoke access from a former employee.
- Absence of role-based access control: An e-commerce company experienced a significant breach, highlighting the need for tailored access.
Integrating database authorization into the cybersecurity framework is critical for holistic security. Let’s explore how authorization aligns with the core functions of a typical cybersecurity framework, such as NIST's five pillars:
Identify
- Risk assessment: Authorization plays a critical role in identifying who has access to what data, which is essential for assessing risks related to data exposure and access control.
Protect
- Access control: This is where database authorization directly contributes. By ensuring that only authorized individuals have access to specific data, you’re taking a proactive step to protect sensitive information from unauthorized access or leaks.
- Data security: Implementing robust authorization controls helps in safeguarding data against malicious attacks or insider threats.
Detect
- Monitoring access patterns: Proper authorization mechanisms can facilitate the detection of abnormal access patterns or unauthorized attempts to access data, serving as an early warning system for potential breaches.
Respond
- Incident response: In the event of a security incident, having a deep understanding of authorization structures can play a crucial role in quickly identifying and isolating affected areas, thereby aiding in a swift response.
Recover
- Resilience planning: A well-structured authorization system can minimize the impact of a breach, making it easier to recover specific data sets and maintain operational continuity.
Authorization is not just about internal security; it's also about compliance with regulatory requirements and adopting industry best practices. For example, adhering to GDPR, HIPAA, PCI-DSS, etc., often involves stringent data access controls. Following cybersecurity framework guidelines assists with compliance and ensures that your database authorization strategies are up to date and effective.
Effective authorization is a collaborative effort that involves other teams, 2 of which I mentioned in another article in this series titled Database security: Key roles every DBA should know.
- IT Security Team: Aligning database access controls with broader security policies.
- Compliance Officers: Ensuring adherence to legal and regulatory requirements.
- Auditors: Regular reviews to validate the effectiveness of authorization policies.
Now we understand authentication, and where authorization fits into our cybersecurity framework, let's explore the various ways we implement authorization in PostgreSQL and Fujitsu Enterprise Postgres, guided by Chapter 4 (access control) of the CIS Benchmark for PostgreSQL:
- Role-based access control (RBAC)
- Roles: In PostgreSQL, roles are the primary means of controlling access to your database. A role can represent a database user, a group of users, or both.
- Granting permissions: You can grant roles specific permissions on database objects. For example, you can allow a role to read (SELECT), write (INSERT, UPDATE, DELETE), or manage (ALTER, DROP) specific tables.
- Role inheritance: Roles can inherit permissions from other roles, making it easier to manage permissions for groups of users.
- Schema-based authorization
- Schemas as permission boundaries: Schemas in PostgreSQL can be used to group database objects. You can grant a role access to all objects within a schema, simplifying permission management.
- Default privileges: Set default privileges for objects created within a schema, automatically determining what access new objects should have.
- Row-level security (RLS)
- Fine-grained control: RLS allows you to control access at the row level within a table. This means you can define policies that restrict which rows can be viewed or modified based on the user's role.
- Policy creation: Implement RLS by defining policies on tables that specify which rows are accessible. This is particularly useful for multi-tenant applications or when handling sensitive data.
- Column-level permissions
- Restricting column access: You can grant or revoke access to specific columns within a table. This is useful when some columns contain sensitive information that not all users should access.
- Granular control: For example, you might allow a role to view all columns in a customer table except for personal identification numbers.
- Data masking policies (Fujitsu Enterprise Postgres only)
- Definition of rule-based polices: Being able to provide visibility to data based on dynamic rules further refines access on a need-to-know basis.
- Creation of realistic test data: Using production data to create realistic test systems provides many advantages. Obfuscating data but still making it usable is a way to do this.
You can learn more about Fujitsu Enterprise Postgres' data masking here.
- Function-based authorization
- Using functions for controlled access: Sometimes, direct access to tables isn’t ideal. In these situations, you can create functions (stored procedures) that provide an interface to the data, allowing more controlled and indirect access.
- Security definer functions: Functions can be defined with the SECURITY DEFINER attribute, which allows them to be executed with the permissions of the user who created them, not the user who calls them.
- Use of views for restricting access
- Creating views: Views can act as a window to underlying tables, showing only certain rows or columns.
- Permission on views: Grant users access to these views instead of the underlying tables, thereby limiting what they can see and query.
- Connection limitations
- Limiting connections: For roles that should have restricted access to the database, you can set connection limitations, thereby controlling how and when they can connect to the database.
By combining these methods, you can create a comprehensive authorization strategy in both Fujitsu Enterprise Postgres and PostgreSQL that is tailored to your specific requirements and security policies. Each method has its use cases and can be chosen based on the level of security and granularity needed.
Conclusion
Database authorization, an integral part of database security, plays a pivotal role in the broader cybersecurity landscape. It's about creating a secure, well-ordered environment where data integrity and compliance are paramount.
As we continue to explore the intricacies of database security, remember that each element, including authorization, forms a crucial piece of the larger cybersecurity puzzle.
Stay tuned for more deep dives into securing your database!
Next on this series
Next time, I will be exploring the world of database encryption, which is more than just turning your data into an undecipherable format, but rather a method to safeguard your data at various levels.
Want to know more? Then subscribe to be notified of new posts.
