Simple Permission Model Practice

Simple permissions model

Objective: Customize Role objects based on specific functions. These tailored Roles come with predefined permission sets. The user list for each customized Role can be modified using the GRANT and REVOKE commands, simplifying the complex permission management process.

Note: Implement the simple permission model immediately after creating the database. If users or other data already exist, ensure the permissions associated with those objects are properly managed.

Customized Role List

Customized Role	Description	Comment
database_admin	Manage all objects in the database, add and delete users
database_devops	Manage all objects in the database, including creating and deleting Schema, Table, etc.
database_writer	Write access to tables and views in the current database
database_reader	Read access to tables and views in the current database

Creating Roles

CREATE ROLE database_admin WITH createrole;
CREATE ROLE database_devops;
CREATE ROLE database_writer;
CREATE ROLE database_reader;
 
GRANT pg_read_all_data TO database_reader;
GRANT pg_write_all_data TO database_writer;
 
GRANT database_reader TO database_devops;
GRANT database_writer TO database_devops;
GRANT pg_monitor TO database_devops;
 
-- Only admin has permission to manage roles
GRANT database_writer TO database_admin WITH admin option;
GRANT database_reader TO database_admin WITH admin option;
GRANT database_devops TO database_admin WITH admin option;
 
-- Set default privilege of public schema
GRANT all ON schema public TO database_admin WITH GRANT option;
GRANT all ON schema public TO database_devops;
 
-- Admin or devops should have permission to create schemas
GRANT all ON database '<current_database>' TO database_admin WITH GRANT option;
GRANT all ON database '<current_database>' TO database_devops WITH GRANT option;

Check Role Inheritance

SELECT
        CAST(a.rolname AS information_schema.sql_identifier) AS grantee,
        CAST(b.rolname AS information_schema.sql_identifier) AS role,
        m.grantor,
        CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS information_schema.yes_or_no) AS grantable
FROM (SELECT roleid, member, grantor, admin_option FROM pg_auth_members) m
        JOIN pg_authid a ON (m.member = a.oid)
        JOIN pg_authid b ON (m.roleid = b.oid);

Initialize Permissions for User

Execute the following operations for each existing user: (Please replace the user in the command with the intended target user)

-- It is recommended to use the Database Owner user to execute the following command
 
-- database_admin obtains permissions for all schemas, tables, and sequences, and can grant permissions individually
ALTER DEFAULT privileges FOR role "<user>" GRANT all ON schemas TO database_admin;
ALTER DEFAULT privileges FOR role "<user>" GRANT all ON tables TO database_admin;
ALTER DEFAULT privileges FOR role "<user>" GRANT all ON sequences TO database_admin;
 
-- database_devops obtains permissions for all schemas, tables, and sequences, and can be authorized individually
ALTER DEFAULT privileges FOR role "<user>" GRANT all ON schemas TO database_devops;
ALTER DEFAULT privileges FOR role "<user>" GRANT all ON tables TO database_devops;
ALTER DEFAULT privileges FOR role "<user>" GRANT all ON sequences TO database_devops;
 
-- database_admin obtains permissions for all schemas, tables, and sequences, and can grant permissions individually
ALTER DEFAULT privileges GRANT all ON schemas TO database_admin;
ALTER DEFAULT privileges GRANT all ON tables TO database_admin;
ALTER DEFAULT privileges GRANT all ON sequences TO database_admin;
 
-- database_devops obtains permissions for all schemas, tables, and sequences, and can be authorized individually
ALTER DEFAULT privileges GRANT all ON schemas TO database_devops;
ALTER DEFAULT privileges GRANT all ON tables TO database_devops;
ALTER DEFAULT privileges GRANT all ON sequences TO database_devops;

Adding and Deleting Users

-- It is recommended to use the Database Owner user to execute the following command
CREATE USER <user>;

Once you've added a new user, setting up their permissions is necessary.

User Authorization

To manage user role permissions effectively, start by assigning the role of database_admin.

database_admin administration

-- grant
GRANT database_admin TO <user>;
 
-- revoke
REVOKE database_admin FROM <user>;

database_devops administration

-- grant
GRANT database_devops TO <user>;
 
-- revoke
REVOKE database_devops FROM <user>;

database_writer administration

-- grant
GRANT database_writer TO <user>;
 
-- revoke
REVOKE database_writer FROM <user>;

database_reader administration

-- grant
GRANT database_reader TO <user>;
 
-- revoke
REVOKE database_reader FROM <user>;

Simple Permission Model Practice

On this page