DevOps Blog

A Beginner's Guide to Creating Users, Databases, and Managing Permissions in PostgreSQL

PostgreSQL is a powerful, open-source relational database system. If you're just getting started, this guide will walk you through the fundamentals of setting up users, creating databases, and managing permissions.

Understanding the PostgreSQL Security Model

Before we dive into commands, it's important to understand how PostgreSQL handles security:

Roles: PostgreSQL uses "roles" to manage authentication and authorization. A role can be a user or a group of users.
Privileges: These are permissions that determine what actions a role can perform (like creating databases, reading tables, etc.).
Inheritance: Roles can inherit privileges from other roles, making permission management easier.

Creating Your First User (Role)

In PostgreSQL, even individual users are created as "roles". Here's how to create one:

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';

Let's break this down:

CREATE ROLE is the command to create a new role
myuser is the name of your new user
WITH LOGIN allows this role to log in (without this, it would be a group role)
PASSWORD 'mypassword' sets the authentication password

To make this simpler, PostgreSQL provides a convenience command:

CREATE USER myuser WITH PASSWORD 'mypassword';

This is exactly equivalent to CREATE ROLE ... WITH LOGIN - CREATE USER is just more readable.

Creating a Database

Now let's create a database for your application:

CREATE DATABASE mydatabase;

This creates a new database called "mydatabase" with default settings. The database will be owned by the role that created it (usually your initial admin user).

To create a database owned by your new user:

CREATE DATABASE mydatabase OWNER myuser;

Connecting to Your Database

To connect to your new database with your new user, you can use:

psql -U myuser -d mydatabase -h localhost

You'll be prompted for the password you set earlier.

Understanding Privileges and Permissions

Now that you have a user and database, you'll need to grant appropriate permissions. PostgreSQL has many granular permissions, but let's start with the basics.

Common Privileges:

CONNECT: Allows connecting to a database
CREATE: Allows creating schema objects (tables, views, etc.)
SELECT, INSERT, UPDATE, DELETE: Table-level permissions
USAGE: For schemas and sequences

Granting Basic Permissions

To allow your user to connect to the database:

GRANT CONNECT ON DATABASE mydatabase TO myuser;

To allow your user to create tables in the public schema:

GRANT CREATE ON SCHEMA public TO myuser;

For a typical application user that needs full access to their own database:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Practical Example: Setting Up a Web Application

Let's walk through a complete example for setting up a web application:

-- Create a dedicated role for your application
CREATE USER webapp WITH PASSWORD 'securepassword123';
 
-- Create the application database
CREATE DATABASE webapp_db OWNER webapp;
 
-- Grant necessary privileges (connect and create tables)
GRANT CONNECT ON DATABASE webapp_db TO webapp;
GRANT CREATE ON SCHEMA public TO webapp;

Now your web application can:

Connect to the database
Create tables as needed during setup/migrations
Perform all CRUD operations on its own tables

Viewing Existing Roles and Privileges

To see all roles:

SELECT rolname FROM pg_roles;

To see privileges for a specific database:

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name = 'your_table';

Best Practices for Beginners

Avoid using the default postgres superuser for applications - create dedicated roles instead.
Follow the principle of least privilege - only grant the permissions absolutely needed.
Use strong passwords - database security is critical.
Consider using schemas to organize database objects if your application grows.
Document your permissions so you remember why each grant was made.

Next Steps

Now that you have users and databases set up, you might want to:

Create tables to store your data
Learn about schema organization
Explore more advanced permission management
Set up automated backups

Remember, PostgreSQL's security model is powerful but can be complex. Start simple with these basics, and as you grow more comfortable, you can explore more advanced features like role inheritance, row-level security, and default privileges.

Happy databasing!