本文共 20454 字，大约阅读时间需要 68 分钟。
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 */boolsuperuser(void){ return superuser_arg(GetUserId());}/* * The specified role has Postgres superuser privileges */boolsuperuser_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 */boolhas_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;} grep -r "superuser()" *contrib/file_fdw/file_fdw.c: if (catalog == ForeignTableRelationId && !superuser())contrib/dblink/dblink.c: if (!superuser())contrib/dblink/dblink.c: if (!superuser())contrib/pgstattuple/pgstatindex.c: if (!superuser())contrib/pgstattuple/pgstatindex.c: if (!superuser())contrib/pgstattuple/pgstatindex.c: if (!superuser())contrib/pgstattuple/pgstatindex.c: if (!superuser())contrib/pgstattuple/pgstatindex.c: if (!superuser())contrib/pgstattuple/pgstattuple.c: if (!superuser())contrib/pgstattuple/pgstattuple.c: if (!superuser())contrib/pgstattuple/pgstatapprox.c: if (!superuser())contrib/pg_stat_statements/pg_stat_statements.c: bool is_superuser = superuser();contrib/sepgsql/label.c: if (!superuser())contrib/pageinspect/brinfuncs.c: if (!superuser())contrib/pageinspect/brinfuncs.c: if (!superuser())contrib/pageinspect/brinfuncs.c: if (!superuser())contrib/pageinspect/brinfuncs.c: if (!superuser())contrib/pageinspect/fsmfuncs.c: if (!superuser())contrib/pageinspect/ginfuncs.c: if (!superuser())contrib/pageinspect/ginfuncs.c: if (!superuser())contrib/pageinspect/ginfuncs.c: if (!superuser())contrib/pageinspect/heapfuncs.c: if (!superuser())contrib/pageinspect/heapfuncs.c: if (!superuser())contrib/pageinspect/btreefuncs.c: if (!superuser())contrib/pageinspect/btreefuncs.c: if (!superuser())contrib/pageinspect/btreefuncs.c: if (!superuser())contrib/pageinspect/rawpage.c: if (!superuser())contrib/pageinspect/rawpage.c: if (!superuser())contrib/adminpack/adminpack.c: if (!superuser())contrib/adminpack/adminpack.c: if (!superuser())contrib/postgres_fdw/connection.c: if (!superuser() && !PQconnectionUsedPassword(conn))contrib/postgres_fdw/connection.c: if (superuser())src/include/catalog/pg_authid.h: bool rolsuper; /* read this field via superuser() only! */src/test/modules/dummy_seclabel/dummy_seclabel.c: if (!superuser())src/test/regress/regress.c: if (!superuser())src/test/regress/regress.c: if (!superuser())src/backend/commands/copy.c: if (!pipe && !superuser())src/backend/commands/trigger.c: if (!superuser())src/backend/commands/dbcommands.c: (encoding == PG_SQL_ASCII && superuser())))src/backend/commands/dbcommands.c: (encoding == PG_SQL_ASCII && superuser())))src/backend/commands/dbcommands.c: if (superuser())src/backend/commands/opclasscmds.c: if (!superuser())src/backend/commands/opclasscmds.c: if (!superuser())src/backend/commands/opclasscmds.c: if (!superuser())src/backend/commands/tablespace.c: if (!superuser())src/backend/commands/event_trigger.c: if (!superuser())src/backend/commands/foreigncmds.c: if (!superuser())src/backend/commands/foreigncmds.c: if (!superuser())src/backend/commands/foreigncmds.c: if (!superuser())src/backend/commands/foreigncmds.c: if (!superuser())src/backend/commands/tsearchcmds.c: if (!superuser())src/backend/commands/tsearchcmds.c: if (!superuser())src/backend/commands/amcmds.c: if (!superuser())src/backend/commands/amcmds.c: if (!superuser())src/backend/commands/functioncmds.c: if (!superuser())src/backend/commands/functioncmds.c: if (isLeakProof && !superuser())src/backend/commands/functioncmds.c: if (procForm->proleakproof && !superuser())src/backend/commands/functioncmds.c: if (!superuser())src/backend/commands/functioncmds.c: if (!superuser())src/backend/commands/tablecmds.c: if (!superuser())src/backend/commands/extension.c: if (control->superuser && !superuser())src/backend/commands/aggregatecmds.c: if (transTypeId == INTERNALOID && superuser())src/backend/commands/aggregatecmds.c: if (mtransTypeId == INTERNALOID && superuser())src/backend/commands/alter.c: if (!superuser())src/backend/commands/alter.c: if (!superuser())src/backend/commands/alter.c: if (!superuser())src/backend/commands/proclang.c: if (!superuser())src/backend/commands/proclang.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: !superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/user.c: if (grantorId != GetUserId() && !superuser())src/backend/commands/user.c: if (!superuser())src/backend/commands/typecmds.c: if (!superuser())src/backend/commands/typecmds.c: if (!superuser())src/backend/libpq/be-fsstubs.c: if (!superuser())src/backend/libpq/be-fsstubs.c: if (!superuser())src/backend/catalog/system_views.sql:-- than use explicit 'superuser()' checks in those functions, we use the GRANTsrc/backend/catalog/pg_proc.c: (superuser() ? PGC_SUSET : PGC_USERSET),src/backend/tcop/utility.c: load_file(stmt->filename, !superuser());src/backend/tcop/utility.c: if (!superuser())src/backend/postmaster/pgstat.c: if (checkUser && !superuser() && beentry->st_userid != GetUserId())src/backend/replication/slotfuncs.c: if (!superuser() && !has_rolreplication(GetUserId()))src/backend/replication/walreceiver.c: if (!superuser())src/backend/replication/walsender.c: if (!superuser())src/backend/replication/logical/origin.c: if (!superuser())src/backend/replication/logical/logicalfuncs.c: if (!superuser() && !has_rolreplication(GetUserId()))src/backend/utils/fmgr/fmgr.c: (superuser() ? PGC_SUSET : PGC_USERSET),src/backend/utils/misc/guc.c: !superuser())src/backend/utils/misc/guc.c: if ((record->flags & GUC_SUPERUSER_ONLY) && !superuser())src/backend/utils/misc/guc.c: if (!superuser())src/backend/utils/misc/guc.c: (superuser() ? PGC_SUSET : PGC_USERSET),src/backend/utils/misc/guc.c: (superuser() ? PGC_SUSET : PGC_USERSET),src/backend/utils/misc/guc.c: (superuser() ? PGC_SUSET : PGC_USERSET),src/backend/utils/misc/guc.c: (superuser() ? PGC_SUSET : PGC_USERSET),src/backend/utils/misc/guc.c: bool am_superuser = superuser();src/backend/utils/misc/guc.c: if ((record->flags & GUC_SUPERUSER_ONLY) && !superuser())src/backend/utils/misc/guc.c: ((conf->flags & GUC_SUPERUSER_ONLY) && !superuser()))src/backend/utils/misc/guc.c: if (conf->source == PGC_S_FILE && superuser())src/backend/utils/misc/guc.c: if (superuser())src/backend/utils/misc/guc.c: if (superuser())src/backend/utils/misc/guc.c: else if (gconf->context == PGC_SUSET && superuser())src/backend/utils/misc/guc.c: superuser() ? PGC_SUSET : PGC_USERSET,src/backend/utils/misc/superuser.c: * The superuser() function. Determines if user has superuser privilege.src/backend/utils/init/postinit.c: am_superuser = superuser();src/backend/utils/init/postinit.c: am_superuser = superuser();src/backend/utils/init/postinit.c: if (!superuser() && !has_rolreplication(GetUserId()))src/backend/utils/adt/misc.c: if (superuser_arg(proc->roleId) && !superuser())src/backend/utils/adt/genfile.c: if (!superuser())src/backend/utils/adt/genfile.c: if (!superuser())src/backend/utils/adt/genfile.c: if (!superuser())src/backend/utils/adt/genfile.c: if (!superuser())src/bin/psql/prompt.c: if (is_superuser())
去代码里面看吧，举一些例子
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.cDatumpg_read_file(PG_FUNCTION_ARGS){... if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), (errmsg("must be superuser to read files"))));Datumpg_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 insrc/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 verbosetest=> \set VERBOSITY verbosetest=> create role b replication;ERROR: 42501: must be superuser to create replication usersLOCATION: 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_counterselect 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_normbegin;insert into test.test values(2);prepare transaction 't1';\c - test_rdssuperselect * from pg_prepared_xacts ;commit prepared 't1';-- alter type owner\c - test_normCREATE TYPE compfoo AS (f1 int, f2 text); \c - test_rdssuperalter 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 togrant test_norm to test_rdssuper;-- revoke role fromrevoke test_norm from test_rdssuper;-- rds_superuser可以setSET SESSION AUTHORIZATION ?;-- 允许rds_superuser通过dblink连接本地数据库,不需要配置port,host,ip,只需要指定dbname-- 支持oss_fdw-- 创建RDS实例时的超级用户自带了replication角色，允许用来做流复制-- 允许rds_superuser设置temp_file_limit参数-- 允许rds_superuser修改schemagrant all on schema test to test_another; 转载地址：http://ovpxx.baihongyu.com/