Getting an error when creating a USER with a PASSWORD in PostgreSQL using psycopg2

Question

I am trying to create protected code which doesn't leave itself open for SQL Injection attacks. Currently, I want to create 3 users with different passwords. Here is what is looks like:

import psycopg2
from psycopg2 import connect, extensions, sql
# Importing a 0 integer so the process can pass without bothering w/ extensions
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
    
user1 = "jimmy"
user2 = "ray"
user3 = "billy"
secret1 = "gelatto"
secret3 = "cookies"
secret2 = "vanilla"
        
cursor.execute(sql.SQL("CREATE USER {users} WITH PASSWORD {password}")
    .format(users=sql.Identifier(user1),
            password=sql.Identifier(secret1)))
cursor.execute(sql.SQL("CREATE USER {users} WITH PASSWORD {password}")
    .format(users=sql.Identifier(user2),
            password=sql.Identifier(secret2)))
cursor.execute(sql.SQL("CREATE USER {users} WITH PASSWORD {password}")
    .format(users=sql.Identifier(user3),
            password=sql.Identifier(secret3)))
cursor.execute(sql.SQL("GRANT {role} TO {user}")
    .format(role=sql.Identifier(readWrite),
            user=sql.Identifier(user1)))
cursor.execute(sql.SQL("GRANT {role} TO {user}")
    .format(role=sql.Identifier(readWrite),
            user=sql.Identifier(user2)))
cursor.execute(sql.SQL("GRANT {role} TO {user}")
    .format(role=sql.Identifier(readOnly),
            user=sql.Identifier(user3)))

However, I receive an error to since the passwords are being closed in " " when they need to be ' '. Can anybody help me out on how they figured this out?

LINE 1: CREATE USER "jimmy" WITH PASSWORD "gelatto"

Nice! Thanks for the help Mike. I know my code isn't the best but now I can see what else I can improve if an error occurs on the script. — Dylan
– Dylan, Commented Sep 3, 2020 at 20:41

Mike Organek · Accepted Answer · 2020-09-03 21:30:01Z

In case you are looking for pointers, I would write this script like so:

import collections
import psycopg2
from psycopg2 import connect, extensions, sql
# Importing a 0 integer so the process can pass without bothering w/ extensions
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
    

NewUser = collections.namedtuple('NewUser', 'username password access')

users = [
    NewUser('jimmy', 'gelatto', 'readwrite'),
    NewUser('ray', 'cookies', 'readwrite'),
    NewUser('billy', 'vanilla', 'readonly')
]

with psycopg2.connect('dbname=morganek') as conn:
    cur: psycopg2.extensions.cursor = conn.cursor()
    for user in users:
        cur.execute(
            sql.SQL("create user {username} with password %s")
              .format(username=sql.Identifier(user.username)), 
            (user.password, )
        )
        cur.execute(
            sql.SQL("grant {access} to {username}")
              .format(
                access=sql.Identifier(user.access), 
                username=sql.Identifier(user.username)
              )
        )
    conn.commit()

Oh this looks good Mike thanks! Is there a video or documentation so I can get a better dive into? Really appreciate the help.
@Roma I wish I could point you in a direction for this. Most of it is just from experience. I have gotten very useful information from reading articles at realpython.com/learning-paths
Thanks for this Mike. I'll see what I can do from now on. Much appreciated!

Collectives™ on Stack Overflow

Getting an error when creating a USER with a PASSWORD in PostgreSQL using psycopg2

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related