Privilege Management System

Overview

TacNode implements a PostgreSQL-compatible privilege management system with optimizations and extensions for cloud-native architecture. This document provides a comprehensive guide to TacNode's privilege management mechanisms and key differences from standard PostgreSQL.

Core Features:

• Database-Level User Management: Independent user systems for each database instance
• Role-Based Access Control: Flexible role hierarchy with inheritance capabilities
• Enterprise Integration: Seamless cloud account integration with database users
• PostgreSQL Compatibility: Standard SQL privilege commands and permission models
• Security Best Practices: Built-in support for principle of least privilege

Key Differences from PostgreSQL

1. User Management Scope

PostgreSQL:

• Users are global across all database instances
• A user created in one PostgreSQL instance can be used in all databases

TacNode:

• Each database requires independent user creation
• User management is database-scoped, with users isolated between different databases

2. Cloud Platform Integration

PostgreSQL:

• No platform-level account concept
• User management is entirely database-level

TacNode:

• Integrated cloud service with platform account system
• Cloud accounts are automatically added to databases when creating instances
• Mapping relationship exists between cloud accounts and database users

TacNode Privilege Architecture

TacNode's privilege system consists of several hierarchical layers:

1. Global User Management

Global users are registered at the TacNode platform level and can use unified identity across multiple database instances.

2. Database User Management

Each database instance can independently manage local users and roles, visible only within the current database.

3. Role-Based Access Control

Implements fine-grained privilege control through predefined and custom roles.

User and Role Types

Global User

• Visibility: Visible across the entire TacNode platform
• Reference: Can be referenced in any database
• Password Management: Stored in platform services, cannot be managed via SQL
• Authorization: Cannot be granted to other users or roles (only serves as final privilege recipient)

Local User

• Visibility: Only visible within the current database
• Password Management: Can set passwords and manage via SQL
• Authorization: Can be granted to role groups and serve as role group members

Local Role

• Visibility: Only visible within the current database
• Password: No password attribute
• Purpose: Primarily used for privilege grouping and role inheritance

Practical Implementation Scenarios

Scenario 1: Enterprise-Grade Privilege Management

Business Requirements: An e-commerce enterprise needs different data access privileges for team members:

• Data Analysts: Read-only access to specific business data for generating reports
• BI Reporting System: Read access to source data and write access to reporting data for automated report generation
• Database Administrators: Full access privileges for daily maintenance and security management

Implementation Solution:

First, create specialized roles for different responsibilities and assign appropriate privileges:

-- Create roles for different responsibilities
CREATE ROLE data_analyst;
CREATE ROLE bi_system;
CREATE ROLE db_admin WITH CREATEROLE;

Assign read-only privileges to the data analyst role for all tables in the public schema:

-- Grant SELECT privileges to data_analyst role for all tables, views, and materialized views in public schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_analyst;
 
-- Grant USAGE privileges to access public schema
GRANT USAGE ON SCHEMA public TO data_analyst;

Set default privileges so the data analyst role has read-only access to future tables:

-- Set default privileges so data_analyst role has SELECT privileges on future tables in public schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO data_analyst;

Create a dedicated reporting schema for the BI system role and assign appropriate privileges:

-- Create dedicated reporting schema for BI system
CREATE SCHEMA bi_reports;
 
-- Grant full access to all tables in bi_reports schema
GRANT ALL ON ALL TABLES IN SCHEMA bi_reports TO bi_system;
 
-- Grant USAGE and CREATE privileges for using and creating objects in the schema
GRANT USAGE, CREATE ON SCHEMA bi_reports TO bi_system;

Set default privileges for the BI system role on future objects:

-- Set default privileges so bi_system role has full access to future tables in bi_reports schema
ALTER DEFAULT PRIVILEGES IN SCHEMA bi_reports GRANT ALL ON TABLES TO bi_system;

Create specific user accounts (note: these users must be registered on the TacNode platform first):

CREATE USER "analyst@company.com";
CREATE USER "bi-system@company.com";
CREATE USER "admin@company.com";

Assign roles to corresponding users:

GRANT data_analyst TO "analyst@company.com";
GRANT bi_system TO "bi-system@company.com";
GRANT db_admin TO "admin@company.com";

Privilege Verification:

After configuration, verify that privileges are correctly set:

-- Connect as data analyst and execute
-- Should succeed
SELECT * FROM products LIMIT 1;
SELECT * FROM sales_view LIMIT 1;
 
-- Should fail (insufficient privileges)
INSERT INTO products VALUES (1, 'Test Product');
UPDATE products SET name = 'New Name' WHERE id = 1;
DELETE FROM products WHERE id = 1;
 
-- Connect as BI system and execute
-- Should succeed
SELECT * FROM source_data LIMIT 1;
CREATE TABLE bi_reports.monthly_summary AS SELECT * FROM source_data;
INSERT INTO bi_reports.report_table VALUES (1, 'Report Data');
 
-- Should fail (cannot create tables in public schema)
CREATE TABLE public.new_table (id int);

Scenario 2: Simplified Privilege Model

Business Requirements: Startups or small teams need to quickly establish a privilege management system while simplifying privilege management complexity:

• Developers: Development environment read-write privileges for application development and testing
• Data Analysts: Read-only privileges for data analysis and report creation
• Administrators: Complete privileges including user management and privilege assignment capabilities

Implementation Solution:

First, create four basic roles corresponding to different privilege levels:

-- Create predefined roles
CREATE ROLE database_admin WITH CREATEROLE;
CREATE ROLE database_devops;
CREATE ROLE database_writer;
CREATE ROLE database_reader;

Assign corresponding data access privileges to these roles:

-- database_reader role has read-only privileges for all data
GRANT pg_read_all_data TO database_reader;
 
-- database_writer role has read-write privileges for all data
GRANT pg_write_all_data TO database_writer;

Set default privileges for future objects:

-- Set default privileges for database_reader role on future tables, views, and materialized views
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO database_reader;
 
-- Set default privileges for database_writer role on future tables
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO database_writer;
 
-- Set default privileges for database_writer role on future sequences
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO database_writer;

Establish inheritance relationships between roles so higher-level roles automatically inherit lower-level role privileges:

-- database_devops role inherits database_reader and database_writer privileges
GRANT database_reader TO database_devops;
GRANT database_writer TO database_devops;
 
-- database_devops role also has monitoring privileges
GRANT pg_monitor TO database_devops;

Assign management privileges for other roles to administrator role:

-- database_admin role has management privileges for other roles (including ability to grant further)
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;

Privilege Verification:

After configuration, verify that privileges are correctly set:

-- Connect as database_reader role user
-- Should succeed
SELECT * FROM any_table LIMIT 1;
SELECT * FROM any_view LIMIT 1;
 
-- Should fail (insufficient privileges)
INSERT INTO any_table VALUES (1, 'Test');
UPDATE any_table SET column = 'value';
DELETE FROM any_table;
 
-- Connect as database_devops role user
-- Should succeed
SELECT * FROM any_table LIMIT 1;
INSERT INTO any_table VALUES (1, 'Test');
UPDATE any_table SET column = 'value';
DELETE FROM any_table;
CREATE TABLE new_table (id int);
CREATE FUNCTION test_function() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;
 
-- Connect as database_admin role user
-- Should succeed with all operations, including user management
CREATE USER new_user WITH PASSWORD 'password';
GRANT database_reader TO new_user;
REVOKE database_reader FROM existing_user;

Privilege Verification

After completing privilege configuration, verifying successful authorization is crucial for ensuring system security. Proper verification methods can confirm whether users and roles have expected access privileges.

Verifying User Privileges

1. Connect to Database Using psql

# Connect to database as specific user
psql -U username -d database_name -h hostname -p port

2. Check Current User Privileges

After connecting to the database, execute the following SQL commands to view current user privileges:

-- View current user information
SELECT current_user, session_user;
 
-- View current user's role memberships
SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, oid, 'member');

3. Check Object Privileges

-- View privileges for specific tables
SELECT grantee, privilege_type FROM information_schema.table_privileges 
WHERE table_name = 'table_name';
 
-- View schema privileges
SELECT grantee, privilege_type FROM information_schema.schema_privileges 
WHERE schema_name = 'schema_name';

Verifying Role Privileges

1. View Role Definitions

-- View role attributes
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole FROM pg_roles 
WHERE rolname = 'role_name';

2. View Role Inheritance Relationships

-- View role membership relationships
SELECT 
    r1.rolname AS member,
    r2.rolname AS role
FROM pg_auth_members m
JOIN pg_roles r1 ON r1.oid = m.member
JOIN pg_roles r2 ON r2.oid = m.roleid
WHERE r1.rolname = 'user_name' OR r2.rolname = 'role_name';

3. View Default Privileges

-- View default privilege settings
SELECT 
    pg_get_userbyid(defaclrole) AS owner,
    nspname AS schema,
    defaclobjtype AS object_type,
    array_to_string(defaclacl, ', ') AS privileges
FROM pg_default_acl
LEFT JOIN pg_namespace n ON n.oid = defaclnamespace;

Best Practices

1. Principle of Least Privilege

Only grant users the minimum privileges required to complete their work, avoiding over-authorization.

2. Role Reuse

Create common roles to avoid setting privileges individually for each user, facilitating subsequent maintenance.

3. Regular Auditing

Regularly review user privilege assignments and promptly clean up unnecessary privileges.

4. Security Auditing

Record privilege change logs for security auditing and issue tracking.

5. Privilege Verification Best Practices

1. Regular Verification: Periodically check privilege configurations to ensure compliance with security requirements
2. Minimum Privilege Verification: Verify users have only the minimum privileges needed to complete their work
3. Role Inheritance Verification: Verify role inheritance relationships are correctly configured
4. Default Privilege Verification: Verify default privileges are correctly applied to newly created objects
5. Audit Log Review: Check audit logs for privilege-related operations

PostgreSQL Compatibility

Although TacNode differs from PostgreSQL in user management scope and cloud platform integration, it maintains high compatibility in the following areas:

1. SQL Syntax: Commands like CREATE USER, CREATE ROLE, GRANT, REVOKE have identical syntax to PostgreSQL
2. Privilege Model: Privilege types and behaviors like SELECT, INSERT, UPDATE, DELETE are identical to PostgreSQL
3. Role Inheritance: Role inheritance mechanisms and privilege transfer methods are consistent with PostgreSQL
4. Predefined Roles: Predefined roles like pg_read_all_data, pg_write_all_data are identical to PostgreSQL

Troubleshooting

Common Issues

1. User Not Registered Error: Ensure users are registered on the TacNode platform before creating database users
2. Insufficient Privileges Error: Check whether users have the privileges required to perform operations
3. Connection Rejected Error: Confirm whether users have database connection privileges

Troubleshooting Steps

1. Confirm whether users are registered on the platform
2. Check whether users have been correctly authorized to appropriate roles
3. Verify whether corresponding users have been created in the database
4. Check security group and network configuration correctness

Advanced Configuration Examples

Multi-Environment Privilege Separation

-- Create environment-specific roles
CREATE ROLE dev_environment;
CREATE ROLE staging_environment;
CREATE ROLE production_environment;
 
-- Grant different privilege levels to different environments
GRANT ALL ON SCHEMA development TO dev_environment;
GRANT SELECT, INSERT, UPDATE ON SCHEMA staging TO staging_environment;
GRANT SELECT ON SCHEMA production TO production_environment;
 
-- Create environment-specific users
CREATE USER "dev-user@company.com";
CREATE USER "staging-user@company.com";
CREATE USER "prod-user@company.com";
 
-- Assign environment roles
GRANT dev_environment TO "dev-user@company.com";
GRANT staging_environment TO "staging-user@company.com";
GRANT production_environment TO "prod-user@company.com";

Application-Specific Privilege Management

-- Create application-specific roles
CREATE ROLE app_read_only;
CREATE ROLE app_read_write;
CREATE ROLE app_admin;
 
-- Set table-level privileges
GRANT SELECT ON user_profiles, order_history TO app_read_only;
GRANT SELECT, INSERT, UPDATE ON user_profiles, orders TO app_read_write;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_admin;
 
-- Create application service accounts
CREATE USER "app-service@company.com";
CREATE USER "app-admin@company.com";
 
-- Assign appropriate roles
GRANT app_read_write TO "app-service@company.com";
GRANT app_admin TO "app-admin@company.com";

This comprehensive privilege management system ensures that TacNode databases maintain enterprise-grade security while providing flexibility for various organizational structures and security requirements.