Permissions for Objects in Postgres

PostgreSQL Permissions and Access Management

When you grant the USAGE permission on a schema in PostgreSQL, it allows the role to access the schema but does not give permissions on individual objects within the schema. Below is a detailed breakdown of the permissions that can be granted at the object level for tables, views, sequences, functions, procedures, and indexes. Additionally, it includes the corresponding commands for granting, revoking, altering permissions, and shortcuts to verify them.

---

1. Permissions for Tables

Permissions:

• SELECT: Read data from the table.
• INSERT: Add new rows to the table.
• UPDATE: Modify existing rows in the table.
• DELETE: Remove rows from the table.
• TRUNCATE: Remove all rows from the table.
• REFERENCES: Create foreign keys referencing the table.
• TRIGGER: Create triggers on the table.

Granting and Revoking:

-- Grant all permissions on a table
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable TO myrole;

-- Revoke specific permissions
REVOKE UPDATE ON TABLE mytable FROM myrole;

-- Grant all permissions
GRANT ALL ON TABLE mytable TO myrole;

-- Revoke all permissions
REVOKE ALL ON TABLE mytable FROM myrole;
    

Shortcut to Verify:

\z mytable

---

2. Permissions for Views

Permissions:

• SELECT: Query the view.
• INSERT, UPDATE, DELETE: Modify data via an updatable view.

Granting and Revoking:

-- Grant permissions on a view
GRANT SELECT ON VIEW myview TO myrole;

-- Revoke permissions on a view
REVOKE SELECT ON VIEW myview FROM myrole;
    

Shortcut to Verify:

\z myview

---

3. Permissions for Indexes

Indexes do not have explicit permissions. Permissions for indexes are inherited from the table they are attached to. To control access, manage the table's permissions.

---

4. Permissions for Sequences

Permissions:

• SELECT: Use the sequence’s currval and nextval functions.
• USAGE: Use the currval and nextval functions without altering the sequence.
• UPDATE: Change the sequence value with nextval.
• ALL: Grants all of the above.

Granting and Revoking:

-- Grant permissions on a sequence
GRANT USAGE, SELECT ON SEQUENCE mysequence TO myrole;

-- Revoke permissions on a sequence
REVOKE ALL ON SEQUENCE mysequence FROM myrole;
    

Shortcut to Verify:

\z mysequence

---

5. Permissions for Functions and Procedures

Permissions:

• EXECUTE: Execute the function or procedure.

Granting and Revoking:

-- Grant execute permissions
GRANT EXECUTE ON FUNCTION myfunction() TO myrole;

-- Revoke execute permissions
REVOKE EXECUTE ON FUNCTION myfunction() FROM myrole;

-- Grant permissions on a procedure
GRANT EXECUTE ON PROCEDURE myprocedure() TO myrole;

-- Revoke permissions on a procedure
REVOKE EXECUTE ON PROCEDURE myprocedure() FROM myrole;
    

Shortcut to Verify:

SELECT proname, proacl FROM pg_proc WHERE proname = 'myfunction';
    

---

6. Permissions for Other Objects

Foreign Tables:

GRANT SELECT ON FOREIGN TABLE mytable TO myrole;
REVOKE SELECT ON FOREIGN TABLE mytable FROM myrole;
    

Types:

GRANT USAGE ON TYPE mytype TO myrole;
REVOKE USAGE ON TYPE mytype FROM myrole;
    

Shortcut to Verify:

\z mytable

---

Altering Object Ownership

-- Change ownership of an object
ALTER TABLE mytable OWNER TO new_owner;
ALTER VIEW myview OWNER TO new_owner;
ALTER FUNCTION myfunction() OWNER TO new_owner;
ALTER PROCEDURE myprocedure() OWNER TO new_owner;
ALTER SEQUENCE mysequence OWNER TO new_owner;
    

Best Practices

• Use group roles for assigning permissions to multiple users at once.
• Grant minimal necessary permissions to maintain security.
• Periodically review permissions using \z or querying the pg_* catalogs.