Postgresql Learning Notes (1)

First, the environment

#Configure remote connection

su postgres
vim /var/lib/pgsql/9.4/data/postgresql.conf edit configuration file
listen_address = ’localhost’ before the comment #, remove ‘localhost’ as ‘*’;
vim /etc/postgresql/8.2/main/pg_hba.conf
host all all 192.168.1.0/24 password password can be set to trust
/etc/init.d/postgresql-8.2 restart Restart the service: the same as the stop start command. If the configuration is incorrect, it may fail to restart
Second, the syntax:

psql -U postgres #Enter the database
\ l #See what databases are available
\ c postgresql #Select the postgresql database, you will be prompted to enter the connection
\ dt #View all tables
\ d tablename #View the structure of a table
\ h #View help
Create a read-only user

# 1. Create a table
create table t1 (id serial, name varchar (64));
CREATE TABLE
postgres = # \ dt List of relations
 Schema | Name | Type | Owner
-------- + ------ + ------- + ----------
 public | t1 | table | postgres
(1 row)
2. Create user u1
create role u1 with login password ‘123456’; #login is to grant login privileges, otherwise you cannot log in
CREATE ROLE
3. Grant u1 read-only permissions on the table (because the created ordinary user does not have any permissions by default)
postgres = # \ c-u1
FATAL: Peer authentication failed for user "u2"
Previous connection kept

If the above message appears, you need to change the configuration file:
vim /etc/postgresql/9.6/main/pg_hba.conf
Find the following line:
local all postgres peer
Into:
local all postgres trust

If the following error occurs:
FATAL: Peer authentication failed foruser "mypguser"

Please still modify the pg_hba.conf file, the peer of the following line is md5:
local all all md5 # replace peer with md5
    
After completing the above modification, please reload postgresql:
/etc/init.d/postgresql reload

postgres => select * from t1;
ERROR: permission denied for relation t1
postgres => \ c-postgres
You are now connected to database "postgres" as user "postgres".
postgres = # grant select on all tables in schema public to u1;
GRANT
postgres = # \ c-u1You are now connected to database "postgres" as user "u1".
postgres => select * from t1;
 id | name
---- + ------
(0 rows)
4. Create table t2
postgres => \ c-postgres
You are now connected to database "postgres" as user "postgres".
postgres = # create table t2 (id serial, name varchar (64));
CREATE TABLE
postgres = # \ dt List of relations
 Schema | Name | Type | Owner
-------- + ------ + ------- + ----------
 public | t1 | table | postgres
 public | t2 | table | postgres
(2 rows)
5. Verify u1 permissions
postgres = # \ c-u1You are now connected to database "postgres" as user "u1".
postgres => select * from t1;
 id | name
---- + ------
(0 rows)

postgres => select * from t2;
ERROR: permission denied for relation t2

It can be seen that u1 has read permissions on the t1 table, but does not have read permissions on the t2 table. Does this mean that permissions need to be granted once for each new table?
6. Solution
postgres => \ c-postgres
You are now connected to database "postgres" as user "postgres".
postgres = # alter default privileges in schema public grant select on tables to u1;
ALTER DEFAULT PRIVILEGES
# grant is to grant permissions to the current table under the user schema
# alter default privileges

postgres = # create table t3 (id serial, name varchar (64));
CREATE TABLE
postgres = # \ dt List of relations
 Schema | Name | Type | Owner
-------- + ------ + ------- + ----------
 public | t1 | table | postgres
 public | t2 | table | postgres
 public | t3 | table | postgres
(3 rows)
Create updatable users

1. Create u2 user
postgres = # create role u2 with login password ‘123456’;
CREATE ROLE
2. Grant update permissions
postgres = # alter default privileges in schema public grant select, insert, update, delete on tables to u2;
ALTER DEFAULT PRIVILEGES
3. Create table t4
postgres = # create table t4 (id serial, name varchar (64)); CREATE TABLE
postgres = # \ 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 are now connected to database "postgres" as user "u2".
postgres => insert into t4 values (1, ‘aa’);
INSERT 0 1
postgres => select * from t4;
 id | name
---- + ------
  1 | aa
(1 row)

postgres => update t4 set name = ‘bb’ where id = 1;
UPDATE 1
postgres => select * from t4;
 id | name
---- + ------
  1 | bb
(1 row)

postgres => delete from t4 where id = 1;
DELETE 1
postgres => select * from t4;
 id | name
---- + ------
(0 rows)
5. Sequence permissions and solutions
# When inserting, specify the column to insert, and the primary key id is serial, which will default to the next value of sequence, but the previous
# Only the table permissions are granted, so the following problems will occur:

postgres => insert into t4 (name) values (‘aa’);
ERROR: permission denied for sequence t4_id_seq

# The solution is to assign the sequence value again.
postgres => \ c-postgres
You are now connected to database "postgres" as user "postgres".
postgres = # alter default privileges in schema public grant usage on sequences to u2;
ALTER DEFAULT PRIVILEGES
postgres = # create table t5 (id serial, name varchar (64));
CREATE TABLE
postgres = # \ c-u2
You are now connected to database "postgres" as user "u2".
postgres => insert into t5 (name) values (‘cc’);
INSERT 0 1postgres => select * from t5;
 id | name
---- + ------
  1 | cc
(1 row)
Five, delete users

postgres => \ c-postgres
You are now connected to database "postgres" as user "postgres".
postgres = # drop role u2;
ERROR: role "u2" cannot be dropped because some objects depend on it
DETAIL: privileges for table t5
privileges for sequence t5_id_seq
privileges for default privileges on new sequences belonging to role postgres in schema publicprivileges for table t4
privileges for default privileges on new relations belonging to role postgres in schema public

# When we delete a user, we will be prompted to have permission dependencies, so we need to delete these permissions
postgres = # alter default privileges in schema public revoke usage on sequences from u2;
ALTER DEFAULT PRIVILEGES
postgres = # alter default privileges in schema public revoke select, insert, delete, update on tables from u2;
ALTER DEFAULT PRIVILEGES
postgres = # revoke select, insert, delete, update on all tables in schema public from u2;
REVOKE
postgres = # revoke usage on all sequences in schema public from u2;
REVOKE
postgres = # drop role u2;
DROP ROLE
Six, modify the user password

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD ‘passwd’;


Postgresql study notes (1)