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:

    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:

    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:

    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:

With this setup, the app_user will have different levels of access to the public and private schemas, and when connected, it will start in the public schema by default.

Leave a Reply

Your email address will not be published. Required fields are marked *