Overall security of PostgreSQL database, the best replacement for Oracle

Hackers often attack enterprise databases in newspapers. The days when most attacks were initiated by rebellious minors are gone forever. Today, data integration

Hackers often attack enterprise databases in newspapers. The days when most attacks were initiated by rebellious minors are gone forever. Today, data integration

Database security is the biggest challenge for Web-based applications today. If you do not control it, you will be exposed to the company's sensitive information. Even worse, valuable customer information will also be exposed. This article describes the security measures that can be used to protect your PostgreSQL database.

Introduction

Hackers often attack enterprise databases in newspapers. The days when most attacks were initiated by rebellious minors are gone forever. Today, data collection and integration is an important undertaking and is exclusively undertaken by experts working in Enterprise infrastructure. The problem is no longer how you stop unauthorized access attempts-you cannot stop-but how you reduce the impact when this happens.

This article discusses the challenges to protect your PostgreSQL (also known as Postgres) database server. PostgreSQL is a powerful open-source object-relational database system. It has a reliable architecture and is known for its reliability, data integrity, and accuracy. It runs on all mainstream operating systems, including Linux? , UNIX? And Windows ?. It is fully compatible with ACID and fully supports foreign keys, connections, views, triggers, and stored procedures (in multiple languages ).

Ideal Administrator

In traditional UNIX, PostgreSQL is redesigned to supplement the operating system it is attached. To maximize the value of PostgreSQL, the required knowledge exceeds the skills required by Common Database Administrators (DBAs.

To put it simply, a qualified PostgreSQL DBA must have the following background:

? Understand the relationship theory and be familiar with SQL '92, '99, and 2003.

? Understand how to read the source code, especially the C code, and compile the source code on Linux.

? Be able to manage the system and be familiar with system-v unix or Linux.

? Ability to maintain (if needed) various typical hardware projects in the IT organization. Understanding the tcp OS layer, dividing the network into subnets, tuning the firewall, and so on.

Many DBAs only have the skills to manage, monitor, and optimize databases. However, PostgreSQL also considered the OS tool during its construction. Of course, few DBAs are proficient in all disciplines, but these knowledge enables PostgreSQL DBAs to do more work in less time, which cannot be done in other ways.

Access permission Review

If you want to know the possible attack media (attack vector), it is very important to understand the role of the database. First, you must grant and revoke permissions to control data access.

Roles, permissions, and privileges

What is the security of a common role with default permissions and privileges? You can use one of the following commands to create a user account:

? SQL statement CREATE USER

? SQL statement CREATE ROLE

? Postgres command line utility createuser

The three methods for creating user accounts show different behaviors and lead to different default permissions and privileges.

A typical user can perform the following operations for a common role:

? If the data cluster uses the default authentication policy described in pg_cmd.conf, you can access any database.

? Create an object in the PUBLIC mode of any database that you can access.

? Create a session (temporary) object in a temporary session, such as the pattern pg_temp _?

? Modify runtime parameters.

? Create User-Defined Functions

? Execute user-defined functions created by other users in PUBLIC mode (as long as the objects are accessed by the user.

You must be clear about the permissions of users. However, it is equally important to understand what common users cannot do by default. Normal users are not authorized to perform the following operations:

? Create a database or mode.

? Create other users.

? Access objects created by other users.

? Log On (only applicable to the create role Statement ).

Super User Permissions and privileges

Although normal users do not have the permission and privilege to execute functions defined as Super Users, common users still cause problems related to default permissions and privileges.

This article will discuss some attack media that can be operated by common users.

Access Object

A very common and insecure practice occurs when PostgreSQL is used as the backend of the Web server. The purpose of a developer to create a common user is to use the INSERT, UPDATE, and DELETE commands to execute data operation commands. However, unauthorized operations may also be executed because the PUBLIC mode is PUBLIC to everyone. For example, you can perform data mining on these tables. You can even modify a table: add rules and triggers, save data to a table in PUBLIC mode, and then the data will be collected.

Remember, a stolen user account can do anything to the objects it owns.