Create a new user for PostgreSQL

\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