55 角色与 GRANT

-- Inspect the current session
SELECT current_user, session_user;

-- Create ordinary roles (requires CREATEROLE or superuser privileges)
-- CREATE ROLE app_read NOLOGIN;                                 -- a "group" role
-- CREATE ROLE alice LOGIN PASSWORD 'change-me' IN ROLE app_read;
-- CREATE ROLE app_write NOLOGIN;

-- Grant access to a schema and read on tables
-- GRANT USAGE  ON SCHEMA public TO app_read;
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

-- Future tables in this schema also get SELECT for app_read
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public
--   GRANT SELECT ON TABLES TO app_read;

-- Write-level
-- GRANT app_read TO app_write;                          -- inheritance: app_write also reads
-- GRANT INSERT, UPDATE, DELETE ON fruits TO app_write;

-- Revoke
-- REVOKE INSERT ON fruits FROM app_write;

-- Inspect your privileges on a table
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'fruits' AND grantee = current_user;

-- Row-level security: per-row visibility decided by a policy
-- (demo on a separate table so we don't change `fruits` permissions)
DROP TABLE IF EXISTS tenant_docs;
CREATE TABLE tenant_docs (
  id       integer PRIMARY KEY,
  tenant   text NOT NULL,
  content  text NOT NULL
);
INSERT INTO tenant_docs VALUES (1, 'acme', 'hello'), (2, 'globex', 'world');

ALTER TABLE tenant_docs ENABLE ROW LEVEL SECURITY;
-- Apply the policy to the table owner as well (superusers/BYPASSRLS still bypass it).
ALTER TABLE tenant_docs FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tenant_docs
  USING (tenant = current_setting('app.tenant', true));

-- Set the tenant for the session and try a select
SET app.tenant TO 'acme';
SELECT * FROM tenant_docs;        -- only sees the 'acme' row

SET app.tenant TO 'globex';
SELECT * FROM tenant_docs;        -- only sees the 'globex' row

-- Cleanup
DROP POLICY IF EXISTS tenant_isolation ON tenant_docs;
DROP TABLE IF EXISTS tenant_docs;