How do you create a read-only user in PostgreSQL

Question

I d like to create a user in PostgreSQL that can only do SELECTs from a particular database  In MySQL the command would be   GRANT SELECT ON mydb   TO  xxx      IDENTIFIED BY  yyy     What is the equivalent command or series of commands in PostgreSQL   I tried     postgres   CREATE ROLE xxx LOGIN PASSWORD  yyy   postgres   GRANT SELECT ON DATABASE mydb TO xxx    But it appears that the only things you can grant on a database are CREATE  CONNECT  TEMPORARY  and TEMP

User · Answer

Reference taken from this blog   Script to Create Read-Only user   CREATE ROLE Read Only User WITH LOGIN PASSWORD  Test1234   NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL  infinity     Assign permission to this read only user   GRANT CONNECT ON DATABASE YourDatabaseName TO Read Only User  GRANT USAGE ON SCHEMA public TO Read Only User  GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read Only User  GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read Only User    Assign permissions to read all newly tables created in the future  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO Read Only User

User · Answer

I   ve created a convenient script for that  pg grant read to db sh  This script grants read-only privileges to a specified role on all tables  views and sequences in a database schema and sets them as default

User · Answer

Grant usage select to a single table  If you only grant CONNECT to a database  the user can connect but has no other privileges  You have to grant USAGE on namespaces  schemas  and SELECT on tables and views individually like so   GRANT CONNECT ON DATABASE mydb TO xxx  -- This assumes you re actually connected to mydb   GRANT USAGE ON SCHEMA public TO xxx  GRANT SELECT ON mytable TO xxx    Multiple tables views  PostgreSQL 9 0    In the latest versions of PostgreSQL  you can grant permissions on all tables views etc in the schema using a single command rather than having to type them one by one   GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx    This only affects tables that have already been created  More powerfully  you can automatically have default roles assigned to new objects in future   ALTER DEFAULT PRIVILEGES IN SCHEMA public    GRANT SELECT ON TABLES TO xxx    Note that by default this will only affect objects  tables  created by the user that issued this command  although it can also be set on any role that the issuing user is a member of  However  you don t pick up default privileges for all roles you re a member of when creating new objects    so there s still some faffing around  If you adopt the approach that a database has an owning role  and schema changes are performed as that owning role  then you should assign default privileges to that owning role  IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow   Multiple tables views  PostgreSQL versions before 9 0   To avoid errors in lengthy  multi-table changes  it is recommended to use the following  automatic  process to generate the required GRANT SELECT to each table view   SELECT  GRANT SELECT ON      relname      TO xxx   FROM pg class JOIN pg namespace ON pg namespace oid   pg class relnamespace WHERE nspname    public  AND relkind IN   r    v    S      This should output the relevant GRANT commands to GRANT SELECT on all tables  views  and sequences in public  for copy-n-paste love  Naturally  this will only be applied to tables that have already been created

User · Answer

Do note that PostgreSQL 9 0  today in beta testing  will have a simple way to do that   test  gt  GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser

User · Answer

Here is the best way I ve found to add read-only users  using PostgreSQL 9 0 or newer      sudo -upostgres psql postgres postgres   CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD   lt USE A NICE STRONG PASSWORD PLEASE   postgres   GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly    Then log in to all related machines  master   read-slave s  hot-standby s   etc    and run     echo  hostssl  lt PUT DBNAME HERE gt   lt PUT READONLY USERNAME HERE gt  0 0 0 0 0 md5    sudo tee -a  etc postgresql 9 2 main pg hba conf   sudo service postgresql reload

User · Answer

The not straightforward way of doing it would be granting select on each table of the database   postgres   grant select on db name table name to read only user    You could automate that by generating your grant statements from the database metadata

User · Answer

By default new users will have permission to create tables  If you are planning to create a read-only user  this is probably not what you want   To create a true read-only user with PostgreSQL 9 0   run the following steps     This will prevent default users from creating tables REVOKE CREATE ON SCHEMA public FROM public     If you want to grant a write user permission to create tables   note that superusers will always be able to create tables anyway GRANT CREATE ON SCHEMA public to writeuser     Now create the read-only user CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD  strongpassword   GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser    If your read-only user doesn t have permission to list tables  i e   d returns no results   it s probably because you don t have USAGE permissions for the schema  USAGE is a permission that allows users to actually use the permissions they have been assigned  What s the point of this  I m not sure  To fix     You can either grant USAGE to everyone GRANT USAGE ON SCHEMA public TO public     Or grant it just to your read only user GRANT USAGE ON SCHEMA public TO readonlyuser

User · Answer

If your database is in the public schema  it is easy  this assumes you have already created the readonlyuser   db  gt  GRANT SELECT ON ALL TABLES IN SCHEMA public to readonlyuser  GRANT db  gt  GRANT CONNECT ON DATABASE mydatabase to readonlyuser  GRANT db  gt  GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to readonlyuser  GRANT   If your database is using customschema  execute the above but add one more command   db  gt  ALTER USER readonlyuser SET search path customschema  public  ALTER ROLE

User · Answer

CREATE USER username SUPERUSER  password  userpass   ALTER USER username set default transaction read only   on

User · Answer

I read trough all the possible solutions  which are all fine  if you remember to connect to the database before you grant the things    Thanks anyway to all other solutions     user server    sudo su - postgres   create psql user   postgres server    createuser --interactive  Enter name of role to add  readonly Shall the new role be a superuser   y n  n Shall the new role be allowed to create databases   y n  n Shall the new role be allowed to create more new roles   y n  n   start psql cli and set a password for the created user   postgres server    psql psql  10 6  Ubuntu 10 6-0ubuntu0 18 04 1   server 9 5 14  Type  help  for help   postgres   alter user readonly with password  readonly   ALTER ROLE   connect to the target database   postgres    c target database  psql  10 6  Ubuntu 10 6-0ubuntu0 18 04 1   server 9 5 14  You are now connected to database  target database  as user  postgres     grant all the needed privileges   target database   GRANT CONNECT ON DATABASE target database TO readonly  GRANT  target database   GRANT USAGE ON SCHEMA public TO readonly   GRANT  target database   GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly   GRANT   alter default privileges for targets db public shema   target database   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly  ALTER DEFAULT PRIVILEGES

User · Answer

Taken from a link posted in response to despesz  link   Postgres 9 x appears to have the capability to do what is requested   See the Grant On Database Objects paragraph of   http   www postgresql org docs current interactive sql-grant html  Where it says   There is also an option to grant privileges on all objects of the same type within one or more schemas  This functionality is currently supported only for tables  sequences  and functions  but note that ALL TABLES is considered to include views and foreign tables     This page also discusses use of ROLEs and a PRIVILEGE called  ALL PRIVILEGES    Also present is information about how GRANT functionalities compare to SQL standards

[postgresql] How do you create a read-only user in PostgreSQL?

Examples related to postgresql