[psycopg2] User Management

1. Connection to Postgres Server

Unlike SQLite3, Postgres is a client-server model, so different users access databases with different priveleges. For the security of the user, Postgres can access the database server by setting the password. A superuser can adjust the permissions of other users while having full permissions on the Postgres server, similar to the sudo permissions of the file system.

 

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

 

2. User Management

2.1 Create User

When accessing the database as a superuser, a new user can be created by giving an option. The user's previleges can be granted by attaching the WITH option to the generated statement, and NO can be added in front of the option to limit it.

 

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

cursor.execute("CREATE USER data_viewer WITH SUPERUSER PASSWORD 'qwer';") 
conn.commit() 
conn.close()

 

2.2 Change User

In the case of superusers, user privileges should be set carefully because they ignore them. If we want to hcange the user's option, use ALTER USER clause.

 

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

cursor.execute("ALTER USER data_viewer WITH NOSPUERUSER;") 
conn.commit() 
conn.close()

 

2.3 Check User

SQL stores all information as a table. Like the pg_catalog table, the user's information is also managed as a table. The pg_user table is a table having user information, and may check the name of the user, the previleges of the user, and whether the superuser is present.

 

Column name	Description
username	Contains the username. It is important to check whether a specific user exists in the database.
usercreatedb	Specifies whether the user can create databases. This is set to True if the user was created with the CREATEDB option.
usersuper	Specifies whether the user is a superuser. This is set to True if the user was created the SUPERUSER option.

 

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

cursor.execute("SELECT * FROM pg_user;") 
users = cursor.fetchall() 

for row in user: 
    print(row) 
conn.close()

 

3. User Previleges

3.1 Grant or Revoke of Previleges

The first thing to do after creating a user is to create a password for the user. It then specifies a range of commands available to the user. User Previliges is a set of commands that a user can use. Most restrict DDL languages such as DROP, DELETE, and UPDATE.

 

The permission granted to the user must only provide the minimum, which is called the Least Privilege Principle. In other words, if tirst deprives the user of all rights through the REVOKE command and enters the command to be given through the GRANT command.

 

REVOKE/GRANT [commands] ON [table] FROM/TO [user]

 

When autorizing or stealing, the command and the actionable table must be specified.

 

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

cursor.execute("REVOKE ALL ON table FROM data_viewer;") 
cursor.execute("REVOKE SELECT ON table FROM data_viewer;") 

conn.commit() 
conn.close()

 

3.2 Check User's Previleges on Table

In some cases, it is necessary to check the user privileges granted to the user through REVOKE and GRANT. The user priileges assigned per table are stored in information_schema.table_privleges. In the case of superuser, ignore the corresponding privleges and execute the command statement.

 

Column name	Description
grantor	The id who granted this privilege
grantee	The id to whome this privilege was granted
table_catalog	The name of the database that contains the table
table_name	The name of the table which this privleges applies
privilege_type	The command that was granted the privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, or TRIGGER

 

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

# Check only user privileges who grant and is granted
cursor.execute("""SELECT grantor, grantee, privilege_type 
                    FROM information_schema.table_privileges 
                   WHERE table_name = 'table';""")
privileges = cursor.fetchall() 

for row in privileges: 
    print(row) 
conn.close()

 

4. Group

A user whose only SELECT is allowed among user privileges is called readonly user. However, the number of users becomes very large and it is very cubersome to designate readonly users one by one.

 

Postgres can group and manage users with the same user privleges in one group through GROUP command. We can create a group name with CREATE GROUP clause. The NOLOGIN option used at this time is a task that allows users to log in with their personal ID. Afterwards, the created group can define privileges that can be grouped through GRANT/REVOKE, and the user can be added to the group. 

 

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

cursor.execute("CREATE GROUP readonly NOLOGIN;") 
cursor.execute("REVOKE ALL ON table FROM readonly;")
cursor.execute("GRANT SELECT ON table to readonly;")
cursor.execute("GRANT readonly TO data_viewer;") 

conn.commit() 
conn.close()

 

 

Source from : https://www.postgresql.org/docs/9.5/sql-createuser.html