\du viewing the current PostgreSQL user, you can only see postgres at this time
Create user YSR superuser password ' 123456 ';
\du will be able to see two users.
Reference: http://www.cnblogs.com/stephen-liu74/archive/2012/05/18/2302639.html
http://blog.csdn.net/beiigang/article/details/8604578
--------------------------------------------------------------------------------
The PG permissions are divided into two parts, the "System permissions" or the properties of the database user, which can be granted role or user (which differs from the login permission), and part of the operation permissions on the database object. Do not do permission check for Superuser, other go ACL. For database objects, start with only the owner and the superuser can do anything else, take the ACL. In PG, the ACL model is simplified, the groups and roles are role, the difference between users and roles is that the role does not have login permissions.
You can create and delete roles with the following command.
CREATE ROLE name;
DROP ROLE name;
For convenience, you can also invoke the program CreateUser and Dropuser directly on the shell command, which provides encapsulation for the corresponding command:
CreateUser name
Dropuser name
Permissions on database objects are:SELECT,INSERT, UPDATE,DELETE,RULE, REFERENCES, TRIGGER,CREATE, temporary,EXECUTE, and USAGE , see the definition below
typedefUint32aclmode; /* A bitmask of privilege bits */
#define Acl_insert (1<<0)/* forrelations */
#define Acl_select (1<<1)
#define Acl_update (1<<2)
#define Acl_delete (1<<3)
#define Acl_truncate (1<<4)
#define Acl_references (1<<5)
#define Acl_trigger (1<<6)
#define Acl_execute (1<<7)/* for functions */
#define Acl_usage (1<<8)/* For languages, namespaces, FDWs, and
* Servers * *
#define Acl_create (1<<9)/* for namespaces and Databases */
#define Acl_create_temp (1<<10)/* for Databases */
#define Acl_connect (1<<11)/* for Databases */
#define N_acl_rights/* 1plus the last 1<<x */
#define Acl_no_rights 0
/*currently, SELECT ... For update/for SHARE requires UPDATE privileges */
#define Acl_select_for_update acl_update
We can assign the object's permissions to all the roles in the system with a special name of public. Write all at the location of the permission declaration, which means that all permissions that apply to the object are assigned to the target role.
beigang=# Grantall on the schema Csm_ca to public;
GRANT
beigang=# revoke all on schema Csm_ca frompublic;
REVOKE
beigang=#
The all permission for each object is defined as follows:
/*
* bitmasks defining "allrights" for each supported object type
*/
#define Acl_all_rights_column (acl_insert| Acl_select| acl_update| Acl_references)
#define Acl_all_rights_relation (acl_insert| Acl_select| acl_update| acl_delete| acl_truncate| acl_references| Acl_trigger)
#define Acl_all_rights_sequence (acl_usage| Acl_select| Acl_update)
#define Acl_all_rights_database (acl_create| acl_create_temp| Acl_connect)
#define ACL_ALL_RIGHTS_FDW (Acl_usage)
#define Acl_all_rights_foreign_server (Acl_usage)
#define Acl_all_rights_function (Acl_execute)
#define Acl_all_rights_language (Acl_usage)
#define Acl_all_rights_largeobject (acl_select| Acl_update)
#define Acl_all_rights_namespace (acl_usage| Acl_create)
#define Acl_all_rights_tablespace (Acl_create)
The properties of the user can be found in:
View Pg_roles provides an interface for accessing information about a database role. It is just a view of the public readable portion of the Pg_authid table, filling the password field with blanks.
Table 42-39.pg_roles Field
Name |
Type |
Reference |
Describe |
Rolname |
Name |
|
Role name |
Rolsuper |
bool |
|
Roles with Superuser privileges |
Rolcreaterole |
bool |
|
Roles that can create more roles |
Rolcreatedb |
bool |
|
The role of the database can be created |
Rolcatupdate |
bool |
|
The role of system tables can be updated directly. (Unless the word is Ke Weizhen, the superuser cannot do it.) ) |
Rolcanlogin |
bool |
|
The role that can be logged in, that is, the role can give the identifier of the initialization session authentication. |
Rolpassword |
Text |
|
Not a password (always ********) |
Rolvaliduntil |
Timestamptz |
|
Password expiration date (for password authentication only), NULL if no expiration period |
Rolconfig |
Text[] |
|
Session defaults for run-time configuration variables |
The following experiment verifies
First create a role xxx, and then create a super User CSM, ordinary user CSM_CA,CSM user to create a database TestDB, in this database to create a schema:csm_ca, and then give ordinary users Csm_ The CA operates the permissions of the tables in the Schema:csm_ca in the database TestDB.
1
Create role:
testdb=# create role xxx with superuser;
CREATE ROLE
2
Create User:
testdb=# create user CSM with Superuserpassword ' CSM ';
CREATE ROLE
testdb=# Create user Csm_ca with password ' Csm_ca ';
CREATE ROLE
testdb=#
3
Verify
testdb=# \du
List of roles
-[RECORD 1]--------------------------------------
Role name | Csm
Properties | Super User
Members belong to | {}
-[RECORD 2]--------------------------------------
Role name | Csm_ca
Properties |
Members belong to | {}
-[RECORD 3]--------------------------------------
Role name | Postgres
Properties | Super user, build role, build DB, Replication
Members belong to | {}
-[RECORD 4]--------------------------------------
Role name | Xxx
Properties | Super User, unable to log in
Members belong to | {}
testdb=# SELECT * from Pg_roles;
-[RECORD 1]---------+---------
Rolname | Postgres
Rolsuper | T
Rolinherit | T
Rolcreaterole | T
Rolcreatedb | T
Rolcreatedblink | T
Rolcreatepublicdblink | T
Roldroppublicdblink | T
Rolcatupdate | T
Rolcanlogin | T
rolreplication | T
Rolconnlimit | -1
Rolpassword | ********
Rolvaliduntil |
Rolconfig |
OID | 10
-[RECORD 2]---------+---------
Rolname | Csm
Rolsuper | T
Rolinherit | T
Rolcreaterole | F
Rolcreatedb | F
Rolcreatedblink | F
Rolcreatepublicdblink | F
Roldroppublicdblink | F
Rolcatupdate | T
Rolcanlogin | T
rolreplication | F
Rolconnlimit | -1
Rolpassword | ********
Rolvaliduntil |
Rolconfig |
OID | 24598
-[RECORD 3]---------+---------
Rolname | Csm_ca
Rolsuper | F
Rolinherit | T
Rolcreaterole | F
Rolcreatedb | F
Rolcreatedblink | F
Rolcreatepublicdblink | F
Roldroppublicdblink | F
Rolcatupdate | F
Rolcanlogin | T
rolreplication | F
Rolconnlimit | -1
Rolpassword | ********
Rolvaliduntil |
Rolconfig |
OID | 24599
-[RECORD 4]---------+---------
Rolname | Xxx
Rolsuper | T
Rolinherit | T
Rolcreaterole | F
Rolcreatedb | F
Rolcreatedblink | F
Rolcreatepublicdblink | F
Roldroppublicdblink | F
Rolcatupdate | T
Rolcanlogin | F
rolreplication | F
Rolconnlimit | -1
Rolpassword | ********
Rolvaliduntil |
Rolconfig |
OID | 24600
postgres=# \c Beigang
Connected to Database "Beigang" as user "CSM".
5
CSM user creates Schema:csm_ca in Beigang
beigang=#
beigang=#
beigang=# Create schema Csm_ca;
CREATE SCHEMA
beigang=#
6
Validate mode Csm_ca and user Csm_ca
beigang=# \DN
Schema Mode list
name | Owned by
--------+----------
Csm_ca | Csm
dbo | Postgres
Public | Postgres
SYS | Postgres
(4 Line Records)
beigang=# \du
List of roles
Role name | Properties | Members belong to
----------+------------------------------------------+----------
CSM | Super users | {}
Csm_ca | | {}
Postgres | Super user, build role, Build DB, Replication | {}
xxx | Super User, unable to login | {}
beigang=#
7
Superuser CSM gives ordinary user Csm_ca permission to manipulate schema Csm_ca
beigang=# Grant all on the schema Csm_ca to Csm_ca;
GRANT
beigang=# grant all on all tables in the schema Csm_ca to Csm_ca;
GRANT
beigang=#
8
In the PG Group is role, the operation is shown below
beigang=# grant xxx to Csm_ca;
GRANT ROLE
beigang=# revoke xxx from Csm_ca;
REVOKE ROLE
beigang=#
Reference:
Pg Documentation
Src/include/nodes/parsenodes.h
Src/include/utils/acl.h
-----------------
Reprint please source, from the following blog or mail to [email protected] Contact:
Blog.csdn.net/beiigang
Beigang.iteye.com
Create a new user for PostgreSQL