参考文章
1.https://www.cnblogs.com/zhoujinyi/p/10939715.html
2.https://www.cnblogs.com/lijiaman/p/16558877.html
具体实操
创建一个可以登录的用户(user默认可登录,role默认不可登录)
CREATE USER {new_username} WITH
PASSWORD '{new_password}'
NOSUPERUSER
CREATEDB
CREATEROLE;
授予jack连接数据库test_db的权限
grant connect on database test_db to jack
单表授权:授权jack用户可以访问schema为public的jack表
grant select,insert,update,delete on public.jack to jack;
schema所有表授权查权限,其他不授予:
grant select on all tables in schema public to jack;
schema所有表授权增删改查权限:
grant select,insert,update,delete on all tables in schema public to zjy;
创建一个公共的只读用户
CREATE ROLE research_db_common NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN NOREPLICATION NOBYPASSRLS PASSWORD '用户的密码';
grant select on all tables in schema public to 用户名;
查询一个用户具体权限
In PostgreSQL, you can use the `pg_roles` and `information_schema` tables to see what permissions a user has. Here's how you can do it:
1. **To see the role attributes (like SUPERUSER, CREATEDB, etc.) of a user**, you can query the `pg_roles` system catalog table:
```sql
SELECT * FROM pg_roles WHERE rolname='username';
Replace 'username'
with the name of the user.
- To see the privileges of a user on tables, you can query the
information_schema.table_privileges
view:
SELECT * FROM information_schema.table_privileges WHERE grantee='username';
Replace 'username'
with the name of the user.
- To see the privileges of a user on schemas, you can query the
information_schema.schema_privileges
view:
SELECT * FROM information_schema.schema_privileges WHERE grantee='username';
Replace 'username'
with the name of the user.
Please note that these commands need to be executed by a superuser or a user with the necessary privileges. If you don't have these privileges, you might need to ask your database administrator for help.
GitHubID:shiyi23
践行 活在当下 的理念