Protected view-only Postgres schemas

Published in

ITNEXT

5 min readApr 13, 2020

Hope you're all fine during this health crisis. Remember to keep safe and if possible stay at home.

Have you ever needed to give "some" access to your database, let's say SELECT access over certain tables, but also record what queries does the user execute?
We had this requirement a while ago. The first two parts are easy to achieve:

Some access: Create a new use role while only select privileges.
Access to some tables: Create a view over the required tables

Recording the executed queries is not that simple because there are no triggers over a SELECT query.

The dirty trick

So we need to record what queries are executed. We may need a new table, perhaps on another schema, and a Postgres function to insert a record on the log table.

The table

Classic structure:

id: serial, primary key
user: varchar, the database user who is executing the query
sql: text, the query itslf
ip: varchar, the ip from where the user is connected
executed_at: timestamp, the full date when the query was executed.

By the way, a nice idea when creating database objects is to include some comments about each column use (if it is a table). May be some extra work, but everybody will thank that later.

CREATE TABLE public.query_log (
  id serial NOT NULL,
  "user" varchar(150) NULL,
  "sql" text NULL,
  ip varchar(25) NULL,
  executed_at timestamp NULL DEFAULT NOW(),
  CONSTRAINT query_log_pkey PRIMARY KEY (id)
);

The table query_log will be created on the public schema, you may or may not want to change that, depends on your own needs.

The function

It's basically a simple insert, but you require it as a function so you can make the trick.

-- DROP FUNCTION public.log_query_information;
CREATE OR REPLACE FUNCTION public.log_query_information(quser character varying, qsql text, qip character varying)
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
BEGIN  
 INSERT INTO public.query_log
  ("user", "sql", ip)
  VALUES(quser, qsql, qip) ON CONFLICT DO NOTHING;
 RETURN TRUE;
END;
$function$
;

The DROP is commented just in case you need to delete this function later.

There are 2 important things here. First the return true , because this function has to return something (remember, the trick). Second the ON CONFLICT DO NOTHING, and this is to avoid "Houston we have a problem" instances.

I suggest that before trying this on production environments you test them locally on a Postgres installation on your machine or a test Postgres server running on docker. On my personal avoid copy & pasting quest I'm intentionally skipping an important part, to try to ensure you read and, hopefully, understand the whole article.

As you can see both the table and the function are created on the public schema. It may be another schema, but I suggest using a different one from the new one you're going to create for your views, the view schema is for views.

Schema and role

The new schema has to include all views to the tables you want to give view access to.
Skipping some other extras, the new user role has to have SELECT privileges over the the tables (outside the view schema) and INSERT privileges over the query_log table.

-- Create the new schema, grant authorization to 
-- postgres user (change if needed)
CREATE SCHEMA "restricted_schema" AUTHORIZATION postgres;-- Create new role
CREATE ROLE restricted_user NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN PASSWORD 'restricted_USER_passsword';-- Grant SELECT access to original tables on internal schema
-- Will use schema: internal and table: company_users as example
GRANT SELECT ON TABLE internal.company_users TO restricted_user;-- Grant INSERT on query_log and allow operations on sequence
GRANT INSERT ON TABLE public.query_log TO restricted_user;
GRANT ON SEQUENCE public.query_log_id
GRANT USAGE, SELECT ON SEQUENCE public.query_log_id_seq TO restricted_user;-- Allows the user to access the restricted schema
GRANT USAGE ON SCHEMA restricted_schema TO restricted_user;-- Removes all other permissions to the restricted user
REVOKE ALL ON ALL TABLES IN SCHEMA  pg_catalog FROM restricted_user;   
REVOKE ALL PRIVILEGES ON TABLE pg_catalog.pg_proc FROM restricted_user;
REVOKE ALL PRIVILEGES ON TABLE pg_views FROM restricted_user;
REVOKE SELECT ON TABLE pg_proc FROM restricted_user;
REVOKE SELECT ON TABLE pg_views FROM restricted_user;-- Allow the execution of the function
GRANT EXECUTE ON FUNCTION public.log_query_information("varchar","text","varchar") TO restricted_user;

The view

And here's the trick, you will make a view, as usual, but the last column from your "view select" will call the public.log_query_information function (that's the reason why we needed the return true for).

-- DROP VIEW restricted_schema.company_users;
CREATE OR REPLACE VIEW restricted_schema.company_users
AS SELECT 
    pp.id,
    pp.code,
    pp.name,
    pp.last_name,
    pp.soc_sec_number,
    pp.created_at,
    pp.updated_at,
    pp.deleted_at,
    pp.company_id
from (SELECT p.id,
    p.code,
    p.name,
    p.last_name,
    p.soc_sec_number,
    p.created_at,
    p.updated_at,
    p.deleted_at,
    p.company_id,
    public.log_query_information(current_user::varchar, current_query(), inet_server_addr()::varchar) 
   FROM internal.company_users p
     LEFT JOIN internal.company cc ON cc.id = p.company_id AND cc.id = 123) AS pp;

So our view restricts a select only over records with company_id=123 . You should have also noticed a sub-query aliased as pp and this is only to keep a SELECT * from the view clean, with no strange function calls here and there.

THE TRICK: The view returns all requested information, but on every record it also inserts a new record on our public.query_log table.
Go on, give it a try…

See the problem here? watch again… This is not over yet.
Lets suppose you have 50000 company users. As the select inside the view executes the function for every record, your inserts will also be executed that so many times. Too many query_log records for a single query. Not good…

How to solve this: Remember the ON CONFLICT DO NOTHING? Well, this is the Houston I was talking about.
Some how you need to make sure that only one log query record per query is inserted. And to do so we got some UNIQUE INDEXES to the rescue.
Just create a new index on all columns you see are repeated when any select over the view is executed.

That would be:

CREATE UNIQUE INDEX query_log_unique_records ON public.query_log ("user","sql",ip,executed_at);

This should work for most cases, because the user, the sql query, the connection ip and the executed_at timestamp should be the same for a single query.

As always, your mileage may vary, but this should give you a nice idea on how to make a view-only schema with query logging. It can also hep if you need some kind of “SELECT trigger” workaround.