Challenges faced during PostgreSQL DB credentials rotation

In the recent past, we have seen many instances of data breaches targeting reputed companies across the globe. In today's business world, security has become one of the most crucial aspects of any organization. As more and more companies experience crippling security breaches, the wave of compromised data is on the rise. It is always a risk for the data of a company if the username and password are susceptible to theft. It’s no secret that data breaches are costly for businesses.

We at Wego manage multiple databases of different database engines like PostgreSQL, MySQL, Oracle, etc. across different services. Maintaining individual username and password for all services across all teams is very crucial, then we decided to rotate username and password for all databases and have them stored secretly away from developer access and move them to use IAM read only access which can be found here on how to use database via AWS SSO.

In this blogpost, we will walk you through the challenges that was encountered while trying to implement the username and password rotation for PostgreSQL database.

List of issues faced during the rotation of database credentials

1. Sequence Error

PostgreSQL databases use Sequence as an object_type which needs to be explicitly passed as a block of code in Terraform configuration. This issue was not occurring for old user. Here is an error we encountered when we rotated the credentials to use a new username and password during the process of rotating the credentials.

ERROR: permission denied for sequence <sequence_id>

Resolution for this error was to add the below block of code in Terraform configuration file. Note that we are using sequence as an object_type.

2. Ownership Error

When an object is created, it is assigned an owner. The owner is normally the role that executed the creation statement. To allow other roles to use it, privileges must be granted. PostgreSQL seems to have lot more knobs with permissions settings. Below is one such issue where any other user cannot access the tables created by another user.

ERROR 42501 (insufficient_privilege) must be owner of table <table_name>

In order to resolve this issue, ownership of the tables were granted to a new user that had been implemented using the following command.

ALTER TABLE <table_name> OWNER TO <new_user>;

3. Newly created DB tables cannot be accessed by user who has read only access

Another scenario where we found out that a user with read only permission cannot access newly created tables and this turned out to be a PostgreSQL version issue. PostgreSQL version less than 14 wouldn't be permissible to access database tables created by another user even though they have read permission as the role.

Resolution was to update to version 14 or above with the "pg_read_all_data" role. This role lets you assign the read only privileges to a user; the user is able to have read only privileges on any objects created by any user in the database.

4. Datatype Error

PostgreSQL gives us the flexibility to create user defined custom datatypes besides using the built in datatypes. We can create a custom datatype using either CREATE DOMAIN or CREATE TYPE. CREATE DOMAIN creates the user defined datatype with support to use constraints such as NOT NULL, CHECK, etc.. The user who defines a type becomes the owner of that datatype or domain.

A good example of this type of issue is that below is an example of how a user might be unable to access a type created by another user.

ERROR: must be owner of type <data_type> (SQLSTATE 42501))

To resolve this issue, the ownership of the type was granted to a new user that had been implemented using the command below, so that ownership of the type was then assigned to the new user.

ALTER TYPE <type_name> OWNER TO <new_user>;

Conclusion

As social engineering and phishing attacks are becoming more sophisticated. Any organization should protect its data and make sure to not share passwords, credentials over messaging or write them down. In general, everyone are vulnerable but awareness and following protocols do help.

In order to minimise these kinds of events from happening, rotation of database credentials is one of the way to make sure all applications use different username and password. To implement this solution, we had to go through few hiccups especially with the PostgreSQL database and the possible resolutions that followed thereafter are few the things has been discussed in the blogpost.