PostgreSQL: How To Create A New User & Grant Privileges

In this post, I will tell you how to create a new user in PostgreSQL and grant privileges to do CRUD operations on database tables. If you have not installed PostgreSQL in your system please use the following links to install it in your system. In addition, you will also learn some of the most frequently used psql ( CLI used to interact with PostgreSQL ) commands.

Instaling PostgreSQL 

• Windows: installing PostgreSQL in Windows

• MacOS: installing PostgreSQL in MacOS

• Linux: Instaling PostgreSQL in Linux

Interacting with PostgreSQL via psql

The psql command-line interface (CLI) is the primary tool provided by PostgreSQL to interact with its databases. It allows users to execute SQL commands, manage databases, and perform various database-related tasks directly from the terminal. The psql tool is powerful and comes with many built-in commands (called meta-commands) that provide additional functionality beyond standard SQL commands.

Key Features of psql:

1. Connect to PostgreSQL Databases:
   You can connect to a PostgreSQL database with the following command:

   psql -U username -d database_name -h host

• -U: Specifies the username.
• -d: Specifies the database name.
• -h: Specifies the host (optional, defaults to localhost).

1. Execute SQL Queries:
   Once connected, you can run any SQL query or command:

   SELECT * FROM table_name;

3. Meta-Commands (Backslash Commands):
   In addition to SQL, psql provides meta-commands that allow you to perform various tasks. These commands start with a backslash (\) and are specific to psql. Examples:

• \l: List all databases.
• \dt: List all tables.
• \du: List all roles/users.
• \d table_name: Show the structure of a table.

4. Exporting Query Results:
   You can export query results to a file using the \copy command:

   \copy (SELECT * FROM table_name) TO 'output.csv' WITH CSV HEADER;

5. Running Queries with psql Non-Interactive Mode:
   You can run SQL queries or scripts without entering the psql prompt, making it useful for automation:

   psql -U username -d database_name -c "SELECT * FROM table_name;"

6. Help and Documentation:
   You can get help on SQL commands and psql features directly within the interface:

   \h   -- SQL command help
   \?   -- Meta-command help

Creating a new user & Granting Privileges in PostgreSQL

Creating a new user in PostgreSQL involves adding a user and then assigning them specific privileges, such as the ability to read, write, or manage a database. This process helps control access and manage security within the database system, ensuring that each user has the appropriate level of permissions for their tasks.

Step 1: Create a New User in PostgreSQL

You can create a new user in PostgreSQL with

command : CREATE USER username WITH PASSWORD 'password`

CREATE USER john WITH PASSWORD 'stdAdmin';

Step 2: Grant Connection Privilege to the Database

To allow the user to connect to a specific database, grant them the CONNECT privilege

command: GRANT CONNECT ON DATABASE my_database TO username

GRANT CONNECT ON DATABASE student TO john;

Step 3: Grant Usage on Schema

If your tables are in the default public schema or any other schema, you must grant USAGE on that schema to allow access to its objects:

command: GRANT USAGE ON SCHEMA public TO username

GRANT USAGE ON SCHEMA public TO john;

Replace public with the schema name if different.

Step 4: Grant CRUD Privileges on Tables

Now, grant the user CRUD privileges (i.e., SELECT, INSERT, UPDATE, DELETE) on all tables in the schema:

command : GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO username

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO john;

Step 5: Apply Permissions to Future Tables

If you plan to create more tables in the future and want the user to automatically have CRUD privileges on them, you need to set default privileges:

command: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO john;

Step 6: (Optional) Grant Additional Privileges

If the user needs to create new tables or drop existing ones, you can grant them those privileges:

command: GRANT CREATE, DROP ON SCHEMA public TO username

GRANT CREATE, DROP ON SCHEMA public TO john;

This allows the user to create or drop tables within the schema.

Most frequently used psql commands

Here’s a table of commonly used PostgreSQL-specific commands (meta-commands) that are used in the psql command-line interface:

Command	Description
\c dbname	Connect to a specific database.
\l	List all databases.
\du	List all roles (users) and their attributes.
\dt	List all tables in the connected database.
\d tablename	Show the schema (structure) of a specific table.
\dn	List all schemas in the connected database.
\dv	List all views in the connected database.
\df	List all functions in the connected database.
\i filename	Execute commands from a file (SQL script).
\timing	Toggle timing of SQL commands.
\q	Quit the PostgreSQL prompt.
\x	Toggle expanded table output mode.
\g	Execute the last SQL command again.
\h	Display help on SQL commands.
\password	Change the password for the current user.
\encoding	Show the current client encoding.
\watch [seconds]	Re-run the last query every specified number of seconds (default is 2 sec).
\copy	Copy data between a file and a table (for importing/exporting CSV, etc.).
\set	Set a psql variable.
\conninfo	Display information about the current database connection.
\qecho	Write text to the query output stream (useful in scripts).
\q	Quit the psql interface

Example Workflow when working with psql CLI

1. Start psql:
   Open psql and connect to a database:

   psql -U postgres -d mydb

2. View Tables:
   List all tables in the current database:

   \dt

3. Query a Table:
   Run an SQL query to retrieve data:

   SELECT * FROM users;

4. Check Command History:
   Press the Up arrow key to scroll through the command history and re-run or modify a query.
5. Exit psql:
   Quit the psql interface:

   \q

Resources

• What is PostgreSQL

• Tutorials and other resource

• PostgreSQL psql commands