Parse RSS feed in bash

This is probably a fun poke at Planet PostgreSQL itself :) !

Recently I did a multi-part series [1][2][3] on a given topic and ended up "Scheduling" successive posts, so that they don't go out altogether.

And an interesting question came up:

What would be a good day/time for the next post?

A little bit of XML parsing later, I got a hint of what would looks like a lean period!

robins@fw:~/dl$ wget -q https://planet.postgresql.org/rss20_short.xml; echo "cat  //pubDate/text()" | xmllint --shell  rss20_short.xml | grep -ve "----" | grep -v "\/" | date +%A -f - | sort | uniq -c | sort -n
      1 Sunday
      3 Monday
      3 Saturday
      4 Friday
      5 Thursday
      6 Wednesday
      8 Tuesday

Now obviously the count for posts on a given weekday kept changing (depending on when I checked) but Sunday was undeniably a lean day. Given that, for some this may mean that Sunday is NOT the best time to post an article, but think I'll leave that to you 😊 !

Happy reading!

Boost Database Security: Restrict Users to Read Replicas

Only Allow Login to Read-Replicas and Standbys

When you're working with large databases in production, it is incredibly common to use read-replicas to improve performance. These read-replicas are a copy of your primary (main) database and let your applications offload read-heavy queries, which in-turn reduces strain on your primary database, effectively making the application faster and snappier.

Sometimes, you may want to restrict specific database users so they can connect ONLY to these read-replicas, and not to the primary database server. This can be tricky to implement, since any permissions configured for this use-case, whether on the user-level, the database level, the schema-level or even the table level would be quickly replicated to the read-replicas and thus would not work as expected.

This guide will show how to configure a database user to only login successfully on a read-replica. The only requirement is to enable the pg_tle extension [3] on your PostgreSQL database. This is simple to do on your Ubuntu based Laptop (see how to do that here [2]) or virtual-machines offered by your favourite cloud-provider. Furthermore, you could apply your login rules using Pl/PgSQL, PL/v8 or even PL/Rust - See here[1].

Why Restrict Access?

There are many good reasons for restricting users to read-replicas:

• Performance: You can dedicate your primary database server to handling write operations (like updating data), ensuring those operations happen as fast as possible.

• Reporting / Analytics: Production environments often have dedicated users for ancillary tasks, such as monitoring, reporting dashboards, read-only tenants etc. Restricting these database users to read-replica helpsreducing extra load on the primary database.

• Security: In some cases, granting direct access to the primary database might be considered a security risk. Further, you may not be able to force login hygeine for all your database users, and then having a lockdown system to reject those database users to login to primary is crucial for application rollout.

Prerequisites

• An existing PostgreSQL database instance with at least one read-replica.
  • You could also try this on your own Postgres database with pg_tle extension. Read here [2] for more on how to install pg_tle on your Ubuntu system.
• Basic understanding of users and permissions within a database.

Steps

1. Identify Target Database and Users: First we need to define how to implement the restriction. i.e. Which users (and database) are to be restricted to login only to read-replica. In the example below, we would restrict the user standby_only_user to only be able to login to Standbys / Read-Replicas on database prod_db.

psql <<SQL
  \c prod_db
  CREATE EXTENSION pg_tle;
SQL 

2. Ensure that shared_preload_libraries is properly set to allow pg_tle. Also make sure that the pgtle.clientauth_db_name is appropriately set to the desired database (here prod_db):

cat <<EOL >> data/postgresql.conf
  shared_preload_libraries='pg_tle'
  pgtle.enable_clientauth=require
  pgtle.clientauth_db_name=prod_db
  pgtle.clientauth_users_to_skip=robins
  pgtle.clientauth_databases_to_skip=''
EOL

3. Secret Sauce:

Next we create the key pg_tle function that restricts the user standby_only_user to login successfully only if this is a standby / read-replica:

SELECT pgtle.install_extension (
  'standbyusercheck',
  '1.0',
  'Allow some users to login only to standby / read-replicas',
$_pgtle_$
  CREATE SCHEMA standbycheck_schema;

  REVOKE ALL ON SCHEMA standbycheck_schema FROM PUBLIC;
  GRANT USAGE ON SCHEMA standbycheck_schema TO PUBLIC;

  CREATE OR REPLACE FUNCTION standbycheck_schema.standbycheck_hook(port pgtle.clientauth_port_subset, status integer)
  RETURNS void AS $$
    DECLARE
      is_standby bool := TRUE;
    BEGIN
      IF port.user_name = 'standby_only_user' THEN
        SELECT pg_is_in_recovery()
          INTO is_standby;
        IF is_standby THEN
          RAISE NOTICE 'User allowed to login';
        ELSE
          RAISE EXCEPTION 'User can only login to Standby / Read-Replicas';
        END IF;
      END IF;
    END
  $$ LANGUAGE plpgsql SECURITY DEFINER;

  GRANT EXECUTE ON FUNCTION standbycheck_schema.standbycheck_hook TO PUBLIC;
  SELECT pgtle.register_feature('standbycheck_schema.standbycheck_hook', 'clientauth');
  REVOKE ALL ON SCHEMA standbycheck_schema FROM PUBLIC;
$_pgtle_$
);

And now that the function is defined,CREATE EXTENSION would install the function and bind it to future login attempts.

CREATE EXTENSION standbyusercheck;
SHOW pgtle.clientauth_db_name;

4. Test Connection:

• Attempting to connect as a privileged user (here robins) to either of primary or read-replica should succeed.

Logging into Replica as robins
 login  | current_database | pg_is_in_recovery
--------+------------------+-------------------
 robins | prod_db          | t
(1 row)

Logging into Primary as robins
 login  | current_database | pg_is_in_recovery
--------+------------------+-------------------
 robins | prod_db          | f
(1 row)

• However, the user standby_only_user should NOT be able to login to the primary.

Logging into Primary as standby_only_user
psql: error: connection to server at "localhost" (127.0.0.1), port 6432 failed: FATAL:  User can only login to Standby / Read-Replicas

• While the user (standby_only_user) should only be able to login to any read-replica.

Logging into Replica as standby_only_user
       login       | current_database | pg_is_in_recovery
-------------------+------------------+-------------------
 standby_only_user | prod_db          | t
(1 row)

Other important aspects of this feature

• You could force clientauth for all logins by setting the parameter pgtle.enable_clientauth = require

• You could configure some users to always be allowed to login to either of Primary / Read-replica in cases of emergency, by adding that user to the pgtle.clientauth_users_to_skip. Ideally you would want your admin database roles to this list.

• Orthogonally, you could configure some databases to always allow users to skip clientauth by setting the pgtle.clientauth_databases_to_skip feature.

• Note, that both clientauth_databases_to_skip and clientauth_databases_to_skip can be utilised together. This is a good way to ensure that some set of database users (and some databases) are exempt from such a login restriction.

• If pgtle.enable_clientauth is set to on or require and if the database mentioned in pgtle.clientauth_db_name is not configured correctly, postgres would complain with the messsage FATAL: pgtle.enable_clientauth is set to require, but pg_tle is not installed or there are no functions registered with the clientauth feature. This is a good engine check, helping us avoid basic misconfigurations.

• If you're anticipating connection storms, you can also increase the workers (that would help enforce the login restriction) by setting the pgtle.clientauth_num_parallel_workers parameter to greater than 1.

Conclusion

By following the above steps, you've now successfully configured your PostgreSQL environment to restrict certain users to only login to the read-replicas. This helps not just optimize your database performance, but also bolster security.

Let me know if you'd like to explore more advanced scenarios or discuss IAM integration for fine-grained access control!

Reference

1. Clientauth Hook Documentation - https://github.com/aws/pg_tle/blob/main/docs/04_hooks.md'
2. Install pg_tle On Ubuntu - https://www.thatguyfromdelhi.com/2024/04/installing-pgtle-on-ubuntu-quick-guide.html
3. Unlock PostgreSQL Super Powers with pg_tle - https://www.thatguyfromdelhi.com/2024/04/unlock-postgresql-superpowers-with-pgtle.html

Installing pg_tle on Ubuntu: A Quick Guide

Compile & Install pg_tle on Postgres

PostgreSQL is a powerful database, but sometimes you want to extend its functionality with custom features, and that's where extensions like pg_tle (Trusted Language Extensions) come into play.

If you're new to pg_tle, here's a quick round-up of why it makes sense for you - See Unlock PostgreSQL Superpowers with pg_tle.

Given the power of pg_tle, you may want to install it locally (on your laptop or an EC2 instance) before deploying to environments with restricted access (such as Production, or PostgreSQL services offered by major cloud providers). This is not only helpful to thoroughly test your code, but also to save on cost given that all development can then happen on-premise.

In this blog post, we'll go through the process of compiling and installing pg_tle for postgres on your Ubuntu system.

Prerequisites

An operating system running Ubuntu (this guide assumes Ubuntu 20.04 or similar).

• A PostgreSQL database server, installed and running.
• Basic familiarity with the command line and postgresql.conf.
• Some development tools (we'll install these as we go).

Steps

Install Build Tools and Dependencies:

Start by updating your Ubuntu package list & install the necessary tools and libraries (This is required, since we would be compiling the pg_tle extension by source).

$ sudo apt update
$ sudo apt install build-essential make git postgresql-server-dev-all 

The postgresql-server-dev-all package version may need to be adjusted to match your specific PostgreSQL version. If this doesn't work for you, you can instead read more about setting up your Ubuntu operating system (albeit in a dated post) here - See Setup Linux for PostgreSQL development [3].

Download pg_tle Source Code:

Get the pg_tle source code from the GitHub project repository:

$ git clone https://github.com/aws/pg_tle.git

Compile pg_tle:

Compile the source code to create the extension files:

$ cd pg_tle
$ make

Although rare, make may fail if it can't find pg_config. As in the example below, a quick hack could be to help by pointing make to the pg_config binary location:

$ make                                     <<===== Fails
Makefile:24: /usr/lib/postgresql/15/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target '/usr/lib/postgresql/15/lib/pgxs/src/makefiles/pgxs.mk'.  Stop.

$ PG_CONFIG="`type -P -a pg_config`" make  <<===== Works successfully
gcc -Wall -Wmissing-prototypes ...
.
.
. (compiling starts successfully)

Install pg_tle:

Install the compiled extension into your PostgreSQL database. This command would install the extension related files to the postgres binaries folder, pointed to by PG_CONFIG:

$ sudo make install

Enable pg_tle in Your Database:

Connect to your PostgreSQL database using your preferred tool (e.g., psql) and run the following SQL command:

test_pgtle=# CREATE EXTENSION pg_tle;
CREATE EXTENSION

Verification:

To confirm pg_tle is installed correctly, run this SQL query:

test_pgtle=# SELECT * FROM pg_available_extensions WHERE name = 'pg_tle';
-[ RECORD 1 ]-----+-------------------------------------------
name              | pg_tle
default_version   | 1.4.0
installed_version | 1.4.0
comment           | Trusted Language Extensions for PostgreSQL

You should see a result similar to the above, where installed_version confirms the pg_tle version that's installed successfully.

Conclusion

You've now successfully compiled and installed the pg_tle extension on your Ubuntu system! This opens up the possibility to create and deploy custom extensions to enhance your PostgreSQL database.

pg_tle is a powerful tool that allows you to develop more advanced extensions. You can find more information and examples in the official pg_tle documentation at https://github.com/aws/pg_tle.

If you're intrigued, keep an eye out for a follow-up post where I'll show a simple example of how to use pg_tle extension for a real-world need!

References

1. Unlock PostgreSQL Superpowers with pg_tle - https://www.thatguyfromdelhi.com/2024/04/unlock-postgresql-superpowers-with-pgtle.html
2. Setup Linux for PostgreSQL development - https://www.thatguyfromdelhi.com/2011/12/setup-ubuntu-for-postgresql-development.html

Unlock PostgreSQL Superpowers with pg_tle

pg_tle - A Must-Know for Developers

PostgreSQL is a fantastic database, packed with features. But sometimes, you need to add a little something extra – a custom function, a specialized data type, or maybe a procedure written in your favorite programming language. That's traditionally where PostgreSQL extensions come in. But for developers working with managed databases (from major cloud providers), while installing supported extensions is trivial installing custom or unsupported extensions can be tricky or even impossible.

Enter pg_tle!

What exactly is pg_tle?

pg_tle stands for Trusted Language Extensions. Here's the breakdown:

• Extension: A piece of software that adds new functionality to PostgreSQL.
• Trusted Language: A programming language (like JavaScript, Perl, or PL/pgSQL) that the database 'trusts' due to security restrictions built into the language itself.
• The Magic: pg_tle provides a framework to build, package, and install extensions in these trusted languages, even in environments where you can't normally touch the underlying server.

Why is pg_tle Important for Application Developers?

1. Unlock New Possibilities: pg_tle lets you create custom database functions tailored to your application's unique logic. Say you need complex data validation or even specialized calculations – pg_tle can make it happen.

2. Bypass Restrictions: On managed database services, you often can't install traditional extensions. pg_tle works within these constraints allowing you to add functionality even in these environments - See Use Cases below for more.

3. Enhanced Security: Because pg_tle uses trusted languages, your extensions have limits on what they can do. It leads to a more secure database overall.

4. Open Source: pg_tle is open source [3] and available on Github (Apache 2.0 License).

Use Cases - Just A Sample Of Ideas

• Use Unsupported Extensions: This itself is a reason big enough to try out pg_tle! [4]
• Custom Password Strength Checks: Go beyond basic password rules with an extension.
• Login Triggers: Build custom rules that get triggered for every time a user logs into the database - not just for (the upcoming) v17 but also for older PostgreSQL versions!
• Custom Data-Types: Build custom data-types that you could use to store data, functions and views within your database.
• Data Transformations: Perform complex data manipulations directly within the database.

The Trade-offs

• Complexity: Creating pg_tle extensions can be more involved than basic SQL scripting.
• Limitations: Trusted languages still have constraints compared to the full capability of extensions developed in lower-level languages (like C).

Should You Learn More?

If any of the following resonate with you, then deeper pg_tle knowledge could be a big win:

• You crave more flexibility in how you work with data, and although have found an extension it's not yet natively supported in your PostgreSQL installation.
• Your application has a few "extra tricky" calculations or data processing needs, that are much easily possible with Perl, Javascript, PL/PGSQL.
• You work with managed databases and miss those power-user extensions.

Conclusion

pg_tle is a powerful tool that adds flexibility and extensibility for PostgreSQL developers. While a bit more advanced, understanding pg_tle unlocks a new level of database customization for your applications.

If you're intrigued, keep an eye out for a follow-up post where I'll show a simple example of building a pg_tle extension!

References

1. Trusted Language Extensions for PostgreSQL on Amazon Aurora and Amazon RDS - https://aws.amazon.com/blogs/aws/new-trusted-language-extensions-for-postgresql-on-amazon-aurora-and-amazon-rds/
2. Creating custom data-types using Trusted Language Extensions - https://aws.amazon.com/blogs/database/create-custom-postgresql-data-types-using-trusted-language-extensions/
3. pg_tle is open source! - https://github.com/aws/pg_tle
4. pg_tle makes using custom extensions easier - https://aws.amazon.com/blogs/opensource/supabase-makes-extensions-easier-for-developers-with-trusted-language-extensions-for-postgresql/
5. Installing pg_tle on Ubuntu - https://www.thatguyfromdelhi.com/2024/04/installing-pgtle-on-ubuntu-quick-guide.html