PostgreSQL access privileges

Creating PostgreSQL Databases

Darryl Reeves

Industry Assistant Professor, New York University

PostgreSQL roles and privileges

  • Users are a type of role
  • Group roles can also be defined
  • Database object access given to roles
Creating PostgreSQL Databases

The GRANT command

  • Privileges are "granted" to roles by owner
  • The GRANT command bestows privileges
  • Many privileges can be granted including:
    • SELECT
    • DELETE
    • UPDATE
      GRANT p ON obj TO grantee;
Creating PostgreSQL Databases

Example: personal finance database

CREATE TABLE account (
    id SERIAL PRIMARY KEY,
    short_name VARCHAR(25),
    provider_id INTEGER REFERENCES provider(id),
    balance DECIMAL
);

CREATE USER fin WITH PASSWORD '38\5)uk1+3&}*Y';
Creating PostgreSQL Databases

Example: personal finance database

  • fin user needs access to account table
  • fin access
    • Add new accounts
    • Update accounts
    • Query accounts
  • Superuser grants privileges
    GRANT INSERT ON account TO fin;

GRANT UPDATE ON account TO fin;

GRANT SELECT ON account TO fin;
Creating PostgreSQL Databases

Table modification privileges

  • Some privileges cannot be granted
  • Modifying table requires ownership
ALTER TABLE account ADD COLUMN date_opened DATE;

ALTER TABLE account RENAME COLUMN short_name

TO nickname;

ALTER TABLE account OWNER TO fin;
Creating PostgreSQL Databases

Let's practice!

Creating PostgreSQL Databases

Preparing Video For Download...