Create PostgreSQL Read-Only User with SELECT Permissions

Creating a read-only user in PostgreSQL involves several key steps: first creating a role with specific privileges, then creating a user assigned to that role, and finally granting SELECT permissions on the target database while restricting all other privileges. The process requires careful configuration of database privileges to ensure security while providing necessary read access.

Contents

• Creating a Read-Only User in PostgreSQL: Basic Principles
• Step-by-Step Instructions: Creating Roles and Granting Permissions
• Managing Privileges for Existing and New Tables
• Advanced Configuration: Restricting Object Creation and Other Privileges
• Verifying and Testing Security Settings

Creating a Read-Only User in PostgreSQL: Basic Principles

When setting up a read-only user in PostgreSQL, it’s essential to understand the difference between roles and users. In PostgreSQL, a role is essentially a user that can have privileges assigned to it. The security model is privilege-based, meaning you explicitly grant permissions rather than denying them by default.

The fundamental principle is to create a role with the minimum necessary privileges and then create a user that inherits from this role. This approach provides better security management and easier maintenance when dealing with multiple read-only users. The key privileges to grant are:

1. CONNECT - Allows the user to connect to the database
2. USAGE - Allows the user to “use” the schema (access objects within it)
3. SELECT - Allows reading data from tables

All other privileges should remain restricted to maintain the read-only nature of the account.

Step-by-Step Instructions: Creating Roles and Granting Permissions

Create the Read-Only Role

First, let’s create a dedicated role for read-only access:

CREATE ROLE readonly_user NOLOGIN;

This creates a role that cannot be used directly for login (hence NOLOGIN). We’ll create a separate user account that will use this role.

Create the User Account

Next, create a user that will be assigned to this role:

CREATE USER readonly_person WITH PASSWORD 'secure_password' IN ROLE readonly_user;

Replace 'secure_password' with a strong, unique password. This user now inherits all the privileges we’ll assign to the readonly_user role.

Grant Database Connectivity

Allow the role to connect to the specific database:

GRANT CONNECT ON DATABASE target_database TO readonly_user;

Replace target_database with the name of your database.

Grant Schema Usage

Grant USAGE privilege on the schema(s) where the user needs to read data:

GRANT USAGE ON SCHEMA public TO readonly_user;

If your tables are in a different schema, replace public with the appropriate schema name. If you need access to multiple schemas, repeat this command for each schema.

Grant SELECT Privileges

Now, grant SELECT permissions on existing tables. There are several approaches:

For Specific Tables

GRANT SELECT ON table1, table2, table3 TO readonly_user;

For All Tables in a Schema (if PostgreSQL version supports it)

GRANT SELECT ALL TABLES IN SCHEMA public TO readonly_user;

Dynamic Script for Existing Tables (PostgreSQL 9.0+)

For PostgreSQL versions that don’t support ALL TABLES IN SCHEMA, you can use this dynamic SQL approach:

DO $$
DECLARE
 r RECORD;
BEGIN
 FOR r IN SELECT table_schema, table_name 
 FROM information_schema.tables 
 WHERE table_schema = 'public' 
 AND table_type = 'BASE TABLE'
 LOOP
 EXECUTE 'GRANT SELECT ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO readonly_user';
 END LOOP;
END $$;

This script iterates through all tables in the specified schema and grants SELECT privileges to the read-only role.

Managing Privileges for Existing and New Tables

Handling Future Tables

To automatically grant SELECT permissions on new tables as they’re created, you need to set default privileges:

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

This ensures that any new table created in the public schema will automatically have SELECT permissions granted to our read-only role.

Handling Sequences

For applications that need to work with sequences (like some ORMs), you might also need to grant access to sequences:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO readonly_user;

Handling Views

If you have views that the read-only user needs to access:

GRANT SELECT ON ALL VIEWS IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON VIEWS TO readonly_user;

Advanced Configuration: Restricting Object Creation and Other Privileges

Restricting Object Creation

To prevent the read-only user from creating objects, ensure they don’t have CREATE privileges:

REVOKE CREATE ON SCHEMA public FROM readonly_user;

Restricting Modification Privileges

Explicitly revoke any modification privileges that might have been granted by default:

REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public FROM readonly_user;

Restricting Other Privileges

For comprehensive security, consider restricting other potentially dangerous privileges:

REVOKE CREATE, TEMPORARY ON DATABASE target_database FROM readonly_user;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM readonly_user;
REVOKE USAGE ON ALL SEQUENCES IN SCHEMA public FROM readonly_user;

Setting Row-Level Security (PostgreSQL 9.5+)

If your database uses row-level security policies, you might need to adjust them for read-only users:

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
CREATE POLICY read_only_policy ON table_name FOR SELECT USING (true);

This ensures that the read-only user can only access rows according to the defined policy.

Verifying and Testing Security Settings

Check Granted Privileges

Verify the privileges have been correctly assigned:

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE grantee = 'readonly_user' 
AND table_schema = 'public';

Test Connection and Query Access

Connect to the database as the read-only user and attempt various operations:

-- This should work
SELECT * FROM table_name LIMIT 10;

-- These should fail
INSERT INTO table_name (column1) VALUES ('test');
UPDATE table_name SET column1 = 'modified' WHERE id = 1;
DELETE FROM table_name WHERE id = 1;
CREATE TABLE test_table (id integer);

Check for Unintended Privileges

Review all privileges granted to the role:

SELECT * FROM information_schema.role_table_grants 
WHERE grantee = 'readonly_user';

SELECT * FROM information_schema.schema_privileges 
WHERE grantee = 'readonly_user';

SELECT * FROM information_schema.database_privileges 
WHERE grantee = 'readonly_user';

Sources

1. How to Create a Read-Only User in PostgreSQL — Comprehensive guide to creating read-only users with specific privileges: https://www.commandprompt.com/education/how-to-create-a-read-only-user-in-postgresql/
2. PostgreSQL Read-Only User Creation Script — Complete example script for granting SELECT privileges on existing tables: https://gist.github.com/oinopion/4a207726edba8b99fd0be31cb28124d0
3. Dynamic SQL for PostgreSQL Privileges — Dynamic approach to granting permissions on existing tables: https://dev.to/zaratedev/how-to-create-a-read-only-user-in-postgresql-1mok
4. PostgreSQL User Creation Best Practices — Detailed discussion on creating users with appropriate security restrictions: https://serverfault.com/questions/60500/crate-a-new-read-only-user-in-postgres
5. PostgreSQL Documentation: Privileges — Official PostgreSQL documentation on privilege management: https://www.postgresql.org/docs/current/sql-grant.html
6. PostgreSQL Documentation: Roles — Official PostgreSQL documentation on role and user management: https://www.postgresql.org/docs/current/sql-createrole.html
7. Information Schema Views — PostgreSQL documentation on using information_schema to query privileges: https://www.postgresql.org/docs/current/infoschema-views.html
8. Row-Level Security in PostgreSQL — Documentation on implementing row-level security for read-only users: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

Conclusion

Creating a read-only user in PostgreSQL requires careful configuration of multiple privilege levels to ensure security while providing necessary access. The process involves creating a role with restricted privileges, assigning a user to that role, and granting specific SELECT permissions on tables and schemas. By following the steps outlined in this guide—creating the role with NOLOGIN, granting CONNECT and USAGE privileges on the database and schema, and providing SELECT permissions on tables—you can establish secure read-only access to your PostgreSQL database. Remember to also configure default privileges for new tables and periodically verify that no unintended privileges have been granted. This approach ensures that your read-only users can only perform the SELECT operations they need while being prevented from modifying data or creating objects.