When you run a PostgreSQL database in production, you rarely want every application and every developer to connect as the postgres superuser. A clean setup gives each service its own login role with a scoped set of privileges, so a bug or a leaked password cannot touch unrelated data.

PostgreSQL handles this with roles. A role can represent a single user, a group, or both at the same time, and privileges are granted to roles rather than to raw login accounts. This guide explains how to create roles, set passwords, grant and revoke privileges, and clean up roles you no longer need.

Roles vs Users in PostgreSQL #

Historically, PostgreSQL had separate CREATE USER and CREATE GROUP statements. Modern versions replaced both with a single concept: the role. A role with the LOGIN attribute can connect to the server, which makes it a user. A role without LOGIN is typically used as a group that other roles inherit privileges from.

In practice, CREATE USER is still valid and is treated as a shortcut for CREATE ROLE ... LOGIN. We will use both forms in this guide.

Connecting to PostgreSQL #

All the commands below run inside the psql shell. On most systems you can open it as the postgres system user:

sudo -u postgres psql

You will see a prompt like this:

postgres=#

Every SQL statement ends with a semicolon. If you forget it, psql keeps waiting for more input.

Creating a Role #

The simplest form of CREATE ROLE takes just a name:

CREATE ROLE linuxize;

This role exists but cannot log in yet and has no password.

To create a login role with a password, use LOGIN:

CREATE ROLE linuxize_login WITH LOGIN PASSWORD 'strong_password_here';

The equivalent shortcut is:

CREATE USER linuxize_user WITH PASSWORD 'strong_password_here';

Both statements produce the same result. Use whichever form reads more clearly in your scripts.

Useful Role Attributes #

You can combine several attributes in a single CREATE ROLE statement. These are the ones you will reach for most often:

• LOGIN - The role can connect to the server.
• PASSWORD 'secret' - Sets the login password.
• SUPERUSER - Grants full access, equivalent to the postgres role. Use sparingly.
• CREATEDB - The role may create new databases.
• CREATEROLE - The role may create and modify other roles.
• INHERIT - The role automatically inherits privileges of roles it is a member of. This is the default.
• VALID UNTIL 'timestamp' - Expires the password at the given time.
• CONNECTION LIMIT n - Caps the number of concurrent connections for this role.

For example, to create a login role that can also create databases and is limited to ten concurrent connections:

CREATE ROLE app_owner WITH LOGIN PASSWORD 'strong_password_here' CREATEDB CONNECTION LIMIT 10;

Listing Existing Roles #

To see every role on the server, use the \du meta-command:

\du

 List of roles
Role name | Attributes
-----------+------------------------------------------------------------
app_owner | Create DB, 10 connections
linuxize |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS

The Attributes column tells you what each role can do. A blank column means the role has no special attributes beyond the defaults, such as NOLOGIN and INHERIT.

Changing a Role #

Use ALTER ROLE to change attributes, rename a role, or reset a password. To change the password:

ALTER ROLE linuxize WITH PASSWORD 'new_password_here';

To grant an additional attribute:

ALTER ROLE linuxize CREATEDB;

To remove one, prefix it with NO:

ALTER ROLE linuxize NOCREATEDB;

Creating a Database for the Role #

It is common to give each application its own database owned by its login role. Create the database and assign ownership in one statement:

CREATE DATABASE linuxize_app OWNER linuxize;

The owner of a database has full control over it, so the role can create tables, schemas, and other objects without any further grants.

Granting Privileges #

Privileges in PostgreSQL are granted at several levels: database, schema, table, column, sequence, and function. The syntax is consistent across levels:

GRANT privilege_list ON object_type object_name TO role_name;

Database-level Privileges #

To let a role connect to a database and create objects in it:

GRANT CONNECT ON DATABASE linuxize_app TO linuxize;
GRANT CREATE ON DATABASE linuxize_app TO linuxize;

CONNECT controls whether the role can open a session to the database. CREATE controls whether it can create schemas.

Schema-level Privileges #

To let a role create and use objects inside a schema:

GRANT USAGE ON SCHEMA public TO linuxize;
GRANT CREATE ON SCHEMA public TO linuxize;

USAGE is required for almost every operation inside the schema. CREATE lets the role add new tables, views, or functions.

Table-level Privileges #

Table privileges match the common SQL operations:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO linuxize;

To grant every available table privilege at once:

GRANT ALL PRIVILEGES ON TABLE orders TO linuxize;

To grant the same privileges on every existing table in a schema:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO linuxize;

This only affects tables that exist at the moment you run the command. Tables created later are not covered.

Default Privileges for Future Objects #

To cover tables created in the future, set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO linuxize;

From this point on, any new table created in public by the current role inherits the listed privileges for linuxize.

Revoking Privileges #

REVOKE is the mirror of GRANT and uses the same structure:

REVOKE INSERT, UPDATE, DELETE ON TABLE orders FROM linuxize;

To strip every privilege on a table:

REVOKE ALL PRIVILEGES ON TABLE orders FROM linuxize;

Revoking a privilege only affects the privileges you previously granted. Ownership is a separate concept: the owner of an object always keeps full control over it, regardless of grants.

Group Roles #

To manage privileges for a team, create a role without LOGIN and add members to it:

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE linuxize_app TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

GRANT readonly TO linuxize;

linuxize now inherits every privilege granted to readonly. To remove the membership later:

REVOKE readonly FROM linuxize;

This pattern scales better than granting privileges role by role, because you change permissions in one place.

Deleting a Role #

To drop a role, use DROP ROLE:

DROP ROLE linuxize;

PostgreSQL refuses the command if the role still owns any objects or holds any privileges. To clean these up first, reassign the objects and drop dependent privileges in each database where the role owns objects:

REASSIGN OWNED BY linuxize TO postgres;
DROP OWNED BY linuxize;
DROP ROLE linuxize;

REASSIGN OWNED transfers ownership of objects owned by the role in the current database, and DROP OWNED removes any remaining privileges there. If the role owns objects in other databases, repeat the cleanup in each one before dropping the role.

Quick Reference #

Troubleshooting #

ERROR: permission denied for schema public
Grant USAGE on the schema: GRANT USAGE ON SCHEMA public TO role_name;. On PostgreSQL 15 and later, the public schema is no longer writable by default, so you may also need GRANT CREATE ON SCHEMA public.

ERROR: role "name" cannot be dropped because some objects depend on it
Reassign and drop the role’s objects first: REASSIGN OWNED BY name TO postgres; DROP OWNED BY name;, then run DROP ROLE name; again.

FATAL: password authentication failed for user
Check that the role has LOGIN and that the authentication method in pg_hba.conf matches the client (for example, md5 or scram-sha-256). Reload the configuration with SELECT pg_reload_conf(); after editing pg_hba.conf.

New tables are not visible to a read-only user
Grants on ALL TABLES IN SCHEMA only cover tables that exist at grant time. Set ALTER DEFAULT PRIVILEGES for the owning role so new tables inherit the read permissions automatically.

FAQ #

What is the difference between CREATE USER and CREATE ROLE?
CREATE USER is a shortcut for CREATE ROLE ... LOGIN. Both create the same kind of object. Use CREATE USER when you want a login account and CREATE ROLE when you are creating a group role without login.

Can a single role be both a user and a group?
Yes. A role with LOGIN can still be granted to other roles. Members inherit its privileges as long as INHERIT is set, which is the default.

How do I change the owner of an existing database?
Use ALTER DATABASE db_name OWNER TO new_owner;. The new owner gains full control over the database and its objects.

Do I need to restart PostgreSQL after creating a role?
No. Role changes take effect immediately. Only changes to pg_hba.conf or the main server configuration require a reload or restart.

Conclusion #

Roles are the single unit of access control in PostgreSQL. Give each application its own login role, group shared privileges into group roles, and use ALTER DEFAULT PRIVILEGES so future objects stay consistent with your current policy.

For related reading, see how to check the PostgreSQL version and the installation guide for your distribution under the PostgreSQL tag .