How to Create a New User and Assign Database Permissions in PostgreSQL

PostgreSQL, as a powerful open-source relational database management system (RDBMS), places strong emphasis on security and granular permission control.

Properly creating users and assigning appropriate permissions is fundamental to safeguarding database integrity and data security.

This article provides a comprehensive, step-by-step guide to creating new users and configuring database permissions in PostgreSQL, adhering to industry best practices and the principle of least privilege.

Prerequisites

Before starting the operations, ensure you meet the following requirements:

1. Access to a Superuser Account: You need to log in to PostgreSQL using a superuser (the default superuser is postgres), as only superusers or database owners have the authority to create users and grant permissions.
2. psql Client or GUI Tool: You can use the command-line psql client (recommended for most scenarios) or graphical tools like pgAdmin for operations. This guide focuses on psql for universal applicability.

Connect to PostgreSQL via the terminal (local connection example):

psql -U postgres -h localhost -p 5432

• U: Specifies the user (here, the superuser postgres).
• h: Specifies the host (use localhost for local connections).
• p: Specifies the port (default PostgreSQL port is 5432).

Step 1: Create a New User (Role)

In PostgreSQL, “users” and “roles” are essentially interchangeable—users are just roles with the LOGIN privilege enabled. Below are the core syntax and common configurations for creating users:

Basic User Creation (with Password)

The most basic command to create a login-enabled user with a password:

-- Create a regular user with login access and a password
CREATE USER [username] WITH LOGIN PASSWORD '[password]';

-- Example: Create user "test_user" with password "Test@123456"
CREATE USER test_user WITH LOGIN PASSWORD 'Test@123456';

Security Note: Always use complex passwords (combining uppercase/lowercase letters, numbers, and special characters) to avoid weak passwords like “123456” or “password”.

Advanced User Configuration (Optional Parameters)

For more refined user management, add additional attributes to control account behavior:

-- Create a user with extended attributes
CREATE USER test_user
WITH
  LOGIN                    -- Mandatory: Allows the user to log in (omitting this creates a non-login role)
  PASSWORD 'Test@123456'   -- Password for authentication
  VALID UNTIL '2027-01-01' -- Optional: Password expiration date (enhances security)
  CONNECTION LIMIT 10      -- Optional: Maximum concurrent connections (0 = unlimited)
  NOSUPERUSER              -- Explicitly deny superuser privileges (default for new users)
  NOCREATEDB               -- Explicitly deny database creation permission (default)
  NOCREATEROLE;            -- Explicitly deny role creation permission (default)

Verify User Creation

To confirm the user was created successfully:

See also: Mastering the Linux Command Line — Your Complete Free Training Guide

-- List all users/roles (psql shortcut)
\\\\du

-- Or query the system catalog for precise verification
SELECT usename, usesysid, passwd FROM pg_user WHERE usename = 'test_user';

The output will display the new user’s details (e.g., test_user with Login privilege).

Step 2: Assign Database Permissions

PostgreSQL’s permission system is hierarchical—database-level permissions only grant basic access, while object-level (e.g., tables, schemas) permissions control actual data operations. Follow this layered approach to grant permissions:

1. Grant Database-Level Basic Permissions

Database-level permissions control core actions like connecting to the database and creating objects. The most commonly used permissions are:

• CONNECT: Allows the user to connect to the target database (the foundation for all operations).
• CREATE: Allows the user to create schemas, tables, views, etc., within the database.
• TEMP/TEMPORARY: Allows the creation of temporary tables (often included with CREATE, but can be granted independently).

Core Syntax for Granting Database Permissions

-- Grant specific permissions on a database to a user
GRANT [permission1, permission2, ...] ON DATABASE [database_name] TO [username];

-- Example 1: Grant only connect permission (read-only foundation)
GRANT CONNECT ON DATABASE mydb TO test_user;

-- Example 2: Grant connect + create permissions (for developers)
GRANT CONNECT, CREATE ON DATABASE mydb TO test_user;

-- Example 3: Grant all database permissions (for admins ONLY—avoid in production)
GRANT ALL PRIVILEGES ON DATABASE mydb TO test_user;

2. Grant Schema-Level Permissions (Critical Supplement)

Granting CONNECT/CREATE on a database is not enough—users also need schema permissions to access or modify objects (e.g., tables) within the database. The default schema is public (adjust if using custom schemas):

-- Switch to the target database first
\\\\c mydb

-- Grant "usage" permission (required to access objects in the schema)
GRANT USAGE ON SCHEMA public TO test_user;

-- Optional: Grant "create" permission (allows creating tables/views in the schema)
GRANT CREATE ON SCHEMA public TO test_user;

3. Grant Table-Level Data Operation Permissions

This is the final layer of permission control, governing actions like querying, inserting, or modifying table data. Tailor permissions to business needs:

Grant Permissions on Existing Tables

-- Grant common data operation permissions on all tables in the "public" schema
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO test_user;

-- For read-only users (only grant SELECT)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_user;

-- For full access (use cautiously in production)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO test_user;

Grant Default Permissions for Future Tables

New tables created after the above grants will not inherit permissions by default. Set default privileges to avoid reconfiguring permissions for new tables:

-- Grant default SELECT/INSERT permissions for future tables in the "public" schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT ON TABLES TO test_user;

-- For sequences (required if tables have auto-increment columns like SERIAL/BIGSERIAL)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO test_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO test_user;

Step 3: Verify Permissions

After configuring permissions, validate that they work as expected to avoid access issues:

1. Check Permissions via System Functions

Use PostgreSQL’s built-in permission-checking functions for precise verification:

-- Check if the user has CONNECT/CREATE permissions on the database
SELECT
  has_database_privilege('test_user', 'mydb', 'CONNECT') AS can_connect,
  has_database_privilege('test_user', 'mydb', 'CREATE') AS can_create_objects;

-- Check if the user has SELECT permission on a specific table
SELECT has_table_privilege('test_user', 'public.existing_table', 'SELECT') AS can_select;

The function returns t (true) if the permission is granted, f (false) otherwise.

2. Test Permissions by Logging in as the New User

Simulate the new user’s access to validate real-world functionality:

-- Switch to the target database as test_user
\\\\c mydb test_user

-- Test CREATE permission (create a test table)
CREATE TABLE test_table (id INT); -- Succeeds if CREATE is granted, fails otherwise

-- Test SELECT permission (query an existing table)
SELECT * FROM existing_table; -- Succeeds if SELECT is granted, fails otherwise

-- Test INSERT permission
INSERT INTO test_table (id) VALUES (1); -- Succeeds if INSERT is granted, fails otherwise

Additional Operations: Modify/Revoke Permissions & Delete Users

Modify User Attributes (e.g., Change Password)

-- Update the user's password
ALTER USER test_user WITH PASSWORD 'NewTest@67890';

-- Lift connection limit (set to 0 for unlimited)
ALTER USER test_user WITH CONNECTION LIMIT 0;

Revoke Permissions

If permissions are over-granted, revoke them immediately:

-- Revoke CREATE permission on the database
REVOKE CREATE ON DATABASE mydb FROM test_user;

-- Revoke SELECT permission on all tables
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM test_user;

Delete a User

Delete a user only after revoking their permissions and ensuring no active connections:

-- Drop the user (use IF EXISTS to avoid errors if the user does not exist)
DROP USER IF EXISTS test_user;

-- If the user has active connections, terminate them first (PostgreSQL 13+)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'test_user';

-- Then delete the user
DROP USER test_user;

Best Practices for Permission Management

1. Principle of Least Privilege: Only grant the minimum permissions required for the user to perform their job (e.g., read-only users get only SELECT, no INSERT/UPDATE).
2. Avoid Superuser Privileges: Never assign superuser status to regular business users—reserve it for database administrators only.
3. Use Roles for Batch Management: For teams, create roles (e.g., read_only_role, write_role) with predefined permissions, then assign roles to users (instead of granting permissions to individual users).
4. Regular Permission Audits: Periodically review user permissions (using \\\\l+, \\\\du+, or system catalog queries) to revoke unused permissions and reduce security risks.
5. Secure Password Policies: Enforce complex passwords and set expiration dates for user accounts to prevent unauthorized access.

Conclusion

Creating users and assigning permissions in PostgreSQL requires a hierarchical approach: start with user creation, then grant database-level access, schema-level permissions, and finally table-level data operation rights. By adhering to the principle of least privilege and verifying permissions rigorously, you can ensure database security while enabling users to perform their required tasks.

Key takeaways:

• Always create users with LOGIN and strong passwords.
• Layer permissions (database → schema → table) to avoid incomplete access.
• Set default privileges for future tables to maintain consistency.
• Regularly audit and revoke unnecessary permissions to minimize security risks.

Following these steps and best practices will help you build a secure, well-managed PostgreSQL permission system tailored to your application’s needs.