createuser allows creation of a user (ROLE) in PostgreSQL. Is there a simple way to check if that user(name) exists already? Otherwise createuser returns with an error:
createuser: creation of new role failed: ERROR: role "USR_NAME" already exists
UPDATE: The solution should be executable from shell preferrably, so that it's easier to automate inside a script.
SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'
And in terms of command line (thanks to Erwin):
psql postgres -tXAc "SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'"
Yields 1 if found and nothing else.
That is:
psql postgres -tXAc "SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'" | grep -q 1 || createuser ...
psql is the command. But if you're talking about createuser command line utility (you obviously do, I didn't notice the lack of space in create user at first), then it may be easier just to ignore the exit status and redirect output to /dev/null.
psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'". Yields 1 if found and nothing else.
echo "SELECT rolname FROM pg_roles WHERE rolname='USR_NAME';" | psql | grep -c USR_NAME. Add your solution as an answer without "postgres" after psql.
-X flag to prevent the .psqlrc file from being read. It is pretty common for that file to turn on \timing which is likely to create false positives with the grep -q 1 since timing values will often include a 1. So, psql postgres -tXAc ....Following the same idea than to check if a db exists
psql -t -c '\du' | cut -d \| -f 1 | grep -qw <user_to_check>
and you can use it in a script like this:
if psql -t -c '\du' | cut -d \| -f 1 | grep -qw <user_to_check>; then
# user exists
# $? is 0
else
# ruh-roh
# $? is 1
fi
pg_roles, but not a change to command \du. Which is most likely not to change?To do this entirely within a single psql command:
DO $$BEGIN
IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'USR_NAME')
THEN CREATE ROLE USR_NAME;
END IF;
END$$;
psql -qtA -c "\du USR_NAME" | cut -d "|" -f 1
[[ -n $(psql -qtA -c "\du ${1}" | cut -d "|" -f 1) ]] && echo "exists" || echo "does not exist"
Hope this helps those of you who might be doing this in python.
I created a complete working script/solution on a GitHubGist--see URL below this code snippet.
# ref: https://stackoverflow.com/questions/8546759/how-to-check-if-a-postgres-user-exists
check_user_cmd = ("SELECT 1 FROM pg_roles WHERE rolname='%s'" % (deis_app_user))
# our create role/user command and vars
create_user_cmd = ("CREATE ROLE %s WITH LOGIN CREATEDB PASSWORD '%s'" % (deis_app_user, deis_app_passwd))
# ref: https://stackoverflow.com/questions/37488175/simplify-database-psycopg2-usage-by-creating-a-module
class RdsCreds():
def __init__(self):
self.conn = psycopg2.connect("dbname=%s user=%s host=%s password=%s" % (admin_db_name, admin_db_user, db_host, admin_db_pass))
self.conn.set_isolation_level(0)
self.cur = self.conn.cursor()
def query(self, query):
self.cur.execute(query)
return self.cur.rowcount > 0
def close(self):
self.cur.close()
self.conn.close()
db = RdsCreds()
user_exists = db.query(check_user_cmd)
# PostgreSQL currently has no 'create role if not exists'
# So, we only want to create the role/user if not exists
if (user_exists) is True:
print("%s user_exists: %s" % (deis_app_user, user_exists))
print("Idempotent: No credential modifications required. Exiting...")
db.close()
else:
print("%s user_exists: %s" % (deis_app_user, user_exists))
print("Creating %s user now" % (deis_app_user))
db.query(create_user_cmd)
user_exists = db.query(check_user_cmd)
db.close()
print("%s user_exists: %s" % (deis_app_user, user_exists))
Provides idempotent remote (RDS) PostgreSQL create role/user from python without CM modules, etc.
Inspired from accepted answer, that unfortunately did not work for me (error on direct psql call), I then did something like this:
if ! echo "SELECT * FROM pg_roles WHERE rolname='USR_NAME'" | psql -h localhost -U postgres | grep -E "[1-9][0-9]* rows"; then
# User not found, create it
if ! echo "CREATE USER USR_NAME WITH PASSWORD 'USR_NAME' CREATEDB SUPERUSER" | psql -h localhost -U postgres; then
echo "Error creating USR_NAME"
exit 1
fi
fi
Even if I think grep -E "1 rows" is safe here since we should not have more that one user of same name, I prefer keeping grep -E "[1-9][0-9]* rows" to get a generic "I got 1 or more result(s)" returning success.
And I put exit 1 if it fails because I'm on a script that needs this user created to run properly.
and furthermore, it might be better to mention the system catalog/table name "pg_roles" in front of the column name "rolname". Like this: "pg_roles.rolname".
and to mention it, because it confused me: it is really spelled "rolname" and not "rolename".
// PHP Version 8.1.13
// PostgreSQL 15
try {
// database connection
$pdo = new PDO("pgsql:host=$host;port=$port;dbname=$dbname", $dbusername, $dbuserkeyword, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
if ($pdo) { // went fine
// a query to ask if user available in table (system catalog) "pg_roles" and column "rolname"
$myResult = $pdo -> query("SELECT * FROM pg_roles WHERE pg_roles.rolname='{$theuseriamlookingfor}'");
// retrieve the values from response in an Array
$myResult = $myResult -> fetchAll();
// output the first value from that Array
echo $myResult[0]["rolname"];
}
} catch (PDOException $e) {
// output the error-message
echo $e->getMessage();
}
