赞
踩
PostgreSQL中使用“角色”的概念管理数据库访问权限,用户表示“拥有LOGIN权限的角色”。
CREATE USER和CREATE ROLE命令都用于定义一个新的数据库角色,唯一的区别是 CREATE USER中LOGIN 被作为默认值,而NOLOGIN是 CREATE ROLE的默认值。
参考:
https://www.postgresql.org/docs/14/sql-createuser.html
https://www.postgresql.org/docs/14/sql-createrole.html
一个角色是一个实体,它可以拥有数据库对象(例如,表和函数)并且拥有某些数据库特权;也可以把对象上的权限或者成员资格赋予给其他角色来控制谁能访问哪些对象(可以被继承);并且数据库角色在一个数据库集簇(Cluster)安装范围内是全局的(实例级别,非某个数据库内)。
数据库初始创建后,会创建一个预定义特权角色(superuser):postgres用于连接和访问等操作。
例:查看角色
postgres-# \set ECHO_HIDDEN on
postgres-# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres-#
例:查看用户
postgres-# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
角色相关操作命令主要包括如下
CREATE ROLE
ALTER ROLE
DROP ROLE
GRANT
REVOKE
CREATE USER
SET ROLE
例:
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# create role role1;
CREATE ROLE
postgres=# create role role2 with password 'pass';
CREATE ROLE
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | Cannot login | {}
例:
postgres=# alter role role2 with login;
ALTER ROLE
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | | {}
postgres=#
修改登录属性后该角色可以登录。
ubuntu@pg-vm:~$ psql -U role2 -h localhost -d postgres
Password for user role2:
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>
例:
postgres=# create role role3;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | | {}
role3 | Cannot login | {}
postgres=# drop role role3;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | | {}
postgres=#
例:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | | {}
postgres=# grant postgres to role2;
GRANT ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | | {postgres}
例:
postgres=# revoke postgres from role2;
REVOKE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | | {}
例:
postgres=# create user user1 with password 'pass';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | | {}
user1 | | {}
可以通过 SET ROLE设置当前会话的当前用户标识符,即控制使用的角色。
postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role1 | Cannot login | {}
role2 | | {}
user1 | | {postgres,role2}
postgres=> SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
--------------+--------------
user1 | user1
(1 row)
postgres=> set role role2;
SET
postgres=> SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
--------------+--------------
user1 | role2
(1 row)
postgres=> drop user role1;
ERROR: permission denied to drop role
postgres=> set role postgres;
SET
postgres=# SELECT SESSION_USER, CURRENT_USER;
session_user | current_user
--------------+--------------
user1 | postgres
(1 row)
postgres=# drop user role1;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
role2 | | {}
user1 | | {postgres,role2}
postgres=#
模式(Schema)本质上是一个名字空间,用于 存放数据库中的逻辑对象(如表、视图、函数、物化视图等),不同的模式下可以有相同名称的表、函数等对象且互相不冲突,提出模式(schema)的概念主要是为了便于管理,只要有权限,每个模式中的对象可以互相调用。
可以通过用模式名作为一个前缀 “限定”命名对象的名称来访问它们,或者通过把要求的模式包括在搜索路径中来访问命名对象。
一个数据库集簇(Cluster)可以包括多个数据库,而一个数据库中包括一个或多个模式,每个模式中可以包括多个数据库对象。
在Oracle中每一个用户都会对应一个同名Schema,创建了用户便拥有了同名的Schema用于存储相关的数据库对象。
在PostgreSQL中则不同,用户(角色)和 模式(Schema)是分别管理的。
模式(Schema)相关操作命令主要包括如下:
CREATE SCHEMA
ALTER SCHEMA
DROP SCHEMA
通过CREATE SCHEMA可以创建一个新的模式。
postgres-# \h create schema
Command: CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
user_name
| CURRENT_USER
| SESSION_USER
URL: https://www.postgresql.org/docs/12/sql-createschema.html
例1:创建一个新模式
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# CREATE SCHEMA myschema;
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
----------+----------
myschema | postgres
public | postgres
(2 rows)
例2:为某个用户创建一个模式,模式名同用户名
postgres=# CREATE SCHEMA AUTHORIZATION user1;
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
----------+----------
myschema | postgres
public | postgres
user1 | user1
(3 rows)
可以通过ALTER SCHEMA修改模式定义。
例1:修改模式名
postgres=# alter schema user1 rename to user2;
ALTER SCHEMA
postgres=# \dn
List of schemas
Name | Owner
----------+----------
myschema | postgres
public | postgres
user2 | user1
(3 rows)
例2:修改模式owner
postgres=> \dn
List of schemas
Name | Owner
----------+----------
myschema | postgres
public | postgres
user2 | user1
(3 rows)
postgres=> alter schema myschema owner to user1;
ALTER SCHEMA
postgres=> \dn
List of schemas
Name | Owner
----------+----------
myschema | user1
public | postgres
user2 | user1
(3 rows)
可以通过DROP SCHEMA删除模式及其中的数据库对象。
例1:删除空Schema
postgres=> drop schema user2;
DROP SCHEMA
postgres=> \dn
List of schemas
Name | Owner
----------+----------
myschema | user1
public | postgres
(2 rows)
例2:删除Schema及其中数据库对象
postgres=> create schema test1;
CREATE SCHEMA
postgres=> \dn
List of schemas
Name | Owner
----------+----------
myschema | user1
public | postgres
test1 | user1
(3 rows)
postgres=> create table test1.t1(a int);
CREATE TABLE
postgres=> drop schema test1;
ERROR: cannot drop schema test1 because other objects depend on it
DETAIL: table test1.t1 depends on schema test1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=> drop schema test1 cascade;
NOTICE: drop cascades to table test1.t1
DROP SCHEMA
postgres=>
简单而言,用户(角色)主要用于权限管理,模式用于数据库对象的管理。
参考:
https://www.postgresql.org/docs/14/sql-createuser.html
https://www.postgresql.org/docs/14/sql-createrole.html
http://www.postgresql.org/docs/14/sql-createschema.html
http://www.postgres.cn/docs/14/sql-createuser.html
http://www.postgres.cn/docs/14/sql-createrole.html
http://www.postgres.cn/docs/14/sql-createschema.html
https://www.postgresql.org/docs/14/ddl-schemas.html
http://www.postgres.cn/docs/14/ddl-schemas.html
https://www.postgresql.org/docs/14/manage-ag-overview.html
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。