Introduction to access control

Creating PostgreSQL Databases

Darryl Reeves

Industry Assistant Professor, New York University

The default superuser

Muscle Image

  • postgres "superuser" role
  • Administers database
  • postgres privileges
    • Creating databases
    • Dropping databases
    • Inserting records
    • Deleting records
    • Dropping tables
  • postgres user should be used with care
Creating PostgreSQL Databases

Example: a personal finance database

  • Creation of finances database Finances Database
Creating PostgreSQL Databases

Example: a personal finance database

Database and Tables

Creating PostgreSQL Databases

Example: a personal finance database

  • Database is personal and not publicly accessible
  • User with restricted access should be created
  • User abilities:
    • Adding records
    • Querying records
    • Editing records
Creating PostgreSQL Databases

Creating new users

  • CREATE USER
    • Used to generate a new account
      CREATE USER newuser;
  • newuser can create tables in database
  • No access to tables created by other users
Creating PostgreSQL Databases

Setting user password

  • Passwords enhance security
  • No passwords by default
    CREATE USER newuser WITH PASSWORD 'secret';

ALTER USER newuser WITH PASSWORD 'new_password';
Creating PostgreSQL Databases

Let's practice!

Creating PostgreSQL Databases

Preparing Video For Download...