PostgreSQL User Creation and Permission Setup

This guide defines the standard procedure for initializing the user-db database and configuring roles. The goal is to adhere to the Principle of Least Privilege: the application (user_app) must only have data manipulation rights, while schema changes are reserved for the admin (user_admin).

1. Role Definitions

Account	Role Type	Scope	Allowed Actions	Blocked Actions
postgres	Superuser	System Admin	DB/User Creation, Permission Grants	(None)
user_admin	Schema Owner	DDL + DML	CREATE TABLE, DROP, ALTER	(None)
user_app	Service User	DML Only	SELECT, INSERT, UPDATE, DELETE	TRUNCATE, DROP, CREATE

2. Initialization Scripts (Run as Superuser)

Log in as the postgres user (or any user with cloudsqlsuperuser role) and execute the following steps in order.

Step 1: Create Database & Users

-- 1. Create Database (Quotes required if name contains hyphens)
CREATE DATABASE "user-db";

-- 2. Create Users (Ensure strong passwords in production)
CREATE USER user_admin WITH PASSWORD 'admin_strong_password';
CREATE USER user_app WITH PASSWORD 'app_strong_password';

-- 3. Grant Connection Privileges
GRANT CONNECT ON DATABASE "user-db" TO user_admin;
GRANT CONNECT ON DATABASE "user-db" TO user_app;

-- 4. Cloud SQL Membership (Optional but recommended for management)
GRANT user_admin TO postgres;
GRANT user_app TO postgres;

Step 2: Schema Level Security

⚠️ Important: You must be connected to the target database (\c "user-db") before running these commands.

-- 1. Revoke default CREATE permission on public schema (Security hardening)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 2. user_admin: Full control (Create tables, etc.)
GRANT ALL ON SCHEMA public TO user_admin;

-- 3. user_app: Access only (Cannot create tables)
GRANT USAGE ON SCHEMA public TO user_app;

Step 3: Configure Default Privileges (Automation)

This ensures that future tables created by admins are automatically accessible by the app user with the correct restricted permissions.

-- [Case A] When 'user_admin' creates a new table -> Auto-grant DML to 'user_app'

ALTER DEFAULT PRIVILEGES FOR ROLE user_admin IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_app;

ALTER DEFAULT PRIVILEGES FOR ROLE user_admin IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO user_app;

-- [Case B] When 'postgres' creates a new table -> Auto-grant DML to 'user_app'
-- (Crucial for preventing permission errors if the superuser creates tables)

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_app;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO user_app;

Step 4: Apply to Existing Objects (Migration/Reset)

Run this block if you have just imported a dump file or if permissions are out of sync.

-- 1. Reset permissions for user_app
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user_app;

-- 2. Grant strictly defined DML permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user_app;

-- 3. Grant Sequence permissions (Required for auto-increment IDs)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO user_app;

3. Verification Guide

Run the following commands in psql to verify the setup.

Check 1: Schema Permissions

userdb=> \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +| 
        |                   | cloudsqlsuperuser=UC/pg_database_owner+| 
        |                   | user_admin=UC/pg_database_owner       +| 
        |                   | user_app=U/pg_database_owner           | 

• Success Criteria:
  • user_admin: Should have UC (Usage + Create).
  • user_app: Should have U only (Usage). If C exists, it is insecure.

Check 2: Table Permissions

user-db=> \dp
                                        Access privileges
 Schema |        Name        | Type  |     Access privileges      | Column privileges | Policies 
--------+--------------------+-------+----------------------------+-------------------+----------
 public | accounts           | table | postgres=arwdDxtm/postgres+|                   | 
        |                    |       | user_app=arwd/postgres     |                   | 

• Success Criteria:
  • user_app: Should show arwd (Append, Read, Write, Delete).
  • Failure: If you see D (Truncate), x (References), or t (Trigger), permissions are too loose.

Check 3: Default Privileges

user-db=> \ddp
                   Default access privileges
   Owner    | Schema |   Type   |      Access privileges       
------------+--------+----------+------------------------------
 postgres   | public | sequence | user_app=rwU/postgres
 postgres   | public | table    | user_admin=arwdDxtm/postgres+
            |        |          | user_app=arwd/postgres
 user_admin | public | sequence | user_app=rwU/user_admin
 user_admin | public | table    | user_app=arwd/user_admin

• Success Criteria:
  • Ensure there are entries for both user_admin and postgres granting arwd on tables to user_app.

4. Operational Best Practices

1. Who creates tables?
   • Always use the **user_admin** account (or postgres) for DDL operations (Create/Alter/Drop).
   • The user_app account will receive a “Permission Denied” error if it attempts to create tables.
2. How to handle “Permission Denied” errors?
   • If the application logs “Permission denied for relation…”, it usually means a table was created by a user without the Default Privileges set up correctly.
   • Fix: Re-run the queries in Step 4 (Existing Objects) to sync permissions.
3. Why can’t the app TRUNCATE tables?
   • TRUNCATE is a destructive administrative command. It is intentionally blocked for user_app to prevent accidental data loss. Use DELETE FROM table_name instead for application logic.