PostgreSQL: PostgreSQL用户、角色与权限管理

-- Group roles (cannot login)
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team NOINHERIT;
CREATE ROLE audit_team NOINHERIT;

-- Login users
-- ⚠️ 生产环境不要硬编码密码，应使用环境变量或密钥管理服务
CREATE USER alice_dev PASSWORD 'SecureP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'SecureP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'SecureP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'SecureP@ss4' IN ROLE ops_team INHERIT;

CREATE TABLE

-- Add CREATEDB privilege to ops team lead
ALTER ROLE diana_ops CREATEDB;

-- Set password expiry
ALTER ROLE charlie_analyst VALID UNTIL '2025-12-31';

-- Rename a role
ALTER ROLE dev_team RENAME TO engineering_team;

-- Disable login temporarily
ALTER ROLE bob_dev NOLOGIN;

CREATE TABLE

-- Dev team: full CRUD on business tables
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO dev_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON order_items TO dev_team;

-- Analytics team: read-only
GRANT SELECT ON products TO analytics_team;
GRANT SELECT ON orders TO analytics_team;
GRANT SELECT ON customers TO analytics_team;

-- Audit team: only audit logs
GRANT SELECT ON price_audit_log TO audit_team;
GRANT SELECT ON ddl_audit_log TO audit_team;

INSERT 0 1

-- Dev team can create objects in dev schema
GRANT CREATE, USAGE ON SCHEMA dev TO dev_team;

-- Analytics team can use but not create in public schema
GRANT USAGE ON SCHEMA public TO analytics_team;

-- Allow analytics to create materialized views in their own schema
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;

-- App service connects to the database
GRANT CONNECT ON DATABASE shop_db TO app_service;

CREATE TABLE

-- Revoke DELETE from dev team on products
REVOKE DELETE ON products FROM dev_team;

-- Revoke all privileges on a table
REVOKE ALL PRIVILEGES ON orders FROM public;

-- Revoke schema creation right
REVOKE CREATE ON SCHEMA dev FROM dev_team;

-- Cascade: revoke and dependent grants
REVOKE SELECT ON products FROM analytics_team CASCADE;

DELETE 2

-- All future tables in public schema are readable by analytics_team
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;

-- All future tables created by app_service are writable by dev_team
ALTER DEFAULT PRIVILEGES FOR ROLE app_service IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO dev_team;

-- All future sequences in public schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO dev_team;

-- All future functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT EXECUTE ON FUNCTIONS TO analytics_team;

INSERT 0 1

SELECT
  pg_get_userbyid(defaclrole) AS grantor,
  pg_get_userbyid(defaclnamespace) AS namespace_owner,
  n.nspname AS schema,
  defaclobjtype AS object_type,
  defaclacl AS acl
FROM pg_default_acl d
JOIN pg_namespace n ON n.oid = d.defaclnamespace;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Enable RLS on orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Analysts can only see completed orders
CREATE POLICY pol_analytics_completed
  ON orders FOR SELECT
  TO analytics_team
  USING (order_status = 'completed');

-- Dev team can see all rows
CREATE POLICY pol_dev_all
  ON orders FOR ALL
  TO dev_team
  USING (true)
  WITH CHECK (true);

CREATE TABLE

-- Enable RLS on customers table
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

-- Each customer service user sees only their assigned region
CREATE POLICY pol_region_access
  ON customers FOR SELECT
  USING (region = current_setting('app.region', true));

-- Force table owner to also comply with RLS
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE TABLE

-- Sales team can only insert orders in their region
CREATE POLICY pol_sales_insert
  ON orders FOR INSERT
  TO dev_team
  WITH CHECK (region = current_setting('app.region', true));

-- Sales team can only update orders in their region
CREATE POLICY pol_sales_update
  ON orders FOR UPDATE
  TO dev_team
  USING (region = current_setting('app.region', true))
  WITH CHECK (region = current_setting('app.region', true));

INSERT 0 1

-- Create a powerful role with NOINHERIT
CREATE ROLE admin_role NOINHERIT;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

-- Diana is in admin_role but cannot use admin privileges automatically
CREATE USER diana PASSWORD 'SecureP@ss5' IN ROLE admin_role NOINHERIT;

-- Diana must explicitly switch to use admin rights
SET ROLE admin_role;
-- Now diana can perform admin operations
RESET ROLE;
-- Back to normal privileges

CREATE TABLE

-- List all roles and their attributes
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolinherit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;

-- List role membership
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
ORDER BY r2.rolname, r1.rolname;

-- Check table privileges for a role
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'analytics_team'
ORDER BY table_name, privilege_type;

CREATE TABLE

-- Step 1: Create group roles
CREATE ROLE dev_team NOINHERIT;
CREATE ROLE analytics_team NOINHERIT;
CREATE ROLE ops_team INHERIT;
CREATE ROLE audit_team NOINHERIT;
CREATE ROLE app_service LOGIN PASSWORD 'AppSecRet!';

-- Step 2: Grant schema permissions
GRANT CREATE, USAGE ON SCHEMA public TO dev_team;
GRANT USAGE ON SCHEMA public TO analytics_team;
GRANT CREATE, USAGE ON SCHEMA analytics TO analytics_team;
GRANT USAGE ON SCHEMA public TO ops_team;
GRANT USAGE ON SCHEMA audit TO audit_team;

-- Step 3: Grant table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dev_team;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA audit TO audit_team;
GRANT SELECT, INSERT, UPDATE, DELETE ON products, orders, order_items, customers TO app_service;

-- Step 4: Default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dev_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit
  GRANT SELECT ON TABLES TO audit_team;

-- Step 5: Sequence and function permissions
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dev_team;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO dev_team;

-- Step 6: RLS - analysts only see completed orders
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_analytics_orders
  ON orders FOR SELECT
  TO analytics_team
  USING (order_status = 'completed');

-- Step 7: RLS - audit team only sees audit schema
ALTER TABLE price_audit_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY pol_audit_read
  ON price_audit_log FOR SELECT
  TO audit_team
  USING (true);

-- Step 8: Create login users and assign roles
CREATE USER alice_dev PASSWORD 'DevP@ss1' IN ROLE dev_team;
CREATE USER bob_dev PASSWORD 'DevP@ss2' IN ROLE dev_team;
CREATE USER charlie_analyst PASSWORD 'AnlP@ss3' IN ROLE analytics_team;
CREATE USER diana_ops PASSWORD 'OpsP@ss4' IN ROLE ops_team CREATEROLE;
CREATE USER eve_audit PASSWORD 'AudP@ss5' IN ROLE audit_team;