aaHVDXHG

1# Lib
2
3```
4apt-get -y install postgresql postgresql-contrib
5```
6
7# List all Databases
8
9```
10\list
11```
12
13# All tables
14
15```
16\dt *
17\d
18```
19
20# Connect on other Databases
21
22```
23\connect db_production
24```
25
26# Connect on a Database as a user
27
28```
29psql -U deploy -d db_production
30```
31
32# Change the owner of Database
33
34```
35ALTER DATABASE db_production OWNER TO deploy;
36```
37
38# Grant privileges on a database
39
40```
41GRANT ALL PRIVILEGES ON DATABASE db_production to deploy;
42```
43
44# Grant privileges on a table
45
46```
47GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO deploy;
48GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO deploy;
49GRANT SELECT ON ALL TABLES IN SCHEMA public TO deploy;
50ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO deploy;
51```
52
53# Create User
54
55```
56sudo -u postgres createuser --superuser admin
57```
58
59# Login
60
61```
62psql -U postgres
63\du
64```
65
66# Create Database
67
68```
69sudo -u postgres createdb -T template1 db_development
70```
71
72# Plugins
73
74```
75sudo -u postgres psql template1 -c 'create extension if not exists "hstore"'
76```
77
78# Drop
79
80```
81dropdb 'db_production'
82```
83
84# Change Mode to "Password authentication"
85
86sudo vim /etc/postgresql/9.3/main/pg_hba.conf
87
88```
89local   all             postgres                                peer
90```
91
92to
93
94```
95local   all             postgres                                trust
96```
97
98```
99sudo service postgresql restart
100
101su – postgres
102psql
103ALTER USER postgress with password 'password-new';
104\q
105create role deploy with createdb login password 'xxx';
106\du
107
108sudo -u postgres createdb -T template1 db_production
109```
110
111# Login with a User
112
113```
114psql -U postgres
115```
116
117# Login with a User on a Database with Password
118
119```bash
120psql -d db -U user -W
121```
122
123# Change User password
124
125```
126ALTER USER user with password 'password-new';
127
128sudo -u postgres psql
129create user username with password 'password';
130alter role username superuser createrole createdb replication;
131create database projectname_production owner username;
132```