. Connect to PostgreSQL as an admin user:

psql -U postgres
  1. Create the new database:
CREATE DATABASE mydatabase;
  1. Verify the database was created:
\l

Create a New Role (User)

  1. Create a new role with password authentication:
CREATE ROLE myuser WITH LOGIN PASSWORD 'secure_password';
  1. Grant this role permissions to connect to the new database:
GRANT CONNECT ON DATABASE mydatabase TO myuser;
  1. Connect to the new database:
\c mydatabase
  1. Grant permissions on the public schema:
GRANT USAGE ON SCHEMA public TO myuser;
GRANT CREATE ON SCHEMA public TO myuser;
  1. Grant privileges to create tables and use sequences:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;
  1. Set default privileges for future tables and sequences:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO myuser;

Verify the Setup

  1. Exit from the PostgreSQL prompt:
\q
  1. Connect to the database as the new user:
psql -U myuser -d mydatabase
  1. Try creating a test table:
CREATE TABLE test (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test (name) VALUES ('test entry');
SELECT * FROM test;
  1. Verify that the user cannot access other databases:
\c postgres

This should fail with a permission denied error.