PostgreSQL Create User: A Complete Guide [2024]

Meta Information

Introduction

Basic User Creation

Creating a New User

-- Basic user creation
CREATE USER username WITH PASSWORD 'secure_password';

-- Create user with additional options
CREATE USER username 
WITH PASSWORD 'secure_password'
VALID UNTIL '2025-01-01'
CONNECTION LIMIT 5
CREATEDB;

Common User Creation Options

• SUPERUSER / NOSUPERUSER - Grant or deny superuser privileges
• CREATEDB / NOCREATEDB - Allow/disallow database creation
• CREATEROLE / NOCREATEROLE - Allow/disallow role creation
• LOGIN / NOLOGIN - Allow/disallow login
• REPLICATION / NOREPLICATION - Allow/disallow replication
• VALID UNTIL - Set password expiration date
• CONNECTION LIMIT - Set maximum concurrent connections

User Management Examples

1. Create a Database Owner

-- Create user for database ownership
CREATE USER app_owner WITH PASSWORD 'secure_password';
CREATE DATABASE app_database OWNER app_owner;

-- Grant necessary privileges
GRANT ALL PRIVILEGES ON DATABASE app_database TO app_owner;

2. Create a Read-Only User

-- Create read-only user
CREATE USER read_only_user WITH PASSWORD 'secure_password';

-- Grant connect and usage
GRANT CONNECT ON DATABASE app_database TO read_only_user;
GRANT USAGE ON SCHEMA public TO read_only_user;

-- Grant select on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

-- Grant select on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO read_only_user;

3. Create an Application User

-- Create application user with specific privileges
CREATE USER app_user WITH PASSWORD 'secure_password';

-- Grant necessary privileges
GRANT CONNECT ON DATABASE app_database TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

Role Management

Creating and Managing Roles

-- Create a role
CREATE ROLE developers;

-- Add privileges to role
GRANT CONNECT ON DATABASE app_database TO developers;
GRANT USAGE ON SCHEMA public TO developers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developers;

-- Assign user to role
GRANT developers TO username;

Security Best Practices

1. Password Policies

-- Create user with password encryption
CREATE USER secure_user WITH 
    PASSWORD 'secure_password' 
    ENCRYPTED 
    VALID UNTIL '2025-01-01';

2. Connection Limits

-- Limit concurrent connections
CREATE USER limited_user WITH 
    PASSWORD 'secure_password' 
    CONNECTION LIMIT 5;

3. Schema Isolation

-- Create schema and restrict access
CREATE SCHEMA app_schema;
REVOKE ALL ON SCHEMA app_schema FROM PUBLIC;
GRANT USAGE ON SCHEMA app_schema TO app_user;

User Management Commands

View Users and Roles

-- List all users
SELECT usename, usesuper, usecreatedb, valuntil 
FROM pg_user;

-- List user privileges
SELECT grantor, grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'username';

Modify User Properties

-- Change password
ALTER USER username WITH PASSWORD 'new_secure_password';

-- Modify user properties
ALTER USER username WITH CONNECTION LIMIT 10;
ALTER USER username VALID UNTIL '2025-12-31';

Remove Users and Privileges

-- Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE app_database FROM username;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;

-- Drop user
DROP USER IF EXISTS username;

Troubleshooting Common Issues

1. Connection Issues

-- Check user connection settings
SELECT * FROM pg_user WHERE usename = 'username';
SELECT * FROM pg_stat_activity WHERE usename = 'username';

2. Permission Issues

-- Check user privileges
SELECT * FROM information_schema.role_table_grants 
WHERE grantee = 'username';

Best Practices Summary

1. Always use strong passwords
2. Implement the principle of least privilege
3. Use roles for group permissions
4. Regularly audit user access
5. Set password expiration dates
6. Limit connection counts for service accounts
7. Use schema isolation for security
8. Regular backup of user privileges

Conclusion