室内设计方案网站,深圳市官网,做pc端网站策划,苏州优化费用标签 PostgreSQL , 超级用户 , superuser 背景 在数据库中哪些操作需要超级用户的权限才能执行#xff1f; 这个问题翻文档可能翻不全面#xff0c;或者是已经比较难以完成的任务。 但是从源码里面是比较好找出这个答案的。 权限 例如 postgres# select * from pg_authid;rol… 标签 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 (-1no 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.cif (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.cDatum
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 /* cant 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 /* cant set if security restricted */
#define GUC_DISALLOW_IN_AUTO_FILE 0x0800 /* cant set insrc/backend/utils/misc/guc.cif (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.cif (!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 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_sharedpgstat_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;
