How to check if a postgres user exists?

Question

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.

score 189 · Accepted Answer · edited Nov 04 '22 at 01:06

189

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 ...

edited Nov 04 '22 at 01:06

jbranchaud

5,909
9
45
70

answered Dec 17 '11 at 18:04

Michael Krelin - hacker

138,757
24
193
173

Do you remember what's the built-in command-line utility to execute SQL? In the end I'd prefer to execute and retrieve the result from shell if possible. – m33lky Dec 17 '11 at 18:08
1

`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`. – Michael Krelin - hacker Dec 17 '11 at 18:13
3

@m33lky: Or you could test the return value of this command in the shell (as postgres user): `psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='USR_NAME'"`. Yields `1` if found and nothing else. – Erwin Brandstetter Dec 17 '11 at 18:23
1

Haha, I just did that in a little more ugly fashion: `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. – m33lky Dec 17 '11 at 18:41
2

@m33lky: I only wrote a comment, because I think Michael deserves the credit on this one. He contributed the main part. And he proved to be a good sport in the past. :) Maybe Michael wants to incorporate it in his answer? – Erwin Brandstetter Dec 17 '11 at 18:46
@ErwinBrandstetter, okay, to make us even :) – Michael Krelin - hacker Dec 17 '11 at 18:55
He recommended directing output to /dev/null ;D – m33lky Dec 17 '11 at 18:59
@m33lky, I recommended devnulling `createuser` output, not `psql`'s. – Michael Krelin - hacker Dec 17 '11 at 19:00
1

I'd recommend including the `-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 ...`. – jbranchaud Nov 04 '22 at 01:02
@jbranchaud, makes sense. I personally never once created a `.psqlrc` file for myself, didn't think of it :) – Michael Krelin - hacker Nov 04 '22 at 09:25

score 12 · Answer 2 · edited May 23 '17 at 12:26

12

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

edited May 23 '17 at 12:26

Community

1
1

answered Aug 19 '16 at 09:43

matt

1,046
1
13
26

This would generate a larger query result than answer http://stackoverflow.com/a/8546783/107158. However, unlike that answer, this one would survive a rename to system table `pg_roles`, but not a change to command `\du`. Which is most likely not to change? – Derek Mahar Oct 04 '16 at 17:24

score 3 · Answer 3 · answered Jan 11 '19 at 12:34

3

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"

answered Jan 11 '19 at 12:34

Pascal Polleunus

2,411
2
28
30

cmcc · Answer 4 · 2017-11-02T19:27:06.683

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.

score 2 · Answer 5 · answered May 26 '22 at 14:24

2

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$$;

answered May 26 '22 at 14:24

Ben Millwood

6,754
24
45

How to check if a postgres user exists?

5 Answers5

Linked