[psycopg2] Database Management

1. Database Management

1.1 Create Database

Postgres can create multiple databases inside the server. The databse can be created with CREATE DATABASE clause, and the OWNER option is used to specify who owns the databse. If the OWNER option is not specified, the current user user is the owner.

 

import psycopg2
conn = psycopg2.connect(dbname="db", user="ur") 
conn.autocommit = True 
cursor = conn.cursor() 

cursor.execute("CREATE DATABASE my_db OWNER master;") 

cursor.autocommit = False

 

1.2 Database Previleges

The database can also be defined as which users are accessible to the database. The group of all users in Postgres is called public. Therefore, when restricting the previleges of a database, we should first limit the entire previleges for the public group and then limit the previlges for the desired group. Otherwise, a problem arises in which the previleges exisiting in puliic group are inherited from the previleges of the sub-group.

 

import psycopg2
conn = psycopg2.connect(dbnam="db", user="ur", password="zxcv") 
cursor = conn.cursor() 

curosr.execute("REVOKE ALL ON DATABASE my_db FROM public;")
cursor.execute("GEANT CONNECT ON DATABASE my_db TO readonly;") 

conn.commit() 
conn.close()

 

2. Schema

A databse schema is a structure that defines the structure of data in a database, the method of expressing data, and the relationship between data in a formal language. The database management system generates a database schema according to the given settings, and when a database user stores, queries, deletes, or changes data, the DBMS refers to the database schema that is creates and executes commands.

 

The schema is like a file that organizes tables inside a database. A database has multiple schemas, and each schema has multiple tables. It is the same as referring to table_previleges.information_schema to check the user previleges of the table following the schema name.table name format when accessing the table inside the schema.

 

The creation of a schema is possible through the CREATE command as previously done, and commands that give each user access to the schema are also possible through GRANT/REVOKE clause.

 

import psycopg2
conn = psycopg2.connect(dbname="db", user="ur", password="zxcv") 
cursor = conn.cursor() 

cursor.execute("CREATE SCHEMA my_schema;") 
cursor.execute("REVOKE DELETE ON ALL TABLES IN my_schema FROM data_viewer;") 
cursor.execute("GRANT SELECT ON ALL TABLES IN my_schema TO data_viewer;") 

conn.commit() 
conn.close()