Security on PostgreSQL and RDS

This blog describes how to set up the security in PostgreSQL and AWS Aurora and related best practices. The Database security refers to a set of procedures and policies that safeguard data. It becomes crucial to understand its significance.

Important Components
Let us look at the important components around the same:

• Host-based authentication (HBA) Authentication, privilege of a role/group/user (Authorization)
• Different Levels of Security
• pg_hba.conf file supports multiple methods of authentication
• Password authentication
• Ident authentication
• Trust authentication
• PAM authentication
• LDAP authentication
• SSPI authentication
• GSSAPI authentication
• Certificate authentication
• Peer authentication
• RADIUS authentication
• BSD authentication
• Row Level Security (RLS)
• Security Logging for RDS /Aurora PostgreSQL

    
 I.    Default Logging

II.    DB Parameter Group

III.    Object-based Query Logging with the pgaudit extension

IV.    Publishing PostgreSQL Logs to CloudWatch Logs

• Object Ownership
• Application Access Parameters
• Protecting Against Injection Attacks with SQL/Protect
• Source Code Protection for Functions
   

Authentication and Authorization

Secure access is a two-stage (AA) process, and it ensures that only authorized users are logging into the Database with the assigned privileges.

• Authentication: It ensures that a user is who he or she claims to.
• Authorization: It allows the user access to DB resources based on the user’s identity and the user has been granted the appropriate privileges.

Different Levels of Security

The Postgres level of security is based on Network-level, Transport Level and Database level

• Server and Application

     Check Client IP/Authentication mode
     

      Pg_hba.conf

• Database 

Role/User/Password
Connect Privilege
Schema Permission (usage, CRUD)
       

• Object 

Table Level Privilege/execute privilege on the function 
Grant/Revoke

pg_hba.conf – Access Control

• Host-based access control file (pg_hba.conf)
• Pg_hba.conf file is located in Cluster data directory
• Read at startup, any change requires to reload(using pg_reload_conf())
• Each record specify connection type, database name, user name , client IP and method of authentication(LDAP,TRUST,PAM)
• Top to bottom reading of a pg_hba file
• Hostnames, IPv6 and IPv4 supported with CIDR
• Authentication methods – password, trust, reject, md5, sspi , gss, , krb5,ident, peer, pam, ldap, radius or cert etc.

Row Level Security (RLS)

RLS enables you to use group membership or execution context for controlling access to the rows in a database Table.

• GRANT and REVOKE can be used at table level
• PostgreSQL >9.5 supports security policies for limiting access at row level
• RLS offers stronger security as it is enforced by the database
• Security policies are controlled by DBA not by the application team
• All rows of a table are visible by default
• Once RLS is enabled on a table, all the queries must go through the security policy

Example:

To enable row-level security for the table:

• Create the table first and enable RLS on table

   
      ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

• To create a policy on the table to allow the role to view the rows of their table, the CREATE POLICY command can be used.

  CREATE POLICY policy_name ON table_name TO role_name USING(role = Current_user);
      
Note:  To allow all users to view their own row in a user table, a simple policy can be used:

CREATE POLICY usr_policy ON users USING (user = current_user);

Security Logging for RDS /Aurora PostgreSQL

PostgreSQL generates event logs that contain useful information for DBAs like SQL query issues, failed login, and dead locks are captured in the database by default. However, from a security perspective compliance and security groups are also interested in capturing addition database activity. For instance, successful logins and user queries for security monitoring and audit purposes. 

Default Logging

DB Parameter Group

Each RDS and Aurora PostgreSQL instances are associated with a parameter group that contains engine specific settings. The engine configurations also include several parameters that control the PostgreSQL logging behavior. AWS provides the parameter groups default configuration settings to use for your instance. To enable additional options the default settings for the parameters given in the following table must be changed to the recommended value.

Object based query Logging with pgaudit extension

The following summaries show how to enable with pgaudit and provides, and example of configuring object level query auditing and table mytable.

Create specific database role called rds_pgaudit

CREATE ROLE rds_pgaudit;

Modify the parameter group that is associated with your DB instance to use the shared preload libraries that contain pgaudit.role. (it needs to restart the DB instance)

Pgaudit.role=rds_pgaudit
Shared_preload_libraries=pgaudit

Run the following command to create the pgaudit extension

CREATE EXTENSION pgaudit;

To test the audit logging,run several commands that you have chosen to audit

CREATE TABLE mytable(id int);
GRANT SELECT ON mytable to rds_pgaudit;

Publishing PostgreSQL Logs to CloudWatch Logs.

By default, PostgreSQL log records are stored in a DB instance for three days and a maximum of seven days. With CloudWatch logs, you can store PostgreSQL log records for much longer, and also perform real-time analysis of log data and use CloudWatch to view metrics and create alarms.

To work with the CloudWatch logs, configure RDS for PostgreSQL instance to publish log data to a log group. You can Publish the following log types to CloudWatch logs for PostgreSQL
PostgreSQL log
Upgrade log (not available for Aurora PostgeSQL)

To publish PostgreSQL logs to CloudWatch logs using the console:

• Open the RDS console
• Choose the DB instance and select modify
• In the log exports section, choose the logs to start publishing to CloudWatch logs.
• Choose continue, and then choose to modify DB instance on the summary page.
• Select the log types to publish to Amazon Cloudwath Logs, PostgreSQL and Upgrade log

We can publish PostgreSQL logs with the AWS CLI. The following command will modify an existing DB instance:

AWS rds modify-db-instance –db-instance-identifier mydbinstance  --cloudwatch-logs-export-configuration ‘{“EnableLogTypes”:[“postgresql”,”upgrade”]}’

Similar AWS CLI commands are available for publishing the logs to CloudWatch Logs when creating an instance or restoring an instance from a snapshot.

Application Access

- Application/user access is controlled by settings in both (postgresql.conf ,pg_hba.conf)
- Set the following parameters in postgresql.conf:

• unix_socket_directory
• unix_socket_group
• unix_socket_permissions
• listen_addresses
• max_connections
• superuser_reserved_connections
• port

SQL Level Protect

• To Protects Postgres databases from SQL injection attacks
• SQL Protect add a security layer managed by DBA
• We can add-on module delivered through Stack Builder

EDB*Wrap

EDB*Wrap utility converts the source code of database functions

• Everything is wrapped in obfuscated form like Oracle wrap capability

Conclusion:

In this document, we have looked at the security of PostgreSQL/RDS/Aurora Postgres implementation and the factors related to server access, database access, database object access and row-level access.
We have seen the best practices for securing access and adding entry and select authentication methods.

Hopefully, this overview helps review the security of PostgreSQL.