When working with PostgreSQL on Ubuntu, it's important to distinguish between system users (managed via adduser) and database roles (managed via PostgreSQL commands). For read-only database access, we only need to create a PostgreSQL role - no Ubuntu system user required.

First, connect to PostgreSQL as a superuser (typically postgres):

sudo -u postgres psql

Execute these SQL commands to create the user and set a password:

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'secure_password123';
GRANT CONNECT ON DATABASE your_database TO readonly_user;

For granting SELECT privileges on all current tables in a schema:

GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

To ensure the read-only access applies to tables created in the future:

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly_user;

Test the new user's access:

psql -U readonly_user -d your_database -h 127.0.0.1
-- Try selecting from a table
SELECT * FROM sample_table LIMIT 1;
-- Attempt to modify data (should fail)
INSERT INTO sample_table VALUES (1);

For production environments, consider these additional measures:

1. Set appropriate pg_hba.conf entries to restrict connections
2. Implement connection limits for read-only users
3. Regularly audit permissions with \du command

If the user can't connect:

• Verify pg_hba.conf has proper authentication entries
• Check the user has CONNECT privilege on the database
• Ensure password authentication is properly configured

In PostgreSQL, creating a read-only user doesn't require creating a system user on Ubuntu. PostgreSQL manages its own roles (users) independently from the operating system users. A read-only user typically needs:

• CONNECT privilege to the database
• USAGE privilege on schemas
• SELECT privilege on tables/views

Here's how to create and configure a read-only user:

-- Connect to PostgreSQL as superuser
psql -U postgres

-- Create the read-only user
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'secure_password' NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- Grant connection privilege to database
GRANT CONNECT ON DATABASE your_database TO readonly_user;

-- Grant usage on all schemas
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Grant SELECT on all tables (current and future)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

-- Grant SELECT on all sequences if needed
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly_user;

To test if the permissions work correctly:

-- Connect as the new user
psql -U readonly_user -d your_database

-- Try a SELECT query
SELECT * FROM some_table LIMIT 1;

-- Attempt to modify data (should fail)
INSERT INTO some_table VALUES (1, 'test');
-- ERROR: permission denied for table some_table

For more granular control:

1. Schema-specific permissions: Restrict access to certain schemas only
2. Column-level security: Create views exposing only specific columns
3. Row-level security: Implement policies for data filtering

-- Example of column-level restriction
CREATE VIEW limited_view AS
SELECT id, name FROM sensitive_table;

GRANT SELECT ON limited_view TO readonly_user;

Always follow these best practices:

• Use strong passwords and consider rotating them periodically
• Regularly audit user privileges with \du and \dp commands
• Consider using certificate authentication for production environments
• Document all permission changes for audit purposes