CREATE USER statement

CREATE USER username WITH PASSWORD 'your_password';

User options

Apart from PASSWORD, there are some other options that we can add in:

  • SUPERUSER / NOSUPERUSER
  • CREATEDB / NOCREATEDB
  • CREATEROLE / NOCREATEROLE
  • INHERIT / NOINHERIT
  • LOGIN / NOLOGIN
  • REPLICATION / NOREPLICATION
  • BYPASSRLS / NOBYPASSRLS
  • CONNECTION LIMIT connlimit
  • VALID UNTIL ‘timestamp’
  • IN ROLE role_name [, …]
  • IN GROUP role_name [, …]
  • ROLE role_name [, …]
  • ADMIN role_name [, …]
  • USER role_name [, …]
  • SYSID uid

Then grant the privileges to the new user:

GRANT ALL PRIVILEGES ON dbname TO username;

If you want to grant only some specific privileges to the new user:

GRANT privilege ON dbname TO username;

The possible privileges are:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • REFERENCES
  • TRIGGER
  • CREATE
  • CONNECT
  • TEMPORARY
  • EXECUTE
  • USAGE

If you want to take back all the privileges:

REVOKE ALL PRIVILEGES ON dbname FROM username;

To create a superuser that can bypass all permission checks:

CREATE USER username SUPERUSER WITH PASSWORD 'your_password';

To learn more about creating superuser, visit this post.

If you want to delete a user:

DROP USER username;

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

Download TablePlus here.

TablePlus GUI Tool PostgreSQL