作者
digoal
日期
2016-12-07
标签
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; ```
代码中如何判断用户是否有replication的权限
以此类推，
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;复制
} ```
人肉时间, 找出需要判断超级用户的地方
``` 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 GRANT src/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.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_superuser和superuser有什么区别
为了维护便利，阿里云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; ```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
