dongnan
总版主
总版主
  • 粉丝52
  • 发帖数2190
  • 铜币13747枚
  • 威望5993点
  • 银元150个
  • 社区居民
  • 最爱沙发
  • 忠实会员
  • 喜欢达人
  • 原创写手
阅读:2145回复:1

postgresql 授权只读用户

楼主#
更多 发布于:2017-02-22 11:30
目标
postgresql serviceDB 库创建一个只读的用户;

条件
使用超级用户授权操作

操作步骤
1. 进入授权的数据库
\c serviceDB

2. 创建角色
CREATE ROLE xxx LOGIN PASSWORD 'yyy';
注,xxx是用户名,yyy密码是。

3. 为角色加只读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ruser;

4. 调整默认权限
注,因为作用在现在已有的表上,之后可能会添加新的表,所以要改一下默认权限:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ruser;

验证
psql -h postgresql_IP -U ruser -d postgres
Password for user postgres:
psql (9.5.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

扩展:常用命令

1. 列出数据库
postgres=> \l
省略...
 serviceDB   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |

(4 rows)

2. 当前数据库
postgres=> \conninfo
You are connected to database "postgres" as user "ruser" on host "postgres_IP" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

3. 切换数据库
postgres=> \c serviceDB
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "serviceDB" as user "ruser".
serviceDB=>

3. 列出角色
serviceDB=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 ruser     |                                                            | {}

4. 列出所有表
serviceDB=> \dt
                        List of relations
 Schema |                 Name                 | Type  |  Owner  
--------+--------------------------------------+-------+----------
 public | attachment                           | table | postgres
省略...

5. 列出表结构
serviceDB=> \d attachment
                    Table "public.attachment"
   Column    |           Type           |       Modifiers        
-------------+--------------------------+------------------------
 uid         | character varying(64)    | not null
 file_name   | character varying(200)   | not null
省略
Indexes:
省略...
    "attachment_object_uid_idx" btree (object_uid)

6. 列出表空间
serviceDB=> \db attachment
   List of tablespaces
 Name | Owner | Location
------+-------+----------
(0 rows)

7. 列出表权限
serviceDB=> \dp attachment
                                   Access privileges
 Schema |    Name    | Type  |     Access privileges     | Column privileges | Policies
--------+------------+-------+---------------------------+-------------------+----------
 public | attachment | table | postgres=arwdDxt/postgres+|                   |
        |            |       | ruser=r/postgres          |                   |
(1 row)

#
dongnan
总版主
总版主
  • 粉丝52
  • 发帖数2190
  • 铜币13747枚
  • 威望5993点
  • 银元150个
  • 社区居民
  • 最爱沙发
  • 忠实会员
  • 喜欢达人
  • 原创写手
沙发#
发布于:2017-02-22 11:40
psql 内的命令
postgres=> \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gset [PREFIX]         execute query and store results in psql variables
  \q                    quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \ir FILE               as \i, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \ddp    [PATTERN]      list default privileges
  \dD[S+] [PATTERN]      list domains
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[+]  [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[+]  [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dE[S+] [PATTERN]      list foreign tables
  \dx[+]  [PATTERN]      list extensions
  \dy     [PATTERN]      list event triggers
  \l[+]   [PATTERN]      list databases
  \sf[+] FUNCNAME        show a function's definition
  \z      [PATTERN]      same as \dp

Formatting
  \a                     toggle between unaligned and aligned output mode
  \C [STRING]            set table title, or unset if none
  \f [STRING]            show or set field separator for unaligned query output
  \H                     toggle HTML output mode (currently off)
  \pset [NAME [VALUE]]   set table output option
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager|
                         unicode_border_linestyle|unicode_column_linestyle|unicode_header_linestyle})
  \t [on|off]            show only rows (currently off)
  \T [STRING]            set HTML <table> tag attributes, or unset if none
  \x [on|off|auto]       toggle expanded output (currently off)

Connection
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         connect to new database (currently "postgres")
  \encoding [ENCODING]   show or set client encoding
  \password [USERNAME]   securely change the password for a user
  \conninfo              display information about current connection

Operating System
  \cd [DIR]              change the current working directory
  \setenv NAME [VALUE]   set or unset environment variable
  \timing [on|off]       toggle timing of commands (currently off)
  \! [COMMAND]           execute command in shell or start interactive shell

Variables
  \prompt [TEXT] NAME    prompt user to set internal variable
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters
  \unset NAME            unset (delete) internal variable

Large Objects
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID      large object operations
游客

返回顶部