PostgreSQL從原始碼找出哪些操作需要超級使用者許可權-阿里雲rds_superuser和superuser有什麼區別
標籤
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;
相關文章
- Linux配置IP地址需要什麼許可權？可以執行哪些操作？Linux
- 實現直播app原始碼前端許可權設計，需要做什麼？APP原始碼前端
- 淺談PostgreSQL使用者許可權SQL
- CDB和PDB關於使用者建立和使用者許可權區別
- 如何檢視postgresql使用者許可權SQL
- [20180417]使用10046事件需要什麼許可權.txt事件
- 阿里雲RDS的高許可權不是真正的高許可權阿里
- PostgreSQL：許可權管理SQL
- PostgreSQL資料庫使用者許可權管理SQL資料庫
- 做什麼操作會丟失其他使用者對錶的許可權
- 一對一原始碼，前端頁面許可權和按鈕許可權控制原始碼前端
- cython和python分別是什麼？區別有哪些？Python
- postgresql關於訪問檢視需要的許可權SQL
- mysql 8.0.21使用者及許可權操作MySql
- MYSQL學習筆記13: DCL許可權控制(使用者許可權操作)MySql筆記
- Gate 超級管理員擁有全部許可權踩坑點
- 超級簽名是什麼？超級簽名跟企業簽名有什麼區別？
- 雲原生和雲端計算分別指什麼？有什麼區別？
- 群控和雲控有什麼區別？
- PostgreSQL_通過schema控制使用者許可權SQL
- Django（63）drf許可權原始碼分析與自定義許可權Django原始碼
- HIVE的許可權控制和超級管理員的實現Hive
- vue要做許可權管理該怎麼做？如果控制到按鈕級別的許可權怎麼做？Vue
- 使用者和組的許可權
- 讓root使用者有super許可權
- Linux中檔案的特殊許可權有幾種？分別是什麼？Linux
- 特網雲 CPU和GPU有什麼區別GPU
- MySQL如何查詢all有哪些許可權?MySql
- OA管理系統，有哪些許可權管理？
- 阿里雲輕量應用伺服器和ECS雲伺服器有什麼區別?阿里伺服器
- 阿里雲國際站雲伺服器和國內站產品有什麼區別？阿里伺服器
- 什麼是DNS雲解析？雲解析和普通解析有什麼區別？DNS
- linux 檔案許可權 s 許可權和 t 許可權解析Linux
- mysql 給了使用者所有許可權ALL PRIVILEGES，但是該使用者沒有grant許可權MySql
- 【程式碼實驗室】.->和.有什麼區別？
- Python指令碼和網頁有什麼區別?Python指令碼網頁
- Spring安全的角色和許可權原始碼與教程 - javadevjournalSpring原始碼Javadev
- 駭客型別有哪些?他們有什麼區別?型別
- 一個好的代理IP有哪些許可權？