Creating a Read-Only Postgres User

A recent (well depending on when you read this) Twitter discussion mentioned the topic of creating the quintessential "read-only Postgres user" that can, well, only read info from a database, not write to it. A simple way to handle this case is to create a read-only Postgres replica, but that may not make sense based on your application.

So, how can you simply create a read-only Postgres user (and note that I will use "user" and "role" interchangeably)? Let's explore!

Background: GRANT, REVOKE, ALTER DEFAULT PRIVILEGES

If you have managed users in Postgres before, you've likely run into GRANT and REVOKE. These are the fundamental building blocks of building up access control (ACL) in Postgres. They can be a bit tedious to wield: Postgres offers many access control settings, so you have to be able to carefully navigate them.

The other thing you should note about GRANT and REVOKE is that they do not necessarily apply to new objects within a database, such as tables. This is where default privileges come into play. Default privileges are what the name implies: the default privileges that Postgres applies to a role when a new object is created. You can modify a role's default privileges using the ALTER DEFAULT PRIVILEGES command.

There is a catch to this method, but we will explore it further on in the blog post.

Let's work through an example to see how we can create a "read only" role in a Postgres database.

Read Only User By Example

Let's say I have a database named "stocks" that is collecting stock data. I am going to follow some Postgres best practices on schemas and keep all of my objects in a schema called "app" and REVOKE all privileges from the public schema.

Setting Up the Data Set

Initially, I am going to set things up as a Postgres superuser. Here is how I set up my "stocks" database (the astute reader will point out that this is not normalized, but the main focus of the blog is for the read only user):

CREATE SCHEMA app;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE TABLE app.stock_data(
  symbol text NOT NULL,
  tick_at timestamptz,
  value float
);
CREATE INDEX ON app.stock_data USING brin(tick_at);

From there, I am going to populate this with some random data. Note that any semblance to a real stock ticker is purely coincidental.

INSERT INTO app.stock_data
SELECT 'NOTREAL123', tick, 50 + random()
FROM generate_series(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '7 days'::interval, '1 second'::interval) tick;

Lastly, let's create a function that will return the high and low value of the stock over a given period of time:

CREATE FUNCTION app.hi_lo(timestamptz, timestamptz)
RETURNS TABLE (hi float, lo float)
AS $$
  SELECT max(value) AS hi, min(value) AS lo
  FROM app.stock_data
  WHERE tick_at >= $1 AND tick_at < $2
$$ LANGUAGE SQL STABLE;

Give the function a whirl:

SELECT *
FROM app.hi_lo(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '1 hour'::interval);

Alright, now with this set up, let's add our readonly user.

Create the Read Only User

Let's say that we have a scraper that pulls information from our stock data, and we want this scraper to be read only. As a Postgres superuser, let's create this user, and provide some readonly privileges for them:

CREATE ROLE scraper LOGIN;

As "scraper", let's log into the "stocks" database and try to pull the first stock data entry:

SELECT * FROM app.stock_data ORDER BY tick_at LIMIT 1;

You will be met with the error:

ERROR: permission denied for schema app
LINE 1: TABLE app.stock_data;

Postgres is not permissive by default (whew!) so we need to explicitly GRANT some of the privileges to scraper. Using the Postgres GRANT guide, execute this as the Postgres superuser to provide the "readonly" permissions:

GRANT USAGE ON SCHEMA app TO scraper;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO scraper;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO scraper;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO scraper;

Now, if you run the below query as "scraper"

SELECT * FROM app.stock_data ORDER BY tick_at LIMIT 1;

You should see something like:

   symbol   |            tick_at            |      value
------------+-------------------------------+-------------------
 NOTREAL123 | 2021-05-10 17:37:51.041158-04 | 50.59352969008823

Similar with executing the function:

SELECT * FROM app.hi_lo(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '1 hour'::interval);

          hi        |        lo
--------------------+-------------------
 50.999959157759335 | 50.00003781290681

Now try adding a table as "scraper", or inserting a value as "scraper": you will receive permission denied errors:

CREATE TABLE app.nope (id int);
ERROR: permission denied for schema app

INSERT INTO app.stock_data
SELECT 'NOTREAL123', tick, 50 + random()
FROM generate_series(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '7 days'::interval, '1 second'::interval) tick;
ERROR: permission denied for table stock_data

Now, if we were to add a new table, "scraper" would not be able to access the new table. As a quick experiment, add a new table as the Postgres superuser:

CREATE TABLE app.cant_access (id int);

If you try running the following as "scraper":

TABLE app.cant_access;

You will be greeted by this error:

ERROR: permission denied for table cant_access

We could execute all of the above "GRANT" commands every single time we add a new table or object, but that would be exhausting. Instead, as the Postgres superuser, we can set up default privileges:

ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT SELECT ON TABLES TO scraper;

ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT EXECUTE ON FUNCTIONS TO scraper;

Now, as the superuser, let's add a new view that gets the high, low, and average, value grouped by every day:

CREATE VIEW app.stats AS
SELECT date_trunc('day', tick_at) AS hour, max(value) AS high, min(value) AS low, avg(value)
FROM app.stock_data
GROUP BY date_trunc('day', tick_at)
ORDER BY date_trunc('day', tick_at);

Now, try executing this as the "scraper" user.

SELECT * FROM app.stats;

You should see something like:

         hour           |        high        |        low         |        avg
------------------------+--------------------+--------------------+--------------------
 2021-05-10 00:00:00-04 | 50.999959157759335 | 50.0000010967176   | 50.50069733090165
 2021-05-11 00:00:00-04 | 50.99998822962728  | 50.000037657627324 | 50.499243373963495
 2021-05-12 00:00:00-04 | 50.99998035799526  | 50.00001062504181  | 50.49961331819138
 2021-05-13 00:00:00-04 | 50.99997434373391  | 50.000011150584584 | 50.49975960441223
 2021-05-14 00:00:00-04 | 50.99999746712692  | 50.00000637052951  | 50.49973713724854
 2021-05-15 00:00:00-04 | 50.99998313692336  | 50.00000551638095  | 50.4990941843102
 2021-05-16 00:00:00-04 | 50.99997623347603  | 50.000037383459244 | 50.50041404286691
 2021-05-17 00:00:00-04 | 50.99999846984804  | 50.000032706022424 | 50.50219071959834

Excellent, we have created a readonly Postgres user. But...there's a catch.

Next Steps - Download Postgres 14

There is a catch to the above method that is astutely pointed out by my friend and colleague Stephen Frost: ALTER DEFAULT PRIVILEGES only work for the user that is creating the objects. Huh?

With ALTER DEFAULT PRIVILEGES, the role that creates the objects will assign the privileges for those objects. This means that if a different user were to create a new table, "scraper" would not be able to access it. To work around this, you can set ALTER DEFAULT PRIVILEGES for these other users, but it seems that we only kicked the can a bit farther down.

Postgres 14 makes this much easier with the "pg_read_all_data" role, which was implemented by Stephen. "pg_read_all_data" 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.

GRANT pg_read_all_data TO scraper;

As of the time of this writing, Postgres 14 is about to begin its first beta, which makes this an opportune time to test out the new "pg_read_all_data" role.

Postgres access control is certainly a deep topic (we've talked quite a bit about Postgres security at Crunchy Data) and you should certainly double-check your work. Postgres does contain many conveniences for making it simple to lock down access to your data, including creating "readonly" users that are easy to manage.