{"id":18476,"date":"2021-05-03T09:00:03","date_gmt":"2021-05-03T06:00:03","guid":{"rendered":"http:\/\/dev.profelis.net\/?p=18476"},"modified":"2023-02-01T10:57:59","modified_gmt":"2023-02-01T07:57:59","slug":"postgresql-icin-en-iyi-guvenlik-uygulamalari","status":"publish","type":"post","link":"https:\/\/profelis.com.tr\/en\/2021\/05\/03\/postgresql-icin-en-iyi-guvenlik-uygulamalari\/","title":{"rendered":"PostgreSQL i\u00e7in En \u0130yi G\u00fcvenlik Uygulamalar\u0131"},"content":{"rendered":"<p><strong style=\"color: #000000; font-size: 20px;\">1. Summary<\/strong><br \/>\n<span style=\"color: #000000;\">This white paper provides a framework and a set of recommendations for securing and protecting the PostgreSQL database. We discuss a layered security model that covers physical security, network security, server access control, database access management, and data security. Although all of these features are equally important, this report focuses on the database and data security features specific to PostgreSQL. In our discussion of specific security issues related to the database and the data managed within it, we use a common AAA (Authentication, Authorization, and Accounting) approach for computer and network security.<\/span><\/p>\n<p style=\"color: #000000;\">Most of the recommendations in this white paper apply to PostgreSQL (community edition) and EDB Postgres\u2122 Advanced Server (EDBTM), which is an enterprise-class, feature-rich commercial distribution of PostgreSQL. Advanced Server provides additional security enhancements not found in PostgreSQL in the same way, such as Password Profiles, Auditing, Data Redaction, and SQL Injection Protection.<\/p>\n<p style=\"color: #000000;\">This document has been updated for PostgreSQL 12 and EDB Postgres Advanced Server 12.<\/p>\n<p><b style=\"color: #000000; font-size: 20px;\">2. Introduction<\/b><br \/>\n<span style=\"color: #000000;\">We can think of security as layers and recommend the strategy of granting the least access necessary for any business or role by blocking unnecessary access at the first opportunity.<\/span><br \/>\n<span style=\"color: #000000;\">First, it is to secure physical access to the server<\/span><br \/>\n<span style=\"color: #000000;\">2- The next step is to generally restrict access to your corporate network<\/span><br \/>\n<span style=\"color: #000000;\">3- The next step is to restrict access to the database server.<\/span><br \/>\n<span style=\"color: #000000;\">4- The next step is to restrict access to the database application<\/span><br \/>\n<span style=\"color: #000000;\">5- The next step is to restrict access to the data contained within.<\/span><br \/>\n<span style=\"color: #000000;\">6- The next step is to secure the stored data.<\/span><\/p>\n<p style=\"color: #000000;\">We can think of security as layers and propose a strategy of granting the minimum required access.<\/p>\n<p><b style=\"color: #000000; font-size: 20px;\">General Recommendations<\/b><\/p>\n<ul>\n<li><span style=\"color: #000000;\">Keep your operating system and database up to date. EDB\u2019s support subscriptions provide timely notifications of appropriate security updates and patches for Postgres. There are many tools available that can monitor operating system updates, which can be integrated with package management systems such as yum\/dnf or apt.<\/span><\/li>\n<li><span style=\"color: #000000;\">Do not leave postmaster ports open to the internet unless it is truly critical for your business. Protect this port properly with a firewall; if this is not possible, use a read-only standby database instead of a read-write database on this port. Audited network port forwarding for all connections is another valid option.<\/span><\/li>\n<li><span style=\"color: #000000;\">Isolate the database port from other network traffic using subnetting or other techniques.<\/span><\/li>\n<li><span style=\"color: #000000;\">Grant users the minimum access privileges necessary to do their jobs, no more; strictly reserve the use of privileged user accounts for tasks or roles when absolutely necessary.<\/span><\/li>\n<li><span style=\"color: #000000;\">Restrict system administrators' access to configuration files (postgresql.conf and pg_hba.conf) and log files (pg_log).<\/span><\/li>\n<li><span style=\"color: #000000;\">Deny server login by database superuser roles (postgres in PostgreSQL, enterprisedb in EDB Postgres Advanced Server). Enable authorized user access only when necessary in exceptional circumstances.<\/span><\/li>\n<li><span style=\"color: #000000;\">Give all users their own login credentials; shared access credentials are not a recommended practice and make auditing more complex. As an option, use the edb_audit_tag feature (available only in EDB Postgres Advanced Server) to allow applications to add more auditing information to sessions originating from application-level connections.<\/span><\/li>\n<li><span style=\"color: #000000;\">To prevent unauthorized access to your database, do not rely solely on your frontend application; integrate database security with enterprise-grade authentication and authorization models such as LDAP\/AD or Kerberos.<\/span><br \/>\n<span style=\"color: #000000;\">Keep backups and have a tested recovery plan. No matter how well you secure your system, an intruder can delete or modify your data. Make sure your backups are also securely stored to prevent unauthorized access.<\/span><\/li>\n<\/ul>\n<p style=\"color: #000000;\">It can be useful to think about security in terms of the AAA model developed for network and computer security. AAA stands for Authentication, Authorization, and Auditing.<\/p>\n<ul>\n<li><b style=\"color: #000000;\">Authentication:<\/b><span style=\"color: #000000;\"> Verify that the user is the person they claim to be.<\/span><\/li>\n<li><b style=\"color: #000000;\">Authorization:<\/b><span style=\"color: #000000;\"> Verify that user access is granted.<\/span><\/li>\n<li><b style=\"color: #000000;\">Audit (or accountability):<\/b><span style=\"color: #000000;\"> Log all database activities, including username and time information, to log files.<\/span><\/li>\n<\/ul>\n<p style=\"color: #000000;\">Not all features fit neatly into these categories, but the AAA model provides a useful framework for this technical report.<\/p>\n<p><b style=\"color: #000000; font-size: 20px;\">Implementation of PostgreSQL Security Features to Authentication, Authorization, and Auditing Framework<\/b><\/p>\n<p style=\"color: #000000;\">The following sections provide detailed information on how to incorporate PostgreSQL security features into the Authentication, Authorization, and Accounting (AAA) Framework.<\/p>\n<p style=\"padding-left: 40px;\"><b style=\"color: #000000; font-size: 20px;\">3.1. Authentication<\/b><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong><span style=\"color: #000000;\"> The PG_hba.conf (PostgreSQL host-based access) file restricts access based on username, database, and source IP address (if the user is connecting via TCP\/IP). Authentication methods are also assigned in this file. The authentication method (or methods) you choose depends on your use case.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong><span style=\"color: #000000;\"> Kerberos\/GSSAPI \u2014 PostgreSQL supports GSSAPI, which includes Kerberos authentication according to RFC 1964. GSSAPI provides automatic authentication (single sign-on) for supporting systems. The authentication itself is secure, but the data sent over the database connection is not encrypted unless GSS or SSL encryption is used.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">SSPI \u2014 Use this feature if you are using a Windows system and want to implement Single Sign-On (SSO) authentication.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">LDAP should only be used if Kerberos\u2014involving both SSPI and GSSAPI\u2014is not an option. LDAP is less secure because passwords are transmitted to the LDAP server and can easily be intercepted in an insecure manner.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">LDAP and RADIUS \u2014 LDAP and RADIUS are useful when you have a large number of users and need to manage passwords from a centralized location. The advantage of this centralization is that it keeps your pg_hba.conf file small and manageable, and with centralization, your users can be provided with a \u201cunified password experience\u201d across your infrastructure. Since you will be relying on this service and connection when accessing your database, a robust infrastructure is required for both LDAP and RADIUS.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">RADIUS should not be used as its encryption is weak and it relies on MD5 hashing for credentials.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">Cert \u2014 TLS certificate authentication (sometimes called SSL) can be used for encrypting traffic over the wire and for authentication. Certificates are typically used in machine-to-machine communication.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">md5 \u2014 md5 stores username and password information in the database; it can be a suitable alternative if you have a very small number of users. Since passwords are securely hashed, SCRAM is preferred over md5.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">Scarm \u2014 if you have a very small number of trusted users, you can use scram-sha-256 authentication. Since passwords are securely hashed, SCRAM is preferred over md5.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">Reject (Reddet) \u2014 use this method to reject specific users, connections to specific databases, and\/or specific source IP addresses.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000;\">\u2014<\/strong> <span style=\"font-weight: 400; color: #000000;\">Trust (G\u00fcven) \u2014 since trusted authentication allows a matched client to connect to the server without further authentication, it should only be used in exceptional circumstances.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">You need to know the results of all authentication methods completely. For more detailed research on the ones given above and other authentication methods, you can review the PostgreSQL documentation.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">As stated in the introduction section, access to the pg_hba.conf file must be restricted to system administrators. Try to keep this file properly restricted; larger, more complex files are harder to maintain and are more likely to contain incorrect or outdated entries. Review this file regularly for unnecessary entries.<\/span><\/p>\n<p style=\"padding-left: 40px; font-size: 20px;\"><strong style=\"color: #000000;\">3.2. Word Profiles<\/strong><\/p>\n<p style=\"padding-left: 40px; color: #000000;\">Starting from version 9.5, Advanced Server supports Oracle-compatible password profiles using MD5 or SCRAM authentication. A password profile is a set of password properties that allows the DBA to easily manage a group of roles sharing similar authentication requirements. Each profile can be associated with one or more users. When a user connects to the server, the server applies the profile associated with the login role.<\/p>\n<p style=\"padding-left: 40px; color: #000000;\">For more information, please refer to Section 2.3 \u201cProfile Management\u201d of the EDB\u2019s Database Compatibility for Oracle\u00ae Developer\u2019s Guide.<\/p>\n<p style=\"padding-left: 40px;\"><span style=\"color: #000000;\">Profiles can be used for the following operations:<\/span><span style=\"color: #000000;\"><br \/>\n\u2022 Specifying the allowed number of failed login attempts.<\/span><span style=\"color: #000000;\"><br \/>\nAccount lockout due to excessive failed login attempts.<\/span><span style=\"color: #000000;\"><br \/>\n\u2022 Marking a password to expire.<\/span><span style=\"color: #000000;\"><br \/>\n\u2022 Definition of the grace period after the parole ends.<\/span><span style=\"color: #000000;\"><br \/>\n\u2022 Defining rules for password complexity.<\/span><span style=\"color: #000000;\"><br \/>\n\u2022 Defining rules to limit password reuse.<\/span><\/p>\n<p style=\"padding-left: 40px; font-size: 20px;\"><strong style=\"color: #000000;\">3.3. Authorization<\/strong><\/p>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">Once the user has authenticated properly, you must grant them permission to view data and operate on the database. As previously mentioned, grant only the privileges required for the user to perform the relevant task and do not allow shared (group) login credentials. Manage users and groups in PostgreSQL through role assignments. A role can represent an individual user or a group of users. In Postgres, roles are created at the cluster (or database server) level. This means that roles apply to all databases defined for the cluster\/database server, and it is crucial to limit role permissions appropriately. Permissions can be applied to database objects (tables, views, functions, etc.), rows within tables, and data reduction policies.<\/span><\/p>\n<p style=\"padding-left: 80px; font-size: 20px;\"><strong style=\"color: #000000;\">3.3.1 \u2013 Access to database objects<\/strong><\/p>\n<p style=\"padding-left: 80px;\"><span style=\"font-weight: 400; color: #000000;\">The privileges and warnings assigned are summarized in the PostgreSQL CREATE ROLE documentation:<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400; color: #000000;\">Revoke CREATE privileges from all users and grant them back only to trusted users.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400; color: #000000;\">Disallow the use of functions or triggers written in untrusted procedural languages.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400; color: #000000;\">SECURITY DEFINER functions allow users to run functions with a controlled high privilege level, but an accidentally carelessly written function can reduce security. Check the documentation for more details (Writing Security Definer Functions Safely section of CREATE FUNCTION).<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400; color: #000000;\">Database objects should be owned not by a role that any application user can connect to, but ideally by a secure role with very restricted access to the database (e.g., only from a Unix Domain socket). Thus, the chance of an attacker modifying or dropping objects is minimized. While this is preferred from a security standpoint, it can create issues with application frameworks that manage the schema themselves; such functionalities must be implemented carefully.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p style=\"padding-left: 80px; color: #000000;\">Note that when Log_Statement is set to \u2018ddl\u2019 or higher, changing a role's password via the ALTER ROLE command will result in the password being exposed in the logs, except in EDB Postgres Advanced Server 11 and later, where edb_filter_log.redact_password_command instructs the server to redact passwords from the log file. For more information <span style=\"color: #eb1c24;\"><a style=\"color: #eb1c24;\" href=\"https:\/\/www.enterprisedb.com\/edb-docs\/d\/edb-postgres-advanced-server\/installation-getting-started\/release-notes\/11\/EPAS_Release_Notes.1.4.html\" target=\"_blank\" rel=\"noopener noreferrer\">Click.<\/a><\/span><\/p>\n<p style=\"padding-left: 80px; color: #000000;\">When authentication credentials (e.g., usernames and passwords) are kept in a table, the use of statement logging can expose this information even if the table is specifically secure. Similarly, if sensitive information is used in queries (for example, any personally identifiable information as a key), these parameters can be exposed through statement logs.<\/p>\n<p style=\"padding-left: 80px; font-size: 20px;\"><strong style=\"color: #000000;\">3.3.2 \u2013 Views<\/strong><\/p>\n<p style=\"padding-left: 80px;\"><span style=\"font-weight: 400;\"><span style=\"color: #000000;\">Access to database objects, which are views, can be controlled as explained above. By creating a VIEW on a table and restricting the permissions for this VIEW, it is possible to limit data visibility to specific user groups. Since it is thought that extra precautions must be taken to avoid potential security issues as described by Robert Haas, PostgreSQL versions 9.2 and later offer the CREATE VIEW WITH (security_barrier) option.<\/span> <\/span><\/p>\n<p style=\"padding-left: 80px; font-size: 20px;\"><strong style=\"color: #000000;\">3.3.3 \u2013 Row-Level Security<\/strong><\/p>\n<p style=\"padding-left: 80px;\"><span style=\"font-weight: 400; color: #000000;\">PostgreSQL introduced Row-Level Security (RLS) in version 9.5. RLS provides highly granular access to table rows depending on the user role. This includes SELECT, UPDATE, DELETE, and INSERT operations. For more information <a style=\"color: #eb1c24;\" href=\"https:\/\/www.postgresql.org\/docs\/current\/ddl-rowsecurity.html\" target=\"_blank\" rel=\"noopener noreferrer\">You can find it here.<\/a><\/span><\/p>\n<p style=\"padding-left: 80px; color: #000000;\">EDB Postgres Advanced Server includes Oracle-compatible implementations of ADD_POLICY, DROP_POLICY, and UPDATE_POLICY in the DBMS_RLS package. For more information <a style=\"color: #eb1c24;\" href=\"https:\/\/www.enterprisedb.com\/edb-docs\/d\/edb-postgres-advanced-server\/user-guides\/database-compatibility-for-oracle-developers-built-in-package-guide\/12\/Database_Compatibility_for_Oracle_Developers_Built-in_Package_Guide.1.31.html\" target=\"_blank\" rel=\"noopener\">Click.<\/a><\/p>\n<p style=\"padding-left: 80px; font-size: 20px;\"><strong style=\"color: #000000;\">3.3.4 \u2013 Data Minimization<\/strong><\/p>\n<p style=\"padding-left: 80px;\"><span style=\"font-weight: 400; color: #000000;\">Data reduction \u2013 the ability to mask certain data elements or selectively hide data for specific user groups \u2013 is another technique used to manage data access. EDB Postgres Advanced Server introduced the data reduction feature in version 11.<\/span><\/p>\n<p style=\"padding-left: 80px; color: #000000;\">Data masking is a policy-based tool that works with PostgreSQL roles to grant or revoke read access to specific data elements. For example, while a group of users sees social security numbers as XXX-XX-1235, members with the data manager role see all the details. Additional information about data masking can be found here.<\/p>\n<div class=\"table-1\" style=\"padding-left: 80px;\">\n<table width=\"100%\">\n<thead>\n<tr>\n<th align=\"left\"><span style=\"color: #000000;\">Fixed<\/span><\/th>\n<th align=\"left\"><span style=\"color: #000000;\">Tip<\/span><\/th>\n<th align=\"left\"><span style=\"color: #000000;\">Value<\/span><\/th>\n<th align=\"left\"><span style=\"color: #000000;\">Description<\/span><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td align=\"left\"><span style=\"color: #000000;\">NONE<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">INTEGER<\/span><\/td>\n<td align=\"left\"><span style=\"color: #000000;\">0<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">No mitigation, zero impact on the query result against the table.<\/span><\/td>\n<\/tr>\n<tr>\n<td align=\"left\"><span style=\"color: #000000;\">FULL<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">INTEGER<\/span><\/td>\n<td align=\"left\"><span style=\"color: #000000;\">1<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">Full reduction, all values of the column data are reduced.<\/span><\/td>\n<\/tr>\n<tr>\n<td align=\"left\"><span style=\"color: #000000;\">PARTIAL<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">INTEGER<\/span><\/td>\n<td align=\"left\"><span style=\"color: #000000;\">2<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">Partial reduction, a portion of the column data is reduced.<\/span><\/td>\n<\/tr>\n<tr>\n<td align=\"left\"><span style=\"color: #000000;\">ALEATORIO<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">INTEGER<\/span><\/td>\n<td align=\"left\"><span style=\"color: #000000;\">4<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">Random reduction results in a different random value depending on the data type of each query column.<\/span><\/td>\n<\/tr>\n<tr>\n<td align=\"left\"><span style=\"color: #000000;\">Regular expression<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">INTEGER<\/span><\/td>\n<td align=\"left\"><span style=\"color: #000000;\">5<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">Regular expression-based reduction searches for the data template to be reduced.<\/span><\/td>\n<\/tr>\n<tr>\n<td align=\"left\"><span style=\"color: #000000;\">Custom<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">INTEGER<\/span><\/td>\n<td align=\"left\"><span style=\"color: #000000;\">99<\/span><\/td>\n<td align=\"left\"><span style=\"font-weight: 400; color: #000000;\">Custom reduction type.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<div><\/div>\n<div class=\"table-1\" style=\"padding-left: 80px;\"><strong><span style=\"font-family: NonBreakingSpaceOverride, 'Hoefler Text', Garamond, 'Times New Roman', serif; font-size: revert; letter-spacing: normal;\">3.13.1 DBMS_REDACT Constants and Function Parameters are used<\/span><\/strong><\/div>\n<p style=\"padding-left: 40px; font-size: 20px;\"><strong style=\"color: #000000;\">3.4 Audit<\/strong><\/p>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">Advanced Server provides the ability to generate audit reports. Database auditing allows database administrators, auditors, and operators to track and analyze database activities to support complex auditing requirements. These audited activities consist of database access and usage, along with data creation, modification, or deletion. The auditing system relies on configuration parameters defined in the configuration file.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><strong style=\"color: #000000; font-size: 20px;\">We recommend increasing the audit level to audit the following:<\/strong><br \/>\n<span style=\"color: #000000;\">\u2022 User connections<\/span><br \/>\n<span style=\"color: #000000;\">\u2022 DDL changes<\/span><br \/>\n<span style=\"color: #000000;\">\u2022 Data changes<\/span><br \/>\n<span style=\"color: #000000;\">\u2022 Data views<\/span><\/p>\n<p style=\"padding-left: 40px; color: #000000;\">Very detailed auditing levels can result in a large number of logs; only collect the level of logs you need. With Postgres, you can set log levels for each user and each database. Frequently review your audit logs for abnormal activity. Establish a chain of custody for your logs.<\/p>\n<p style=\"padding-left: 40px; color: #000000;\">Please note that a high logging level can cause passwords to appear in the logs along with their storage in the database. EDB Postgres Advanced Server introduced the edb_filter_log.redact_password_commands extension in version 11 to instruct the server to redact stored passwords from the audit log file.<\/p>\n<p style=\"padding-left: 40px; color: #000000;\">Advanced Server enables database and security administrators, auditors, and operators to monitor and analyze database activities using the EDB Audit Logging feature.<\/p>\n<p style=\"padding-left: 40px; font-size: 20px;\"><b style=\"color: #000000;\">3.5 Data Encryption<\/b><\/p>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">PostgreSQL offers various levels of encryption, providing flexibility in preventing data exposure due to database server theft, unethical administrators, and insecure networks:<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li><span style=\"font-weight: 400; color: #000000;\"> User connections<\/span><\/li>\n<li><span style=\"font-weight: 400; color: #000000;\"> DDL changes<\/span><\/li>\n<li><span style=\"font-weight: 400; color: #000000;\"> Data changes<\/span><\/li>\n<li><span style=\"font-weight: 400; color: #000000;\"> Data visualizations<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">More information about these options can be found in the PostgreSQL documentation.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">If you are concerned about data interception during transfer between a client and a database, enable SSL in the postgresql.conf file unless you are sure that data interception does not pose a risk. SSL encryption can add overhead and certificate management can be difficult, but in general, this is a best practice.<\/span><\/p>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">You can also encrypt data either within the database or at the file system level (using one or the other). You can find more information about Transparent Data Encryption on EDB's blog. With this encryption option, data is decrypted as soon as it is read from the file system, allowing database administrators to view the data; therefore, roles and privileges must be locked down. Other options include the use of Thales Vormetric Transparent Encryption (VTE).<\/span><\/p>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">Use the pgcrypto contrib module to encrypt data per column. This method has several drawbacks:<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li><span style=\"font-weight: 400; color: #000000;\"> There is a potential performance bottleneck depending on the size of the table.<\/span><\/li>\n<li><span style=\"font-weight: 400; color: #000000;\"> Encrypted fields cannot be searched or indexed.<\/span><\/li>\n<li><span style=\"font-weight: 400; color: #000000;\"> Encryption must be applied during table creation and also requires advance planning.<\/span><\/li>\n<li><span style=\"font-weight: 400; color: #000000;\"> Encryption key management can also be complex.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p style=\"padding-left: 40px;\"><span style=\"font-weight: 400; color: #000000;\">In addition to this, your application needs to perform the encryption\/decryption process, so that every change in the database remains encrypted to prevent an unethical database administrator from viewing the data.<\/span><\/p>\n<p style=\"padding-left: 40px; font-size: 20px;\"><b style=\"color: #000000;\">3.6 SQL Injection Attacks<\/b><\/p>\n<p style=\"padding-left: 40px; color: #000000;\">An SQL injection attack is an attempt to compromise a database by executing SQL commands that provide the attacker with clues about the database's content, structure, or security. Preventing an SQL injection attack is normally the responsibility of the application developer. Database administrators usually have very little or no control over the potential threat.<\/p>\n<p style=\"padding-left: 40px; color: #000000;\">PostgreSQL uses parameterized queries as the standard method to prevent SQL injection attacks. If you are using EDB Postgres Advanced Server, we recommend using the SQL\/Protect module to protect against SQL injection attacks. SQL\/Protect provides an additional layer of security to normal database security policies by inspecting incoming queries against general SQL profiles. SQL\/Protect returns control to the database administrator by alerting them to potentially dangerous queries and blocking those queries. For more information<a style=\"color: #eb1c24;\" href=\"https:\/\/www.enterprisedb.com\/edb-docs\/d\/edb-postgres-advanced-server\/user-guides\/user-guide\/12\/EDB_Postgres_Advanced_Server_Guide.1.50.html#pID0E0BDD0HA\" target=\"_blank\" rel=\"noopener noreferrer\">\u00a0Click.<\/a><\/p>\n<p>Source: <a href=\"https:\/\/info.enterprisedb.com\/white-paper_security-best-practices-postgresql.html?_ga=2.62855532.1950402146.1620022971-1640779360.1612185529\" target=\"_blank\" rel=\"noopener\">Security Best Practices for PostgreSQL<\/a><br \/>\n\u00a9 Copyright EnterpriseDB Corporation 2020 EnterpriseDB Corporation<br \/>\n34 Crosby Drive Suite 201 Bedford, MA 01730<br \/>\nEnterpriseDB and Postgres Enterprise Manager are registered trademarks of EnterpriseDB Corporation. EDB and EDB Postgres are trademarks of EnterpriseDB Corporation. Oracle is a registered trademark of Oracle, Inc. Other trademarks may be trademarks of their respective owners.<\/p>","protected":false},"excerpt":{"rendered":"<p>1. \u00d6zet Bu teknik inceleme raporu, PostgreSQL veritaban\u0131n\u0131 g\u00fcvenli\u011fini sa\u011flamak ve korumak i\u00e7in bir \u00e7er\u00e7eve ve bir dizi \u00f6neri sunmaktad\u0131r. Fiziksel g\u00fcvenli\u011fi, a\u011f g\u00fcvenli\u011fini, sunucu eri\u015fim kontrol\u00fcn\u00fc, veritaban\u0131 eri\u015fim y\u00f6netimini ve veri g\u00fcvenli\u011fini ele alan katmanl\u0131 bir g\u00fcvenlik modelinden s\u00f6z&#8230;<\/p>","protected":false},"author":16,"featured_media":22104,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,45],"tags":[58,48,50],"class_list":["post-18476","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","category-yazi","tag-acikkaynak","tag-enterprisedb","tag-veritabani"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/18476","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/comments?post=18476"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/18476\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media\/22104"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=18476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=18476"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=18476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}