\nLet us look at the important components around the same:<\/p>\n
- \n
- Host-based authentication (HBA) Authentication, privilege of a role\/group\/user (Authorization)<\/li>\n
- Different Levels of Security<\/li>\n
- pg_hba.conf file supports multiple methods of authentication<\/li>\n
- Password authentication<\/li>\n
- Ident authentication<\/li>\n
- Trust authentication<\/li>\n
- PAM authentication<\/li>\n
- LDAP authentication<\/li>\n
- SSPI authentication<\/li>\n
- GSSAPI authentication<\/li>\n
- Certificate authentication<\/li>\n
- Peer authentication<\/li>\n
- RADIUS authentication<\/li>\n
- BSD authentication<\/li>\n
- Row Level Security (RLS)<\/li>\n
- Security Logging for RDS \/Aurora PostgreSQL<\/li>\n<\/ul>\n
\n I. Default Logging<\/p>\nII. DB Parameter Group<\/p>\n
III. Object-based Query Logging with the pgaudit extension<\/p>\n
IV. Publishing PostgreSQL Logs to CloudWatch Logs<\/p>\n
- \n
- Object Ownership<\/li>\n
- Application Access Parameters<\/li>\n
- Protecting Against Injection Attacks with SQL\/Protect<\/li>\n
- Source Code Protection for Functions
\n\t <\/li>\n<\/ul>\nAuthentication and Authorization<\/strong><\/p>\n
Secure access is a two-stage (AA) process, and it ensures that only authorized users are logging into the Database with the assigned privileges.<\/p>\n
- \n
- Authentication: <\/strong>It ensures that a user is who he or she claims to.<\/li>\n
- Authorization: <\/strong>It allows the user access to DB resources based on the user\u2019s identity and the user has been granted the appropriate privileges.<\/li>\n<\/ul>\n
Different Levels of Security<\/strong><\/p>\n
The Postgres level of security is based on Network-level, Transport Level and Database level<\/strong><\/p>\n
- \n
- Server and Application<\/li>\n<\/ul>\n
Check Client IP\/Authentication mode
\n <\/p>\nPg_hba.conf<\/em><\/p>\n
- \n
- Database <\/li>\n<\/ul>\n
Role\/User\/Password
\nConnect Privilege
\nSchema Permission (usage, CRUD)
\n <\/p>\n- \n
- Object <\/li>\n<\/ul>\n
Table Level Privilege\/execute privilege on the function
\nGrant\/Revoke<\/p>\npg_hba.conf \u2013 Access Control<\/strong><\/p>\n
- \n
- Host-based access control file (pg_hba.conf)<\/li>\n
- Pg_hba.conf file is located in Cluster data directory<\/li>\n
- Read at startup, any change requires to reload(using pg_reload_conf())<\/li>\n
- Each record specify connection type, database name, user name , client IP and method of authentication(LDAP,TRUST,PAM)<\/li>\n
- Top to bottom reading of a pg_hba file<\/li>\n
- Hostnames, IPv6 and IPv4 supported with CIDR<\/li>\n
- Authentication methods \u2013 password, trust, reject, md5, sspi , gss, , krb5,ident, peer, pam, ldap, radius or cert etc.<\/li>\n<\/ul>\n
Row Level Security (RLS)<\/strong><\/p>\n
RLS enables you to use group membership or execution context for controlling access to the rows in a database Table.<\/strong><\/p>\n
- \n
- GRANT and REVOKE can be used at table level<\/li>\n
- PostgreSQL >9.5 supports security policies for limiting access at row level<\/li>\n
- RLS offers stronger security as it is enforced by the database<\/li>\n
- Security policies are controlled by DBA not by the application team<\/li>\n
- All rows of a table are visible by default<\/li>\n
- Once RLS is enabled on a table, all the queries must go through the security policy<\/li>\n<\/ul>\n
Example:<\/p>\n
To enable row-level security for the table:<\/p>\n
- \n
- Create the table first and enable RLS on table<\/li>\n<\/ul>\n
\n ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;<\/em><\/p>\n- \n
- 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.<\/li>\n<\/ul>\n
CREATE POLICY policy_name ON table_name TO role_name USING(role = Current_user);<\/em>
\n
\nNote: To allow all users to view their own row in a user table, a simple policy can be used:<\/p>\nCREATE POLICY usr_policy ON users USING (user = current_user);<\/em><\/p>\n
Security Logging for RDS \/Aurora PostgreSQL<\/strong><\/p>\n
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. <\/p>\n
Default Logging<\/strong><\/p>\n
![\"postgres](\"\/wp-content\/uploads\/2026\/blog_article_images\/26250-body-Picture1_13.png\")
<\/p>\nDB Parameter Group<\/strong><\/p>\n
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.<\/p>\n
![\"postgres](\"\/wp-content\/uploads\/2026\/blog_article_images\/26250-body-Picture3_14.png\")
<\/p>\n![\"postgres](\"\/wp-content\/uploads\/2026\/blog_article_images\/26250-body-Picture4_0.PNG\")
<\/p>\nObject based query Logging with pgaudit extension<\/strong><\/p>\n
The following summaries show how to enable with pgaudit and provides, and example of configuring object level query auditing and table mytable.<\/p>\n
Create specific database role called rds_pgaudit<\/strong><\/p>\n
CREATE ROLE rds_pgaudit;<\/em><\/p>\n
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)<\/p>\n
Pgaudit.role=rds_pgaudit
\nShared_preload_libraries=pgaudit<\/em><\/p>\nRun the following command to create the pgaudit extension<\/strong><\/p>\n
CREATE EXTENSION pgaudit;<\/em><\/p>\n
To test the audit logging,run several commands that you have chosen to audit<\/strong><\/p>\n
CREATE TABLE mytable(id int);<\/em>
\nGRANT SELECT ON mytable to rds_pgaudit;<\/em><\/p>\nPublishing PostgreSQL Logs to CloudWatch Logs.<\/strong><\/p>\n
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.<\/p>\n
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
\nPostgreSQL log
\nUpgrade log (not available for Aurora PostgeSQL)<\/p>\n\nTo publish PostgreSQL logs to CloudWatch logs using the console:<\/strong><\/p>\n
- \n
- Open the RDS console<\/li>\n
- Choose the DB instance and select modify<\/li>\n
- In the log exports section, choose the logs to start publishing to CloudWatch logs.<\/li>\n
- Choose continue, and then choose to modify DB instance on the summary page.<\/li>\n
- Select the log types to publish to Amazon Cloudwath Logs, PostgreSQL and Upgrade log<\/li>\n<\/ul>\n
We can publish PostgreSQL logs with the AWS CLI. The following command will modify an existing DB instance:<\/strong><\/p>\n
AWS rds modify-db-instance \u2013db-instance-identifier mydbinstance –cloudwatch-logs-export-configuration \u2018{\u201cEnableLogTypes\u201d:[\u201cpostgresql\u201d,\u201dupgrade\u201d]}\u2019<\/em><\/p>\n
\nSimilar AWS CLI commands are available for publishing the logs to CloudWatch Logs when creating an instance or restoring an instance from a snapshot.<\/p>\n
Application Access<\/strong><\/p>\n
– Application\/user access is controlled by settings in both (postgresql.conf ,pg_hba.conf)<\/strong>
\n– Set the following parameters in postgresql.conf:<\/strong><\/p>\n- \n
- unix_socket_directory<\/li>\n
- unix_socket_group<\/li>\n
- unix_socket_permissions<\/li>\n
- listen_addresses<\/li>\n
- max_connections<\/li>\n
- superuser_reserved_connections<\/li>\n
- port<\/li>\n<\/ul>\n
SQL Level Protect<\/strong><\/p>\n
- \n
- To Protects Postgres databases from SQL injection attacks<\/li>\n
- SQL Protect add a security layer managed by DBA<\/li>\n
- We can add-on module delivered through Stack Builder<\/li>\n<\/ul>\n
EDB*Wrap<\/em><\/strong><\/p>\n
EDB*Wrap utility converts the source code of database functions<\/p>\n
- \n
- Everything is wrapped in obfuscated form like Oracle wrap capability<\/li>\n<\/ul>\n
\nConclusion:<\/strong><\/p>\n
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.
\nWe have seen the best practices for securing access and adding entry and select authentication methods.<\/p>\nHopefully, this overview helps review the security of PostgreSQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
Security on PostgreSQL and RDS<\/p>\n","protected":false},"author":2,"featured_media":7088,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26250","content_type":"blog","created_by":"10130","authored_on":"2023-11-16T18:18:12+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Amit Singh, Postgres Database Administrator, Rackspace Technology","seo_description":"Explore PostgreSQL and AWS Aurora security setup and best practices in this blog. Learn essential procedures and policies for safeguarding your data.","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26250-insights_image-security-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Big Data","product":"","publisheddate":"2022-10-22T17:18:13","rxt_solutions":"Data, Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Security on PostgreSQL and RDS | Rackspace Technology","field_topics":"","field_slug":"security-on-postgresql-and-rds","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7086","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7086","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7086"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7086\/revisions"}],"predecessor-version":[{"id":8940,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7086\/revisions\/8940"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7088"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7086"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7086"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7086"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Everything is wrapped in obfuscated form like Oracle wrap capability<\/li>\n<\/ul>\n
- 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.<\/li>\n<\/ul>\n
- Create the table first and enable RLS on table<\/li>\n<\/ul>\n
- Object <\/li>\n<\/ul>\n
- Database <\/li>\n<\/ul>\n
- Authorization: <\/strong>It allows the user access to DB resources based on the user\u2019s identity and the user has been granted the appropriate privileges.<\/li>\n<\/ul>\n
- Authentication: <\/strong>It ensures that a user is who he or she claims to.<\/li>\n