PostgreSQL , 超级用户 , superuser
在数据库中哪些操作需要超级用户的权限才能执行？
这个问题翻文档可能翻不全面，或者是已经比较难以完成的任务。
但是从源码里面是比较好找出这个答案的。
例如
postgres=# select * from pg_authid; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- postgres | t | t | t | t | t | t | t | -1 | | pg_signal_backend | f | t | f | f | f | f | f | -1 | | test | f | t | f | f | t | f | f | -1 | | dba | f | t | f | f | t | f | f | -1 | | test1 | f | t | f | f | f | f | f | -1 | | digoal | f | t | f | f | t | f | f | -1 | | a | f | t | f | f | t | f | f | -1 | | b | f | t | f | f | t | f | f | -1 | | (8 rows)rolsuper 就表示是否具备超级用户权限。
src/backend/utils/misc/superuser.c
/* * The Postgres user running this command has Postgres superuser privileges */ bool superuser(void) { return superuser_arg(GetUserId()); } /* * The specified role has Postgres superuser privileges */ bool superuser_arg(Oid roleid) { bool result; HeapTuple rtup; /* Quick out for cache hit */ if (OidIsValid(last_roleid) && last_roleid == roleid) return last_roleid_is_super; /* Special escape path in case you deleted all your users. */ if (!IsUnderPostmaster && roleid == BOOTSTRAP_SUPERUSERID) return true; /* OK, look up the information in pg_authid */ rtup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); if (HeapTupleIsValid(rtup)) { result = ((Form_pg_authid) GETSTRUCT(rtup))->rolsuper; // 最终还是读取pg_authid中的rolsuper字段 ReleaseSysCache(rtup); } else { /* Report "not superuser" for invalid roleids */ result = false; } /* If first time through, set up callback for cache flushes */ if (!roleid_callback_registered) { CacheRegisterSyscacheCallback(AUTHOID, RoleidCallback, (Datum) 0); roleid_callback_registered = true; } /* Cache the result for next time */ last_roleid = roleid; last_roleid_is_super = result; return result; }在PostgreSQL代码里面，通过FormData_pg_authid结构来表示pg_authid的表结构
CATALOG(pg_authid,1260) BKI_SHARED_RELATION BKI_ROWTYPE_OID(2842) BKI_SCHEMA_MACRO { NameData rolname; /* name of role */ bool rolsuper; /* read this field via superuser() only! */ bool rolinherit; /* inherit privileges from other roles? */ bool rolcreaterole; /* allowed to create more roles? */ bool rolcreatedb; /* allowed to create databases? */ bool rolcatupdate; /* allowed to alter catalogs manually? */ bool rolcanlogin; /* allowed to log in as session user? */ bool rolreplication; /* role used for streaming replication */ int32 rolconnlimit; /* max connections allowed (-1=no limit) */ /* remaining fields may be null; use heap_getattr to read them! */ text rolpassword; /* password, if any */ timestamptz rolvaliduntil; /* password expiration time, if any */ } FormData_pg_authid; #undef timestamptz /* ---------------- * Form_pg_authid corresponds to a pointer to a tuple with * the format of pg_authid relation. * ---------------- */ typedef FormData_pg_authid *Form_pg_authid;以此类推，
src/backend/utils/init/miscinit.c
/* * Check whether specified role has explicit REPLICATION privilege */ bool has_rolreplication(Oid roleid) { bool result = false; HeapTuple utup; utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); if (HeapTupleIsValid(utup)) { result = ((Form_pg_authid) GETSTRUCT(utup))->rolreplication; ReleaseSysCache(utup); } return result; }去代码里面看吧，举一些例子
1. 只有超级用户可以修改或设置file_fdw foreign table的options
contrib/file_fdw/file_fdw.c if (catalog == ForeignTableRelationId && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("only superuser can change options of a file_fdw foreign table")));2. 只有超级用户能读取数据库所在服务器的文件
src/backend/utils/adt/genfile.c Datum pg_read_file(PG_FUNCTION_ARGS) { ... if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to read files")))); Datum pg_read_binary_file(PG_FUNCTION_ARGS) { ... if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to read files"))));3. 只有超级用户可以查看需要超级用户权限才能查看的参数
src/include/utils/guc.h /* * bit values in "flags" of a GUC variable */ #define GUC_LIST_INPUT 0x0001 /* input can be list format */ #define GUC_LIST_QUOTE 0x0002 /* double-quote list elements */ #define GUC_NO_SHOW_ALL 0x0004 /* exclude from SHOW ALL */ #define GUC_NO_RESET_ALL 0x0008 /* exclude from RESET ALL */ #define GUC_REPORT 0x0010 /* auto-report changes to client */ #define GUC_NOT_IN_SAMPLE 0x0020 /* not in postgresql.conf.sample */ #define GUC_DISALLOW_IN_FILE 0x0040 /* can't set in postgresql.conf */ #define GUC_CUSTOM_PLACEHOLDER 0x0080 /* placeholder for custom variable */ #define GUC_SUPERUSER_ONLY 0x0100 /* show only to superusers */ #define GUC_IS_NAME 0x0200 /* limit string to NAMEDATALEN-1 */ #define GUC_NOT_WHILE_SEC_REST 0x0400 /* can't set if security restricted */ #define GUC_DISALLOW_IN_AUTO_FILE 0x0800 /* can't set in src/backend/utils/misc/guc.c if (restrict_superuser && (record->flags & GUC_SUPERUSER_ONLY) && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to examine \"%s\"", name))); ...4. 只有超级用户能执行alter system
src/backend/utils/misc/guc.c if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to execute ALTER SYSTEM command"))));5. 只有超级用户能创建language
src/backend/commands/proclang.c /* * Check permission */ if (!superuser()) { if (!pltemplate->tmpldbacreate) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to create procedural language \"%s\"", stmt->plname))); if (!pg_database_ownercheck(MyDatabaseId, GetUserId())) aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE, get_database_name(MyDatabaseId)); }如果你发现报错中包含了权限错误的问题，也可以使用这个方法找到对应的代码。
\set VERBOSITY verbose test=> \set VERBOSITY verbose test=> create role b replication; ERROR: 42501: must be superuser to create replication users LOCATION: CreateRole, user.c:319 对应的代码在user.c的319行, CreateRole函数中.就举这些例子，其他的可以自己看一下。
为了维护便利，阿里云RDS PostgreSQL开放了一个权限名为rds_superuser给用户，比superuser少一些权限，主要是文件操作相关的，并且不能越权做superuser能做的事情。
相比普通用户，rds_superuser多了以下权限。
-- 创建插件 create extension ?; -- 创建非superuser,repliction权限用户 create role ?; -- 非supuer owned对象的操作 alter table test.test rename to test1; alter table test.test1 owner to test_rdssuper; alter table test.test1 rename to test; alter table test.test owner to test_norm; -- SET SESSION AUTHORIZATION、SET ROLE可以set非superuser用户 set role to test_norm; reset role; -- 所有对象的vacuum、analyze操作 vacuum pg_class; analyze pg_class; -- pg_stat_reset (pgstat_reset_counters) pg_stat_reset_shared（pgstat_reset_shared_counters） select pg_stat_reset(); select pg_stat_reset_shared('bgwriter'); -- pgstat_reset_single_counter select pg_stat_reset_single_table_counters('test.test'::regclass::oid); select pg_stat_reset_single_function_counters('pg_stat_get_activity'::regproc::oid); -- pg_stat_get_activity pg_stat_get_backend_activity pg_stat_get_backend_waiting pg_stat_get_backend_activity_start pg_stat_get_backend_xact_start pg_stat_get_backend_start pg_stat_get_backend_client_addr pg_stat_get_backend_client_port -- select pg_stat_get_activity() SELECT pg_stat_get_backend_pid(s.backendid) AS pid, pg_stat_get_backend_activity(s.backendid) AS query,pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_xact_start(s.backendid) AS xact_start, pg_stat_get_backend_start(s.backendid) AS bd_start, pg_stat_get_backend_client_addr(s.backendid) AS ip, pg_stat_get_backend_client_port(s.backendid) AS port FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; -- pgstatindex pgstatindexbyid pgstatginindex pg_relpages pg_relpagesbyid pgstattuple create extension pgstattuple; SELECT * FROM pgstattuple('pg_catalog.pg_proc'); SELECT * FROM pgstatindex('pg_cast_oid_index'); SELECT * FROM pgstatginindex('test_gin_index'); -- pg_stat_statments -- 中文分词相关 CREATE EXTENSION zhparser; CREATE TEXT SEARCH CONFIGURATION testzhcfg (PARSER = zhparser); ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; -- finish prepared transaction，提交或回滚两阶段提交的事务 \c - test_norm begin; insert into test.test values(2); prepare transaction 't1'; \c - test_rdssuper select * from pg_prepared_xacts ; commit prepared 't1'; -- alter type owner \c - test_norm CREATE TYPE compfoo AS (f1 int, f2 text); \c - test_rdssuper alter type compfoo owner to test_rdssuper; -- 允许rds_superuser用户设置session_preload_libraries，以便于用户使用pg_hint_plan等 alter role all set session_preload_libraries = 'pg_hint_plan'; alter role all set set pg_hint_plan.debug_print = on; \c - superuser /*+ SeqScan(test.test) */ select * from test.test; \c - test_rdssuper /*+ SeqScan(test.test) */ select * from test.test; -- 支持设置session_preload_libraries，设置的值必须是rds_available_extensions里面的插件 alter role all set session_preload_libraries = 'plperlu'; -- ERROR: invalid value for parameter "session_preload_libraries": "plperlu" alter role all set session_preload_libraries = 'plperl,postgis'; -- grant role to grant test_norm to test_rdssuper; -- revoke role from revoke test_norm from test_rdssuper; -- rds_superuser可以set SET SESSION AUTHORIZATION ?; -- 允许rds_superuser通过dblink连接本地数据库,不需要配置port,host,ip,只需要指定dbname -- 支持oss_fdw -- 创建RDS实例时的超级用户自带了replication角色，允许用来做流复制 -- 允许rds_superuser设置temp_file_limit参数 -- 允许rds_superuser修改schema grant all on schema test to test_another; 相关资源：七夕情人节表白HTML源码(两款)