PG用户权限管理

参考文章

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.

2. 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.

3. 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.