postgresql 用户常用的一些授权

最新推荐文章于 2025-01-27 20:05:16 发布

数据库人生

最新推荐文章于 2025-01-27 20:05:16 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

分类专栏： # postgresql role user priv 文章标签： grant revoke alter default privileges

本文链接：https://blog.csdn.net/ctypyb2002/article/details/102503894

postgresql role user priv 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍如何在PostgreSQL中修改数据库和schema的属主，以及如何批量授予用户在多个schema下的只读权限。通过执行匿名块，可以高效地对非默认public下的所有schema进行权限设置，确保新创建的表默认具有SELECT权限。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

修改 database 的属主

postgres=# \h alter database
postgres=# \h ALTER DATABASE database_xxxxx OWNER TO user_xxxxx;

修改 schema 的属主

postgres=# \h alter schema
postgres=# ALTER SCHEMA schema_xxxxx OWNER TO user_xxxxx;

用户非默认public下的所有表的只读权限

postgres=# GRANT USAGE ON SCHEMA schema_xxxxx TO user_xxxxx;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_xxxxx to user_xxxxx;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_xxxxx GRANT SELECT ON TABLES TO user_xxxxx;

当 schema 较多时可以执行一个匿名块

DO $$ 
DECLARE 
  r record;
  lv_user varchar;
BEGIN
  lv_user:='user_xxxxx';
  
  FOR r IN 
    SELECT schema_name 
	  FROM information_schema.schemata 
	 where 1=1
	   and schema_name not in ('pg_toast','pg_temp_1','pg_toast_temp_1','pg_catalog','public','information_schema') 
  LOOP     
    EXECUTE 'grant usage on schema '|| r.schema_name ||' to '||lv_user;
    EXECUTE 'grant select on all tables in schema '|| r.schema_name ||' to '||lv_user;
    EXECUTE 'alter default privileges in schema '|| r.schema_name ||' grant select on tables to '||lv_user;  
  END LOOP;
END $$;

一定要记得执行 ALTER DEFAULT PRIVILEGES IN SCHEMA，这样以后在该 SCHEMA 下新创建的 TABLE 也默认具有 SELECT 权限。

参考:
http://postgres.cn/docs/11/sql-alterdefaultprivileges.html