# 在shell命令中,username是用户名，比如 postgres，dbname是要进入的数据库名
psql -U [username] -d [dbname]
# 如何已经进入了数据，可以用下面的sql切换数据库 dbname是要进入的数据库名
\c [dbname]

-- 都是在sql的交互式终端中运行
\list
\l
select datname from pg_database;

CREATE DATABASE [数据库名] WITH ENCODING = 'UTF8';

DROP DATABASE database_name;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'database_name';

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'database_name';

\conninfo

postgres=# \conninfo
You are connected to database "database_test" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

-- 总连接数、空闲连接数和最大连接数
SELECT
(SELECT COUNT(*) FROM pg_stat_activity) AS total_connections,
(SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
(SELECT COUNT(*) AS idle_in_transaction_connections FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_transaction_connections,
current_setting('max_connections')::int AS max_connections;

\! clear

\! cls

# 1、连接上数据库实例
# shell终端切换数据库超级用户
su postgres
# 并连接上实例
psql

# 2、新建角色
# 创建角色并设置登录密码 sahdiohdiqwho
create role [角色名] LOGIN PASSWORD 'sahdiohdiqwho';

create role anime_time_test LOGIN PASSWORD 'kLYSE*XbFAu3RN6l';
create role anime_time_prod LOGIN PASSWORD 'kLYSE*XbFAu3RN6l';

# 3、赋予角色权限
# 授权角色在数据库 test的 连接、创建新模式等权限
GRANT ALL PRIVILEGES ON DATABASE [数据库名] TO [角色名];

# 进入需要赋权的数据库(test数据库)中
\c test
# 授权角色在数据库 test 的 public 模式上所以权限
GRANT ALL PRIVILEGES ON SCHEMA public TO [角色名];


# 将 public 模式下现存的所有表的所有可用权限赋给角色
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO [角色名];
# 表创建者自动有该表的所以权限，也可以显示授权，此步骤可用可不用(当数据库中有多角色是，这条命令就很有用，比如生产上，可能增删改查的应用连接角色，另一个是仅有查询权限的角色。就可以让 表创建的时候自动默认给 仅查询角色 赋予查询权限)
# 这可就是当 test_role 在 public 模式下创建表，都会执行 GRANT ALL PRIVILEGES ON TABLES TO test_role;
ALTER DEFAULT PRIVILEGES FOR ROLE test_role IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO test_role;

GRANT ALL PRIVILEGES ON SCHEMA public TO anime_time_prod;

-- 以下命令都在 数据的交互命令行中执行
-- [name] 填写自定义的角色名
create role [name];

# 需要使用 postgres 用户进行执行
su postgres
createuser [name]

--  以下命令都在 数据的交互命令行中执行
-- [name] 填写需要删除的角色名
drop role [name];

# 需要使用 postgres 用户进行执行
su postgres
dropuser [name]

ALTER ROLE role_specification [ WITH ] option [ ... ]

其中option可以是：

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

其中role_specification可以是：

    role_name
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

REVOKE [要移除的权限] ON [在什么对象上] FROM [角色]

REVOKE ALL PRIVILEGES ON DATABASE [数据库名] FROM [角色名];

REVOKE ALL PRIVILEGES ON SCHEMA public FROM [角色名];

SELECT spcname, spcowner::regrole AS owner, array_agg(spcacl) AS privileges
FROM pg_tablespace
WHERE spcname = 'pg_default'
GROUP BY spcname, spcowner;

  spcname   |  owner   |                     privileges                     
------------+----------+----------------------------------------------------
 pg_default | postgres | {{postgres=C/postgres,test_role=C/postgres}}
(1 row)

REVOKE ALL PRIVILEGES ON TABLESPACE [表空间] FROM [角色];

-- 查询所有
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public' AND grantee = '[角色名]';
-- 查询指定表名
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public' AND grantee = '[角色名]' AND table_name IN ('[表名]');

-- 撤销单个表
REVOKE ALL PRIVILEGES ON TABLE [表名] FROM [角色名];
-- 撤销所有表
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM [角色名];

SELECT schemaname AS schema, 
       tablename AS table, 
       tableowner AS owner
FROM pg_tables
WHERE tableowner = '[角色名]';

-- 单个表转移所有者权限
ALTER TABLE [表名] OWNER TO [转给的目标角色];

-- 批量
DO $$ 
DECLARE 
    rec RECORD;
BEGIN
    FOR rec IN (SELECT schemaname, tablename 
                FROM pg_tables 
                WHERE tableowner = '[被转移的角色]' AND schemaname NOT IN ('pg_catalog', 'information_schema')) 
    LOOP
        EXECUTE format('ALTER TABLE %I.%I OWNER TO [转给的目标角色]', rec.schemaname, rec.tablename);
    END LOOP;
END $$;

SELECT 
    defaclrole::regrole AS role,
    defaclnamespace::regnamespace AS schema,
    defaclobjtype AS object_type,
    defaclacl AS privileges
FROM pg_default_acl
WHERE defaclnamespace = 'public'::regnamespace 
  AND defaclrole = (SELECT oid FROM pg_roles WHERE rolname = '[提示中的角色名]');

ALTER DEFAULT PRIVILEGES FOR ROLE [提示中的角色名] IN SCHEMA public
	REVOKE ALL ON TABLES FROM [要删除的角色];
	
ALTER DEFAULT PRIVILEGES FOR ROLE [提示中的角色名] IN SCHEMA public
	REVOKE ALL ON SEQUENCES FROM [要删除的角色];
	
ALTER DEFAULT PRIVILEGES FOR ROLE [提示中的角色名] IN SCHEMA public
	REVOKE ALL ON FUNCTIONS FROM [要删除的角色];
	
ALTER DEFAULT PRIVILEGES FOR ROLE [提示中的角色名] IN SCHEMA public
	REVOKE ALL ON TYPES FROM [要删除的角色];
	
ALTER DEFAULT PRIVILEGES FOR ROLE [提示中的角色名] IN SCHEMA public
	REVOKE ALL ON SCHEMAS FROM [要删除的角色];

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
	REVOKE ALL ON TABLES FROM anime_time_test;

-- 不填角色名，显示所以
\du [role_name]

                                      List of roles
    Role name    |                         Attributes                         | Member of 
-----------------+------------------------------------------------------------+-----------
 nacos           | Create DB                                                  | {}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

SELECT * FROM pg_roles;

 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvali
duntil | rolbypassrls | rolconfig | oid 
----------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+--------
-------+--------------+-----------+-----
 postgres | t        | t          | t             | t           | t           | t              |           -1 | ********    |        
       | t            |           |  10

\dn+

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +| 
        |                   | test_role=UC/pg_database_owner         | 
(1 row)

-- 不填表名查询所有表
\dp [table_name]

                                         Access privileges
 Schema |      Name       | Type  |        Access privileges         | Column privileges | Policies 
--------+-----------------+-------+----------------------------------+-------------------+----------
 public | test            | table | postgres=arwdDxt/postgres       +|                   | 
        |                 |       | test_role=arwdDxt/postgres       |                   | 
 public | test2           | table |                                  |                   | 
        |                 |       |                                  |                   |                   
(2 rows)

-- 查询模式 public 下所有的表
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE schemaname = 'public';

-- 查询特定的表
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE tablename = 'test';

 schemaname |    tablename    |   tableowner    
------------+-----------------+-----------------
 public     | test            | postgres
 public     | test2           | test_role
(2 rows)

SELECT grantee, table_catalog, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'cc';

     grantee     |  table_catalog  | table_schema | table_name | privilege_type 
-----------------+-----------------+--------------+------------+----------------
 anime_time_test |  database_test  | public       | test       | INSERT
 anime_time_test |  database_test  | public       | test       | SELECT
 anime_time_test |  database_test  | public       | test       | UPDATE
 anime_time_test |  database_test  | public       | test       | DELETE
 anime_time_test |  database_test  | public       | test       | TRUNCATE
 anime_time_test |  database_test  | public       | test       | REFERENCES
 anime_time_test |  database_test  | public       | test       | TRIGGER
(7 rows)

SELECT 
    defaclrole::regrole AS role,
    defaclnamespace::regnamespace AS schema,
    defaclobjtype AS object_type,
    defaclacl AS privileges
FROM pg_default_acl;

SELECT 
    defaclrole::regrole AS role,
    defaclnamespace::regnamespace AS schema,
    defaclobjtype AS object_type,
    defaclacl AS privileges
FROM pg_default_acl
WHERE defaclrole = (SELECT oid FROM pg_roles WHERE rolname = '[角色名]');

   role   | schema | object_type |                        privileges                         
----------+--------+-------------+-----------------------------------------------------------
 postgres | -      | T           | {postgres=arwdDxt/postgres,test_role=arwdDxt/postgres}
 postgres | -      |            | {postgres=rwU/postgres}
(2 row)