Create Databases and Roles
A PostgreSQL cluster can contain multiple databases, and each database can contain schemas and objects. Roles are used for login identities and privilege management; a role can have the LOGIN attribute or be used as a group role. Prefer least privilege: application roles should receive only the permissions they need, and migrations may use a separate role with controlled schema privileges.
A database owner, schema owner, and table privileges are distinct concepts. Grant the necessary rights explicitly and review default privileges for objects created in the future.
Create an application role
CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret';
CREATE DATABASE app_db OWNER app_user;
GRANT CONNECT ON DATABASE app_db TO app_user;💡 This is illustrative SQL. Use a strong secret supplied securely, and follow your environment's password and role policy. Ownership grants broad control, so production role designs may separate database ownership from runtime access.
Key Takeaways
- Roles are central to authentication and authorization.
- Database ownership differs from individual table privileges.
- Use least-privilege roles for application connections.
Knowledge Check
Which principle should guide permissions for an application database role?