Read-only user in PostgreSQL

 

As a Database Administrator, we always classify various types of schema with specific privileges. In the standard application, there are a few various types of Database Users like Application user, Read-only user, Admin user.

This article gives an example to configure read-only access in Postgres data source

 

Create the Read-Only user :

postgres# CREATE ROLE test_user WITH LOGIN PASSWORD 'dummypassword123' 

 NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Grant the Read-Only privileges : 

postgres# GRANT CONNECT ON DATABASE nexaprd TO test_user;
GRANT

postgres# GRANT USAGE ON SCHEMA public TO test_user;
GRANT

postgres# GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_user;
GRANT

postgres# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO test_user;
GRANT

 

To Assign permissions to read all newly tables created in the future, you can use below

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_user;

 

Read-only privilege can also be granted on specific objects or specific columns of the object.

GRANT SELECT ON TABLE "company"."employee" TO test_user;

GRANT SELECT (id, name) ON TABLE company.employee TO test_user;

 

To avoid errors in lengthy, multi-table changes, it is advisable to use dynamic query as below to generate the required GRANT SELECT for each table/view:

SELECT 'GRANT SELECT ON ' || relname || ' TO REPLACE_USERNAME_HERE;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S'); 

 

As we see using the above statements we have restricted user "test_user" by Database, Schema and gave only SELECT permission for tables.

 

You can also use a ROLE an assign this role to users.

-- Create a group
CREATE ROLE readaccess;

 
-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

 
-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

 
-- Create a final user with password
CREATE USER new_user WITH PASSWORD 'strong_password';
GRANT readaccess TO new_user ;