PostgreSQL Create User and Database

Monday, August 05, 2024

Create User in PostgreSQL

To create a user in PostgreSQL, you can use the CREATE USER command. The following example creates a user named myuser with the password mypassword.

CREATE USER myuser WITH PASSWORD 'mypassword';

Create Database in PostgreSQL

To create a database in PostgreSQL, you can use the CREATE DATABASE command. The following example creates a database named mydatabase with the owner postgres.

CREATE DATABASE mydatabase WITH OWNER postgres ENCODING 'UTF8';

The following example creates a database named mydatabase with the owner myuser.

CREATE DATABASE mydatabase WITH OWNER myuser ENCODING 'UTF8';

Grant Privileges to User in PostgreSQL

To grant privileges to a user in PostgreSQL, you can use the GRANT command. The following example grants all privileges on the database mydatabase to the user myuser.

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Grant Schema Privileges to User in PostgreSQL

To grant schema privileges to a user in PostgreSQL, you can use the GRANT command. The following example grants all privileges on the schema public to the user myuser. This ensures that user myuser can create, modify, and delete objects within the public schema.

-- connect to the mydatabase database.
postgres=>\c mydatabase
GRANT ALL PRIVILEGES ON SCHEMA public TO myuser;