chenjiazhu 2020-06-03
今天pg数据库需要添加一个只读用户,由于在pgAdmin4中无法添加,遂用sql添加,遇到了一个大坑,很基础的问题,只怪自己学艺不精,一次提醒自己;
注意;postgresql数据库对大小写不敏感!!!
CREATE USER test2 WITH ENCRYPTED PASSWORD ‘test2‘; alter user test2 set default_transaction_read_only=on; GRANT USAGE ON SCHEMA public to test2; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test2; GRANT USAGE ON SCHEMA public to test2; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO test2; GRANT SELECT ON ALL TABLES IN SCHEMA public TO test2;
参考 内容;
创建用户及指定密码: CREATE USER readonly WITH ENCRYPTED PASSWORD ‘ropass‘; 设置用户默认事务只读: alter user readonly set default_transaction_read_only=on; 赋予用户权限,查看public模式下所有表: GRANT USAGE ON SCHEMA public to readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; 赋予用户连接数据库权限: GRANT CONNECT ON DATABASE foo to readonly; 切换到指定数据库: \c foo 赋予用户表、序列查看权限: GRANT USAGE ON SCHEMA public to readonly; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
postgresql创建只读用户 1.创建一个用户名为readonly密码为ropass的用户 CREATE USER readonly WITH ENCRYPTED PASSWORD ‘ropass‘; 2.用户只读事务 alter user readonly set default_transaction_read_only=on; 3.把所有库的语言的USAGE权限给到readonly GRANT USAGE ON SCHEMA public to readonly; 4.授予select权限(这句要进入具体数据库操作在哪个db环境执行就授予那个db的权) grant select on all tables in schema public to readonly;
这个是如何删除的
https://blog.csdn.net/qq_37568918/article/details/85339277?utm_medium=distribute.pc_relevant_right.none-task-blog-OPENSEARCH-3.nonecase&depth_1-utm_source=distribute.pc_relevant_right.none-task-blog-OPENSEARCH-3.nonecase
https://www.cnblogs.com/liuyanerfly/p/10731271.html
https://blog.csdn.net/dhiivgzni65686145/article/details/101870382