Mastering PostgreSQL Stored Procedures: A Comprehensive Guide

[pwgit-create]

---

For those who are relatively new to PostgreSQL (Postgres), stored procedures can seem like an advanced concept.
However, they offer several benefits that can make your database interactions more efficient, secure, and
maintainable.

What Are Stored Procedures?

Stored procedures are precompiled sets of SQL statements stored in the database itself. They allow you to
encapsulate complex logic that can be executed with a single call from an application or another database object.

---

Using stored Procedures for Encapsulation

Stored procedures allow you to encapsulate logic within your database, enabling you to define complex queries or
operations once and reuse them across different parts of your application, reducing code duplication.

In this example, we'll create a database for managing users and their roles, with the role assignment logic
encapsulated in a stored procedure.

Step 1: Create Database and Tables

First, let's create the users table with columns for user ID and username, and the roles table to store role
information:

CREATE DATABASE user_management;

\c user_management

-- Create users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE
);

-- Create roles table
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    role_name VARCHAR(20) NOT NULL
);

Step 2: Define the Encapsulated Procedure

We'll create a stored procedure called assign_role that will encapsulate the logic for assigning a role to a
user. This way, we keep the business logic within the database and hide the complexities from external
applications.

CREATE OR REPLACE PROCEDURE assign_role(
    IN p_username VARCHAR,
    IN p_role_name VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Check if the user exists
    IF NOT EXISTS (SELECT 1 FROM users WHERE username = p_username) THEN
        RAISE EXCEPTION 'User % does not exist', p_username;
    END IF;

    -- Insert role for the user
    INSERT INTO roles (user_id, role_name)
    SELECT id, p_role_name
    FROM users
    WHERE username = p_username;
END $$;

Step 3: Test the Procedure

Let's create a few test users and assign them roles using our procedure:

-- Insert some sample users
INSERT INTO users (username) VALUES ('alice'), ('bob');

-- Assign roles to the users using the stored procedure
CALL assign_role('alice', 'admin');
CALL assign_role('bob', 'user');

-- Verify the assignments
SELECT * FROM users;
SELECT * FROM roles;

Explanation

1. Encapsulation: The assign_role procedure encapsulates all logic related to assigning a role to a user. It
   ensures that:
   • The user exists before attempting to assign a role.
   • The role is assigned correctly by associating it with the user's ID.

---

Benefits of Using Stored Procedures

Stored procedures in PostgreSQL provide a powerful way to encapsulate business logic securely and efficiently,
enhancing both security and performance. By storing these procedures separately, you can achieve better
manageability, maintain clear separation of concerns, and ensure robust disaster recovery processes.

This approach is particularly valuable for specialized applications like file integrity scanners or systems
employing a freemium model where secure, efficient, and scalable handling of data operations is crucial.

Improved Security

• Centralized Business Logic: Having critical logic encapsulated within stored procedures reduces the risk of
  security breaches at the application layer.
• Access Control: You can grant permissions at a granular level, ensuring that only authorized users have
  access to sensitive operations.
• Audit Trails: Stored procedures can be used to create detailed audit logs of user actions, which is
  essential for compliance and tracking.

Enhanced Performance

• Reduced Network Latency: Since stored procedures execute on the database server itself, there's less network
  traffic between the application and the database.
• Optimization: Database engines like PostgreSQL are optimized for executing SQL operations. Stored procedures
  can take advantage of this optimization to perform complex queries more efficiently.

Maintainability

• Consistency: Centralizing business logic in stored procedures ensures that all clients (applications)
  interact with the same logic, reducing discrepancies.
• Versioning and Rollbacks: With version control systems like Git, you can manage changes to your database
  schema and procedures separately from application code.

Flexibility and Scalability

• Scalable Operations: Stored procedures can handle complex operations that might otherwise strain an
  application server.
• Encapsulation of Complexity: Encapsulating business logic within stored procedures makes the application
  layer cleaner and easier to maintain.

---

Remote Calls in PostgreSQL

PostgreSQL doesn’t natively support traditional remote procedure calls (RPCs), but you have several options:

1. Foreign Data Wrappers:

   • Connect to other databases and execute queries as if they were part of your local schema.

   CREATE EXTENSION IF NOT EXISTS postgresql_fdw;
   CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgresql_fdw OPTIONS (host 'remote_host', dbname 'remote_db');

2. PL/Proxy:

   • An extension that allows you to manage connections between PostgreSQL instances and execute distributed
     queries.

3. HTTP Requests from Procedures (less common):

   • Using extensions like pg_http or writing custom scripts to make HTTP calls.

   CREATE EXTENSION pg_http;

4. Using dblink :

   • The dblink extension allows you to connect to other PostgreSQL databases from within a stored procedure and
     execute queries on those remote databases.

• First, ensure that the dblink module is installed and available in your PostgreSQL instance. You can load it
  using:

CREATE EXTENSION dblink;

• Here's an example of how you might use dblink in a stored procedure

CREATE OR REPLACE FUNCTION remote_call_example()
RETURNS VOID AS $$
DECLARE
    conn dblink_conn;
    result TEXT;
BEGIN
    -- Establish a connection to the remote database
    conn := dblink_connect('dbname=remotedb user=remoteuser password=remotepass hostaddr=192.168.1.10 port=5432');

    -- Execute a query on the remote database
    result := dblink_exec(conn, 'SELECT * FROM remote_table WHERE id = 1');

    -- Process the result if needed (result contains the rows returned by the query)
    RAISE NOTICE 'Result: %', result;

    -- Close the connection to the remote database
    PERFORM dblink_disconnect(conn);
END;
$$ LANGUAGE plpgsql;

5. Using postgres_fdw :
   • First, ensure that the postgres_fdw module is installed and available in your PostgreSQL instance. You can load
     it using:

CREATE EXTENSION postgres_fdw;

• Then you need to set up a foreign server and user mapping:

-- Create a foreign server object representing the remote database
CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.10', dbname 'remotedb', port '5432');

-- Define a user mapping for connecting to the remote server
CREATE USER MAPPING FOR current_user
    SERVER foreign_server
    OPTIONS (user 'remoteuser', password 'remotepass');

• Create a foreign table that maps to a table in the remote database:

CREATE FOREIGN TABLE remote_table (
    id INT,
    name TEXT
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'remote_table');

• Now you can use this foreign table as if it were a local table in your stored procedures:

CREATE OR REPLACE FUNCTION remote_call_example()
RETURNS VOID AS $$
BEGIN
    -- Query the foreign table which references data on the remote database
    PERFORM * FROM remote_table WHERE id = 1;
END;
$$ LANGUAGE plpgsql;

Considerations

• Security: Both methods require careful handling of credentials and connections, especially in production
  environments.
• Performance: Remote calls can introduce latency depending on network conditions between the databases.
• Transaction Management: Be aware that remote transactions might not be part of your local transaction scope,
  which can lead to inconsistencies if not managed properly.

By using either dblink or postgres_fdw, you can effectively make remote database calls from within PostgreSQL
stored procedures.

---

Use Case: Freemium Software Protection

In a freemium software model where you provide basic features for free but charge for premium features:

1. Access Control: Stored procedures can enforce access control rules that prevent unauthorized users from
   accessing premium functionalities.
2. Usage Tracking: Sequences and tables can track how often free and paid users are using certain features,
   helping to identify potential upsells or abuse of the system.
3. Feature Flags: Procedures can manage feature flags, enabling/disabling specific features based on user type
   (free vs. premium).
4. Billing Integration: Procedures can be used to trigger billing events when a user upgrades from free to
   premium.

Example: Tracking User Actions

Here’s how you could set up a stored procedure to track user actions in PostgreSQL:

-- Create table for storing user actions if it doesn't already exist
CREATE TABLE IF NOT EXISTS user_actions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,     -- Foreign key linking to users table
    action_type VARCHAR(50) NOT NULL,  -- Type of action (e.g., 'login', 'purchase')
    action_details TEXT,       -- Additional information about the action
    action_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- When the action occurred
);

-- Create a stored procedure to log user actions
CREATE OR REPLACE PROCEDURE track_user_action(
    IN p_user_id INT,
    IN p_action_type VARCHAR(50),
    IN p_action_details TEXT DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO user_actions (user_id, action_type, action_details)
    VALUES (p_user_id, p_action_type, p_action_details);
END;
$$;

-- Example call to log a user login event
CALL track_user_action(1, 'login', 'User logged in from browser');

---

Centralized Storage of Procedures in a Distinct Repository

Storing stored procedures separately offers additional benefits:

Version Control and Management:

• Isolation: Keeping database scripts separate allows developers to focus on the business logic without being
  overwhelmed by application code.
• Versioning: Tracking changes independently ensures that you can roll back specific database modifications if
  needed.

Security:

• Access Control: By isolating sensitive procedures, you limit who has access to critical operations and
  reduce potential attack vectors.
• Tamper Resistance: Freemium software often has logic that must be protected. Separating these scripts makes
  unauthorized tampering harder.

Deployment and Maintenance:

• Independent Deployment: Database changes can be deployed independently of application code changes,
  streamlining release cycles.
• Environment Consistency: Ensures the same database logic is applied uniformly across different environments.

Collaboration:

• Specialization: Database administrators or specialized developers can work on stored procedures without
  needing access to the entire application codebase.
• Clear Responsibilities: Separation of concerns helps in delineating responsibilities among teams and
  individuals.

Backup and Recovery:

• Targeted Backups: Stored procedures can be backed up separately, simplifying recovery processes.
• Disaster Recovery: Ensures that critical database logic is preserved even if the main application codebase
  encounters issues.

---

Benefits for a File Integrity Scanner

1. Security Enhancements:

   • In a file integrity scanner, sensitive information such as hashes and checksums can be protected by using
     stored procedures to ensure that only authorized operations are performed on this data.
   • Stored procedures can enforce security rules at the database level, reducing the risk of unauthorized access
     or modifications.

2. Efficiency:

   • File integrity checks often involve complex queries over large datasets. Using stored procedures allows you
     to optimize these queries and execute them more efficiently.
   • By encapsulating the logic for checking file integrity in a procedure, you can avoid the overhead of sending
     multiple commands from your application to the database.

3. Consistent Scanning Logic:

   • Having the scanning logic within stored procedures ensures that it's consistently applied regardless of how
     or where it's called within the system.
   • This consistency is crucial for accurate file integrity checks and reporting.

---

Benefits for a Freemium Release Plan

1. Differentiated Functionality:

   • For freemium models, you can use stored procedures to implement feature differentiation between free and
     premium users. Only certain procedures could be accessible to premium customers.
   • This approach simplifies the implementation of tiered access control.

2. Security for Premium Features:

   • Sensitive or high-value features (e.g., advanced analytics) available only to paying customers can be
     protected by using stored procedures, ensuring that free users cannot access them directly.
   • Procedures can also help enforce rate limits or usage quotas for premium services.

3. Ease of Updates and Maintenance:

   • As you roll out new features or updates, having the business logic in stored procedures makes it easier to
     manage changes without redeploying application code.

---

Benefits for a File Integrity Scanner

1. Security Enhancements:

   • In a file integrity scanner, sensitive information such as hashes and checksums can be protected by using
     stored procedures to ensure that only authorized operations are performed on this data.
   • Stored procedures can enforce security rules at the database level, reducing the risk of unauthorized access
     or modifications.

2. Efficiency:

   • File integrity checks often involve complex queries over large datasets. Using stored procedures allows you
     to optimize these queries and execute them more efficiently.
   • By encapsulating the logic for checking file integrity in a procedure, you can avoid the overhead of sending
     multiple commands from your application to the database.

3. Consistent Scanning Logic:

   • Having the scanning logic within stored procedures ensures that it's consistently applied regardless of how
     or where it's called within the system.
   • This consistency is crucial for accurate file integrity checks and reporting.

---

Overview of Benefits of Using Procedures in PostgreSQL

1. Encapsulation:

   • Stored procedures allow you to encapsulate logic within your database. This means you can define complex
     queries or operations once and reuse them across different parts of your application, reducing code duplication.

2. Performance:

   • Because stored procedures are precompiled and executed on the server side, they can reduce network latency by
     minimizing the number of round trips between the client and the database server.
   • They also allow for more efficient execution plans as PostgreSQL can optimize them better than individual
     ad-hoc queries.

3. Security:

   • Stored procedures can enhance security by restricting direct access to tables. Users can be granted
     permissions only to execute specific stored procedures, ensuring that they cannot perform arbitrary operations on
     the database.
   • By using procedures, you have fine-grained control over what actions users can perform on your data.

4. Maintainability:

   • Changes to business logic or queries are localized within stored procedures, making it easier to maintain and
     update without modifying application code.

5. Consistency:

   • Using stored procedures ensures that all operations are consistent across different parts of the system, as
     everyone is using the same predefined procedure for common tasks.

---

[pwgit-create]

Hi Stefan! @lilstiffy

I spent a lot of hours after our boxing session creating a tutorial article about psql stored procedures. I'm sure
you've heard about it before, but in case not, consider this a refresher or further education on the topic.

Please note that the remote database section is just for your knowledge. While we won't use spy apps, it's good
for you to understand how these things work. However, stored procedures might be useful locally if we proceed with
releasing this application/service as a freemium service (following the release of the file integrity scanner). So I wanted to provide a
tutorial unrelated to the project that will significantly boost your skills—more than mine, that is!

Note that this is purely educational and for interest. It's an optional feature in PostgreSQL (psql), not
something project-related that you have to do. 🥲

Now I'm going to review your feedback and then watch a movie or something. I'm really tired now. :P

Thanks for the great boxing session tonight and have a fantastic weekend, Stefan! 💠 🥊