Postgresql – Replacing Postgres User

postgresqlSecurity

I'm new to postgres. It might sound a silly question, due to some security requirement I need to remove default superuser 'postgres' and replace it with any other super user (for example:abc) created by me.

To test this, I created a database user 'abc' and grated all superuser permission(like postgres user) to this user. However, when I'm trying to connect to postgresql prompt from my linux machine with below command:

    sudo su - abc -c "psql"

It wasn't working. I know there is a default database with postgres user 'postgres' in postgres and it's template database.
Postgres is open source and I believe, there should be a possibility to accomplish what I want to. Anyone could please let me how can I make it happen?

Best Answer

It might sound a silly question,

I think you have a security requirement written by someone who doesn't know what he is doing, and then implemented by someone else who doesn't know what he is doing. "Silly" is far too benign a word to describe this. I hope this is a toy system completely firewalled off from the internet.

sudo su - abc -c "psql"

You said you created a database user named "abc", but now you are trying to do something as the operating system user named "abc", which probably does not exist, since you didn't describe creating it.

Try psql -U abc. It will probably get farther than your last effort, but still not work due to pg_hba.conf settings. Once you see the error message produced (look in the server log file for postgresql for the complete error message, the one returned to the client is intentionally vague to avoid leaking sensitive information to a presumed attacker), then you can decide what to do about it.

Related Solutions

PostgreSQL – How to Restore Superuser Account

Superuser rights aren't in any config file, they're part of the pg_catalog.pg_authid database table, which is shared between all databases in a PostgreSQL install.

You need to stop the PostgreSQL server, then restart it in single user mode, where it's always running as superuser.

There, you can ALTER USER myuser SUPERUSER to grant superuser rights. Exit single user mode, and start the server back up.

The exact details are a little distro and version dependent, but something like:

service postgresql stop
sudo -u postgres postgres --single /var/lib/pgsql/9.1/main/data

ALTER USER postgres SUPERUSER;

(control-D to exit)

service postgresql start

On some systems you might need to use an explicit path to the postgres executable, like /usr/pgsql-9.1/bin/postgres. Again, paths may vary based on version and distro.

Postgresql – Postgres DB user permission

You should log in as the table owner (the user you used to create the tables) and then grant the necessary privileges to your user:

grant select on acs_activities to your_user;

If you want to do that for all tables, you can use:

grant select on all tables in schema public to you_user;

Or maybe you created them as the superuser, but your regular user should be the real owner, then you should do:

alter table acs_activities owner to your_user;

Best Answer

Related Solutions

PostgreSQL – How to Restore Superuser Account

Postgresql – Postgres DB user permission

Related Question