Connection Management Professional

In this chapter, the relationship between connections and their respective user as well as security management in general are covered.

As with most client/server systems, a connection needs to be established with the server prior to sending commands. Furthermore, a database context must be provided for the connection. As outlined in the previous chapter, this is done by specifying the database as a connection attribute. In addition, a user is required.

Until now, only the user "postgres" had been used in the examples. However, this user plays a special role and actually should not be used in real-world scenarios. Hence, the first step to move forward in using the PostgreSQL system is to develop a deeper understanding of the security and user management provided.

In order to use the PostgreSQL server, it is required to first establish a connection to the server, which later needs to be disconnected when the application closes. Connections are established using the DacSession() class. An example for connecting to the mdidemo database which was upsized in the "Upsizing MDIDEMO" chapter is shown below:

cConnStr := "DBE=pgdbe;SERVER=localhost;" 
cConnStr += "DB=mdidemo;UID=postgres;PWD=postgres" 

oSession := DacSession():New(cConnStr) 
IF(!oSession:IsConnected()) 
  MsgBox("Connection failed ("+Var2Char(oSession:GetLastMessage())+")") 
  QUIT 
ENDIF 

Note that the PGDBE DatabaseEngine must already be loaded for this code to work correctly.

To assemble a connection string, we need to know the server name as well as the name of the database that we want to use. Finally, a proper username (UID) and password (PWD) is required in order to get access to the server. See the DacSession() class documentation for more details.

PostgreSQL-specific connection attributes

SQL-only connections

By default, the PostgreSQL Database Engine connects to the DBMS with full ISAM-emulation-support. This way providing maximum compatiblity with your existing code. Since this type of connection creates additional overhead and enforces the availability of required ISAM meta tables, the DBE also supports SQL-only connections by disabling the ISAM emulation. Add the NO-ISAM token to the connection string to enable this mode. When using NO-ISAM in your connection string as shown below, the connection is limited to SQL-only operations. In this case, even though result sets are available via a dedicated workarea, these workareas are read-only. Also, operations such as DbUseArea() or DbCreate() will raise a runtime error when used with SQL-only connections.

Note that it is legal to have multiple connections open to the same server, some with the ISAM-emulation-support and others in SQL-only mode.

oSession := DacSession():New("DBE=pgdbe;SERVER=localhost;DB=mdidemo;UID=postgres;PWD=postgres;NO-ISAM") 

PostgreSQL-proprietary connection attributes

The PostgreSQL server provides various proprietary connection attributes like PORT or SSLMODE. See the PostgreSQL documentation for more details. The PostgreSQL Database Engine transparently forwards all "unknown" connection attributes to the server. Consequently, the connection string to connect to a PostgreSQL server installed at port 4005 does look like this:

oSession := DacSession():New("DBE=pgdbe;SERVER=localhost;DB=mdidemo;UID=postgres;PWD=postgres;PORT=4005") 

Using an application-specific user for a PostgreSQL client application is considered best practice for several reasons:

Security:

Limited Permissions: An application-specific user can be granted only the necessary permissions, reducing the risk of accidental or malicious changes to the database.

Credential Management: Centralized management of a single set of credentials is simpler and more secure than managing multiple end-user credentials.

Consistency:

Stable Access Control: Using a single application-specific user ensures consistent access policies across all users of the application.

Simplified Auditing: Tracking and auditing database access is easier with one application user rather than multiple end-user accounts from an application context. Auditing generally shall be delegated to the application level as only then the operative context is known.

Performance:

Connection Pooling: Connection pooling is more efficient with a single application user, reducing the overhead of establishing and tearing down connections for multiple end-users.

Scalability:

Resource Optimization: It is more scalable to manage a few application users rather than numerous individual end-user accounts, especially in high-traffic applications.

Setting up an application-specific user

To set up an application-specific user for a CRM database, follow the steps below. At first create the user and then adapt the Xbase++ connection string to use the credentials of that user.

CREATE USER crm_app_user WITH PASSWORD 'secure_password'; 
GRANT CONNECT ON DATABASE crm_database TO crm_app_user; 
GRANT USAGE ON SCHEMA public TO crm_app_user; 
-- required for existing objects of crm database 
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO crm_app_user; 
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO crm_app_user; 
-- only required for future objects of crm database 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON TABLES TO crm_app_user; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO crm_app_user; 

cConnStr := "DBE=pgdbe;SERVER=localhost;" 
cConnStr += "DB=crm_database;UID=crm_app_user;PWD=secure_password" 

oSession := DacSession():New(cConnStr) 
IF(!oSession:IsConnected()) 
  MsgBox("Connection failed ("+Var2Char(oSession:GetLastMessage())+")") 
  QUIT 
ENDIF 

With the PostgreSQL DBMS security and access rights are managed for different contexts such as database, table or actions. An overview is shown below, followed by an practical example how to setup a read-only user e.g. for a CRM database with SELECT access to all tables of that database.

Security

Access Rights

Setting Up a Read-Only Application User

Create the Read-Only User.

CREATE USER crm_readonly_user WITH PASSWORD 'readonly_password'; 
GRANT CONNECT ON DATABASE crm_database TO crm_readonly_user; 
GRANT USAGE ON SCHEMA public TO crm_readonly_user; 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO crm_readonly_user; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO crm_readonly_user; 

This setup ensures the user can only read data without making any modifications. To ensure that the following permissions were not granted:

- INSERT: Prevents the user from adding new rows to tables.

- UPDATE: Prevents the user from modifying existing rows in tables.

- DELETE: Prevents the user from removing rows from tables.

- ALTER: Prevents the user from altering the structure of database objects (e.g., ALTER TABLE, ALTER INDEX).

- EXECUTE: Prevents the user from executing functions or stored procedures that could modify data.

By omitting these permissions, we ensure that `crm_readonly_user` can only view data without making any changes, maintaining the integrity and security of the database.