Skip to main content

Access Control

Updated Oct 11, 2019 ·

Default Superuser​

In PostgreSQL, the default postgres user has superuser privileges, which allows for extensive database management.

  • Created automatically during installation
  • Capable of creating and dropping databases
  • Can insert, delete, and manage tables
  • Should be used with caution due to its broad permissions

Example: Personal Finance Database​

If existing finance tools don’t meet your needs, you can create a PostgreSQL database to manage your finances.

  • Track bank accounts, purchases, debts, and investments
  • Stored on your personal computer, which is not publicly accessible
  • Create a specific user for daily operations and use the postgres user for advanced tasks

Creating New Users​

Setting up a new database often involves creating users with restricted access. To do this, we can use the CREATE USER command to add new users with limited privileges.

CREATE USER newuser;

Note that new users cannot access tables created by others.

Setting User Password​

To secure user accounts, you should assign passwords. We can add a password using:

CREATE USER newuser WITH PASSWORD 'secret';

Users can later change their passwords with:

ALTER USER newuser WITH PASSWORD 'new_password';

Roles and Privileges​

In PostgreSQL, roles include both individual user accounts and groups of users. These roles control access to various database objects like databases, tables, and schemas.

  • User accounts are one type of role
  • Groups can consist of one or more users
  • Roles determine access to database objects

The GRANT Command​

To allow other roles to access a database object, the owner must use the GRANT command. This command specifies which privileges are granted to which roles.

GRANT p ON o-b-j TO grantee

Where:

  • p is the privilege (e.g., SELECT, DELETE, UPDATE)
  • o-b-j is the database object (e.g., table)
  • grantee is the role receiving the privilege

Example: Personal Finance Database​

To demonstrate the GRANT command, consider a personal finance database with an account table created by a superuser.

When a new user ted is created, it has no access to the account table.

CREATE USER ted WITH PASSWORD '!Qwaszxerdfcv10101';

To allow write access, use the superuser account to grant ted privileges to add, update, and query accounts:

GRANT INSERT ON account TO ted;
GRANT UPDATE ON account TO ted;
GRANT SELECT ON account TO ted;

Table Modification Privileges​

Not all privileges can be granted directly. Some actions, like altering table structure, require table ownership. This means that only the table owner can perform some of these actions.

  • Users cannot modify table structure without ownership

  • To enable such modifications, transfer table ownership using ALTER TABLE

    ALTER TABLE table_name OWNER TO user_name;

After ownership transfer, the user can now perform restricted commands.

Access Control with Schemas​

Schemas act as containers for database objects like tables, helping manage access to multiple objects efficiently.

  • Control access by granting privileges on entire schemas
  • Allows for managing permissions across related tables

Example: Schema Use in Finances Database​

To manage individual and shared finances, you can use schemas to separate accounts.

  • Create two schemas: "me" and "spouse"
  • Move existing tables from the public schema to these new schemas
  • Organize accounts separately while sharing access where needed

The SQL commands that you can use:

-- create the schemas 
CREATE schema me; 
CREATE schema spouse; 

-- create the tables 
CREATE TABLE me.account (...)
CREATE TABLE spouse.account (...)
CREATE USER

Granting Schema Privileges​

When creating user accounts for specific schemas, you need to assign appropriate privileges.

  • Grant USAGE privileges to users for accessing specific schemas
  • Assign specific privileges (e.g., SELECT, INSERT) for different schemas as needed

The SQL commands that you can use:

-- create the user 
CREATE USER better_half WITH PASSWORD '!p@ncak3h0us32023!'

-- grant USAGE on the "spouse" schema so user can access it
GRANT USAGE ON SCHEMA spouse TO "better_half";
GRANT USAGE ON SCHEMA public TO "better_half";

-- grant WRITE privileges
GRANT 
  SELECT, 
  INSERT,
  UPDATE,
  DELETE
ON ALL TABLES IN SCHEMA public 
TO "better_half";  

GRANT 
  SELECT, 
  INSERT,
  UPDATE,
  DELETE
ON ALL TABLES IN SCHEMA spouse 
TO "better_half";

Using Groups​

Managing privileges for multiple users can be simplified using group roles.

  • Create a group role to assign privileges to multiple users at once
  • Add users to the group to inherit these privileges

As an example, create a family group and grant it privileges on tables or schemas:

-- create the group 
CREATE GROUP family;

-- grant usage to schema 
GRANT USAGE ON SCHEMA public TO family; 

-- grant the privileges
GRANT SELECT, INSERT, UPDATE, DELETE 
ON ALL TABLES IN SCHEMA public TO family;

After this, individual user accounts can be created/added to the family group:

ALTER GROUP family 
ADD USER ted, better_half;

If there are more users to add in the group:

ALTER GROUP family 
ADD USER ted, better_half, user1, user2, user3;

Shared and Individual Data Access​

With schemas and groups, you can manage both shared and personal data access efficiently.

  • Group has access to all public schema tables
  • Each member can have a separate schema for personal data management

Removing Access​

Rollback Privileges​

If a user accidentally deletes data or misuses their access, you might need to adjust their privileges. To prevent unwanted actions like data deletion, you can remove specific privileges.

  • Use the command to revoke DELETE and TRUNCATE privileges to all tables in specific schema:

    REVOKE DELETE, TRUNCATE ON schema_name.* FROM user;

  • To revoke all access on all tables in the public schema:

    REVOKE DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public FROM user;

Resetting and Adjusting Privileges​

If a user should have very limited access, reset their privileges and grant only what’s needed.

  • Reset all privileges with the command:

    REVOKE ALL PRIVILEGES ON schema_name.* FROM user;

  • Restore only SELECT rights with:

    GRANT SELECT ON schema_name.* FROM user;

  • Remove the user from groups if necessary with:

    REVOKE group_name FROM user;

Feedback