Database Users, Roles, and Permissions

Database security determines who can connect to a database, which objects they can access, and what operations they can perform.

Consider an application with three kinds of access:
- Customers should access only the data exposed to them by the application.
- The backend service needs permission to read and modify specific application tables.
- Reporting staff may need read-only access to selected data.

Giving every account full database privileges would be dangerous. PostgreSQL provides roles and privileges so access can be separated by responsibility.

A **role** is a database identity that can own objects and receive privileges. A role may represent a person, an application service, or a group of permissions.

A **user** in PostgreSQL is simply a role that has the LOGIN attribute. PostgreSQL uses the same role system for both users and groups.

A **privilege** is permission to perform a specific operation, such as SELECT, INSERT, UPDATE, DELETE, or USAGE on a schema.

The goal is not to give everyone access to everything. The goal is to provide the minimum access required for each responsibility.

PostgreSQL uses CREATE ROLE to create roles. You can specify whether a role can log in.

A role without LOGIN is commonly used as a group or permission bundle. A role with LOGIN can authenticate as a database user when authentication is configured to allow it.

Example:

CREATE ROLE reporting_role;
CREATE ROLE reporting_user LOGIN;

The first role is a permission group. The second can be used as a login identity.

A role can be granted to another role, allowing the member to receive the granted role's permissions according to membership and inheritance settings.

Avoid using a superuser account for ordinary application requests. A superuser can bypass many security restrictions, so its credentials should be reserved for controlled administrative work.

**Important:** Creating a role does not automatically grant it access to every database, schema, table, or sequence. Access must be configured separately.

Use CREATE ROLE to define an account or permission group.

The LOGIN attribute allows a role to authenticate. A role created with NOLOGIN can be used as a reusable permission group.

The following commands create a backend service role and a read-only reporting role. Run them only in a development database where you have the necessary administrative privileges.

Do not place real production passwords directly in source code or commit them to a repository. In production, use a managed secrets system and your organization's approved credential-rotation process.

GRANT assigns privileges to a role. REVOKE removes previously granted privileges.

For example:

GRANT SELECT ON TABLE public.products TO reporting_role;

This grants the role permission to read the products table, assuming the role can also access the schema containing it.

To remove that table privilege:

REVOKE SELECT ON TABLE public.products FROM reporting_role;

Common table privileges include:
- SELECT: Read rows.
- INSERT: Add rows.
- UPDATE: Modify rows.
- DELETE: Remove rows.
- TRUNCATE: Empty a table.
- REFERENCES: Create foreign keys that reference the table.
- TRIGGER: Create triggers on the table.

Do not confuse privileges with ownership. The owner of an object has special authority over that object. Granting SELECT does not make the recipient the owner.

Also, REVOKE removes a particular grant; it does not necessarily guarantee that access is gone. A role may receive the same privilege through another role, PUBLIC, or another applicable grant path. Always examine effective access rather than relying on one REVOKE statement.

The following example creates a products table and grants different privileges to different roles.

The application service receives SELECT, INSERT, UPDATE, and DELETE. The reporting role receives only SELECT.

These examples are intentionally scoped to one table rather than granting unrestricted access to every table in the database.

PostgreSQL access is layered. A user may need permission to connect to a database, access a schema, and perform an operation on a table.

**Database CONNECT:** Allows a role to connect to the database, subject to authentication and other restrictions.

**Schema USAGE:** Allows a role to look up objects in the schema by name, subject to object permissions.

**Schema CREATE:** Allows a role to create objects in the schema.

**Table privileges:** Determine whether a role can read or modify a particular table.

Granting schema USAGE alone does not grant SELECT on the tables inside that schema. Likewise, granting SELECT on a table does not replace the need for the relevant schema access.

Example:

GRANT CONNECT ON DATABASE codenivo_db TO app_service;
GRANT USAGE ON SCHEMA public TO app_service;
GRANT SELECT ON TABLE public.products TO app_service;

Replace codenivo_db with your actual database name. Database-level grants must be executed while connected to a database where you have permission to manage the target database's privileges. Schema and table grants apply to the relevant objects in the database where those objects exist.

For a private application schema, consider separating application tables from unrelated objects rather than giving the application broad access to the default public schema.

The principle of least privilege means that each role receives only the permissions needed for its work.

For example:
- A reporting account may need SELECT but not INSERT, UPDATE, or DELETE.
- An application account may need to modify application data but should not be a superuser.
- A deployment account may need schema changes during releases but should not be used for routine application traffic.
- A monitoring account may need access to selected system statistics but not customer data.

A useful design is to create NOLOGIN roles that represent responsibilities and grant those roles to login identities.

For example:

CREATE ROLE product_reader NOLOGIN;
GRANT USAGE ON SCHEMA public TO product_reader;
GRANT SELECT ON TABLE public.products TO product_reader;

CREATE ROLE product_writer NOLOGIN;
GRANT USAGE ON SCHEMA public TO product_writer;
GRANT SELECT, INSERT, UPDATE
ON TABLE public.products
TO product_writer;

GRANT product_reader TO reporting_user;

This makes permissions easier to audit and reuse. Keep role membership intentional: granting a powerful role to a login user can give that user substantially more access than expected.

The example assumes the tables and roles exist and that the commands are run by a sufficiently privileged database administrator.

Use REVOKE when access is no longer required or a previous grant was too broad.

The example removes INSERT, UPDATE, and DELETE from the reporting role while retaining SELECT.

REVOKE INSERT, UPDATE, DELETE
ON TABLE public.products
FROM reporting_role;

You can also revoke schema privileges:

REVOKE CREATE ON SCHEMA public FROM app_service;

A REVOKE statement does not automatically remove all access a role might receive through other grants or role membership. PostgreSQL privileges may be granted directly, inherited from memberships, or granted to PUBLIC.

For this reason, review both direct grants and role memberships when investigating an unexpected permission.

Use RESTRICT or CASCADE carefully when revoking privileges that have been granted onward to other roles. CASCADE can remove dependent grants, so understand the impact before using it.

Role membership allows permissions to be grouped and reused.

For example:

CREATE ROLE inventory_reader NOLOGIN;
GRANT SELECT ON TABLE public.products TO inventory_reader;
GRANT inventory_reader TO reporting_user;

This grants the inventory_reader role to reporting_user. The effective access depends on membership options and PostgreSQL's role-inheritance rules.

PostgreSQL supports role attributes such as INHERIT and NOINHERIT, and role membership can have options controlling whether privileges are inherited or whether a member can SET ROLE. Exact behavior depends on the membership configuration and PostgreSQL version.

SET ROLE can switch the current session's effective role to a role of which the session user is a member, when permitted. It is not a way to become an arbitrary role.

Do not assume that membership means every privilege is always immediately available in every execution context. Inspect the role's attributes and membership options when debugging permission problems.

Keep membership chains short and understandable. A role hierarchy that is too complex makes security reviews and troubleshooting harder.

PostgreSQL distinguishes between the owner of an object and roles that receive privileges on it.

An owner normally has the authority to alter or drop the object and to manage its privileges, subject to PostgreSQL's rules. A role that receives SELECT can read the table but does not become its owner.

This distinction matters when deploying applications. If a runtime service owns every table, it may have far more authority than needed.

A common separation is:
- An owner or migration role creates and changes database objects.
- An application runtime role reads and writes only the data it needs.
- A reporting role receives read-only access to selected tables or views.

Changing ownership uses ALTER ... OWNER TO or related ownership commands, not GRANT.

For example:

ALTER TABLE public.products OWNER TO database_owner;

This requires appropriate authority, and database_owner must be an existing role. Review dependencies and deployment practices before changing ownership in a live system.

Sequence privileges are separate from table privileges.

Identity columns and SERIAL columns use sequences to generate values. An application role that can INSERT into a table may still encounter a permission error if the sequence permissions required by the insertion are missing.

For a table using a sequence, an administrator may need to grant the relevant sequence privileges:

GRANT USAGE ON SEQUENCE public.products_product_id_seq
TO app_service;

The exact sequence name depends on how the table was created. Use PostgreSQL catalog queries or your database client to find the actual sequence name rather than assuming it always follows this pattern.

Sequence privileges include USAGE, SELECT, and UPDATE. The required privilege depends on the sequence operation being performed.

For a group of existing sequences, PostgreSQL also supports:

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_service;

This applies to sequences that exist at the time the command runs. It does not automatically grant privileges on sequences created later. Default privileges can be configured for future objects, as explained in the next section.

Ordinary GRANT ON ALL TABLES IN SCHEMA statements affect objects that already exist. To configure privileges for objects created later, PostgreSQL provides ALTER DEFAULT PRIVILEGES.

For example:

ALTER DEFAULT PRIVILEGES
FOR ROLE database_owner
IN SCHEMA public
GRANT SELECT ON TABLES TO reporting_role;

This configures default SELECT privileges for eligible tables created in the specified schema by database_owner. The role name must exist, and the command must be executed by a role authorized to configure those defaults.

Default privileges are associated with the role that creates the objects. They are not a universal rule for every object created by every user.

To configure future sequence privileges:

ALTER DEFAULT PRIVILEGES
FOR ROLE database_owner
IN SCHEMA public
GRANT USAGE ON SEQUENCES TO app_service;

Use this deliberately. A future table may contain sensitive columns or data that a reporting role should not automatically see. Review default grants whenever schema ownership or deployment roles change.

Permission checks should be part of development and operational troubleshooting.

PostgreSQL provides helper functions such as:
- has_database_privilege(...)
- has_schema_privilege(...)
- has_table_privilege(...)
- has_sequence_privilege(...)

For example:

SELECT
has_schema_privilege(
'reporting_user',
'public',
'USAGE'
) AS can_access_schema,
has_table_privilege(
'reporting_user',
'public.products',
'SELECT'
) AS can_read_products;

These checks report whether the specified role has the requested privilege, taking applicable grants into account.

You can also inspect system catalogs such as pg_roles and information_schema views. Graphical database tools often display object privileges, but always verify the actual database state.

When debugging an access error, check:
1. Can the role connect to the database?
2. Does it have USAGE on the schema?
3. Does it have the required table or sequence privilege?
4. Does it receive privileges through role membership?
5. Is the object owned by a different role?
6. Is row-level security or another security mechanism restricting the result?

A successful privilege check does not mean every query will succeed. Constraints, row-level security policies, object dependencies, and other conditions may still affect execution.

PostgreSQL commonly reports errors such as permission denied for schema, permission denied for table, or permission denied for sequence.

These messages identify different layers of access control.

**Permission denied for schema:** The role may lack USAGE or CREATE, depending on the operation.

**Permission denied for table:** The role may lack the required table privilege, such as SELECT or INSERT.

**Permission denied for sequence:** The role may lack the sequence privilege required by an identity or sequence-backed operation.

**Cannot connect to database:** Check authentication, database CONNECT privileges, connection settings, and server access configuration.

Do not solve every permission error by granting ALL PRIVILEGES. First identify the specific missing privilege and grant only that permission to the intended role.

If the error occurs only for some rows or operations, also check row-level security policies, constraints, triggers, and the current effective role.

Use these guidelines when designing access control for an application.

**Separate runtime and migration accounts:** Runtime code usually should not be able to change table definitions or create arbitrary database objects.

**Avoid superuser access:** Superuser credentials should not be used by ordinary application processes.

**Prefer group roles:** Put reusable permissions on NOLOGIN roles and grant those roles to approved login identities.

**Use schema isolation:** Keep application objects in clearly defined schemas and avoid unnecessary CREATE privileges in shared schemas.

**Review PUBLIC privileges:** PUBLIC represents all roles. Check whether public grants are appropriate for your environment.

**Protect credentials:** Use secure secret storage, avoid hardcoding passwords, and rotate credentials according to policy.

**Audit membership changes:** Role membership can grant significant indirect access.

**Test with the actual runtime role:** A query that succeeds as the database owner may fail as the application account. Test using the least-privileged role used in production.

**Consider row-level security:** If users must access only their own records, table-level grants alone may be insufficient. PostgreSQL row-level security policies can restrict which rows are visible or modifiable, when enabled and configured correctly.

Security is a combination of authentication, privileges, ownership, schema design, application behavior, and operational controls.

Complete the following exercises in a PostgreSQL development database. Use test roles and tables; do not change production permissions.

**Exercise 1 — Create roles:** Create a NOLOGIN role named sales_reader and a LOGIN role named sales_analyst. Grant sales_reader to sales_analyst.

**Exercise 2 — Read-only access:** Create a sales table. Grant sales_reader USAGE on the containing schema and SELECT on the table. Verify access using has_table_privilege.

**Exercise 3 — Least privilege:** Confirm that sales_reader cannot INSERT, UPDATE, or DELETE rows unless it receives those privileges through another grant.

**Exercise 4 — Revoke access:** Revoke SELECT from sales_reader and inspect the result. Consider whether sales_analyst still has access through another role or direct grant.

**Exercise 5 — Sequence access:** Create a table with an identity column and investigate the permissions required for inserts under a restricted application role.

**Exercise 6 — Default privileges:** Configure default SELECT privileges for a reporting role on tables created by a specific owner. Create a new test table as that owner and verify the resulting access.

**Exercise 7 — Debugging:** Given a permission denied for schema error, determine which schema privilege is missing before changing any table privileges.

For every exercise, explain what each grant permits, what each revoke removes, and whether any other grant path could still provide access.

**Question 1:** In PostgreSQL, what is a user?

A. A role with the LOGIN attribute
B. A table owner only
C. A schema
D. A database extension

**Answer:** A. A PostgreSQL user is a role with LOGIN.

**Question 2:** Which statement grants a role permission to read a table?

A. ALLOW READ
B. GRANT SELECT
C. CREATE USER
D. ALTER TABLE ONLY

**Answer:** B. GRANT SELECT gives read permission on the specified table.

**Question 3:** Does schema USAGE by itself grant permission to read a table?

A. Yes
B. No

**Answer:** B. Schema USAGE allows access to objects by name, but the required table privilege must also be available.

**Question 4:** What does REVOKE do?

A. Creates a role
B. Removes a specified grant
C. Changes a table's owner automatically
D. Drops the database

**Answer:** B. REVOKE removes a specified privilege grant, but other grant paths may still provide access.

**Question 5:** Why should an application runtime role usually not be a superuser?

A. Superusers cannot execute SELECT
B. Superusers cannot connect remotely
C. Superusers have excessive authority for routine application work
D. Superusers cannot own tables

**Answer:** C. Excessive privileges increase the potential impact of a bug or compromise.

**Question 6:** Does GRANT ON ALL TABLES IN SCHEMA apply automatically to tables created in the future?

A. Yes, always
B. No

**Answer:** B. It affects existing tables. Use ALTER DEFAULT PRIVILEGES to configure grants for future objects.

**Question 7:** Are sequence privileges always included when table privileges are granted?

A. Yes
B. No

**Answer:** B. Sequence privileges are separate and may need to be granted explicitly.

**Question 8:** What is the principle of least privilege?

A. Give every user the same access
B. Give users only the access required for their responsibilities
C. Always grant ALL PRIVILEGES
D. Avoid using roles

**Answer:** B. Least privilege limits each role's access to what it needs.

**Q1. What is a role in PostgreSQL?**

A role is a database identity that can own objects, receive privileges, and participate in role membership. It may represent a login user or a permission group.

**Q2. What is the difference between a user and a role?**

In PostgreSQL, a user is a role with the LOGIN attribute. Roles without LOGIN are commonly used to group permissions.

**Q3. What are GRANT and REVOKE?**

GRANT assigns specified privileges to a role. REVOKE removes specified grants, although access may remain through other grant paths.

**Q4. What is the principle of least privilege?**

It means granting only the permissions necessary for a role to perform its intended job. It reduces the potential damage from mistakes or compromised credentials.

**Q5. What is the difference between schema USAGE and table SELECT?**

USAGE permits access to objects in a schema by name. SELECT allows reading rows from a table. Both may be needed for a query.

**Q6. Why can an INSERT fail because of a sequence permission?**

A table's identity or sequence-backed column may use a sequence to generate a value. The role may need sequence privileges in addition to the table's INSERT privilege.

**Q7. What is the difference between object ownership and granted privileges?**

An owner has special authority over an object, including the ability to manage it and its privileges. A role granted SELECT can read the object but does not become its owner.

**Q8. How do you grant privileges on future tables?**

Use ALTER DEFAULT PRIVILEGES for the role that will create the objects. Default privileges depend on the object-creating role and do not automatically apply to every future object in every schema.

**Q9. Why use NOLOGIN roles?**

They provide reusable permission groups. Multiple approved login users can receive the same role instead of duplicating every grant.

**Q10. Does revoking SELECT always remove a user's ability to read a table?**

No. The user may still receive SELECT through another role, a direct grant, or PUBLIC. Review all applicable grant paths.

**Q11. What is row-level security?**

Row-level security is a PostgreSQL feature that can restrict which rows a role may read or modify, based on configured policies. It complements ordinary table privileges rather than replacing them.

**Q12. Why should application and migration roles be separated?**

The application usually needs routine data access, while migrations may need to create or alter database objects. Separation reduces the authority available to the running application.

Database security in PostgreSQL is managed through roles, memberships, ownership, and privileges.

Remember:
- A PostgreSQL user is a role with LOGIN.
- GRANT assigns privileges; REVOKE removes specified grants.
- Database CONNECT, schema USAGE, table privileges, and sequence privileges control different operations.
- Role membership lets you reuse permission groups.
- Ownership grants special authority and is not the same as receiving SELECT or INSERT.
- ALTER DEFAULT PRIVILEGES configures grants for eligible objects created in the future by a specified role.
- Least privilege, secure credential handling, and separate runtime and migration roles improve security.
- Effective access can come from multiple grant paths, so audit the complete role and privilege configuration.

You should now be able to create roles, grant read-only or read-write access, revoke privileges, troubleshoot common permission errors, and explain how PostgreSQL access control supports secure application development.