PostgreSQL - How to create a new user?
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.
