PostgreSQL Rights Management Create an updatable table for ordinary users

First, the environment

$ psql--versionpsql (PostgreSQL) 9.4.4

As we all know, superuser privileges are too large for the security of the database, for non-administrator accounts, you need to create a normal user.

Second, the grammar

$ psql psql  (9.4.4) type  "Help"  for help.postgres=# \h create role  Command:     CREATE ROLEDescription: define a new  DATABASE ROLESYNTAX:CREATE ROLE NAME [ [ WITH ] OPTION [&NBSP, .....  ] ]where option can be:      SUPERUSER |  nosuperuser    | createdb | nocreatedb    |  createrole | nocreaterole    | createuser | nocreateuser     | inherit | noinherit    | login | nologin     | replication | noreplication    | connection  LIMIT connlimit    | [ ENCRYPTED | UNENCRYPTED ]  password  ' PASSWORD ' &NBsp;   | valid until  ' timestamp '     | IN ROLE  ROLE_NAME [,&NBSP, ...]     | IN GROUP ROLE_NAME [,&NBSP, ...]     | ROLE ROLE_NAME [,&NBSP, ...]     | ADMIN ROLE_NAME [,&NBSP, ...]     | USER ROLE_NAME [,&NBSP, ...]     | sysid uid

Third, create a read-only user1. Create a table First T1

postgres=# CREATE TABLE T1 (ID serial, name varchar (64)); CREATE tablepostgres=# \dt List of relations Schema | Name |  Type | Owner--------+------+-------+----------Public | T1 | Table | Postgres (1 row)

2. Create user U1

postgres=# Create role u1 with login password ' 123456 '; CREATE ROLE

Login is given logon permissions, otherwise it is not possible to log in

3. Give U1 read-only access to the table

Because the normal user created by default is without any permissions

postgres=# Grant Select on all tables in the schema public to U1; grantpostgres=# \c-u1you is now connected to database "Postgres" as the user "U1" .postgres=> select * from T1; ID | Name----+------(0 rows)

4. Create a table t2

Postgres=> \c-postgresyou is now connected to database "Postgres" as User "Postgres". postgres=# CREATE TABLE t2 (ID Serial, name varchar (64)); CREATE tablepostgres=# \dt List of relations Schema | Name |  Type | Owner--------+------+-------+----------Public | T1 | Table | Postgres Public | T2 | Table | Postgres (2 rows)

5. Verify the permissions of the U1

postgres=# \c-u1you is now connected to database "Postgres" as the user "U1" .postgres=> select * from T1; ID | Name----+------(0 rows) postgres=> select * from T2; Error:permission denied for relation T2

Visible U1 is the Read permission for the T1 table, but there is no Read permission on the T2 table, does this mean that each new table will have to be assigned a permission?

6. Workaround

postgres=> \c - postgresyou are now connected to database  " Postgres " as user " Postgres ".postgres=# alter default privileges in  schema public grant select on tables to u1; alter default privilegespostgres=# create table t3  ( id serial,  Name varchar (+)  ); create tablepostgres=# \dt        list of  relations schema | name | type  |  owner   ------- -+------+-------+---------- public | t1   | table | postgres  public | t2   | table | postgres public | t3    | table | postgres (3 rows) postgres=# \c - u1you are  now connected to database  "Postgres"  as user  "U1" .postgres=> select * from t3;  id | name ----+------(0 rows)

Give default permissions to all tables in the schema to U1, so that new tables are not assigned permissions at a later time.

Iv. Creating an updatable user1. Create U2 users

postgres=# Create role U2 with login password ' 123456 '; CREATE ROLE

2. Give update permissions

postgres=# alter default privileges in the schema public grant Select,insert,update,delete on tables to U2; ALTER DEFAULT Privileges

3. Create a table T4

postgres=# CREATE table t4 (ID serial, name varchar (64)); CREATE tablepostgres=# \dt List of relations Schema | Name |  Type | Owner--------+------+-------+----------Public | T1 | Table | Postgres Public | T2 | Table | Postgres Public | T3 | Table | Postgres Public | T4 | Table | Postgres (4 rows)

4. View Permissions

postgres=# \c-u2you is now connected to database "Postgres" as User "U2" .postgres=> insert into T4 values (1, ' AA ' ); INSERT 0 1postgres=> select * from T4; ID | Name----+------1 | AA (1 row) postgres=> update t4 set name = ' BB ' WHERE id = 1; UPDATE 1postgres=> select * from T4; ID | Name----+------1 | BB (1 row) postgres=> delete from T4 where id = 1;delete 1postgres=> select * from T4; ID | Name----+------(0 rows)

You can change the normal additions and deletions

5. Sequence Permissions and Workarounds

In the INSERT, specify the column insert, the primary key ID is the serial type will default to go to the next value of sequence, but the previous only given the permissions of the table, so the following problem occurs:

postgres=> insert into t4 (name) VALUES (' AA '); Error:permission denied for sequence T4_id_seq

The solution is to assign a sequence value again.

Postgres=> \c-postgresyou is now connected to database "Postgres" as User "Postgres". postgres=# alter default Privil Eges in schema public grant usage on sequences to U2; ALTER DEFAULT privilegespostgres=# CREATE TABLE T5 (ID serial, name varchar (64)); CREATE tablepostgres=# \c-u2you is now connected to database "Postgres" as User "U2" .postgres=> insert into T5 (NAM e) VALUES (' cc '); INSERT 0 1postgres=> select * from T5; ID | Name----+------1 | CC (1 row)

V. Delete users

Postgres=> \c-postgresyou is now connected to database "Postgres" as User "Postgres". postgres=# drop role U2; Error:role "U2" cannot is dropped because some objects depend on itdetail:privileges for table t5privileges for Sequen Ce t5_id_seqprivileges for default privileges on new sequences belonging to role Postgres in schema publicprivileges for t Able T4privileges for default privileges on new relations belonging to role Postgres in schema public

When we delete the user, we will be prompted to have permission to rely on, so we want to remove these permissions

postgres=# alter default privileges in the schema public revoke usage on sequences from U2; Alter default privilegespostgres=# ALTER default privileges in the schema public revoke select,insert,delete,update on tables from U2; ALTER DEFAULT privilegespostgres=# revoke select,insert,delete,update on all tables in the schema public from U2; revokepostgres=# revoke usage on all sequences in the schema public from U2; revokepostgres=# drop role U2;drop role

It's too much trouble, is there a quicker way?

postgres=# drop role U1; Error:role "U1" cannot is dropped because some objects depend on itdetail:privileges for table t5privileges for table T4privileges for table t3privileges for default privileges on new relations belonging to role Postgres in schema Publicpri Vileges for table t1postgres=# drop owned by U1;drop ownedpostgres=# drop role u1;drop role

PostgreSQL Rights Management Create an updatable table for ordinary users