Postgresql 添加用户，设置表权限

最新推荐文章于 2024-09-23 13:15:00 发布

zengqun89

最新推荐文章于 2024-09-23 13:15:00 发布

阅读量6.8k

点赞数

文章标签：数据库 database postgresql

本文链接：https://blog.csdn.net/zengqun89/article/details/125338461

版权

一、添加用户，带过期时间
CREATE USER xxuser WITH PASSWORD 'xxpassword' VALID UNTIL '2022-11-30 23:00';
GRANT xxrole TO xxuser;

GRANT ALL PRIVILEGES ON table
xxtable1,xxtable2,xxtable3
to xxuser;

二、postgres 只读账号，设置某个用户，表的权限

   -- postgres 删除用户权限，重新授权表
   -- 删除用户对表的增删查改权限
   -- 1、变更表的所有人，
   ALTER TABLE xxtable1 OWNER TO xx-newuser;
   -- 2、撤回此用户，此表所有的权限
   REVOKE ALL PRIVILEGES ON table xxtable1,xxtable2 from xxuser ;
   -- 3、重新授权
   GRANT select ON TABLE xxtable1, xxtable2 to xxuser ;

三、删除用户
   --- drop database user ,删除用户

   revoke ALL PRIVILEGES on all tables in schema public from xxuser;

   revoke ALL PRIVILEGES on all tables in schema information_schema from xxuser;

   revoke ALL PRIVILEGES on all tables in schema pg_catalog from xxuser;

   revoke all on database xx-database from xxuser;

drop user if exists xxuser;