How to create a new user with full privileges in Postgres?

Create a new user with all privileges in Postgres

This tutorial shows how to create a new user in PostgreSQL and grant all privileges on the newly created user.

1. From command line:

$ su - postgres 
$ psql template1
template1=# CREATE USER user_name WITH PASSWORD 'password';
template1=# GRANT ALL PRIVILEGES ON DATABASE db_name to user_name;
template1=# \q

2. From SQL query:

CREATE USER user_name [ [ WITH ] option [ ... ] ];

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

Then grant access to the new user:

GRANT ALL PRIVILEGES ON DATABASE db_name to user_name;

---

Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS