启动数据库
[postgres@cjc-db-01 ~]$ pg_ctl -D /pg/data -l /pg/log/pg.log start waiting for server to start.... done server started
登录数据库
[postgres@cjc-db-01 ~]$ psql
查看pg_roles表字段
postgres=# \d pg_roles View "pg_catalog.pg_roles" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-----------+----------+--------- rolname | name | | | rolsuper | boolean | | | rolinherit | boolean | | | rolcreaterole | boolean | | | rolcreatedb | boolean | | | rolcanlogin | boolean | | | rolreplication | boolean | | | rolconnlimit | integer | | | rolpassword | text | | | rolvaliduntil | timestamp with time zone | | | rolbypassrls | boolean | | | rolconfig | text[] | | | oid | oid | | |
或者
postgres=# SELECT table_name, column_name, data_type, column_default FROM information_schema.columns WHERE table_name = 'pg_roles'; table_name | column_name | data_type | column_default ------------+----------------+--------------------------+---------------- pg_roles | rolname | name | pg_roles | rolsuper | boolean | pg_roles | rolinherit | boolean | pg_roles | rolcreaterole | boolean | pg_roles | rolcreatedb | boolean | pg_roles | rolcanlogin | boolean | pg_roles | rolreplication | boolean | pg_roles | rolconnlimit | integer | pg_roles | rolpassword | text | pg_roles | rolvaliduntil | timestamp with time zone | pg_roles | rolbypassrls | boolean | pg_roles | rolconfig | ARRAY | pg_roles | oid | oid |
查看角色信息
postgres=# select rolname,rolsuper,rolcreatedb from pg_roles; rolname | rolsuper | rolcreatedb ----------------------+----------+------------- postgres | t | t pg_monitor | f | f pg_read_all_settings | f | f pg_read_all_stats | f | f pg_stat_scan_tables | f | f pg_signal_backend | f | f (6 rows)
在PostgreSQL数据库中，角色和用户没有区别，一个角色就是一用户，这块和Oracle数据库有一定差异。 相关描述如下：
CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.
查看create user帮助信息
postgres=# \h create user; Command: CREATE USER Description: define a new database role Syntax: CREATE USER name [ [ WITH ] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid
查看create role帮助信息，和create user完全一样。
postgres=# \h create role; Command: CREATE ROLE Description: define a new database role Syntax: CREATE ROLE name [ [ WITH ] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid
参数说明：
1.SUPERUSER |NOSUPERUSER 创建出来的用户是否为超级用户 2.CREATEDB |NOCREATEDB 指定创建出来的用户是否有创建数据库的权限 3.CREATEROLE |NOCREATEROLE 指定创建出来的用户是否有创建其他角色权限 4.CREATEUSER |NOCREATEUSER 指定创建出来的用户是否有创建其它用户的权限 5.INHERIT | NOINHERIT 创建的用户拥有某个或几个角色的权限 6.LOGIN | NOLOGIN 创建出来的用户是否有连接数据库的权限 7.CONNECTION LIMIT connlimit 户可以使用的并发连接的数量，默认为 "-1"，表示没有限制 8.[ENCRYPTED | UNENCRYPTED] PASSWORD 'password' 存储的用户口令是否加密 9.VALID UNTIL 'timestamp' 密码失效时间，不指定的话永久有效 10.IN ROLE role_name [...] 指定用户成为哪些角色的成员 11.IN GROUP role_name [...] 等同于IN ROLE rome_name，不过已过时 12.ROLE role_name [...]role_name 将成为这个新建的角色的成员 13.ADMIN role_name [...]role_name 将有这个新建角色 WITH ADMIN OPTION权限 14.USER role_name 与ROLE相同，不过已过时 15.SYSID uid 用于SQL兼容
创建用户
create user chen LOGIN;
创建角色
create role cjc SUPERUSER PASSWORD '1';
查看用户已经创建
postgres=# select rolname,rolsuper,rolcreatedb from pg_roles; rolname | rolsuper | rolcreatedb ----------------------+----------+------------- postgres | t | t pg_monitor | f | f pg_read_all_settings | f | f pg_read_all_stats | f | f pg_stat_scan_tables | f | f pg_signal_backend | f | f chen | f | f cjc | t | f (8 rows)
指定用户登录
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U chen -W postgres Password for user chen: psql.bin (10.23) Type "help" for help. postgres=> select current_user; current_user -------------- chen (1 row)
注意：创建用户方式创建出来的用户默认有 LOGIN 权限，而创建角色创建出来的用户没有 LOGIN 权限。
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W postgres Password for user cjc: psql.bin: FATAL: role "cjc" is not permitted to log in
授予登录权限
postgres=# grant connect on database postgres to cjc; postgres=# \q
还是不能登录
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W postgres Password for user cjc: psql.bin: FATAL: role "cjc" is not permitted to log in
修改角色
postgres=# alter role cjc login superuser;
重新登录
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W postgres Password for user cjc: psql.bin (10.23) Type "help" for help. postgres=# select current_user; current_user -------------- cjc (1 row)
为用户指定表空间 查看表空间
postgres=# \db List of tablespaces Name | Owner | Location ------------+----------+---------- pg_default | postgres | pg_global | postgres | (2 rows)
创建表空间
postgres=# \h create tablespace
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH ( tablespace_option = value [, ... ] ) ]
注意： 表空间的名称不能以 'pg_' 开头，它们是系统表空间的保留名称； LOCATION 参数必须指定绝对路径名， 指定的目录必须是一个已经存在的空目录， PostgreSQL 操作系统用户（postgres）必须是该目录的拥有者，以便能够进行文件的读写。 创建空目录
[postgres@cjc-db-01 data]$ pwd /pg/data [postgres@cjc-db-01 data]$ mkdir cjctbs
创建表空间，有WARNING，新增的表空间不能在数据路径里面
postgres=# CREATE TABLESPACE cjctbs OWNER cjc LOCATION '/pg/data/cjctbs'; WARNING: tablespace location should not be inside the data directory CREATE TABLESPACE
自动生成的文件
[postgres@cjc-db-01 PG_10_201707211]$ pwd /pg/data/cjctbs/PG_10_201707211 [postgres@cjc-db-01 PG_10_201707211]$ ls
删除表空间
postgres=# drop tablespace cjctbs; DROP TABLESPACE
创建表空间,重新指定路径，没有WARNING
[postgres@cjc-db-01 pg]$ mkdir /pg/tbs/cjctbs -p postgres=# CREATE TABLESPACE cjctbs OWNER cjc LOCATION '/pg/tbs/cjctbs'; CREATE TABLESPACE
自动生成目录
[postgres@cjc-db-01 PG_10_201707211]$ pwd /pg/tbs/cjctbs/PG_10_201707211
创建数据库 指定用户和表空间
postgres=# create database cjcdb owner cjc tablespace cjctbs; CREATE DATABASE
查看新生成的文件
[postgres@cjc-db-01 16396]$ pwd /pg/tbs/cjctbs/PG_10_201707211/16396 [postgres@cjc-db-01 16396]$ ls 112 13696_vm 13713 1417_vm 2601 2606_vm 2612_fsm 2619 2661 2681 2702 2834 2996 3350_vm 3456_vm 3576 3602_vm 3997 826_vm 113 13698 13715 1418 2601_fsm 2607 2612_vm 2619_fsm 2662 2682 2703 2834_vm 3079 3351 3466 3576_vm 3603 5002 827 1247 13700 13716 1418_vm 2601_vm 2607_fsm 2613 2619_vm 2663 2683 2704 2835 3079_fsm 3379 3466_vm 3596 3603_fsm 548 828 1247_fsm 13701 13716_fsm 174 2602 2607_vm 2613_vm 2620 2664 2684 2753 2836 3079_vm 3380 3467 3596_vm 3603_vm 549 pg_filenode.map 1247_vm 13701_fsm 13716_vm 175 2602_fsm 2608 2615 2620_vm 2665 2685 2753_fsm 2836_vm 3080 3381 3468 3597 3604 6102 PG_VERSION 1249 13701_vm 13718 2187 2602_vm 2608_fsm 2615_fsm 2650 2666 2686 2753_vm 2837 3081 3381_vm 3501 3598 3605 6102_vm 1249_fsm 13703 13720 2224 2603 2608_vm 2615_vm 2651 2667 2687 2754 2838 3085 3394 3501_vm 3598_vm 3606 6104 1249_vm 13705 13721 2224_vm 2603_fsm 2609 2616 2652 2668 2688 2755 2838_fsm 3118 3394_fsm 3502 3599 3607 6104_vm 1255 13706 13721_fsm 2328 2603_vm 2609_fsm 2616_fsm 2653 2669 2689 2756 2838_vm 3118_vm 3394_vm 3503 3600 3608 6106 1255_fsm 13706_fsm 13721_vm 2328_vm 2604 2609_vm 2616_vm 2654 2670 2690 2757 2839 3119 3395 3534 3600_fsm 3609 6106_vm 1255_vm 13706_vm 13723 2336 2604_vm 2610 2617 2655 2673 2691 2830 2840 3164 3439 3541 3600_vm 3712 6110 1259 13708 13725 2336_vm 2605 2610_fsm 2617_fsm 2656 2674 2692 2830_vm 2840_fsm 3256 3439_vm 3541_fsm 3601 3764 6111 1259_fsm 13710 13726 2337 2605_fsm 2610_vm 2617_vm 2657 2675 2693 2831 2840_vm 3256_vm 3440 3541_vm 3601_fsm 3764_fsm 6112 1259_vm 13711 13728 2600 2605_vm 2611 2618 2658 2678 2696 2832 2841 3257 3455 3542 3601_vm 3764_vm 6113 13696 13711_fsm 13730 2600_fsm 2606 2611_vm 2618_fsm 2659 2679 2699 2832_vm 2995 3258 3456 3574 3602 3766 6117 13696_fsm 13711_vm 1417 2600_vm 2606_fsm 2612 2618_vm 2660 2680 2701 2833 2995_vm 3350 3456_fsm 3575 3602_fsm 3767 826
查询数据库
postgres=# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------- cjcdb | cjc | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7745 kB | cjctbs | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +| 7879 kB | pg_default | default administrative connection database | | | | | postgres=CTc/postgres+| | | | | | | | cjc=c/postgres | | | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7745 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7745 kB | pg_default | default template for new databases
查询数据库物理存储位置
cjcdb=# select oid,datname from pg_database where datname = 'cjcdb'; oid | datname -------+--------- 16396 | cjcdb (1 row)
根据oid进行查找
[postgres@cjc-db-01 16396]$ pwd /pg/tbs/cjctbs/PG_10_201707211/16396 [postgres@cjc-db-01 16396]$ ls |wc -l 296
查看指定数据库的大小
postgres=# select pg_database_size('cjcdb');
pg_database_size
------------------
8076423
(1 row)
查看指定数据库的大小
postgres=# select pg_size_pretty(pg_database_size('cjcdb'));
pg_size_pretty
----------------
7887 kB
(1 row)
查看数据库的大小
postgres=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; datname | size -----------+--------- template1 | 7930371 template0 | 7930371 postgres | 8068231 cjcdb | 8076423 (4 rows)
查询数据库默认表空间
cjcdb=# select datname,dattablespace from pg_database where datname='cjcdb'; datname | dattablespace ---------+--------------- cjcdb | 16395 (1 row)
登录数据库
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb
建表
cjcdb=# create table t1(id int,name char(10));
查询表物理存储位置
cjcdb=# select oid,relfilenode from pg_class where relname = 't1'; oid | relfilenode -------+------------- 16397 | 16397 (1 row)
根据relfilenode进行查找
[postgres@cjc-db-01 16396]$ pwd /pg/tbs/cjctbs/PG_10_201707211/16396 [postgres@cjc-db-01 16396]$ ls -lrth 16397* -rw------- 1 postgres postgres 8.0K Jan 17 17:23 16397
查询表所在表空间 新增表
create table t2(id int,name char(10)); create table t3(id int,name char(10)) tablespace cjctbs;
没表空间信息?
cjcdb=# \d+ t3
Table "public.t3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character(10) | | | | extended | |
---
cjcdb=# select tableowner,schemaname,tablename,tablespace from pg_tables where tableowner='cjc';
tableowner | schemaname | tablename | tablespace
------------+------------+-----------+------------
cjc | public | t1 |
cjc | public | t5 |
cjc | public | t6 |
(3 rows)
---
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname
from pg_class a, pg_tablespace tb
where a.reltablespace = tb.oid
and a.relkind in ('r', 'i')
order by a.relpages desc;
---
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a, pg_tablespace tb
where a.relkind in ('r', 'i')
and a.reltablespace=tb.oid
and tb.spcname='cjctbs'
order by a.relpages desc;
修改表所在表空间
alter table t1 set tablespace cjctbs;
查询表结构
cjcdb=# \d t1 Table "public.t1" Column | Type | Collation | Nullable | Default --------+---------------+-----------+----------+--------- id | integer | | | name | character(10) | | |
插入数据
cjcdb=# insert into t1 values(1,'a'),(2,'aaa'); INSERT 0 2 cjcdb=# select * from t1; id | name ----+------------ 1 | a 2 | aaa (2 rows)
查看当前数据库下所有表
postgres=# \c cjcdb You are now connected to database "cjcdb" as user "postgres". cjcdb=# \d List of relations Schema | Name | Type | Owner --------+------+-------+------- public | t1 | table | cjc (1 row)
查看表大小
cjcdb=# select pg_relation_size('t1');
pg_relation_size
------------------
8192
(1 row)
查看表大小
cjcdb=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
8192 bytes
(1 row)
创建索引
cjcdb=# create index i_t1_id on t1(id);
查看表的总大小，包括索引大小
cjcdb=# select pg_size_pretty(pg_total_relation_size('t1'));
pg_size_pretty
----------------
24 kB
(1 row)
查看当前数据库下所有索引
cjcdb=# \di List of relations Schema | Name | Type | Owner | Table --------+---------+-------+-------+------- public | i_t1_id | index | cjc | t1 (1 row)
查看单个索引大小
cjcdb=# select pg_size_pretty(pg_relation_size('i_t1_id'));
pg_size_pretty
----------------
16 kB
(1 row)
查询表空间大小
cjcdb=# select pg_size_pretty(pg_tablespace_size('cjctbs'));
pg_size_pretty
----------------
7911 kB
(1 row)
当前chen只有登录权限
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U chen -W cjcdb Password for user chen: psql.bin (10.23) Type "help" for help.
没有查询权限
cjcdb=> select * from t1; ERROR: permission denied for relation t1
查看授权语句
postgres=# \h grant;
Command: GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT role_name [, ...] TO role_specification [, ...]
[ WITH ADMIN OPTION ]
[ GRANTED BY role_specification ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER
表级别授权
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb cjcdb=# GRANT SELECT ON TABLE t1 TO chen;
可以查询
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U chen -W cjcdb Password for user chen: psql.bin (10.23) Type "help" for help. cjcdb=> select * from t1; id | name ----+------------ 1 | a 2 | aaa (2 rows)
不能执行其他操作
cjcdb=> insert into t1 values(3,'xxx'); ERROR: permission denied for relation t1 cjcdb=> delete from t1; ERROR: permission denied for relation t1
回收权限
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb cjcdb=# revoke select on t1 from chen;
库级别授权
cjcdb=# GRANT ALL ON DATABASE cjcdb to chen;
查询权限 查看用户表权限
cjcdb=# select * from information_schema.table_privileges where grantee='cjc'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ---------+---------+---------------+--------------+------------+----------------+--------------+---------------- cjc | cjc | cjcdb | public | t1 | INSERT | YES | NO cjc | cjc | cjcdb | public | t1 | SELECT | YES | YES cjc | cjc | cjcdb | public | t1 | UPDATE | YES | NO cjc | cjc | cjcdb | public | t1 | DELETE | YES | NO cjc | cjc | cjcdb | public | t1 | TRUNCATE | YES | NO cjc | cjc | cjcdb | public | t1 | REFERENCES | YES | NO cjc | cjc | cjcdb | public | t1 | TRIGGER | YES | NO (7 rows)
查看usage权限表
select * from information_schema.usage_privileges where grantee='cjc';
查看存储过程函数相关权限表
select * from information_schema.routine_privileges where grantee='cjc';
###chenjuchao 20230118 10:20###
