分配权限
标签
开发/数据/数据库
计算机/数据库/postgres
计算机/数据库/postgresql
字数
561 字
阅读时间
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;
絢香猫