postgresql 如何检查postgres用户是否存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8546759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a postgres user exists?
提问by m33lky
createuserallows 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允许在 PostgreSQL 中创建用户 (ROLE)。有没有一种简单的方法来检查该用户（名称）是否已经存在？否则 createuser 返回错误：
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.
更新：该解决方案最好可以从 shell 执行，以便在脚本内更容易自动化。
回答by Michael Krelin - hacker
SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'
And in terms of command line (thanks to Erwin):
在命令行方面（感谢 Erwin）：
psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'"
Yields 1 if found and nothing else.
如果找到，则产生 1，没有别的。
That is:
那是：
psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'" | grep -q 1 || createuser ...
回答by matt
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
回答by cmcc
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.
希望这可以帮助那些可能在python 中执行此操作的人。
我在 GitHubGist 上创建了一个完整的工作脚本/解决方案——请参阅此代码片段下方的 URL。
# 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.
回答by Pascal Polleunus
psql -qtA -c "\du USR_NAME" | cut -d "|" -f 1
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"
[[ -n $(psql -qtA -c "\du ${1}" | cut -d "|" -f 1) ]] && echo "exists" || echo "does not exist"
