Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the knowledge points about permissions in postgresql". In the daily operation, I believe that many people have doubts about the knowledge points about permissions in postgresql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "what are the knowledge points about permissions in postgresql?" Next, please follow the editor to study!

1. Each instance can have multiple db, each db has its own owner, multiple schema can be established under each db, each schema has its own owner, multiple tables can be created under each schema, and each table has its own owner.

2. Db owner may not be able to operate a schema below it.

3. Schema owner may not be able to manipulate a table below it

4. When granting a user select on all tables in schema XX, you need to grant usage access to schema XX to the user.

Grant usage on schema s9 to owner_2

Grant select on all tables in schema s9 to owner_2

-- Authorization owner_2 can query all tables under S9, which is valid only for existing tables. Later created tables will not automatically have read-only access

5. The above 4 users can only query the tables that already exist under the schema, but cannot query the tables created under the schema. If you want to obtain permission for the newly created tables under the schema, you need to authorize the user to owner the schema.

Alter default privileges for user s9_owner in schema s9 grant select on tables to owner_2

-- user owner_2 can access all new tables created by owner s9_owner of schema S9 under schema S9

6. The execution order of pg_hba.conf is from top to bottom, that is, the above takes effect. Pg_hba.conf is a client authentication file. It does not restrict permissions, but where you can only come from and what authentication method you must use.

Db owner may not be able to operate a schema below it

Schema owner may not be able to manipulate a table below it

1. Superuser establishes three users: dbuser1, schemauser1 and schemauser2, and the authorized user dbuser1 has the right to create db

Create user dbuser1 createdb password '123456'

Create user schemauser1 password '123456'

Create user schemauser2 password '123456'

2. Dbuser1 creates DB1,superuser authorization schemauser1, and schemauser2 has permission to create schema on db1

\ C-dbuser1

Create database db1

\ C-postgres

Grant create on database db1 to schemauser1

Grant create on database db1 to schemauser2

3. Schemauser1 and schemauser2 create schema1 and schema2 on db1 respectively, and create tables schema1.table1 and schema2.table2.

\ C db1

\ C-schemauser1

Create schema schema1

Create table schema1.table1 (hid int)

Insert into schema1.table1 values (1) (2)

Select * from schema1.table1

\ C-schemauser2

Create schema schema2

Create table schema2.table2 (hid int)

Insert into schema2.table2 values (1) (2)

Select * from schema2.table2

4. Superuser sets up table supertable1,supertable2 on db1.schema1 and db1.schema2

\ C-postgres

Create table schema1.supertable1 (hid int)

Insert into schema1.supertable1 values (1) (2)

Select * from schema1.supertable1

Create table schema2.supertable2 (hid int)

Insert into schema2.supertable2 values (1) (2)

Select * from schema2.supertable2

5. Verification

5.1. can dbuser1 query schema1.table1, schema2.table2, schema1.supertable1, schema2.supertable2

No

5.2. whether dbuser1 can set up tables schema1.dbtable1 and schema2.dbtable2 on schema1 and schema2

No

5.3. whether schemauser1 can query schema1.supertable1, schema2.table2, schema2.supertable2

No

Can schemauser2 query schema2.supertable2, schema1.table1, schema1.supertable1

No

\ C-dbuser1

Db1= > select * from pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE' sql_%' ORDER BY tablename

Schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity

-+-

Schema1 | supertable1 | postgre2 | | f | f | f | f

Schema2 | supertable2 | postgre2 | | f | f | f | f

Schema1 | table1 | schemauser1 | | f | f | f | f

Schema2 | table2 | schemauser2 | | f | f | f | f

(4 rows)

Db1= > select * from schema1.table1

ERROR: permission denied for schema schema1

LINE 1: select * from schema1.table1

Db1= > select * from schema1.supertable1

ERROR: permission denied for schema schema1

LINE 1: select * from schema1.supertable1

Db1= > create table schema1.dbtable1 (hid int)

ERROR: permission denied for schema schema1

LINE 1: create table schema1.dbtable1 (hid int)

Db1= > create table schema2.dbtable2 (hid int)

ERROR: permission denied for schema schema2

LINE 1: create table schema2.dbtable2 (hid int); users cannot query tables under schema because they authorize select on all tables in schema without authorization usage on schema

Postgres=# create user testuser1 password '123456'

CREATE ROLE

Postgres=# create user testuser2 password '123456'

CREATE ROLE

Db1=# grant select on all tables in schema schema1 to testuser1

GRANT

Db1=#\ c-testuser1

You are now connected to database "db1" as user "testuser1".

Db1= > select count (*) from schema1.table1

ERROR: permission denied for schema schema1

LINE 1: select * from schema1.table1

Db1= >\ c-postgres

Db1=# grant usage on schema schema1 to testuser1

GRANT

Db1=#\ c-testuser1

You are now connected to database "db1" as user "testuser1".

Db1= > select count (*) from schema1.table1

Count

-

two

(1 row) db1=# grant usage on schema schema1 to testuser2

GRANT

Db1=# grant select on all tables in schema schema1 to testuser2

GRANT

Db1=#\ c-testuser2

You are now connected to database "db1" as user "testuser2".

Db1= > select count (*) from schema1.table1

Count

-

two

(1 row) the newly created table under schema can also be queried by authorized users, and the owner of the schema needs to be authorized to the user. The following testuser1 and testuser2 both have the owner of select on all tables in schema schema1,schema1 and the permission of schemauser1,schemauser1 is granted to testuser2, so the new table created by schemauser1 in schema1 can be queried by testuser2, but not by testuser1.

Db1= >\ c-postgres

Db1=# alter default privileges for user schemauser1 in schema schema1 grant select on tables to testuser2

Db1=#\ c-schemauser1

Db1= > select * into schema1.table3 from schema1.table1

Db1= >\ c-testuser1

You are now connected to database "db1" as user "testuser1".

Db1= > select * from schema1.table3

ERROR: permission denied for table table3

Db1= >\ c-testuser2

You are now connected to database "db1" as user "testuser2".

Db1= > select * from schema1.table3

Hid

-

one

two

(2 rows)

Without createdb permission, you cannot create a database. With createdb permission, you can also create a schema under the db you have created.

Postgres=#\ c-testuser1

You are now connected to database "postgres" as user "testuser1".

Postgres= > create database testdb

ERROR: permission denied to create database

Postgres= >\ c-postgres

Postgres=# alter user testuser1 createdb

Postgres=#\ c-testuser1

Postgres= > create database testdb

CREATE DATABASE

Postgres= >\ c testdb

You are now connected to database "testdb" as user "testuser1".

Testdb= > create schema tests1

If CREATE SCHEMA is under the db of other db_ower, users cannot create schema without authorized CREATE on database permission. With create permission, they can create tables under their own schema.

Testdb= >\ c db1

You are now connected to database "db1" as user "testuser1".

Db1= > create schema tests2

ERROR: permission denied for database db1

Testdb= >\ c-postgres

Db1=# grant CREATE on database db1 to testuser1

Db1=#\ c-testuser1

Db1= > create schema tests2

Db1= > create table tests2.table1 (hid int); under the schema of other schema_owner, users cannot create tables without CREATE on schema permission

Db1= >\ c-postgres

Db1=# create schema tests3

Db1=#\ c-testuser1

Db1= > create table tests3.table (hid int)

ERROR: permission denied for schema tests3

LINE 1: create table tests3.table (hid int)

Db1= >\ c-postgres

Db1=# grant CREATE on schema tests3 to testuser1

Db1= > create table tests3.table (hid int)

CREATE TABLE

The above pg_hba.conf takes effect.

If the pg_hba.conf content is as follows, after systemctl restart postgresql-11, the local psql command requires a password

Local all all md5

Local all all trust

If the pg_hba.conf content is as follows, the local psql command does not require a password after systemctl restart postgresql-11

Local all all trust

Local all all md5

At this point, the study of "what are the knowledge points of postgresql about permissions" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.