How to Create a Readonly Admin User in PostgreSQL

This guide provides a detailed guide on how to create a user in PostgreSQL with full read privileges while preventing any write access.
---
This video is based on the question https://stackoverflow.com/q/66472224/ asked by the user 'Darren Oakey' ( https://stackoverflow.com/u/2446374/ ) and on the answer https://stackoverflow.com/a/66474946/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Readonly admin user in postgresql

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the Need for a Readonly Admin User in PostgreSQL

In today's world of data management, ensuring the right access levels for users is critical. Specifically, when working with a PostgreSQL database, you may find yourself in a situation where you need to perform checks across various tables and views — all while ensuring that certain permissions are intact. This raises a common question: Can we create a user that has comprehensive read privileges across the database without granting them write access?

It's a valid concern, especially in environments where sensitive data is present. In this guide, we'll explore how you can achieve this by setting up a readonly admin user in PostgreSQL.

Steps to Create a Readonly Admin User

Creating a readonly user in PostgreSQL involves granting read access without giving any write privileges. Below, we break down the steps in a structured manner.

Step 1: Granting READ Access

The first thing to understand is that PostgreSQL does not allow for blanket read privileges; you need to explicitly grant the SELECT privilege to each database object that needs to be accessible. Here's how you can efficiently grant access:

Granting SELECT Privileges on Existing Objects

You can run a command to grant SELECT on all tables or sequences within a given schema using the following syntax:

[[See Video to Reveal this Text or Code Snippet]]

Examples

To make it clearer, here's how you can implement this:

Granting SELECT privileges to all tables in a schema called public for a user named readonly_user would look like this:

[[See Video to Reveal this Text or Code Snippet]]

Note: Replace public with your actual schema name and readonly_user with your desired username.

Step 2: Setting Default Privileges for Future Objects

To simplify the process for any future objects that may be created, you can use the ALTER DEFAULT PRIVILEGES command. This allows you to set default permissions for objects created in the future:

[[See Video to Reveal this Text or Code Snippet]]

Example

If you want readonly_user to have SELECT permission on any new tables created in the public schema, you can run the following command:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Creating a Readonly Role

It's a good practice to create a readonly role that encapsulates all these permissions. This allows for easier management of user privileges.

Create the Readonly Role

Run the following command to create a new role:

[[See Video to Reveal this Text or Code Snippet]]

Grant SELECT Privileges to the Readonly Role

Now, assign the same permissions that we discussed earlier to the readonly role:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Adding the User to the Readonly Role

Finally, to give the readonly_user the necessary capabilities, you can add them to the readonly role like this:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

To summarize, creating a readonly admin user in PostgreSQL is not as involved as it may seem. By following the outlined steps — granting SELECT privileges, setting default privileges for future objects, and utilizing a dedicated readonly role — you can effectively manage read-only access without the risk of unintended changes to your database.

Using these practices will enhance your database security while making it easier to run checks on all the necessary objects without the hassle of managing individual permissions.

Take charge of your PostgreSQL database today and implement these strategies for a controlled access environment!