The logic structure of PG

The instance - & gt; The database - & gt; Schema - & gt; The object

Permissions system

Instance authority - & gt; Pg_hba.conf

The database privileges - & gt; Grant or revoke whether to allow the connect or create schema permissions

Scehma permissions - & gt; Grant or revoke whether to allow the query schema objects, creating objects in the schema

The object permissions - & gt; Grant or revoke the select, insert, update, delete etc permission

Table space - & gt; Grant, fu and allowed in the corresponding table space to create tables, indexes, temporary tables, etc.

The default access

After the database is created, allowing the public role connection, allowing anyone to connect,

After the database is created, don't allow anybody besides super user and the owner in the database to create schema,

After the database is created, and will automatically create a schema of the public, the schema of all authority has been given to a public role, allowing anyone in it to create objects, but to have the list of other users do not have any privileges, including the select

Permissions query

1, check the database which users can connect

The select datname, datacl from pg_database where datname='su_db';

2, check the specified schema users have permission to

The select nspname, nspacl from pg_namespace where nspname='schema_001';

3, check a user table permissions

Select * from information_schema. Table_privileges where grantee='user_su';

Letters on behalf of the meaning of the permissions are as follows:

R - SELECT (" read ")

W - UPDATE (" write ")

A - INSERT (" append ")

D -- DELETE

D - TRUNCATE

X - REFERENCES

T - TRIGGER

X - EXECUTE

U - the USAGE

C - the CREATE

C - CONNECT

T - TEMPORARY

ArwdDxt -- ALL PRIVILEGES (for tables, around for other objects)

* - grant option for preceding privilege

Permissions

# 1, after creating the database, cancel the public entry permission, does not affect his own connection session

Revoke the CONNECT ON the DATABASE db_001 from the PUBLIC;

# 2, cancel all permissions

Revoke all on database db_001 from the public;

Table 3, cancel the default user create public mode permissions

Revoke the create on schema public from the public;

4, assign permissions with the connection library

Grant connect on the database db_001 user_001),

5, fu and create schema permissions

Grant create on the database db_001 user_001),

6, fu with permissions to create tables

Grant create on schema schema_001 user_001),

7, assignment and modification step 6 to create table permissions

Grant the usage on schema schema_001 user_001),

8, single table authorization

Grant select on public. Su_test to user_001;

9, all authorization query table

GRANT SELECT ON ALL TABLES IN the SCHEMA dbas TO mobile;

10, below is PG permissions list



From "ITPUB blog," link: http://blog.itpub.net/16835711/viewspace-2736730/, if you want to reprint, please indicate the source, otherwise will be investigated for legal responsibility,