postgresql模式管理

最新推荐文章于 2024-06-24 20:29:26 发布

久违的太阳

最新推荐文章于 2024-06-24 20:29:26 发布

阅读量2.5k

点赞数

分类专栏： postgresql基础

本文链接：https://blog.csdn.net/su377486/article/details/50810476

版权

postgresql基础专栏收录该内容

13 篇文章 0 订阅

订阅专栏

一.用户

pg的user和role几乎是一样的,只是role是无法登录系统的,而用户可以

创建用户使用create user语句,一般使用create user with password 'xxx' XXX 其中最后的XXX是给予的角色权限

   
   
    
    
     
     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
    
    
    
    
     
         | CREATEUSER | NOCREATEUSER
    
    
    
    
     
         | INHERIT | NOINHERIT
    
    
    
    
     
         | LOGIN | NOLOGIN
    
    
    
    
     
         | REPLICATION | NOREPLICATION
    
    
    
    
     
         | BYPASSRLS | NOBYPASSRLS
    
    
    
    
     
         | CONNECTION LIMIT connlimit
    
    
    
    
     
         | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    
    
    
    
     
         | VALID UNTIL 'timestamp'
    
    
    
    
     
         | IN ROLE role_name [, ...]
    
    
    
    
     
         | IN GROUP role_name [, ...]
    
    
    
    
     
         | ROLE role_name [, ...]
    
    
    
    
     
         | ADMIN role_name [, ...]
    
    
    
    
     
         | USER role_name [, ...]
    
    
    
    
     
         | SYSID uid

    
    
     
     
      
      postgres=# create user test with password 'test' superuser;

使用\du来查看当前pg中的用户

    
    
     
     
      
       Role name |                         Attributes                         | Member of 
     
     
     
     
      
      -----------+------------------------------------------------------------+-----------
     
     
     
     
      
       brent     |                                                            | {}
     
     
     
     
      
       dsg       | Create DB                                                  | {}
     
     
     
     
      
       postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
     
     
     
     
      
       suq       |                                                            | {}
     
     
     
     
      
       test      | Superuser                                                  | {}

用户的权限分为系统权限和对象权限

对象权限使用grant语句来赋予:

    
    
     
     
      
      postgres=# grant select,update,insert on pg_catalog.pg_roles to brent;
     
     
     
     
      
      GRANT

而系统权限其实就是用户是否具有一些系统角色,可以使用alter语句来赋予,例如给brent加上replication的角色

    
    
     
     
      
      postgres=# alter role brent with replication;
     
     
     
     
      
      ALTER ROLE

二.数据库

1.创建数据库

使用create database来创建数据库,在psql中使用\l来查看当前实例中包含哪些数据库,其中postgres和template0和template1为initdb的时候就系统创建的.

   
   
    
    
     
     postgres=# \h create database 
    
    
    
    
     
     Command:     CREATE DATABASE
    
    
    
    
     
     Description: create a new database
    
    
    
    
     
     Syntax:
    
    
    
    
     
     CREATE DATABASE name
    
    
    
    
     
         [ [ WITH ] [ OWNER [=] user_name ]
    
    
    
    
     
                [ TEMPLATE [=] template ]
    
    
    
    
     
                [ ENCODING [=] encoding ]
    
    
    
    
     
                [ LC_COLLATE [=] lc_collate ]
    
    
    
    
     
                [ LC_CTYPE [=] lc_ctype ]
    
    
    
    
     
                [ TABLESPACE [=] tablespace_name ]
    
    
    
    
     
                [ ALLOW_CONNECTIONS [=] allowconn ]
    
    
    
    
     
                [ CONNECTION LIMIT [=] connlimit ] ]
    
    
    
    
     
                [ IS_TEMPLATE [=] istemplate ]
    
    
    
    
     
      
    
    
    
    
     
     postgres=# create database brent with owner=suq ;
    
    
    
    
     
     CREATE DATABASE
    
    
    
    
     
     postgres=# \l 
    
    
    
    
     
                                  List of databases
    
    
    
    
     
        Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
    
    
    
    
     
     -----------+----------+----------+---------+-------+-----------------------
    
    
    
    
     
      brent     | suq      | UTF8     | C       | C     | 
    
    
    
    
     
      postgres  | postgres | UTF8     | C       | C     | 
    
    
    
    
     
      template0 | postgres | UTF8     | C       | C     | =c/postgres          +
    
    
    
    
     
                |          |          |         |       | postgres=CTc/postgres
    
    
    
    
     
      template1 | postgres | UTF8     | C       | C     | =c/postgres          +
    
    
    
    
     
                |          |          |         |       | postgres=CTc/postgres

连接数据库可以使用\c DATABASENAME 或者在登录的时候直接指定登录哪个数据库

   
   
    
    
     
     [postgres@pg1 ~]$ psql brent
    
    
    
    
     
     psql (9.5.1)
    
    
    
    
     
     Type "help" for help.
    
    
    
    
     
      
    
    
    
    
     
     brent=# \q
    
    
    
    
     
     [postgres@pg1 ~]$ psql 
    
    
    
    
     
     psql (9.5.1)
    
    
    
    
     
     Type "help" for help.
    
    
    
    
     
      
    
    
    
    
     
     postgres=# \c brent
    
    
    
    
     
     You are now connected to database "brent" as user "postgres".

2.修改数据库

使用alter database命令修改数据库

   
   
    
    
     
     postgres=# \h alter database 
    
    
    
    
     
     Command:     ALTER DATABASE
    
    
    
    
     
     Description: change a database
    
    
    
    
     
     Syntax:
    
    
    
    
     
     ALTER DATABASE name [ [ WITH ] option [ ... ] ]
    
    
    
    
     
      
    
    
    
    
     
     where option can be:
    
    
    
    
     
      
    
    
    
    
     
         ALLOW_CONNECTIONS allowconn
    
    
    
    
     
         CONNECTION LIMIT connlimit
    
    
    
    
     
         IS_TEMPLATE istemplate
    
    
    
    
     
      
    
    
    
    
     
     ALTER DATABASE name RENAME TO new_name
    
    
    
    
     
      
    
    
    
    
     
     ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
    
    
    
    
     
      
    
    
    
    
     
     ALTER DATABASE name SET TABLESPACE new_tablespace
    
    
    
    
     
      
    
    
    
    
     
     ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
    
    
    
    
     
     ALTER DATABASE name SET configuration_parameter FROM CURRENT
    
    
    
    
     
     ALTER DATABASE name RESET configuration_parameter
    
    
    
    
     
     ALTER DATABASE name RESET ALL

可以修改最大连接数

   
   
    
    
     
     postgres=# alter database brent connection limit 10;
    
    
    
    
     
     ALTER DATABASE

可以修改数据库名

   
   
    
    
     
     postgres=# alter database brent rename to suq;
    
    
    
    
     
     ALTER DATABASE

删除数据库,使用drop database命令,当有人连接在数据库的时候是不能删除数据库的.

三 .模式

模式即schema,类似于oracle中schema,mysql中的database概念,使用create schema来创建schema,一般我们的 schema和owner都设置为相同

   
   
    
    
     
     suq=# \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:
    
    
    
    
     
      
    
    
    
    
     
         [ GROUP ] user_name
    
    
    
    
     
       | CURRENT_USER
    
    
    
    
     
       | SESSION_USER

使用\n查看当前数据库下有哪些schema,public是创建database的时候系统生成的默认schema,类似于oracle中的public用户

   
   
    
    
     
     suq=# create schema brent authorization suq;
    
    
    
    
     
     CREATE SCHEMA
    
    
    
    
     
     suq=# \dn
    
    
    
    
     
       List of schemas
    
    
    
    
     
       Name  |  Owner   
    
    
    
    
     
     --------+----------
    
    
    
    
     
      brent  | suq
    
    
    
    
     
      public | postgres

使用drop schema来删除schema

   
   
    
    
     
     suq=# drop schema brent;
    
    
    
    
     
     DROP SCHEMA

使用alter schema来修改schema,例如修改schema的名字

   
   
    
    
     
     suq=# alter schema brent rename to suq;
    
    
    
    
     
     ALTER SCHEMA
    
    
    
    
     
     suq=# \dn
    
    
    
    
     
       List of schemas
    
    
    
    
     
       Name  |  Owner   
    
    
    
    
     
     --------+----------
    
    
    
    
     
      public | postgres
    
    
    
    
     
      suq    | postgres

修改schema的所属主:

   
   
    
    
     
     suq=# alter schema suq owner to suq;
    
    
    
    
     
     ALTER SCHEMA
    
    
    
    
     
     suq=# \dn
    
    
    
    
     
       List of schemas
    
    
    
    
     
       Name  |  Owner   
    
    
    
    
     
     --------+----------
    
    
    
    
     
      public | postgres
    
    
    
    
     
      suq    | suq

当连接到一个数据下的时候,创建表或者其它对象的时候一般都需要打上schema.name

或者使用修改搜索路径的方法,设置路径,类似于linux中设置PATH环境变量,当设置完成之后,所有的操作都默认

是在搜索路径下的schema执行,例如,当我们第一次登录到psql的时候,当前的search_path为登录public

此时使用\dt查看不到任何表

   
   
    
    
     
     [postgres@pg1 ~]$ psql suq
    
    
    
    
     
     psql (9.5.1)
    
    
    
    
     
     Type "help" for help.
    
    
    
    
     
      
    
    
    
    
     
     suq=# show search_path
    
    
    
    
     
     suq-# ;
    
    
    
    
     
        search_path   
    
    
    
    
     
     -----------------
    
    
    
    
     
      "$user", public
    
    
    
    
     
     (1 row)
    
    
    
    
     
      
    
    
    
    
     
     suq=# \dt
    
    
    
    
     
     No relations found.

设置完成search_path后,就可以在suq这个模式下查看到表名了:

   
   
    
    
     
     suq=# set search_path=suq;
    
    
    
    
     
     SET
    
    
    
    
     
     suq=# \dt
    
    
    
    
     
             List of relations
    
    
    
    
     
      Schema | Name | Type  |  Owner   
    
    
    
    
     
     --------+------+-------+----------
    
    
    
    
     
      suq    | t1   | table | postgres

久违的太阳

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
postgresql模式管理

一.用户pg的user和role几乎是一样的,只是role是无法登录系统的,而用户可以创建用户使用create user语句,一般使用create user with password 'xxx' XXX 其中最后的XXX是给予的角色权限postgres=# \h create user;Command: CREATE USERDescription: de
复制链接

扫一扫

专栏目录