Skip to content

分配权限

标签
开发/数据/数据库
计算机/数据库/postgres
计算机/数据库/postgresql
字数
570 字
阅读时间
3 分钟

使用管理员连入

shell
psql -h pgsql.ayaka.srv.local -u postgres -W

列出数据库

shell
psql (14.0)
Type "help" for help.

> \l # 列出库
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 app_sso   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres         +
           |          |          |            |            | postgres=CTc/postgres+
           |          |          |            |            | app_sso=CTc/postgres +
           |          |          |            |            | grafana=c/postgres
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

选中库

shell
> \c app_sso
Password:
You are now connected to database "app_sso" as user "postgres".

分配连接权限

sql
GRANT CONNECT ON DATABASE <数据库名称> TO <用户>;

分配模式使用权限

sql
GRANT USAGE ON SCHEMA <模式名称> TO <用户>;

分配表权限

sql
GRANT ALL PRIVILEGES ON DATABASE <数据库名称> TO <用户>;

分配只读用户权限

sql
GRANT SELECT ON ALL TABLES IN SCHEMA <模式名称> TO <用户>;

分配全部权限

sql
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <模式名称> TO <用户>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <模式名称> TO <用户>;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <模式名称> TO <用户>;
ALTER TABLE <表名> OWNER TO <用户>;
GRANT <> TO <用户>;

分配模式下的创建权限

sql
GRANT CREATE ON SCHEMA <模式名称> TO <用户>;

序列(自增键)属性授权

指定 test schema 下的 seq_id_seq 给 test 用户

sql
GRANT SELECT, UPDATE ON SEQUENCE <序列名称> TO <用户>;

给用户 test 授权 test schema 下的所有序列

sql
GRANT SELECT, UPDATE ON ALL SEQUENCES ON SCHEMA <模式名称> TO <用户>;

实际操作案例

创建数据库并授权

sql
CREATE DATABASE "app"
WITH
  OWNER = "postgres"
  LC_COLLATE = 'en_US.utf8'
  LC_CTYPE = 'en_US.utf8'
;

GRANT service_account TO postgres;

创建模式并授权

sql
CREATE SCHEMA IF NOT EXISTS "users";

GRANT USAGE ON SCHEMA users TO service_account;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA users TO service_account;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA users TO service_account;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA users TO service_account;
GRANT CREATE ON SCHEMA users TO service_account;

创建表并分配权限

sql
CREATE TABLE "users"."infos"
(
 "id" bigserial NOT NULL,
 "username" text NOT NULL ,
CONSTRAINT "pk_users_infos" PRIMARY KEY ("id") ,
CONSTRAINT "users_infos_username_unique" UNIQUE ("username") WITH (FILLFACTOR=100)
)
WITH (
    FILLFACTOR = 100,
    OIDS = FALSE
)
;

CREATE TABLE "users"."accounts"
(
 "id" bigserial NOT NULL ,
 "user_id" bigint NOT NULL ,
 "identifier" text NOT NULL ,
 "metadata" text ,
 "auth" text NOT NULL
CONSTRAINT "pk_users_accounts" PRIMARY KEY ("id") ,
CONSTRAINT "users_accounts_identifier_unique" UNIQUE ("identifier") WITH (FILLFACTOR=100)
)
WITH (
    FILLFACTOR = 100,
    OIDS = FALSE
)
;

ALTER TABLE users.infos OWNER TO service_account;
ALTER TABLE users.accounts OWNER TO service_account;

贡献者

文件历史

撰写