PostgreSQL Permissions

Controlling access to database objects

🔐 What are Permissions?

Permissions control what actions users can perform on database objects like tables, views, and functions. They ensure data security by restricting access to authorized users only, protecting sensitive information from unauthorized modifications. 


-- Grant SELECT permission on a table
GRANT SELECT ON employees TO app_user;

-- Grant multiple permissions
GRANT SELECT, INSERT ON products TO sales_team;

Output:

GRANT

Permission Types

👁️

SELECT

Read data from tables 

GRANT SELECT ON table TO user;
➕

INSERT

Add new rows to tables 

GRANT INSERT ON table TO user;
✏️

UPDATE

Modify existing data 

GRANT UPDATE ON table TO user;
🗑️

DELETE

Remove rows from tables 

GRANT DELETE ON table TO user;

🔹 Granting Table Permissions

Table permissions control data access and modification rights. Granting appropriate permissions ensures users can perform their tasks while maintaining data integrity. Always follow the principle of least privilege, giving users only the permissions they need. 

-- Grant SELECT on a table
GRANT SELECT ON customers TO readonly_user;

-- Grant multiple permissions
GRANT SELECT, INSERT, UPDATE ON orders TO sales_user;

-- Grant all permissions
GRANT ALL PRIVILEGES ON products TO admin_user;

-- Grant on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

Output:

GRANT

🔹 Granting Database Permissions

Database-level permissions control connection and schema creation rights. These permissions are broader than table permissions and affect the entire database. CONNECT allows users to access the database, while CREATE enables schema and object creation. 

-- Grant connection permission
GRANT CONNECT ON DATABASE mydb TO app_user;

-- Grant schema creation
GRANT CREATE ON DATABASE mydb TO developer;

-- Grant temporary table creation
GRANT TEMP ON DATABASE mydb TO temp_user;

Output:

GRANT

🔹 Granting Schema Permissions

Schema permissions control object creation and usage within schemas. Schemas organize database objects into logical groups. USAGE permission allows access to schema objects, while CREATE permits new object creation within the schema. 

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant create permission
GRANT CREATE ON SCHEMA analytics TO data_team;

-- Grant all schema permissions
GRANT ALL ON SCHEMA reporting TO report_admin;

Output:

GRANT

🔹 Revoking Permissions

Remove permissions when users no longer need access to objects. Revoking permissions is essential for maintaining security when roles change or users leave. Always audit permissions regularly to ensure appropriate access levels. 

-- Revoke SELECT permission
REVOKE SELECT ON customers FROM old_user;

-- Revoke multiple permissions
REVOKE INSERT, UPDATE ON orders FROM former_employee;

-- Revoke all permissions
REVOKE ALL PRIVILEGES ON products FROM temp_user;

-- Revoke from all tables
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM guest;

Output:

REVOKE

🔹 Column-Level Permissions

Grant permissions on specific columns for fine-grained access control. Column-level permissions allow users to access only certain fields in a table, protecting sensitive data like salaries or personal information while allowing access to other columns. 

-- Grant SELECT on specific columns
GRANT SELECT (name, email) ON employees TO hr_user;

-- Grant UPDATE on specific columns
GRANT UPDATE (status, updated_at) ON orders TO support_team;

-- Multiple columns with different permissions
GRANT SELECT (id, name), UPDATE (status) ON tasks TO project_manager;

Output:

GRANT

🔹 Default Permissions

Set default permissions for future objects automatically. Default privileges ensure new tables, views, or functions inherit appropriate permissions without manual intervention. This simplifies permission management as your database grows and evolves. 

-- Set default SELECT for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_role;

-- Default permissions for specific user's objects
ALTER DEFAULT PRIVILEGES FOR USER developer IN SCHEMA public
GRANT SELECT, INSERT ON TABLES TO app_user;

-- Default for sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE ON SEQUENCES TO app_user;

Output:

ALTER DEFAULT PRIVILEGES

🔹 Checking Permissions

View current permissions to audit database security effectively. Regular permission audits help identify over-privileged accounts and ensure compliance with security policies. PostgreSQL provides several commands and views to inspect access rights. 

-- Check table permissions
\dp tablename

-- Query table privileges
SELECT grantee, privilege_type 
FROM information_schema.table_privileges 
WHERE table_name = 'employees';

-- Check schema permissions
SELECT nspname, nspacl 
FROM pg_namespace 
WHERE nspname = 'public';

Output: 

 grantee    | privilege_type
------------+----------------
 app_user   | SELECT
 admin_user | SELECT
 admin_user | INSERT

🧠 Test Your Knowledge

Which command grants read access to a table?