Postgresql – Create Database and Add Users.

To create a user in PostgreSQL and differentiate it between schemas, you’ll typically follow these steps:

1. Create the User: You can create a new user in PostgreSQL using the CREATE ROLE statement. Here’s a basic example:
   CREATE ROLE username WITH LOGIN PASSWORD 'password';

   1 2 3

   CREATE ROLE username WITH LOGIN PASSWORD 'password';

   Replace username with the desired username and password with the password for the user. You can also specify additional options such as SUPERUSER, CREATEDB, CREATEROLE, etc., based on your requirements.

2. Grant Privileges on Schemas: After creating the user, you can grant privileges on specific schemas to control its access. You can use the GRANT statement to grant privileges to the user on the desired schema. For example:
   GRANT USAGE ON SCHEMA schema_name TO username;

   1 2 3

   GRANT USAGE ON SCHEMA schema_name TO username;

   This grants the user username the ability to use objects in the schema named schema_name. You can grant other privileges like SELECT, INSERT, UPDATE, DELETE, etc., depending on the level of access required.

3. Set Search Path: Optionally, you can set the search path for the user to default to a specific schema. This ensures that when the user connects to the database, it starts with the specified schema. You can set the search path using the SET statement:

   ALTER ROLE username SET search_path = schema_name;

   1 2 3

   ALTER ROLE username SET search_path = schema_name;

   Replace schema_name with the name of the schema you want to set as the default search path for the user.

4. Connect as the User: After creating the user and granting privileges, the user can connect to the database using a PostgreSQL client (such as psql) and start working within the specified schema.

Here’s an example of how you might create a user named app_user and differentiate its access to two schemas, public and private: