To create a user in PostgreSQL and differentiate it between schemas, you’ll typically follow these steps:
- Create the User: You can create a new user in PostgreSQL using the
CREATE ROLE
statement. Here’s a basic example:123CREATE ROLE username WITH LOGIN PASSWORD 'password';Replace
username
with the desired username andpassword
with the password for the user. You can also specify additional options such asSUPERUSER
,CREATEDB
,CREATEROLE
, etc., based on your requirements. - 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:123GRANT USAGE ON SCHEMA schema_name TO username;This grants the user
username
the ability to use objects in the schema namedschema_name
. You can grant other privileges likeSELECT
,INSERT
,UPDATE
,DELETE
, etc., depending on the level of access required. - 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:123ALTER 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. - 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
:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Create the user CREATE ROLE app_user WITH LOGIN PASSWORD 'password'; -- Create the restricted schema CREATE SCHEMA restricted_schema; -- Grant USAGE on the restricted schema GRANT USAGE ON SCHEMA restricted_schema TO app_user; -- Set the default search path to the restricted schema ALTER ROLE app_user SET search_path = restricted_schema; |
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.